Copy Data to New Table - Is There a Quicker Way?

[mykrobinson]

Morning, folks.

I "fixed" an app made by another entity, it had a lot of issues that stemmed from there being formulas and pivot tables in the spreadsheets he was using. I was able to convert all the formulas and such to appsheet expressions and the app is working fine. The app is a calculator for medical stuffs that is a huge time save for him.

Anyways, he would like to be able to log historical data from the calculations he runs on patients he sees and have these accessible within the app. I have done enough to understand that I need a separate table to house the data, and it needs to be tied to the patients by way of a reference to the primary key. Easy enough, but here's my issue.

The primary table has a LOT of columns... Most of these columns have a lot of complicated math that the app does automatically, which is fine. I'm not needing to do the math again for the historical content. What i want to do is have appsheet add rows to another table when the data from the primary table is updated. I can set this up in the automation section, but it seems I need to manually enter every row and tell appsheet what column to assign to the corresponding column in the other table.

Before i proceed, i just want to make sure there is not a faster way to do this. If this is the right way, i'll continue, just trying to avoid manually adding 50+ columns here 🙂 I pretty much just want it to copy the row and add a unique id for the copied record.

[MultiTech · Accepted solution]

One of the downsides of being a no code platform, is that you can't code things programmatically. You have to go through and do each individual change individually, one by one, for every individual thing you need to do.

Inside that action you're creating, to create a record in another table, you have to include every column that you want to copy. If you have 50 columns, you have to add all 50 columns.

There is a little wiggle room here, as you can use a macro recorder on your PC to record the various steps you need to do. The nice thing about the app sheet editor is that everything is in the same place for each screen, so if you record clicking on your screen in a particular place and play that back... It's clicking the same button in the same place.

So what I usually do is record the sequence of events to put the column name into the formula field.  Inside the expression assistant, the name of the field is up at the top, you can copy it from there and paste it down inside the formula space. So it's just a matter of recording the keystrokes and mouse clicked, and playing those back over and over and over again.

The real benefit of all of this, is that you can play back that macro faster than you recorded it; so fast that it actually processes faster than the display stuff. Meaning the animations of the expression assistant opening, all the content loading inside it, all of that stuff.... You can progress your macro faster than all of that, and the system will still accept it.

[mykrobinson]

just had a thought, i bet this could be done through AppsScripts! Still learning that though 🙂

I'll do it the old fashioned way while having coffee. Thank you for the confirmation.