Value in Parent Table baed on Related Record - Only Working on Record Creation

[Andrew_Ford2]

Hello AppSheet Community,

I have a parent table ‘incidents’ that has a one:many relationship with the child table ‘actions.’ The incidents table has a status field. The actions table has a actType field. I want the status field to be “Closed” if any related actType field has the value “yes” and “Open” otherwise.

I use this formula for the status field in the incidents table:
IF(IN(“Yes”,[Related actions][actType]),“Closed”,“Open”)

If I create a closing action WHILE I’m creating a new incident this formula seems to work. However, if I create a new incident, save it, and then later add a closing action then the status column does not update.

• Andrew

[Marc_Dillon]

I’m guessing the [Status] column is a real column? As a real column, it will only update when the record it is in is updated. If you make it a Virtual Column, it will update upon every sync.

[Andrew_Ford2]

Yes, I have it as a real column. That makes sense. I think I do want to save that information as a real column too. Is there anyway to have a real column auto-update whenever I add a new action?

[Marc_Dillon]

There is. It is a bit more advanced (but not too much).

You simply need to trigger an edit (any edit) in a record in order to force all real column expressions to recalculate.

One way (of many), is to create a hidden column in the Incidents table, of DateTime type. Create an action (action A) that sets the value of this column to UTCNOW().

You need to run this action A upon making any edits to Action records, or creating new ones.

One way to do this is to create another action (action B) for the Actions table, of type “execute action on set of rows” and point it to your Incidents table, the parent Incident record (as a list), and action A.

Then you set action B as the Form-Saved Behavior for the actions_Form UX view.

[Andrew_Ford2]

Interesting. Thanks Marc_Dillon!