Trying to update a formula field of table row when a reference field is updated

  • 1
  • Problem
  • Updated 3 years ago
  • In Progress
I am trying to update a formula field Available_Quantity__c in table row when WarehouseReference__r(a field in order object) is updated. Available_Quantity__c is a formula field in Order object which is referencing a field from Repository_Product (Available_Qty__c).
I wrote below code referring code snippet from this community. But unfortunately its not working for me.
Please let me know whats the issue, or is there any other way i can do this without using code snippet by using conditions on models and actions.

(function(skuid){
var $ = skuid.$;

var makeFieldsEditable = function() {
var field = skuid.$M('Order').getField('Available_Quantity__c');
$.extend(field, { createable: true, editable: true });
};

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, 'RepositoryProduct__r.Available_Qty__c', true)
, formulaValue = acctCity;

field.model.updateRow(field.row, 'Available_Quantity__c', formulaValue);
});

$(function(){
// make sure our fields are marked creatable/editable
makeFieldsEditable();
});
})(skuid);
Photo of Ritz Gupta

Ritz Gupta

  • 320 Points 250 badge 2x thumb

Posted 3 years ago

  • 1
Photo of Ritz Gupta

Ritz Gupta

  • 320 Points 250 badge 2x thumb
Also, I have created an action on Order when a WarehouseReference is updated then run the JS snippet.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,704 Points 20k badge 2x thumb
So, you want to change the value of a reference field and get the new value of the formula field?

If so, the formula field value won't change until the record is saved.
Photo of Ritz Gupta

Ritz Gupta

  • 320 Points 250 badge 2x thumb
Yes, want to change the value of reference field and get the new value of formula field which is referencing some other object, before saving the record. i am new to SKUID, please let me know in what other condition it will work, or after saving the record what changes I should make to make the above code snippet to work, or any other SKUID condition/action I can use to make this working. I prefer to make this run without using code snippet. But any solution would do for me.Please suggest.

Thanks.
(Edited)
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,704 Points 20k badge 2x thumb
Every time a record a saved, all the values on the record are updated. So the formula field will update.

Now, if there is a formula field or roll up field or some other automatically updated field on an associated record, then you'll have to query the associated row upon saving the primary row.
Photo of Ritz Gupta

Ritz Gupta

  • 320 Points 250 badge 2x thumb
Can you please elaborate this. "then you'll have to query the associated row upon saving the primary row."
Photo of Ritz Gupta

Ritz Gupta

  • 320 Points 250 badge 2x thumb
Yes you are right, its working when I save the record. Still my requirement is updating the value before save.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,704 Points 20k badge 2x thumb
This is not possible unless you want to hack a solution into place. You'd have to create a custom field renderer that does some whacky things behind the scenes. Possible to be done, but not here in a post without a significant time commitment.

Can be done though.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,704 Points 20k badge 2x thumb
Also, if you're willing to wait, the new version will have a UI only formula field capability.
Photo of Ritz Gupta

Ritz Gupta

  • 320 Points 250 badge 2x thumb
i have one more query, When I update the reference field, then the other field which i want to update(i.e. Available quantity), it updates only when its a field from same reference field, If i access the field from some other reference field, it doesn't update available quantity after save. DO i need to change code snippet.
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
I assume you mean that only the fields on a single row are updated, and you want the field on all rows to be updated - regardless of where in the table the change is made. 

If this is true then substantial changes will need to be made in the snippet so that the "available quantity" field in each row is listening to any change in the updated field.   At this point what you start doing is creating a completely "UI only" field that replicates the roll up client side.  As Pat says, in the new release some of this capability will be availalbe to build declaratively. 
Photo of Ritz Gupta

Ritz Gupta

  • 320 Points 250 badge 2x thumb
@Rob, @Pat, My condition is: I want to update it on the same row. There is a formula field (Available Quantity), When I change the reference field (WarehouseReference__c), my formula field gets updated after save if the Available Quantity is a formula field from the same object like(WarehouseReference__r.AvailableQuantity). But if the Available Quantity formula field is reference from some other object (RepositoryProduct__r.Available_Qty__c) Then on changing the WarehouseReference__c on a row, its Available Quantity is not updating. Do I need to change anything in above code snippet. (RepositoryProduct__c has a reference field of WarehouseReference__c).
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,704 Points 20k badge 2x thumb
Your snippet will not work.

You'd have to recreate the formula in the snippet. What is the formula?
Photo of Ritz Gupta

Ritz Gupta

  • 320 Points 250 badge 2x thumb
I have the formula field, the formula field is RepositoryProduct__r.Available_Qty__c.
I referred this link, https://community.skuidify.com/skuid/..., it updates the formula field before save when reference field changes
Photo of Ritz Gupta

Ritz Gupta

  • 320 Points 250 badge 2x thumb
This issue is still not resolved! need some help
Photo of Ritz Gupta

Ritz Gupta

  • 320 Points 250 badge 2x thumb
The scenario is:-
There is a table (referencing model: Order). Order has a reference field WarehouseReference__c and Product__c, also it has a field Available_Quantity__c which is a formula field from some other object(RepositoryProduct.available_qty__c). Repository Product also has reference field of WarehouseReference__c and Product__c.

I have one action on the Order model --> when row in model updated, field(WarehouseReference__c) is updated then update the corresponding value of available quantity from RepositoryProduct object.

Please give some approach on how to write code snippet, are there any actions also required before running code snippet.

https://community.skuidify.com/skuid/..., I saw this post, looking for a similar functionality, but when i make reference field as custom field renderer, it displays nothing/column value is blank, as the code snippet is taking arguments[0], i thought snippet should get called from custom field renderer.
(Edited)
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
You have two options here.

1. What Pat said above.  Rebuild the formula in a javascript snippet.  This means you will have to sourece all your source fields for the formula to your page, and then build these in the client.  Here are a few links to posts and tutorials where this is described. 

https://community.skuidify.com/skuid/topics/custom_field_renderer_simple_math   Doing math in custom field renderer. 
http://help.skuidify.com/m/supercharge-your-ui/l/153540-highlighting-critical-data-using-templates-a...    Second half of the tutorial shows how to build custom component that brings data from multiple models together. 

2. You could also simply have the model save and requery on update of that field.  This might be a problematic user experience because every time the user made a change the table he was changing would update.  But you would get the server side formula recalcuation.  
Photo of Ritz Gupta

Ritz Gupta

  • 320 Points 250 badge 2x thumb
Thanks Rob!!! I am able to update formula field on table row, but when I click on save button, its doesn't update the formula field, on refresh of page it shows the old value. This issue is coming for formula field only, when i tried the same thing for some editable text field, this issue is not coming.

Please suggest.
(Edited)
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Are you sure that the fields that drive the formula are in fact being updated? 

I'd reccomend abandoning display of the formula field if you are reproducing the whole thing client side. 
Photo of Ritz Gupta

Ritz Gupta

  • 320 Points 250 badge 2x thumb
Hey Rob,

i am not updating the fields of the formula, its just that i am fetching the field from the reference table(Repository product) and displaying it when a reference field(WarehouseReference) is updated on a table row of order line item object.

Now, when i click on save button, I again want to do the same set of actions and display the new value of available quantity, I tried doing same set of actions but somehow I its not getting row ID and says
Uncaught TypeError: Cannot read property 'Id' of undefined.

As before saving the recording I had model action as 'row of model updated'. so it was getting the row Id and updating available quantity.
After saving I have action as 'model saved', and i think its not finding which row to update, how can I track the row/row Id that was updated before save and use the save row id to display on the corresponding table row.
Photo of Ritz Gupta

Ritz Gupta

  • 320 Points 250 badge 2x thumb
It's working, thanks!!!