Automatically entering a column value in a different table

[TedM]

I know I'm being dumb here but here goes anyway!

I have two tables:
1) Projects - that contains information for each site and its job number
2) Inspections - this uses a lookup based on the the job number to complete data from the Projects table and contains other inspection data.

When an inspection is completed I'd like the column in Projects [for that row] called "Last Inspection" to be automatically undated with TODAY().

I thought I could do this with an action but it seems not.  Pointers would be appreciated.

[Suvrutt_Gurjar]

Could you mention if the Inspections table is child table of the Projects table, meaning do the two tables have reference relationship?

Also does a project have many inspection recorda?

[TedM]

The two tables are currently not related. 

Each project could have many inspection records (rows) ...... 100's

[Suvrutt_Gurjar]

Based on understanding so far, please note below.

It sounds that the table relationship between Projects table and Inspections table is well suited for a referencing relationship. Any specific reasons you have not done so? In general, it sounds that you could do away with lookups by having referencing relationship and using dereference expressions. I would request you to evaluate it if it fits in your app design.

References between tables - AppSheet Help

Dereference expressions - AppSheet Help

Coming back to your current setup , you could try below steps:

1. You could add a reference action as an event action on Inspections form save. The reference action will initiate on the Inspections table and the referenced table would be the Projects table. The referenced rows expression would be something like SELECT (Projects[Key Column] , [Job Number]=[_THISROW].[Job Number]) This expression assumes that the link between Inspections and Projects table is [Job Number]

2. The referenced data change action on the Projects table can set the value of [Last Inspection] column to TODAY()

[TedM]

Hi Suvrutt

Thanks for your help above.  I'm not quite sure why I didn't use a ref relationship...... the app just developed that way!  But you are right, it is very suitable and I have now changed the app to reflect this with the [Job No] now being a Ref field.

Does this change the potential solution you posted above, as I have tried that and it isn't working - but maybe I need to put some more effort into it!

[Suvrutt_Gurjar]

The solution will not change. Though with referencing relationship established, you may want to verify that all the inspections are appearing as children records for the corresponding job number now. Also reference rows expressions in the action can be simply LIST([Ref Column]) instead of a SELECT().

Please note job number in itself may not be a very good field for the key, if job number is human generated field , sequential in nature and can be created by more than one users. You may wish to evaluate something like UNIQUEID() as a key and [Job Number] as label.

Of course, all above are just suggestions and please note those will involve some complexities , if the app is already having substantial data. So, please proceed only if you are confident of reestablishing necessary reference relationships. The community will not be able to solve any design issues because it does not know the entire configuration of the app.

Edit: For the second action to update [Last Inspection] , the expression could be something like 

INDEX(SORT( [Related Inspections][TimeStamp], TRUE) ,1)

Where [Related Inspections] is the reverse reference column in the Projects table. [Timestamp] is the datetime type column in the Inspections table that records when the inspection record was created.

[TedM]

Thank you for the update - I shall look into it further. 

There is also a UNIQUEID() key and all the work I am doing on the app at the moment is on a copy of the original in a test environment - I won't be changing the existing deployed app until I'm 100% confident in any changes!

[Suvrutt_Gurjar]

---

@TedM wrote:

- I won't be changing the existing deployed app until I'm 100% confident in any changes!

---

Great. That is a correct approach.