Update table 1 column with the changes made to referenced table 2

[Daryl_Zernick]

Hello,

It's a simple task, but I am just stuck on writing the proper expression.

I have two tables in one Google Sheet, referenced together with unique "ID"s. I am using one, "EEs", as an employee record and the other, "History", to record the historical changes to the 'EE' table. So, if I want to change a date in the 'EE' table, I open the EE record and scroll down to "Add" under the EE table which opens the 'History' form where I enter the column to be updated and the update value.

Can someone please help with the expression to write to carry over the updated value from the History table to the appropriate column in the EE table and insert the value? 

Here is the sheet:  https://docs.google.com/spreadsheets/d/12JgGVkOjhMRwxjmXd1AKD1TdQVMp_mglN3k0xcMan9s/edit?usp=sharing

Any help is truly appreciated!

Daryl

[TeeSee1]

This is not to directly answer your query because I do not know how to do what you are asking.

An alternative approach is to create a log table that simply records all fields on change.

This way a user does not have to add an entry per changed filed but can update multiple fields and record them all at once...

[Daryl_Zernick]

Thank you for your reply. Essentially, my History table is the log table. The question remains, when you save the data to the log table, how does the data then get inserted into the employee table?

[Ryan_Mortimer]

After re-reading this and looking at your spreadsheet, I think you are using the data structure the wrong way for what you are trying to achieve.

• Which columns in your sheet are you planning on updating?
• Why can't you just update them and then record that an UPDATE was made?
• If you want the column values from HISTORY to auto-populate the EE table, you will need to start with a blank entry and then add your column values into the the HISTORY table after

If you didn't want to do that last part, you are going to need to over complicate the data structure and expressions to the point of absurdity (in my opinion).

Alternatively, you could have a crazy amount of actions/utilise INPUT() functions, but it appears that you have 20+ columns that you might want to update - which leads me back to my initial suggestion(s)

[TeeSee1]

What I am suggesting is

1, Create an action to copy EE to History using "Data: add a new row to another table by using values from this row"

You can copy the entire EE row as well as a few other items like change datetime (=NOW()) and who changed it (USEREMAIL())

2. Set this action as the save event action of the EE Form

So you make changes to EE in the first place. No need to copy back from History

[Ryan_Mortimer]

• Setup an automation/bot
• trigger = any edit
• Data action of add rows to another table 
• Set columns you want to add (include unique ID and every column that you want to record, even if only one of them has changed from the last edit)
• I would also include a time stamp column
• (optional and not difficult at all) write conditional formatting to show WHICH columns have changed from the last entry in both the log table and/or the main table

 This actually isn’t too tricky - as usual at this time of night I am on my phone, but happy to help 🙂 

i have a few versions of this setup in different capacities. Using bots/automation is IMO the better way to do it. 

[TeeSee1]

@Ryan_Mortimer Now that you mentioned that you prefer an automation approach, I have given it some thought and I agree with you.

I have seen others including myself implement subsequent data updates using a Form's save event. This approach requires you to wait for all the actions to complete before you close the app in order to retain the integrity of your app, whereas a bot approach requires only the initial data change to be finished which is definitely faster. I have modified one of my apps to a bot and it seems to work very well.

Any other reasons why you prefer a bot approach?

(To be honest, I have been sort of avoiding bots because my free plan does not allow me to use scheduled bots and that also pulled me away from using bots on data change unless I am forced to... A bad case of preconception)

[Ryan_Mortimer]

Hey @TeeSee1 

I don't think that either way is more right or wrong, per say.

Mainly though, I make my judgement for automations vs actions

• Automation means that the changes are essentially happening in the background - making the user experience smoother (as well as the impression of faster)
• Longer sync times = increased risk of incomplete tasks, errors which can result in issues with data integrity.

For example, in some industries, a failure to update one column or a group of actions not completing can cost thousands of dollars in errors.

I think that there are definitely use cases for your initial suggestion, and I think I may even look into this a bit more.

There are a few clicky, weird things with how bots work and how we interact with them, but they are definitely the way to go for most cases... you just have to get used to them and find some ways to stretch them around your task.

[Daryl_Zernick]

So, I did try this and it is where I ran into problems. This is what I attempted to do:

• The EE table is ref linked to the History table
• Open AppSheet, select specific EE
• This view shows history as a form under the main EE view... select "Add" to add to the History table
• This view has two questions: 1) What is the data column to update and what is the data
• The Bot I created (that I could not get to work) would run on any change to the table and was to change the data in the EE column indicated with the data from the History table entry. I need a History table to log to all changes made in the EE table.

Any chance you could share a bot that you may have written that does this?

Than you!

Daryl