Using Update Form to modify orders need recalculation

I have an order form which collects quantity and applicable discounts in a form then enters the results into a table in the database. My user is finding the need to make post order adjustments to the quantities, and sometime the discounts applied. I have a form to do this, however the totals are not recalculated as the data for the form is coming from the database table. The user must recalulate and enter the right amounts.

I can't seem to get my head around how to best work around this situation. Should I use an interim table or other component to present the current data and then allow the user to modify the quantities and discounts in a separate form which is then used to post to the database?

Sorry if this looks like I am "thinking out loud", but I guess I am...

Hey @edmartin!

Would you mind sharing exactly how you're calculating the totals and where that's currently happening in your app? It sounds like a temporary state could work nicely here but it might not be necessary!

Thanks for your time in the Community Office Hours. I am trying a simpler approach to make sure I can get the edits to update the database. I created a transformer and am able to get the right calculations, but I am not able to get the database table updated, either from direct edits to the table or using the Edit Order form.

Good to hear you were able to get things working up until that point! Are you seeing any error messages when you try uploading the data? Would you mind sharing a screenshot of your upload query?

No errors on load

:thinking: is the update query triggering the query that gets your table data on success? Would you mind otherwise posting a screenshot of the query result?

I am not quite sure if I am following. I modified the last row to 4 units and $88.00 ExtCost (manually) I did a preview of the query and got these results:

The first question was about whether you have an event handler on the update query like this one, that re-triggers the query which populates your table:

If you have a refresh trigger attached to the submit event of your form, for instance, that will run simultaneously with the update query which would be too early to read in the new data. Attaching it to the success event of the update is a good way to ensure it runs at the right time.

That preview is helpful for answering the second question, thanks! It looks as though things are good there.

Awesome advice! I noticed it didn't always run, now I know why!

I do have a question regarding the "recalculation" of the ext cost total. I have a transformer that caclulates the total based on the form components. The first four components are the ones that would likely change. I am returning the results of any of those changes.

let q = {{editQty.value}};
let p = {{editPrice.value}};
let v = {{editVolDisc.value}};
let a = {{editAddDisc.value}};

return {
'extCost':(p - (a + v)) * q,
'sPrice': p - (a + v)


Is it possible to trigger a transformer on the change of one or more of the four components? I see thgat I can trigger an event handler on change, but the only option that appears close to what I need it script.

Is this calculation something that can be done in temporary state and then used to update the two target components (SalePrice and ExtCost)

The transformer should automatically recalculate when any one of its inputs is recalculated! Scripts/JS queries tend to be useful when you want something to run less often but if you're looking to have this run automatically when an input changes you should already be set.

Is there any instance where the calculation should be happening and it isn't?

So far, it does not recalculate. If I change the quantity, the quantity will update, but the extended total does not change

When I update the component fields, the transformer preview shows the recalculated value, but the target components are not changing. I have the default value set to {{recalcTotals.value.sPrice}} . I figured it would correctly calculate the extended cost and salesPrice from the component fields being read from the table. I just can't seem to get the value to update the form from the transformer.

The transformer shows $22.00 but the form component tied to the transformer shows 24.25. I modified the Add discount to 2.25 to get the net price of 22

I have modified the update script to use the two returned values from the Transformer, now the totals will update from the database. I think this will be acceptablesince it runs the update after the commit as you pointed out.

:thinking: the fact the component isn't updating seems like a bug. I'll try and reproduce it on my end!

If you need to, you can also run your transformer as a JS query and then set it as a handler on the change event of each of your inputs. It sounds like you're good to go for now though?

It looks good for now, thanks for your help and support!