How to update a formula-based field if one of the input variable fields is edited

  • 1
  • Question
  • Updated 4 years ago
  • Answered
Hello,

I would like to accomplish two very similar things in different areas of my Skuid layout for my Leads. Both have to deal with updating a formula-based field if one of the input fields is edited in any way (the formula field should always be accurate, even without a save/refresh). I have a field titled Opportunity Score which is calculated based on three other variables: Number of Customers, Annual Revenue, and Number of Employees.

In the field-editor. If someone edits any of the above 3 fields, the Opportunity Score should adjust accordingly without having to save or refresh.

I also want to do the same thing for those fields in a pop-up window, but I assume the process would be the same.

Currently I've been using this previous thread:
https://community.skuidify.com/skuid/topics/update_of_opportunity_line_items

Using that I've been setting the Field Renderer to Custom and creating a Render Snippet on the # of Customers field which in the JavaScript code (I believe) is set to update the Opportunity Score field. I'm guessing my issue is that that is more geared towards tables?

I hope that properly explains the issue, but please let me know if you need further clarification.

Thanks!
Dylan
Photo of Dylan Rodgers

Dylan Rodgers

  • 748 Points 500 badge 2x thumb

Posted 4 years ago

  • 1
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,714 Points 20k badge 2x thumb
From what I understand the formula field values are calculated each time they are requested via SOQL. I think Skuid would have only one option if this is the case.

Pull the formula into the page so to speak in order to be able to evaluate it on any of the fields required. That would get tricky as it would require all the fields referenced in the formula. That could get real messy real fast. Models and fields could be required from all over the place.
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
By going down the Custom Render Snippet route - you are doing the right thing.  Essentially you need to replicate the formula calculations in the client,  rather than simply waiting for the server side formula recalculation.  This will let your calculations occur in real time. 

The Custom Renderer will work in a field editor as well as in the Table.   But of course since you will be using javascript - the trial and error process just gets more difficult.  Welcome to the dark side and make sure your syntax is all correct.... 
Photo of Dylan Rodgers

Dylan Rodgers

  • 748 Points 500 badge 2x thumb
Hi Rob,

Thanks for the quick reply.

I'd call myself a novice in JavaScript to be honest, and somewhere just below adequate as a programmer in general, just as a heads-up.

How different will my snippet be from this: https://gist.github.com/zachelrath/5276990

Would I essentially be replacing the fields from that snippet with the field that I wish to update (being the Opp Score), and then inserting the formula to calculate the score referencing the other variables? The formula I have isn't especially complex, but it's not simple either (each variable is weighted, but if the variable is missing it readjusts the overall weighting), so will the formula need to be re-written in JS?

And where would that formula be in the following? Or am I way off right now?

field.model.updateRow(
field.row,
'UnitPrice',
field.model.getFieldValue(field.row,'UnitPrice')
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
We are all novices.  No worries. 

I was actually thinking you should look into this post: https://community.skuidify.com/skuid/topics/custom_field_renderer_simple_math?topic-reply-list[setti...

The basic idea is as follows: 

Step 1: 
Bring a field into your field editor (the specific field does not matter because we are just using it as a placeholder)
Set its renderer to "Custom" and in the Snippet name type the Resource Name of the snippet you will write in the Javascript Resources section. 

Step 2: 
Start a Snippett in the resources section.  Location should be "In-Line (Snippet)"

The snippent brings with it two arguments,  0 is the field and 1 is the Value for the field you selected from your model (which we don't really care about since it is just a placeholder). 
But the Field argument has a row extension.  This can be traversed to bring in data from other fields that are on that row.  Code might look like this: 

var field = arguments[0];
var row = field.row;
var value1 = row.Number_of_Customers__c;
var value2 = row.Annual_Revenue__c;
var value3 = row.Number_of_Employees__c;

 Now that you have your field values you can do your calculation against them.
newvalue = (value1*.1)+(value2*.4)/value3;

And finally you can pass the calculated value back into the page: 

skuid.ui.fieldRenderers.DOUBLE[field.mode](field, newvalue); 

Hope this helps. 
(Edited)
Photo of Dylan Rodgers

Dylan Rodgers

  • 748 Points 500 badge 2x thumb
That really does help, and makes sense. Couple questions:

1) Just to clarify, my original function for Opportunity Score within salesforce no longer matters, correct? At least in terms of Skuid? The formula for this field in Skuid will determine the Opportunity Score entirely? Or will it still use the original field when being referenced in other areas?

2) My formula isn't as simple as the calculation above or in the other thread. It's a weighted formula (20% for Annual Revenue, for example) for starters. Next I'm not actually using the initial variables directly - I'm using an IF statement to determine what value they should take on from 1-5. For instance, Annual Revenue over 1,000,000,000 receives a 5, 500,000,000 receives a 4, and so on. And finally, the Score should be set to NULL if all values are blank (this is rare), and the weights need to be re-adjusted if one of the values is missing (if Revenue is missing I would divide the total by 0.80 as opposed to 1). Is this a case of me just having to figure out JavaScript or ask someone else in order to convert my formula?

Thank you!
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
I think your original function still matters,  but not necessarily on this page.   If you want to see the score on other pages - then having it stored on the server is essential.  This means you will need to match pretty carefully the function that is occuring server side (in the formula field) with the function that is occuring client side in the javascript. 

You may have a pretty complicated calculation,  but you should be able to replicate the functionliaty in Javascript.  It will certainly take a few steps,  but it should be doable.  The google search will be your friend... 
Photo of Dylan Rodgers

Dylan Rodgers

  • 748 Points 500 badge 2x thumb
I think I mostly got it figured out (with like you said, a few steps to convert) thanks to some help from a colleague.

Another issue I'm seeing in my test of the newvalue, it's not updating in real time. That was really the main reason for doing this - we don't want our sales team to have to save/refresh the lead page in order to see an updated Opportunity Score. Am I missing something?

Additionally, when they click a convert button to convert the lead, a pop-up window launches showing all of the above variables again along with the Opportunity Score. Is this process the same to change the Score in that window? And again having it be updated in real-time edits.
Photo of Dylan Rodgers

Dylan Rodgers

  • 748 Points 500 badge 2x thumb
So this is where I am at:

In order to compare I placed both the SFDC Opportunity Score formula field, along with this new custom snippet formula version next to each other.

The formula appears to be correct. When I change (or more importantly, leave blank) any value both Scores are the same (that's good).

However, for whatever strange reason, if I refresh the page after saving when at least one field is blank, the snippet version of the Opportunity Score actually lowers, whereas the old Score remains unchanged and correct.

After playing around with it, I notice that if I make it 0 instead of leaving it blank (which with a correct formula, should be treated equally), the score remains unchanged and is correct.

What would be causing an incorrect score to load if one of the fields is blank?

I imagine I'll figure that out eventually, it's just an issue of understanding the differences between undefined, null and 0 I assume. But the bigger issue is the reason why I've been wanting to create this custom snippet in the first place:

Is it actually possible to get the Opportunity Score to update in real-time? If I have to click Save/Refresh or go to the next page, that defeats the entire purpose of adding this new Opportunity Score formula within Skuid.

If you can address one issue over the other, can it be the latter? 

Thanks again!
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
First off - Appologies for sending you on a premature chase.  I knew you wanted real time update and thought the method I proposed would get it done, but I didn't test it.  And you are right it fails the requirement. 

Back to the books. 

Essentially what we need to do is create a listener in that custom renderer that is paying attention to the updates in your fields and recalculating when those changes are made.  

So...

1.  Step 1:  Put the Opportunity Score field on your field renderer.  (We can use the real formula field,  but we'll be overwriting it with javascript). 
Set the renderer type to custom and put a name there.  I used "RealTimeRenderer"

2. Create a resource of type "In-Line Snippet"  with the same name "RealTimeRenderer"

Here is sample code: 


var FIELDS_TO_LISTEN_ON = ['Number_of_Customers__c','Annual_Revenue__c','Number_of_Employees__c '];
var field = arguments[0],
   value = arguments[1],
   row = field.row,
   listener;

skuid.ui.fieldRenderers.DOUBLE[field.mode](field,value);

// Register a listener so that if any of FIELDS_TO_LISTEN_ON are updated,
// then we will update ourself as well.

if (!listener) {
   
   listener = new skuid.ui.Field(row,field.model,null,{register:false});

   var calcNewValue = function(){

       var value1 = row. Number_of_Customers__c || 0,
           value2 = row. Annual_Revenue__c || 0,
           value3 = row. Number_of_Employees__c || 0,        
        newValue = (value1*.1)+(value2*.4)/value3;
       
       field.model.updateRow(row,'Opportunity_score__c',newValue);

       field.element.empty();
       skuid.ui.fieldRenderers.DOUBLE.readonly(field,newValue);
       
   }

   listener.handleChange = function(){calcNewValue (); };
   listener.render = function() {};

   skuid.$.each(FIELDS_TO_LISTEN_ON,function(i,fieldToRegisterOn){
       field.model.registerField(listener,fieldToRegisterOn);
   });

   setTimeout(function(){
       // If there is already a changes row for this item,
       // then run calculation
       if (field.model.changes[field.row.Id]) {
             calcNewValue ();
       }
   },100);
}


Of course you will need to replace the NewValue calculation with the code you worked out.  But now the real time update should work. 

Also notice that we added some code in the value lines  (|| 0)  this means if no value is found - 0 will be passed into the javascript calculation.  I think this will fix the issues you were having with null values. 

Hopefully this gets you where you need to go. 
(Edited)
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
Yes, you could implement a "live updating rollup field" as long as all of the data rows that contributed to that rollup were in your page, e.g. in a separate Model. You could just go loop through all of the rows in that other Model, and perform the calculations in JavaScript, then dump the total into your rollup field, e.g. this snippet would loop over all records in a "RelatedItems" Model, and SUM the values in each row's Amount__c field:

var totalAmount = 0;
var relatedRecords = skuid.model.getModel('RelatedItems').getRows();
skuid.$.each(relatedRecords,function(i,row){
    if (row.Amount__c) totalAmount += row.Amount__c;
});

console.log('new total: ' + totalAmount);
Photo of Dylan Rodgers

Dylan Rodgers

  • 748 Points 500 badge 2x thumb
Okay that makes sense, and I see it working.

And to implement the update, would I essentially use the same process that Rob showed above? Listen on Amount__c and then register a listener to update the rollup field if Amount__c is updated?
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
Yeah slightly trickier though, because you would want it to update whenever any Amount__c field on any child record is updated, or if any new child records are created. So what I would do is to leverage the Skuid Events API to listen for new rows being created or the Amount__c field being updated on any child records, like this:

Add this to a new Inline JavaScript Resource:
(function(skuid){
   var mainModel = skuid.$M('MainModel');
   var childrenModel = skuid.$M('ChildrenModel');
   // PUT LOGIC HERE which updates your main Amount field
   var updateAmountField = function(){
       //
   };
   skuid.events.subscribe('row.updated',function(result){
       if ((result.modelId===childrenModel.id) && ('Amount__c' in result.updates)) {
            updateAmountField();
       }
   });
   skuid.events.subscribe('row.created',function(result){
       if (result.modelId===childrenModel.id) {
            updateAmountField();
       }
   });
   
})(skuid);


(Edited)
Photo of Dylan Rodgers

Dylan Rodgers

  • 748 Points 500 badge 2x thumb
I think I'm missing a step when it comes to actually updating the value, correct? 

Here's my code so far with my variable and field names.

(function(skuid){   var opportunityModel = skuid.$M('Opportunity');
   var domainModel = skuid.$M('Domains');

   var updateAmountField = function(){
    var totalAmount = 0;
var domainRecords = skuid.model.getModel('Domains').getRows();

skuid.$.each(domainRecords,function(i,row){
    if (row.Sales_Price__c) totalAmount += row.Sales_Price__c;
});

console.log('new total: ' + totalAmount);

//field.model.updateRow(row,'Reach_Price__c',totalAmount);

   };
   skuid.events.subscribe('row.updated',function(result){
       if ((result.modelId===domainModel.id) && ('Sales_Price__c' in result.updates)) {
            updateAmountField();
       }
   });
   skuid.events.subscribe('row.created',function(result){
       if (result.modelId===domainModel.id) {
            updateAmountField();
       }
   });
   skuid.events.subscribe('row.deleted',function(result){
       if (result.modelId===domainModel.id) {
            updateAmountField();
       }
   });
   
})(skuid);

Correct me if I'm wrong, but I believe I need to do something similar to the bold. 

My console.log is showing up correctly, but my Reach_Price__c is just showing up as a blank.

By the way, you guys are awesome helping me power through this with my limited JavaScript knowledge. Your responses have been so quick I feel you're here with me.
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,056 Points 20k badge 2x thumb
var updateAmountField = function(){
    // The row we want to update
    var opportunityRow = opportunityModel.getFirstRow();
    var totalAmount = 0;
    var domainRecords = skuid.model.getModel('Domains').getRows();

    skuid.$.each(domainRecords,function(i,row){
       if (row.Sales_Price__c) totalAmount += row.Sales_Price__c;
    });

    console.log('new total: ' + totalAmount);
    // Update the opportunity row
    opportunityModel.updateRow(opportunityRow,'Reach_Price__c',totalAmount);

   };
Photo of Elissa Bradley

Elissa Bradley

  • 1,672 Points 1k badge 2x thumb
Hi there, 

I'm trying to do something similar, but with a Date/Time field instead of a Number.  I've modified the code above, but when I edit my primary field - "Nurse Intake Time - Patient Time Zone" - I don't see a change in the formula field where this custom renderer is applied. 


var FIELDS_TO_LISTEN_ON = ['Nurse_Intake_Time_Patient_Time_Zone__c'];
var field = arguments[0],
     value = arguments[1],
     row = field.row,
     listener;

skuid.ui.fieldRenderers.DATETIME[field.mode](field,value);

// Register a listener so that if any of FIELDS_TO_LISTEN_ON are updated,
// then we will update ourself as well.

if (!listener) {
   
   listener = new skuid.ui.Field(row,field.model,null,{register:false});

   var calcNewValue = function(){

       var  value0 = row.Nurse_Intake_Time_Patient_Time_Zone__c || 0,
              value1 = skuid.time.parseSFDateTime(value0),
              value2 = row.Nurse_Assignment__r.UTC_Offset__c || 0,
              value3 = row.Account.tz__UTF_Offset__c || 0,        
        
        newValue0 = value1 + (value2/24 - value3/24);
        newValue=skuid.time.getSFDateTime(newValue0);
       
       field.model.updateRow(row,'Nurse_Intake_Time_Patient_Time_Zone__c',newValue);

       field.element.empty();
       skuid.ui.fieldRenderers.DATETIME.readonly(field,newValue);
       
   }
   
    listener.handleChange = function(){calcNewValue (); };
    listener.render = function() {};

   skuid.$.each(FIELDS_TO_LISTEN_ON,function(i,fieldToRegisterOn){
       field.model.registerField(listener,fieldToRegisterOn);
   });

   setTimeout(function(){
       // If there is already a changes row for this item,
       // then run calculation
       if (field.model.changes[field.row.Id]) {
             calcNewValue ();
       }
   },100);

}