Email template trigger cannot retrieve parent record's before and after value

[kennysz]

Hi All,

I have a bot that listens for updates in a Products table. When an update is made to the product, then the email template is sent to the user. 

<<IF: ([_THISROW_BEFORE].[ProductNumber] <> [_THISROW_AFTER].[ProductNumber])>>
Product Number Before: <<[_THISROW_BEFORE].[ProductNumber]>> 
Product Number Updated: <<[_THISROW_AFTER].[ProductNumber]>>
<<EndIf>>
<<IF: ([_THISROW_BEFORE].[ProductNumber] = [_THISROW_AFTER].[ProductNumber])>>
Product Number: <<[ProductNumber]>>
<<EndIf>>
<<IF: ([_THISROW_BEFORE].[Quantity] <> [_THISROW_AFTER].[Quantity])>>
Quantity Before: <<[_THISROW_BEFORE].[Quantity]>>
Quantity Updated: <<[_THISROW_AFTER].[Quantity]>>
<<EndIf>>
<<IF: ([_THISROW_BEFORE].[Quantity] = [_THISROW_AFTER].[Quantity])>>
Quantity: <<[Quantity]>>
<<EndIf>>
Order Date Before: <<[_THISROW_BEFORE].[OrderDate]>>
Order Ship Date Updated: <<[_THISROW_AFTER].[OrderDate]>>
<<IF: ([_THISROW_BEFORE].[OrderDate] = [_THISROW_AFTER].[OrderDate])>>
Order Ship Date: <<[OrderDate]>>
<<EndIf>> 

Basically it will print the before and updated values if it's changed, otherwise it will just print the value. The product number and quantity expressions evaluate as expected but the OrderDate does not. . OrderDate is a virtual column on the Products table referencing the Order table.

The OrderDate is changed in the Order Detail and nested in the detail is the product detail where the product name and quantity are changed. I see in the AppSheet documentation that parent records don't have access to child record updates and vice versa.

How can I retrieve the OrderDate's before and after values from the parent record to the child record? I saw some posts like these that require a few actions/events to update parent records based on child record updates. Is that required here to properly retrieve the OrderDate from the Orders parent record to the Products child record? Any help is greatly appreciated.

[WillowMobileSys · Accepted solution]

This is probably a process order issue.  The Virtual Column re-calculation happening between or outside of the Parent/Child row updates. I am actually not certain of when a Child's virtual Column would re-calc in this scenario when a Parent is updated.

If you were to add the OrderDate column as a table column with an App Formula, rather than a Virtual Column, it should update when the other details are changed providing a BEFORE/AFTER set of values. 

It would mean that you can't rely on the automated update of the Date.  Anytime the Date is changed on the Parent, you would need to "touch" each child to force the Date to get updated.

I hope this helps!

[kennysz]

I added a table column in the Child table that references the OrderDate in the other table, but when I change the Date in the UI, the OrderDate is updated in the other table but not the table column in the child table. And so the email bot doesn't trigger.

Even when I use this:

<<[_THISROW_BEFORE].[RefToParent].[OrderDate]>>
<<[_THISROW_AFTER].[RefToParent].[OrderDate]>>

 The email bot doesn't trigger despite the fact that OrderDate has been updated in the Parent table.

So does this mean I can't rely on the automated update of referencing the column in the parent table or another table? I'd have to create an action that updates the Date in the child table, so that I can use the BEFORE/AFTER set of values? If I don't have access to BEFORE/AFTER values, would I need to create another table column to track the BEFORE/AFTER values?

Appreciate your help here!

[kennysz]

Got this to work using a normal table column. Thanks!