Updating several rows in a table column by using t...

Adam_van_Wyk · 12-01-2021 05:22 AM

Good day

I do apologize if this has been solved before. I have spent hours on the forum but I do not see a concise solution to my problem, so here goes. I have the following:

Table 1: which has columns containing location details. The table is called SiteDetails and has an empty column for a NameSurname based on a user role “Sales Director”

picture1 SiteDetails table before:

Table 2: I have another table, called Users, where I assign an added user, with the [Role] of “Sales Director” to a specific [Retailer] and [Country]

picture 2 Users table:

When I update/add to my Users table I want to update the Sales Director Column in the SiteDetails table with the corresponding values in the Users table, to end up like this:

picture 3 SiteDetails table after (example of required outcome):

I could very easily get hits done by adding a virtual column of LIST type and not a physical one and then adding the following expression to the virtual column in the SiteDetails table:

select(Users[NameSurname],and([Role]="Sales Director",in([_thisrow].[Retailer],[Retail Channel]),in([_thisrow].[Country],[Country])))

This updated the virtual column with the NameSurname assigned to the specific retailer and country. But I realized I do not need a virtual column, but an actual column to be updated as I will have a second app that uses the table with the assigned roles,

so my question is, how do I:

Update the SiteDetails actual column based on the specific country and retailer in the SiteDetails row but using the assigned values in the UsersTable… so, basically do what the virtual column did but to the actual column

I hope this makes sense.

Suvrutt_Gurjar

Have you taken a look at the reference actions?

https://www.appsheet.com/templates/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e7...

shahappsheet

While adding/updating the UserTable, you need to run an Action. This action will be selected of the Event Actions>Form Saved>SELECT the Action here as follows-

The Action should be prepared following SnapShot-

Referenced Rows: Rows should be filtered here.
Referenced Action: Another action to update the SiteDetail Table.

So far, it should be your mode of action.

Thanks.

Adam_van_Wyk

Thank you both the prompt reply and suggestions, I will try and revert back with feedback.

Adam_van_Wyk

Hi Gentlemen, I am still struggling, I have gotten this far and parts seems to be working but I must have my wires mixed up somewhere

Action 1:

with the following formula:

resulting in this:

Action 2:

@Suvrutt_Gurjar I looked at the reference actions app and followed their description up to a point, I just do not understand how the last paragraph will tie into my tables

Here is what I did: (In the below description I substituted the apps description “Child” with “SiteDetails” and “Parent” with “Users”)

On the “SiteDetails” table, I added an action called “Assign Sales Director” that adds the Sales Director selected by the formula: any(select(Users[NameSurname],and([Role]="Sales Director",in([_thisrow].[Retailer],[Retail Channel]),in([_thisrow].[Country],[Country])))) to the current row’s [Sales Director] column. This is a regular “set the values of some columns in this row” action.

On the “Users” table, I added an action called “Assign Sales Director to all rows”. This is a reference action. The reference action is going to call the “Assign Sales Director” action on the “SiteDetails” table, so I pick “SiteDetails” in the Referenced Table dropdown and “Assign Sales Director” in the Referenced Action dropdown.

This part I cannot get to work/do not understand:

A reference action needs a list of keys from the “SiteDetails” table in order to call this.
The “Users” table already has a virtual column called “???” that holds the referring rows to that “Users” row. So I just wrote “???” for the Referenced Rows field in the action configuration.

Suvrutt_Gurjar

Could you please elaborate this? What you mean by “???” Do you really have a VC by that name? Are the two tables referencing each other? If so could you mention which is parent, child table and refe and rev reference columns therein?

Adam_van_Wyk

Apologies!

No I do not have a VC by the name :-), in the "How we built this app section of the link they describe their process. I copied the complete process but did not understand their description of how the virtual column would tie into my table setup, that is what I meant with the “???”…I could not get the two table referencing each other part done.

I would assume in my scenario the child table should be the sitedetails and the “parent” should be my users tabel?

Suvrutt_Gurjar

Please try in referenced rows setting of action 2

SELECT(SiteDetails [Key Column Name of SiteDetails Table], AND( IN([Retailer], [_THISROW].[Retail Channel]), IN([Country], [_THISROW].[Country])))

Adam_van_Wyk

Apologies. The problem is that although it does update the SiteDetails table it does this row by row and it syncs the app for each row, which takes ages. How would I get it to update all the rows filtered in action 2 all at once with the name selected in action 1? So 1 sync and not 854

Suvrutt_Gurjar

So the reference actions typically work in one to many rows format.

If from the “parent” or the driving table has one row that can update n rows in the referenced table , then there will be “n” syncs.

I believe if you have n rows to update in the referenced table, there will be n syncs ( one per referenced row) in this method.

Also, I believe, this approach may not be used for such a mass update system. If a parent has handful children’s rows (5 or 10) as in the sample app, then it will be the best use case for reference actions per my understanding.

You could use possibly use API/Webhooks or options such as Google apps script or other integrating approaches to add so many rows.

Suvrutt_Gurjar

Or if it is a one time operation, you could evaluate using some backend sheet formulas and populate those values once for all.

Adam_van_Wyk

That is just what I was thinking! I will try both ways and see what is the easiest. Thank you for your continued effort with this matter

Adam_van_Wyk

It does work now!..sort of hehe… It updates the SiteDetails Table but as individual syncs

Is there any way to do this in a single sync?

Suvrutt_Gurjar

Nice to know it works to a certain extent. Please elaborate what you wish to exactly do?

Adam_van_Wyk

Thank you for the suggestion, I will try it and get back with feedback.

Updating several rows in a table column by using the values from another table's row