Recalculate When Data Changes

[DaveWillett]

Hi.

I'm having trouble recalculating rows when data changes. To explain.

Two tables:

"MobileEstimateCustomer"

"Ticksheet"

The app starts off with a form using "MobileEstimateCustomer", admin details etc. In that form there is a enum for the [Job Type]. The enum contains, "Insurer", "Customer", "Management".

If the user selects "Customer" a Show_if contains: [Job Type]="Customer" which prompts the use to add a Labour Rate. Once added there are calculations in several columns like so: SUM(SELECT(Ticksheet[Labour Total], [JobID] = [_THISROW].[JobID])).

This all works fine and there is a BOT later in the process which populates a template and emails a quote to a customer.

Here's where we get a bit sticky and problematic.

If the [Job Type] starts off as "Insurer" or "Management" the calculations are ignored, this is fine because that is what it supposed to do because [Job Type] is not "Customer".

But if the user changes from "Insurer" or "Management" To "Customer" the calculations are still not activated.

And I cannot work out how to make the calculations happen once that [Job Type] has changed.

I have looked at the quick solution from @MultiTech by using a [Update] + 1 but it does not work in my situation.

Can anyone help?

[Rafael_ANEIC-PY]

Hello @DaveWillett, are your calculations written as initial_value expressions? you could try moving them to app formulas instead.

Is the [Job Type] change made from within the form or from outside using an action?

[DaveWillett]

Hi @Rafael_ANEIC-PY 

The calculations are App Formula's and [Job Type] are changed within the form from Detail Edit.

[Rafael_ANEIC-PY]

I see, have you checked if those calculation columns have any show_if, edit_if, valid_if or anything that conflicts with data that is only entered when the [Job Type] starts off as "Insurer" or "Management"? by "ignored" you mean that when checking their values you get blank results?

[DaveWillett]

There are Show_Ifs throughout the Formula specs as below. The fields show if the [Job Type] starts out as "Customer", a calculation is done and output to the template.

But if the [Job Type] starts out as anything else but changed to "Customer" then the result on the template are not calculated:

This is the template:

[Rafael_ANEIC-PY]

I see, the problem are the templates then, I highly suggest you move the calculations done in those VC directly into your template, it will work a lot better, since AppSheet runs all template calculations on their servers and they don't need to be synced back into your app for it to work properly, unlike how it works with VC's.

You might even be able to get rid of those VC too, and that's always great for performance.

EDIT: I see you've added the code in your template to your reply, my advice would be to change all the total, subtotal, and amount columns values derived from virtual columns mentioned there for their respective complete expressions

[DaveWillett]

Thats interesting. So for example:

SUM(SELECT(Ticksheet[OTHER], [JobID] = [_THISROW].[JobID]))

Written on the template should be:

<<SUM(SELECT(Ticksheet[OTHER], [JobID] = [_THISROW].[JobID]))>>

And that would work?

Or would the expressions have top be written differently?

Thanks

[Rafael_ANEIC-PY]

The expressions would work in the exact same way, that's why it is recommended to test template expressions in virtual columns on the tables they reside first before placing them inside the template.

[DaveWillett]

I never knew that, that's very useful to know.

I'll see if it makes a difference, thanks Rafael, much appreciated.

[DaveWillett]

Just one more thing.

Table "Ticksheet" has a row calculation of ([RR]+[REP]+[PAI] this is the total Hrs for the job.

Table "MobileEstimateCustomer" pulls [Total Hrs] from multiple rows using the expression:

SUM(SELECT(Ticksheet[Total Hrs], [JobID] = [_THISROW].[JobID]))

What would the template expression be to pull the multiple rows tota from "Ticksheet"? I tried:

<<SUM(SELECT(Ticksheet([RR]+[REP]+[PAI]), [JobID] = [_THISROW].[JobID])))>>

Which doesn't work. Can a calculation be used in this way?

[Rafael_ANEIC-PY · Accepted solution]

It would be quite efficient if that total could be saved as an app formula of a physical column, or if you could add a ref_rows() expression or a enum base type ref column in order to allow the use of dereferences.

If we don't do any of that, then there's this option:

<<SUM(SELECT(Ticksheet[RR], [JobID] = [_THISROW].[JobID]))+
  SUM(SELECT(Ticksheet[REP],[JobID] = [_THISROW].[JobID]))+
  SUM(SELECT(Ticksheet[PAI],[JobID] = [_THISROW].[JobID]))>>

[DaveWillett]

I'll try this:

"<<SUM(SELECT(Ticksheet[RR], [JobID] = [_THISROW].[JobID]))+
  SUM(SELECT(Ticksheet[REP],[JobID] = [_THISROW].[JobID]))+
  SUM(SELECT(Ticksheet[PAI],[JobID] = [_THISROW].[JobID]))>>"

As I am now understanding why there is a change once the original enum changes from "Insurer" to "Customer". Basically if one row is tagged as "Insurer" the calculation is ignored, then if the enum selection is changed to "Customer" the calculation happens. so looking at the raw data in the csv table one row "Insurer" has not been calculated but the next row "Customer" has.

So hopefully :

"<<SUM(SELECT(Ticksheet[RR], [JobID] = [_THISROW].[JobID]))+
  SUM(SELECT(Ticksheet[REP],[JobID] = [_THISROW].[JobID]))+
  SUM(SELECT(Ticksheet[PAI],[JobID] = [_THISROW].[JobID]))>>"

Should look at all rows and produce the correct result.

[MultiTech]

Just chiming in here...  

• @Rafael_ANEIC-PY made a good point about using references to more-efficiently get your information

Anytime you're doing this:

• [JobID] = [_THISROW].[JobID]

You should really be using references to connect your data together; from here you can completely remove all this brute-force overhead from your system.

• In fact, in most instances, this reference connection is already there... you're just not utilizing it to it's best effect.

-----------------------------------------------------------------------------------------

For example:

• SUM(SELECT(Ticksheet[Total Hrs], [JobID] = [_THISROW].[JobID]))

would become

• SUM([Related Ticksheets][Total Hrs])

The impact on your system between these two formulas is HUGE... while they produce the same result.

• The first one, the brute-force SELECT(), is a hard re-query of your database;
• The second one, the list-dereference, simply grabs data that's already there.

If at all possible, you should seek to reduce your use of SELECT() formulas living inside a virtual column to zero.

• The same goes for app-formulas too