Update a formula field if lookup value changes on table row

I wonder if you could


(1) create a model for the Inventory_Item__c object

(2) add a conditional filter on the Id

(3) set and activate the filter

(4) query the model to get Price_Current__c

(5) then update the the table model with the Price_Current__c


Someone could probably articulate this better than me but this may work.

Hello Louis -

The good news is that what you are trying to accomplish is possible.  Unfortunately, depending on your situation, the solution could become rather complex.

A few thoughts…

1) If the Formula on your primary table is a straight output of a field from the lookup field itself with no additional logic applied, then instead of displaying the formula field from your primary table in the field editor, create a template field and reference the value from the lookup (e.g. Inventory_Item__r.Field__c).  If this is the case, you will need to make sure to include Inventory__r.Field__c in both your model field properties and in the search fields for the lookup field making sure to select the “return field value” option (this is the default behavior).

2) If your formula does something more than just displaying the raw value from the lookup field itself (eg. string concat, etc.), this is where it gets more complicated.  Skuid treats formula fields as read-only so even if you update your model programmatically, the field editor won’t re-render the formula field.  You can work around this by using the approach referenced at https://community.skuid.com/t/read-only-field-not-rendering-properly-after-update.  If you follow the steps there and then programmatically update the model (or using action framework), the formula field should re-render automatically after them model row is updated.  

3) Taking #2 a step further, if your formula field relies on other data in the record to derive it’s value, in order to accurately update the model with the new field value, you’ll need to make sure that all necessary fields to properly evaluate the formula are in your model fields and in any lookup field search fields marked to return the value.  

If I’m understanding what you are after, what this really comes down to is how complex the formula is that you are trying to manage client side.  There is no way for skuid to regenerate this formula real-time since it’s evaluated on the server by SFDC and all Skuid knows is the resulting value.  In order to keep the value in that field current, you need to update the model with the current value as other things in the model change.  In essence, you’re replicating the logic from your formula in Skuid/javascript.  

In our solution, in certain situations, we’ve replicated formulas and even rollups so that the client is always “current” as the data on the screen changes.  This isn’t something I’d encourage be done unless there is a very strong need for it - the reason for this is that depending on the complexity of the formulas and relationships, supporting and maintaining this code could become “costly” and it is prone to error.  That said, it can definitely be accomplished and if your situation is just a simple display of the property of the lookup field itself without any additional logic, then it’s fairly straightforward by using a template field instead.

Irvin, Barry - thank you for your responses.

I thought the template trick (adding in the required field as a search field to the lookup and displaying it as a template - neat!) might be the solution - it works but unfortunately I also need to summarise the results in the table which doesn’t seem to be an option with a template field.

Truthfully, I also wanted to do a bit of math on the result here in a second column so it seemed inevitable that I’d have to stumble down the javascript rabbit-hole.

I’m looking for live updates because I want users to be able to add line items to a transaction record and report costs to a customer before saving the transaction once the customer has agreed on the products and prices.

Barry - I’ll take a crack at your 2nd thought above and see how I get on.

Golly - I’m utterly stumped and needing rescue. Does anyone have an example I can use to get me going?

So - I’m looking to update a lookup field on a row in a table and have a custom-render (which can be column summarised) show a field from that related lookup - all prior to a save.

I’m also looking to have another column which does some basic math on that value.

Here’s the model:

1. The rows on the table are Inventory_Adjustment records which have a lookup to Inventory_Item.
2. The item record has a price field which I want to show on the table row and update when the lookup value is updated. I would use a template field except I need to be able to summarise this data which can’t be done with a template.
3. The adjustment records have a quantity field and I’d like to be able to multiply the quantity of the adjustment with the price of the item to get total price.

Hello Louis -

I put together a very small sample, hopefully this will get your going.

A few things to note:

1. On page load, we make sure the formula fields are marked creatable/editable so that skuid automatically rerenders the field on update
2. We use the action framework to call a snippet (handleAccountChange) when the account field changes
3. We make sure that the fields we need for evaluation are in our model and in the search fields marked to return

To get the below to work, you’ll need to add a field called “TestFormula” to your Contact object.

In the sample I just do a string concat but of course you could do any type of operation including calculations like you need in your case.

<skuidpage unsavedchangeswarning="yes" showsidebar="true" showheader="true" tabtooverride="Contact">   <models>
      <model id="Contact" limit="1" query="true" createrowifnonefound="false" sobject="Contact">
         <fields>
            <field id="FirstName"/>
            <field id="LastName"/>
            <field id="CreatedDate"/>
            <field id="AccountId"/>
            <field id="Account.Name"/>
            <field id="Account.BillingCity"/>
            <field id="TestFormula__c"/>
            <field id="Account.BillingCountry"/>
         </fields>
         <conditions>
            <condition type="param" enclosevalueinquotes="true" operator="=" field="Id" value="id"/>
         </conditions>
         <actions>
            <action>
               <actions>
                  <action type="custom" snippet="handleAccountChange"/>
               </actions>
               <events>
                  <event>row.updated</event>
               </events>
               <fields>
                  <field>AccountId</field>
               </fields>
            </action>
         </actions>
      </model>
   </models>
   <components>
      <pagetitle model="Contact">
         <maintitle>
            <template>{{FirstName}} {{LastName}}</template>
         </maintitle>
         <subtitle>
            <template>{{Model.label}}</template>
         </subtitle>
         <actions>
            <action type="savecancel" window="self"/>
         </actions>
      </pagetitle>
      <basicfieldeditor showsavecancel="false" showheader="true" model="Contact" mode="read" buttonposition="" layout="">
         <columns>
            <column width="100%">
               <sections>
                  <section title="Basics" collapsible="no">
                     <fields>
                        <field id="FirstName"/>
                        <field id="LastName"/>
                        <field id="AccountId" valuehalign="" type="" optionsource="">
                           <searchfields>
                              <searchfield query="true" return="true" show="true" field="Name" operator="contains"/>
                              <searchfield query="true" return="true" show="true" field="BillingCity" operator="contains"/>
                              <searchfield query="true" return="true" show="true" field="BillingCountry" operator="contains"/>
                           </searchfields>
                        </field>
                        <field id="TestFormula__c" valuehalign="" type="">
                           <label>Formula Field</label>
                        </field>
                     </fields>
                  </section>
               </sections>
            </column>
         </columns>
      </basicfieldeditor>
   </components>
   <resources>
      <labels/>
      <css/>
      <javascript>
         <jsitem location="inline" name="snippets" cachelocation="false" url="">(function(skuid){
    // shortcut variable for jQuery
var $ = skuid.$;
// helper function that will update the formula field to creatable &amp;amp; editable
// so that skuid will automatically re-render on change
var makeFieldsEditable = function() {
    var field = skuid.$M('Contact').getField('TestFormula__c');
    $.extend(field, { createable: true, editable: true });
};
// snippet that is called from action framework when the account changes
// rebuild the formula value and update the formula field in the model
    skuid.snippet.registerSnippet('handleAccountChange', function(field, value) {
        // pass true for third parameter so that the value returned is not escaped
        var acctCity = field.model.getFieldValue(field.row, 'Account.BillingCity', true)
            , acctCountry = field.model.getFieldValue(field.row, 'Account.BillingCountry', true)
            , formulaValue = acctCity + ' ' + acctCountry;
            
        // update the formula field with the new value
        field.model.updateRow(field.row, 'TestFormula__c', formulaValue);
    });
// document.ready function that will fire when DOM has loaded
$(function(){
    // make sure our fields are marked creatable/editable
    makeFieldsEditable();
});
})(skuid);</jsitem>
      </javascript>
   </resources>
</skuidpage>

Barry - I just checked your test page and it’s working perfectly. I’ll update my own page tomorrow using this as a guide and will let you know how I get on.

Thanks so much for sharing the sample - champion indeed!

Glad to hear it worked Louis, good luck with the changes to the actual page, look forward to hearing good results!

Thank you very much Barry! 

Yeeeeaaahhhooooooo! Job’s a good’n Barry. Nailed it!

Thanks so much - this makes everything much more intuitive as the user can edit values and view calculations in realtime rather than having to save the model first and then edit/delete items, save again, etc.

Gee, I love me some Skuid.

I’m really liking the animated gif… 

Awesome news Louis, great work!