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.

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… 

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(<br> field.row,<br> 'UnitPrice',<br> field.model.getFieldValue(field.row,'UnitPrice')<br>

We are all novices.  No worries. 

I was actually thinking you should look into this post: https://community.skuid.com/t/custom-field-renderer-simple-math?topic-reply-listisetti…

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.DOUBLEifield.mode](field, newvalue); 

Hope this helps. 

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!

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… 

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.

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!

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 = r‘Number_of_Customers__c’,‘Annual_Revenue__c’,'Number_of_Employees__c '];
var field = argumentsy0],
   value = argumentsr1],
   row = field.row,
   listener;

skuid.ui.fieldRenderers.DOUBLEfield.mode;

// 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. 

Hey Rob, no worries - I still was able to pick up quite a few things going down the other path (and writing the code for the calculation was half the bottle anyways).

For this new code, I’m not able to get a different value to display from the correct Opportunity Score (I made the score NumberOfEmployees - AnnualRevenue; it’s nonsensical but just a simpler way for me to test if the proper value is displaying and if it’s updating in real-time).

What I believe to be the source of the problem: your Opportunity_C and total variables. Where is total coming from? Is that what is updating the value in the new JavaScript?

field.model.updateRow(row,‘Opportunity_C’,total);
This is saying to update the current Row of the Opportunity_C with the total variable?

My field in SFDC is named Opportunity_Score__c, so I’m assuming that’s what you’re referring to with Opportunity_C. What is total though? Is that supposed to be newValue? To just reiterate for my own understanding, this line of code is how we replace the original Opportunity Score with this new, JavaScript version?

Another way to answer my questions might be solved if you’d be able to walk me through the final piece of the code. I’ll put my understanding in bold underneath

field.model.updateRow(row,‘Opportunity_Score__c’,newValue);
       field.element.empty();
       //clears Opportunity Score value
       skuid.ui.fieldRenderers.DOUBLE.readonly(field,newValue);
       //sets OppScore value to newValue
       
   }

   listener.handleChange = function(){calcNewValue (); };
   //runs calcNewValue if any of the listener fields are changed
   listener.render = function() {};
   //part of above?

   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 calculate the seconds
       if (field.model.changes�field.row.Id]) {
             calcNewValue ();
       }
   },100);
}

I guess I’m not really sure what this last part of code is doing and why my new OppScore field with a custom renderer isn’t updating live.

Thanks again!

Dylan.  I made some significant transposition errors in my first attempt.  I have doublechecked the code above and made an number of changes.   I think they will answer your questions.  (Or at least make is much more likely that the snippet works…) 

Thanks Rob! The snippet is certainly working, as I did a test and was able to witness live changes when editing the input variables. However, now I am having another issue, when I use the actual formula I’ve created within JavaScript, the actual Skuid page (the lead) where all this information is supposed to be found doesn’t even a load. I just receive a blank page. Obviously this has something to do with my JavaScript. I’m guessing it’s due to the complexity of the code.

The code is a bit lengthy so I’ll just post some of it (since it repeats for each variable).

Basically I’m starting from where you set the newValue variable and the formula is within that. I also added a few extra variables above that. I’m actually not using the NumberOfEmployees, NumberOfCustomers, and AnnualRevenue totals, but rather creating variables that first score them on a scale of 1-5. So for each one I’ve created another variable, for example: numEmployeesScore.

From there, I’m not setting newValue to anything immediately, but rather creating a lengthy if else statement:

if (!numCustomers && !numEmployees && !annualRevenue) {         
    newValue = null;
//This above is the only part I know is completely fine, as I tested it out independently of everything after
    } else { if (!numEmployees) {numEmployeesScore = 0;         
          } else if (numEmployees >= 10000) {
            divisor += 0.20;
            numEmployeesScore = 5;
          } else if (numEmployees >= 2500) {
            divisor += 0.20;
            numEmployeesScore = 4;
          } else if (numEmployees >= 400) {
            divisor += 0.20;
            numEmployeesScore = 3;
          } else if (numEmployees >= 75) {
            divisor += 0.20;
            numEmployeesScore = 2;
          } else if (numEmployees < 75) {
            divisor += 0.20;
            numEmployeesScore = 1;
          }

I repeat similar if else and else if statements for the other variables, and then do a final calculation:

newValue = (0.20 * numEmployeesScore + 0.45 * numCustomersScore + 0.35 * annualRevenueScore) / divisor;

Everything else in the code is the same. What could possibly be causing my entire Skuid Lead page to go blank (absolutely nothing is showing up, other than the SFDC menu and sidebar, just an empty white space in the middle)? I used this code previously when it wasn’t properly updating in real-time, and it WAS showing everything and giving me the proper Opportunity Score (just had to save and/or refresh the page to get it to change). So it must be something about combining the real-time update and this somewhat complex formula code?

Also, I really do appreciate you helping me thus far, along with your quick responses! I feel like I’m right on the verge of getting this to work, which will open up a ton of functionality for us in the future.

Thanks!
   

Blank Skuid pages are generally caused by Javascript errors. 

Are you using the browser developer tools and the console log?  This is where you will see error messages. 
If they are in your code you will generally be able to click through and see specifically where things are failing. 

You can also add   console.log (variableName)  statements into your code to see how variables are being calculated along the way.  

Its hard to debug just a single section of your code,  what you included is missing some closing braces at the end,  but I assume thats just what you copied.  Otherwize it looks ok. 

Thanks Rob! After a few days of messing around with it I got it working perfectly, this is fantastic!

This actually came at the perfect timing because we’re beginning to add some more complexity to our Skuid pages. 

One thing I’d like to try now is adding fields from a different column and/or section. For a basic pricing calculation (price1 + price2 + subtotal), where the subtotal field is from a different column, my new price total does not seem to update at all. If I move said subtotal to the current section, it will now calculate the final price but will not include the subtotal in its calculations.

The subtotal also happens to be a rollup field for a table below these sections - is that also a factor?

Additionally, on that note, is it possible to implement a live update on a rollup field?

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);

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?

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){<br>&nbsp; &nbsp;var mainModel = skuid.$M('MainModel');<br>&nbsp; &nbsp;var childrenModel = skuid.$M('ChildrenModel');<br>&nbsp; &nbsp;// PUT LOGIC HERE which updates your main Amount field<br>&nbsp; &nbsp;var updateAmountField = function(){<br>&nbsp; &nbsp; &nbsp; &nbsp;//<br>&nbsp; &nbsp;};<br>&nbsp; &nbsp;skuid.events.subscribe('row.updated',function(result){<br>&nbsp; &nbsp; &nbsp; &nbsp;if ((result.modelId===childrenModel.id) &amp;&amp; ('Amount__c' in result.updates)) {<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; updateAmountField();<br>&nbsp; &nbsp; &nbsp; &nbsp;}<br>&nbsp; &nbsp;});<br>&nbsp; &nbsp;skuid.events.subscribe('row.created',function(result){<br>&nbsp; &nbsp; &nbsp; &nbsp;if (result.modelId===childrenModel.id) {<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; updateAmountField();<br>&nbsp; &nbsp; &nbsp; &nbsp;}<br>&nbsp; &nbsp;});<br>&nbsp; &nbsp;<br>})(skuid);

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.

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);

   };

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 = argumentsf0],
     value = arguments 1],
     row = field.row,
     listener;

skuid.ui.fieldRenderers.DATETIMEfield.mode;

// 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);

}