AppSheet reference action not working

[asea_tasew]

I have a Parent and Child table in my app. One column in the parent table is computed by summing a specific column of its child rows. To achieve this, I created a reference action in the child table that is triggered when the Child table form is saved. This action, in turn, calls an action in the parent table to update the relevant column. This works well. However, an issue arises when a child row changes its parent. In this case, only the new parent's column gets updated, but both the old and new parent's columns should be updated since the sum for the old parent is also affected. To address this, I saved the old [parentID] value of the child row in a separate column [oldParentID] in the child table. I set the initial property value of this column to [_THISROW_BEFORE].[parentID] and  reset to the initial value on edit property to [_THISROW_BEFORE].[parentID] <> [parentID]. This is making [oldParentID] accurately reflect the previous value of [parentID] in the Google Sheet. I then updated the form saved event action to include both [parentID] and [oldParentID] in its reference list using List([parentID], [oldParentID]). This should trigger the parent action for both the current and the previous parent. Despite this, the old parent's column is still not updating.

What am I missing?

@Suvrutt_Gurjar

 @AleksiAlkio

@Joseph_Seddik

 

[Joseph_Seddik]

I tend to believe you've got the relationship between the two tables the other way round. I think we should first look into whether you've got the data modelling right. 

[asea_tasew]

Thank you for your response. My data model includes Items (for sale), Orders, and Line Items (order items). The issue arises when a Line Item changes its referenced item. When LineItem1 refers to Item1, I can successfully calculate the remaining quantity of Item1. If LineItem1 is changed to refer to Item2, I can also calculate the remaining stock of Item2 correctly. However, I need to recalculate and restore the remaining quantity of Item1 when it is no longer referenced, which is not working properly.

[Joseph_Seddik]

Thanks. For what reason you need to change the reference of LineItem1? This is not a habitual practice. What workflow are you trying to implement? What is the purpose of this change?

[asea_tasew]

Okay, let me explain. Suppose I have an order from Mr. X for one Orange and one Banana. In the backend, I will create one row in the Orders table with the following columns: {orderId, partnerId, orderType, date, total}, where partnerId references the Partners table. Additionally, I will create two rows in the LineItems table with columns: {lineItemId, orderId, itemId, quantity, price, subtotal}, where orderId references the Orders table and itemId references the Items table. When Mr. X changes the Banana order to an Apple, I need to update the itemId reference. In this case, I also need to adjust the remaining quantities: reduce the quantity for Apples and increase the quantity for Bananas.

[asea_tasew]

Guys, please help. I've been stuck here for a while and can't use bots for user experience reasons.

@Joseph_Seddik @Suvrutt_Gurjar @AleksiAlkio @Steve @Marc_Dillon @dbaum 

[asea_tasew · Accepted solution]

Changing [oldParentId] from type text to type ref fixed the issue. This seems to be a bug in AppSheet because it creates unnecessary double reference to the parent in the child and redundant lists of related childs in the parent, leading to unnecessary computational overhead for AppSheet to maintain these references and lists.

@Joseph_Seddik @Suvrutt_Gurjar @AleksiAlkio @Steve @Marc_Dillon @dbaum