Update Status Column from one table in another table

[sam_buzzfree]

I have an app with three tables: Employees, Service Records, and Systems.

The Service Record table contains records from all Systems, and is related to the Systems table with a System ID column in each table. A new service record row will be created when data is entered in the service record form by the Service Technician in the field. In the form, there is an Enum dropdown to select "System Status" at the time of service, with several options, including "Winterized", "Running", and so on. My goal is to have the "System Status" column in the Systems table row for that system update (automatically, upon save of the new Service Record) to reflect the System Status column in Service Records table for the newly created row (record).

Example: Service Technician selects "Winterized" in the form and saves. Row in Service Records is "Winterized", and row in Systems updated to "Winterized" also.

Note: I have used the "Timestamp" column in Service Records as the Key so that I can take advantage of MAXROW functions in looking up previous rows for individual systems. The Systems table uses the "System ID" column as Key.

I have attempted many solutions for this (including the use of Actions and Bots), but none have worked so far.

I am still new at this, and so there is a lot I don't understand. It is also another of many hats I wear as Operations Manager, and so I have a limited budget and background in any IT or coding settings.

I have included several screenshots. I hope these will help. 

Thanks for any help you can offer.

Systems Table, Systems Status column

Service Records table, System Status column

Systems table in AppSheet

Dashboard highlighting finished System Status

Service Records table in browser

Service Records form, System Status selector

Service Records table, AppSheet

[Hussein_Osseily]

if you want the status to be updated in another table once the FORM is submited , you can creat an action to lookup the status in the other table and make it trigger once the form is submitted by using the following: 

creating an action of type excute an action on  a set of rows  (appearance / Prominence : must be don't display)
referenced table (The table whose action will be executed) 

and then in the Form view of the table where the Service Technician selects "Winterized"...
scroll to the bottom to behavior / event actions , and choose the action you have created. 

logically, this shall trigger the lookup action to get the updated status every time the form is submitted or edited

[sam_buzzfree]

Thank you, Hussein. I did in fact find a YouTube video tutorial where the author used the strategy you suggest, and it worked well for his app. His app was a hotel setting where he needed to change the status of rooms as either "available" or "rented" and the bots / tasks approach worked. I even rebuilt his app from watching the video and it worked for me. For now, I have been able to solve it apart from bots and tasks, which I do need to learn fully in time. But time is of the essence, and there are many subtleties that I don't have the time to dedicate to learning at the moment.

For now, I am just using Google Sheets and formulas there, which I am much more comfortable with. I'm simply using a MAXIFS formula in the systems table to look up the most recent service record for the System ID in question, and then a VLOOKUP to search for the system status that matches that System ID and the most recent Timestamp. It works. (A couple of screenshots attached).

Selected System Statuses in Service Records Table

MAXIFS To Find Most Recent Service Record with System ID

VLOOKUP To Find That Specific System Status

The Resulting Map Pin Statuses

Again, I am eager to learn how to do this in AppSheet, but for whatever reason, the learning curve is taking more time for me than I anticipated. AppSheet is a useful platform, and there are a lot of good resources. But with each application (as I seem to see) as being so custom, there is no real "manual" that can train this. The one thing I wish the official AppSheet help contained was more visual examples and samples. Google has done this to a large extent with sheets, where formulas are explained, and then demonstrated in a simple sheet (just below the formula explanation). This is not the case with AppSheet. 

Unfortunately, I am in a spot where I'm using an awkward combination of AppSheet formulas in some cases and Google Sheets formulas in others. It is serving my purpose at the moment, but I'm certain there's a better and more efficient way to do this.

Ben Collins has been a great resource for me in Google Sheets. We need one of him in the AppSheet world.

I digress. Thank you again for your help. I will give it another shot when I have the time to delve into it.

[Hussein_Osseily]

Glad that it could work for your use case, i believe that here in appsheet the learning curve is quit good and the community is very helpful, and as you said you can find different ways to achieve the same result, but some are  "cleaner" than the others. there is some good youtube channels for appsheet one that i follow belongs to one of the very active members here in the appsheet community @MultiTech  Multi Tech Vision 

To get the most recent record (linked to a time stamp) you can use the  MAX() / REF_ROWS() / INDEX() / ORDERBY() 

There is a different approach which is to creat a slice for the service record using an expression somehow like 

[key column]= MAXROW("service records table" , "time stamp column", [the record id]=[_THISROW].[record id])

in my use case 

[ID]= MAXROW("FSU MASTER SHEET" , "arrivetime", [MAWB]=[_THISROW].[MAWB])

ID is my key
FSU master sheet is the table i want to slice
arrivetime is the time stamp (i wanted only the max time stamp of every MAWB)

MAWB is what am looking to get it's MAX Time stamp

and then you can use the lookup function to get the last timestamp into the other table LOOKUP() 

You can also check this helpful video made by @MultiTech , which is also an approach to get the latest whatever record into another table 
How to get details out of the last Whatever