Re: Run a Data Action on All Rows

Qiro · 09-03-2022 06:14 AM

I have an existing table that uses an [ID] to reference a person's [Name] from another table. I want to bring the name of the person into the [ID] table as I'm having issues with VC's not behaving themselves.

I can get this process to work on a row-by-row basis with a data action:

SELECT(Onboarding[Associate Name], [Transporter ID] = [_THISROW].[Transporter ID], TRUE)

How can I run a data action that will check all 997 [ID]s already in the table and match them to the correct [Name] from the other table?

I have found some solutions by searching but they seem to apply to an older version of AppSheet.

WillowMobileSys

@Qiro wrote:
... I want to bring the name of the person into the [ID] table as I'm having issues with VC's not behaving themselves.

Using a virtual column, in this use case, is probably the best way. If the name should ever need updated or changed, it will automatically propagate across the app with VC's. You could use "normal" columns but then you would need to explicitly insure any name change is disseminated throughout the app.

So, the question is...What issue are you experiencing with the VC's?

The way you would want to set this up is to have a REF column in your "existing table". Maybe name that column as "Transporter", define it as REF and set the reference table to "Onboarding". Then add a VC to the "existing table" as "Associate Name" and set its app formula to:

[Transporter].[Associate Name]

This will automatically pull over the name and make it available to the row in the "existing table". More over if the name ever changes in the "Onboarding" table it will automatically update in the "existing table". Use a similar setup anywhere else you need to surface the name and they all will auto-update with the change being made in just a single place - no extra effort required implementation-wise to make sure the name gets updates everywhere.

Now, if your setup is similar to that described above then please elaborate on the issues you are facing so we can help get your app on track!

Qiro

The whole system used VCs to pull the correct name and it worked OK, but I have been experiencing an issue with data failing to save.

There are two quick edit fields, and if you edit more than one at a time, the save process fails and only the first data point is saved.

I noticed that during the save, the entire record disappears temporarily, and guessed this may be down to my using VCs to sort the records and them having to be recalculated.

Frustratingly, moving to normal columns has not fixed the problem, and I completely forgot the benefit of a VC in this application being name changes! 😭

WillowMobileSys

@Qiro wrote:

... using VCs to sort the records and them having to be recalculated.

Based on this comment above, I suspect there may be an inefficient use of VC's causing/contributing to the issue. What kind of sorting are you doing?

Qiro

There are two tables, one contains IDs, the other names and IDs. The VC in the second table checks the first for the corresponding name and displays it against the correct record in the first.

It is my understanding that a VC doesn't store the data anywhere permanent, so needs to be recalculated each time the application is refreshed, so I guessed that that refresh may be the issue - turns out I was wrong and it's still happening.

The issue I'm having with disappearing data I have raised with support, but we don't seem to be getting anywhere, really.

Suvrutt_Gurjar

1. You could use reference actions to do the needful. However if there are 997 rows to be updated , the reference action will create 997 row level data change actions that will update slowly.

2. You could use Appsheet API Webhook based bot to edit those rows.

3, If you are using a spreadsheet (GSheet or Excel) as backend and if you are well versed with spreadsheet operations and formulas, you could possibly copy the name by using lookup functions from the spreadsheet itself. After copying you could paste back the copied name values as "values only" so that the underlying spreadsheet formulas are deleted after copying. Then when you sync the app the names will reflect.

Qiro

Speed is not a concern, so long as the process works for all rows up to now that's a huge win.

I also plan to incorporate a CSV import function to improve ease of use (currently data is added directly to the sheet manually!) next, but the same process would need to happen as part of that process.

Would reference actions do that? I have no experience using them or the AppSheet API

MultiTech

What I've done in the past to solve the problem:
"I need to create a new column and fill it with data from another record"

Add a couple of physical columns to your table:
1. [ChildTable_ParentName] (or whatever you're getting)
2. [Update]
As the app formula for the name, use a dereference, like @WillowMobileSys suggested
Implement the Quick Update System for the Update column
As a way to run the update action through automation real quick, Create a scheduled bot
In the bot, create a Mass Update task
- One of those execute an action on a set of rows, where the rows are all the rows of this table, and the action is the update action
Save everything so it propagates
Go to your scheduled bot, and tap the "RUN" button

This will run the quick update on all the records in your table, which will update all the app formula values - thus getting all the names into your records.

Sure there's a few steps to this process; but this is my standard go to when I need to populate a column with values

Qiro

Brilliant, thanks.

I'll let you know how it goes.

SkrOYC

I'm doing this latelly for almost everything where I'd have added a VC before for performance reasons. It has been reliable for me to update App Formulas via schedule bots using the trusty [update] number column