Update another table when data gets added to a different table

AppSheet AppSheet Q&A
1

I see this question asked a lot, but I don’t understand how to get it to work.

I have an equipment management and user-tracking app. The app works well apart from taking a long time to sync when a user moves the location of equipment. For the location of the equipment, the table has 45 columns but only 5 of these need to be updated with new information. The other 40 columns contain duplicated data from other tables using = ‘this cell’ in excel.

All tables have the same KEY layout in column A, named [asset number] which starts at 0001 and finishes at 2000. There are no adds, only updates to the data.

I believe that I can speed up my app by

  1. Moving these 5 columns into another larger table, named equipment register, and recreating the views from there. This will remove 40 duplicated columns (16% of my app).
  2. When a user updates the location of an app these 5 columns including [asset number] are added to a new table named location history. This will also give me the benefit of tracking the movement of equipment over a period of time. This will have a unique ID key as the table will just be added to it. I can achieve this and the sync process writing to this table is quick.
  3. I then want to start a bot when new data is added to location history to write this new data from the 5 columns into the equipment register table on the same line as the matching Asset ID. This is where I am stuck.

I have created a bot.

  • Event Type, Data Change, Adds Only, Table location history.
  • Run this process, Run a data action, Run action on rows, Referenced Table Equipment Register, Referenced Rows, Referenced action Become User Test (which writes the 1 column as a test)

I need help from this point.

The referenced rows expects a list so I have added SELECT(Equipment Register[Asset number], [Asset number] =[_THISROW].[Asset number])

The referenced action is a bot which for simplicity is

For a record of this table Equipment Register, Set the values of some columns in this row, Current User = “Some Name”

Ultimately I want this to write the data from the location history table but I cannot even get it to change some simple data so I am doing something wrong.

Can someone point me in the right direction?

Many thanks,

2

This is a long-standing gap. The action needed is “update an existing row in another table using values from this row”.

AppSheet’s solution to this gap is the INPUT() function. You will immediately notice comments that this feature is in Beta. However, many app creators are using this function in their live apps with no issues. Not all apps have this feature available

Basically, the INPUT function allows for passing of values between actions. Refer to the article on how to use it. It is very simple to use, but, if you still have questions, come back here and let us know.

If you find that INPUT is not available, again let us know and we can help you work through an alternative.