How to compare two tables and update the 2nd

[gcor71]

I have two tables: LOG and TRACK_ACTIVITIES.
TRACK_ACTIVITIES is a lookup table with rows reflecting unique activity/track combinations.
When a new row is added to the LOG table it records an ACTIVITY_UID and TRACK_UID from the user.
I want to update a "DONE" column (to be added ) in the TRACK_ACTIVITIES table when the log records the relevant activity/track.

I would appreciate help to get this working.  Everything I have tried (Filter, Select, Lookup etc) has failed.

To achieve what I want I think I need to match the value added to LOG.ACTIVITY_UID to the existing value in TRACK_ACTIVITIES.ACTIVITY_UID and then match the value in LOG.TRACK_UID to TRACK_ACTIVITIES.TRACK_UID.  If both conditions are true this should locate the unique row (in TRACK_ACTIVITIES) that I want to update. 

I then want to update the TRACK_ACTIVITIES.DONE column (yet to be added!) to say TRUE.
I think I can trigger this update using an appropriate DO THIS action.

Thanks in advance.

[dbaum]

Quick Update System - How to update records in you... - Google Cloud Community  

[gcor71]

Thanks for the pointer. 

EDIT - ignore the rest of this entry...
Unless I'm misunderstanding your suggestion, it's not the act of updating that is my issue - it's creating action/s / formula/s that are able to perform the compare and update.

I have tried creating a concatenation column in both tables - and locating a match to update based on that.  But again, it's not working as I expect.

[gcor71]

@dbaum I've spent more time working through the quick update system and think I understand why you've suggested it - thank you!  Something isn't quite straight in my implementation - can you spot where I've gone wrong?

LOG table has:
- column [T_A_ref] calculated by formula CONCATENATE([TRACK_UID],[ACTIVITY_UID])
- column [UPDATE]

TRACK_ACTIVITIES table has:
- column [T_A_ref] with a unique track/activity ID concatenation per row
- column [UPDATE]

LOG[T_A_ref] is type 'ref' child to TRACK_ACTIVITIES[T_A_ref].

Under Behaviour I've defined these actions:
- LOG update (always there) - on LOG table, set values in some columns in this row, set UPDATE = [UPDATE]+1
- Ref Update | Update T_A Done from LOG - on LOG table, execute an action on a set of rows, reference table is TRACK_ACTIVITIES, referenced rows is =LIST([T_A_ref]), referenced action is 'update_done'
- update_done- on TRACK_ACTIVITIES table, set the values of some columns in this row, set DONE = TRUE.

In action, the LOG is updated as expected.
On the resulting LOG_detail view I have action buttons for LOG update (always there) and Ref Update | Update T_A Done from LOG as expected.
The problem is - clicking either action doesn't result in the relevant T_A_Done column being updated. (It worked once when I was putting it together, but not since).

It feels like I'm almost there...  suggestions welcome to debug this.
Guy

[dbaum]

With the caveat that I don't understand all the details of your data structure described in your various posts in this conversation, here are some typical pitfalls to check for in the implementation I infer you have created.

• Confirm that each value in any Ref type column verbatim matches a value from the referenced table's key column.
• Confirm that each value in a Referenced rows list for an Execute an action on a set of rows action verbatim matches a value from the referenced table's key column.

That said, it's unclear to me whether you need the [UPDATE] column and associated action. As noted in the linked tip, its purpose is to trigger recalculation of a row's app formulas--which is what I originally interpreted your need to be. That would apply if, for instance, the [DONE] column uses an app formula to determine whether all related rows in another table meet some criteria; in this case, you'd need the [UPDATE] column in the same table as the [DONE] column.

On the other hand, if all you need is an action to explicitly set the value of a column you can just do that directly. Create an action that sets that value and ensure the action is invoked when applicable--e.g., when a user selects the action or when a form is saved.

Finally, if you do indeed need a series of multiple actions to execute whatever you're trying to accomplish, be aware that you can combine those into a single composite action.