Modify values in the main table after changes in the referenced table

[LukeBLDR]

Hi,

hope you can help me out on this one - I'm developping a Proof of concept of an app that is to handle purchase order requests in our company. Before I ask how to solve the problem here is a couple of details that will help you in understanding what I'm dealing with:

1. The main table POR handles POR requests and lets you registet and modify these

2. Every POR has a shopping cart which is handled by the table Shop_crt 

3. Both POR and Shop_crt share a POR_key column which allows me to view the data in the shopping cart coresponding to a particual POR within the detailed view of the POR (a nested table with a two buttons view and edit generated autoamtically by the AppSheet platform

4. The POR table has a Column Total_POR_val which holds the sum of all individual entries in the shoppong cart that coresponds to the given POR

5. The POR view has a default EDIT button which allows to modify the POR entry and the related shoping cart items (via a nested table that leads to the coresponding rows in the Shop_crt table)

Now for the problem:

If I modify the POR entry via the main Edit button in the POR biew and then add  entries in the nested table that coresponds to the Shop_crt table the Total_POR_val gets updated due to the fact that it holds the formula: SUM(SELECT(Shop_crt[Item_total_prc],[POR_key]=[_THISROW].[ID])) and that is the desired outcome

But if I choose in the POR view to modify Shop_crt entries directly via the two View and Add buttons (that look rather like links than buttons and sit directly under the nested table) this does not later modify the value stored in the Total_POR_val of the POR table. 

The easiest way to do this would be to remove the View and Add links that sit under the the nested Shop_crt table in the detailed POR view and force the user to go over the Edit button that does the job but I cannot do this as the Detail view of the POR view cannot be modified (it is some sort of a tamplate)

So now the big question(s):

1. Can the UI be modfied so that I can get rid of the View / Add links and force the users to modify stuff via the Edit button in the detailed POR view ? (I'm on the Core plan and don't know if the higher plans can provide the difference as fas as UI modifications are concerned?)

2. How do I force the app to recalculate the Total_POR_val entry after the Shop_crt get modified via the Add View links that are present in the detailed POR view - there is a Save button in the automatically generated Shop_crt_form but I cannot add actions to it 😞

I know this is a long post but this thing is driving me nuts - I guess the answer is somewhere in the use of the actions but don't know how to configure that properly.

Thanks in advance for any thoughts and ideas on this

Cheers

[Marc_Dillon]

Your [Total_POR_val] column is apparently a real column. App formulas in real columns only re-evaluate when the record is edited. To force this column to re-evaluate, you'd have to trigger any edit to that record.

Or you can just add it as a Virtual Column instead. If you do that, you should change your expression to a more performant version of: SUM( SELECT( [Related...][item_total_prc] ) )

[LukeBLDR]

Hi Marc,

thhx for the swift reply. I followed your advised and created a virtual column which updates nicely the moment the nested Shop_crt entries are modified via the Add Edit, yet still this value shows on my app but not in the POR table, and the POR table is the one reports are going to be generated later on. Any idea how to force the Total_POR_val to take the recalculated value of the shoping cart now stored in the virtual column (I named it POR_Total_prc_VR)?

Have a good day

[Marc_Dillon]

That question must get asked like once a week. You should do some searches and some reading.