Update unrelated second table when row in first table is selected on View

This issue is a little tricky one to describe. In my previous post, I was not able to articulate requirement well enough. Sorry for repeating the question.
Trying a second time, this time with aid of some pictures to illustrate the issue better.

Imagine two tables that are unrelated i.e they do not share any columns in common.
The second table has a virtual column which needs to be overwritten whenever a row is selected on view for first table.

In this contrived example of Name and Address, since row for Name1 was selected in first table, all values of Field3 in second table will will be updated with “Name1”

PS: In case you are wondering, my plan is to have a view on second Table come up and show information that was computed using Field1,2 and 3 of that table. Please disregard this additional information if this background info is confusing.

It might be possible like this (may be, not sure):
Add a physical extra column, Number type to Table1 and name it as you like i.e. [Helper]. Add some actions to this table and choose to hide them (Do not display):
Action 1 > change the value of a column > [Helper] > NUMBER("")
Action 2 > execute on a set of rows >
SELECT(Table1[Helper], ISNOTBLANK([Helper])) > Action: Action 1
Action 3 > change the value of a column > [Helper] > put any value you like, i.e. 100
Action 4 > Goto another view in this app > LINKTOVIEW(“Table2”)
Action 5 > Execute a group of actions > explicitly in the order of Action1, 2, 3, and 4

Set the AppFormula to your Virtual Column in Table2:

ANY(SELECT(Table1[Name], AND(ISNOTBLANK([Helper]),[Helper]=100)))

Assign the group of actions above to the Row selected property of Table1.

Though haven’t tried or tested this, I just think that it might only work this way (if possible)

2 Likes

Very creative! Let me try this and get back with my result. Thanks a lot @LeventK

My idea:

  1. Create a physical column in Table B named Chosen? of type Yes/No.

  2. Create an action called NotChosen for Table B of type Data: set the value of a column to set the Chosen? column to false if or(isblank([Chosen?]), [Chosen?]). See also: ISBLANK(), OR().

  3. Create an action called ChooseNone for Table B of type Data: execute an action on a set of rows to run against Table B on the rows selected by filter("Table B", or(isblank([Chosen?]), [Chosen?])) and apply the NotChosen action if true. See also FILTER().

  4. Create an action called Chosen for Table B of type Data: set the value of a column to set the Chosen? column to true if or(isblank([Chosen?]), not([Chosen?]))

  5. Create an action called Choose for Table B of type Grouped: execute a sequence of actions to run ChooseNone and Chosen if true.

  6. Set the Choose action as the Row selected action in the Table B view.

  7. Add a virtual column to Table A to reflect the choice made from Table B. To make available the chosen row, set the app formula to any(filter("Table B", [Chosen?])); to make availablew only the name from the chosen row, use any(select(Table B[Name], [Chosen?])). See also: ANY(), SELECT()

The ChooseNone action clears any existing choices, ensuring the choice marked by Chosen will be the only one.

Note that this solution only works if the app has only a single user (at a time) or if Table B is a user-private (i.e., not a shared) table. Otherwise, every user will be affected by a single user’s choice.

1 Like

Thanks @Steve
That’s brilliant!
Would the solution work if Chosen in Table B is virtual column? That would address the limitation you mentioned

if the app has only a single user (at a time) or if Table B is a user-private (i.e., not a shared) table. Otherwise, every user will be affected by a single user’s choice.

Virtual columns cannot be manipulated by actions, so no.

Are you using that user table we discussed previously? If so, there might be a way to use it…

@Steve, I was looking at an old thread and you had indeed mentioned the read-only nature of virtual columns then but I somehow missed it then.
Thanks for clarifying.

How about the “Name” field value being written to a UserSetting field when row on table B is selected.
The virtual column on table A would pick up the value of Name from the UserSetting via a formula.
This would allow the capability to be used concurrently by two users without conflict. Agree?
Any feasibility issues there? Any downsides?

That’s what I was going to propose! :slight_smile:

Thanks @Steve
UserSetting can be very versatile. I notice there are only 10 of them. That made me wonder if there are similar altenative, but that is topic for another thread I posted.

Will try ideas you and @LeventK gave and will get back. Thanks!

@Steve, @LeventK, I implemented a solution on lines on your ideas.
It worked well but field update (field Chosen? in case of Steve’s solution or field Helper in case of Levent’s solution) turned out to be the bottleneck. Action fires, it queues the field update (2 row, one to turn off and one to turn on) and immediately presents the new view on table A.

So, imagine Name2 was selected in my example. User would expect to see Name2 on the table A view. But instead previous Name value in still on that view.
It takes about 15-20 seconds for the queued field update to complete and then the table A view refreshes to show Name2. Both table A and B have less than 20 records, so table size does not seem to be a factor here.

I see articles like these to improve performance and I can consider re-engineering to some extent to speed up. But I wonder even if I do that, can the update ever happen fast enough to be done before the view is presented .

@Steve, I could not figure out how to work in UserSetting. May be that holds some promise for better performance.