Multiple REF_ROWS in the same LIST

[Espegg]

I have a key column (USER) related with 4 different tables:
- USER on Activities 1
- USER on Activities 2
- USER on Activities 3
- USER on Activities 4

Now AppSheet has created 4 different REF_ROWS VC:
- REF_ROWS("Activities", "USER on Activities 1")
- REF_ROWS("Activities", "USER on Activities 2")
- REF_ROWS("Activities", "USER on Activities 3")
- REF_ROWS("Activities", "USER on Activities 4")

How could I join all these 4 REF_ROWS tables into a new one and then delete them?

[Espegg]

@AleksiAlkio @Markus_Malessa  how could I proceed?

[AleksiAlkio]

If the structure is exactly the same in every table, you just need to copy the data from those 3 tables and then delete them. This works if the target row contains just one user.

Though if the target record has more users selected, you could select EnumList with the base of Ref. Then that column can have multiple users in one target record.

[Markus_Malessa]

I would suggest either a manual action as @AleksiAlkio suggested, or you could set up a 'temporary' bot that would use the API to write to the new table and then maybe still manually delete the other tables.

[Espegg]

Hello @AleksiAlkio @Markus_Malessa 
thanks for your replies, but maybe I wasn't able to explain myself well.

Consider I have 4 "events" fields (CF Collaboratori, PERSONALE IMPIEGATO 1, PERSONALE IMPIEGATO 2, CF Dipendenti) where I could have (in one of them or in all of them) a key value (VAT Number in my case) , AppSheet has created these 4 VC Related Eventiis etc.... but one for type of event.

I would delete them and create only one VC (and so I need the right expression formula) --> (Total List)
that includes all 4 events type

[Markus_Malessa]

I don't think REF_ROWS is the answer here. You may want to test if you can do SELECT() + SELECT() + ETC. for each of these to see if it will construct a comprehensive list. But even in that case you appear to want a list of references, so if you choose list and REF in your new virtual column it will only allow you to choose one table as the reference. So this would probably not work either.

I think you should possibly think about restructuring your data instead, where all data is in one table rather than four(?) tables and consider a column that maybe tracks roles? The reason Appsheet created these virtual columns seems to be because you have four different tables with each table having a REF column to "Eventi". But this brings us back to the same issue when trying to use a combined virtual column because it would point to multiple different tables.

[Espegg]

Hello,

no, I have only 1 table Eventi where there are 4 columns (CF Collaboratori, PERSONALE IMPIEGATO 1, PERSONALE IMPIEGATO 2, CF Dipendenti) where there is the possibility to have the Key values.

So my source table in only one.

[Markus_Malessa]

I understand that part, but REF_ROWS typically establishes a relationship between other tables. So what are the four other columns that it is pulling "CF Collaboratori" and so on? Are these slices from one and the same table? It looks like you are trying to pull the relationship between an event and maybe attendants?

[Espegg]

There are 2 tables:
- Eventi
- Users

Now, in the Eventi tables there are 4 columns (CF Collaboratori, PERSONALE IMPIEGATO 1, PERSONALE IMPIEGATO 2, CF Dipendenti) where I could ref from the Users tables.

Now Appsheet has created this 4 REF_ROWS:
- REF_ROWS("Eventi", "CF Collaboratori")
- REF_ROWS("Eventi", "PERSONALE IMPIEGATO 1")
- REF_ROWS("Eventi", "UPERSONALE IMPIEGATO 2")
- REF_ROWS("Eventi", "CF Dipendenti")

So when I click a row in the table Users I have 4 table list view, one for REF_ROWS, but I need only one TOTALE table list view that includes all the type of Users

[Markus_Malessa]

Can you post a screenshot of all setting for one of these virtual columns. I specifically need to know what the 'reference table' source is. See my example screenshot below:

[Espegg]

Sure,
in this case the VC Related Eventis By PERSONALE IMPIEGATO 1

[Markus_Malessa]

I think I finally have all the info to derive a possible solution. I will make no guarantee this will work and I'm still not sure how you would want to go about deletion of any of the data if that is still a requirement. Make a new virtual column [CombinedEvents] or something and try this formula:

SELECT(Eventi[keycolumn], IN([keycolumn], LIST([CF Collaboratori], [PERSONALE IMPIEGATO 1], [UPERSONALE IMPIEGATO 2], [CF Dipendenti])))

[RedVox]

Wouldn't that approach just return the keycolumn for the current record?  And it wouldn't eliminate any of the fields that he is hoping to eliminate.  Now I understand that there's not four activity tables, there's four user ref fields within an event.  But i'm still curious about why you want to consolidate them.  If you want them to display as a single string in the event details, that's understandable and doable.  

If you need to display all four users in a single child table, that's doable too.  You could create a third table that stores refs for the event, a user and the activity that user undertook relative to this event.  Then, in your events table detail view, you'd have a related user activity column that could display as a child table, showing the user name and the activity they completed for this event. 

[AleksiAlkio]

When you create a virtual list column with the SELECT(), and you use key column with it, it generates the inline view in the parent record's detail view.

[RedVox]

Yes, if the formula returned refs for other records, that would be true.  But I think the result would be a list of event records with the same user/activity combination.  So it would be a child view of the parent or maybe the parent plus other event records with the same combination of activity-users.

[Espegg]

Hello,
@Markus_Malessa @AleksiAlkio @RedVox 
I have created a VC with the formula:
SELECT(Eventi[ID EVENTO], IN([CODICE FISCALE], LIST([CF Collaboratori], [PERSONALE IMPIEGATO 1], [PERSONALE IMPIEGATO 2], [CF Dipendenti])))
but I cannot see the inline view in the parent record's detail view
in order to check if the expression works

[Markus_Malessa]

Double check your view to see if the new virtual column is actually included, i.e. if you set a specific column order within the view then a new virtual column would not automatically show within the view.

[Espegg]

I have included in my view and now is visible and works properly.
Now, only if I use an interactive dashboard view with the tables Users and Eventi,
if I click on a User, in the Eventi inline view are visible only few eventis for that user but if I click on the new VC, all the eventis for that user, are visible.

Why?

[Markus_Malessa]

I suspect because that is how the new VC is setup from a filtering perspective. Post a screenshot or two on what exactly you mean and what your expectation is it should do instead.

[Espegg]

Simply, I have created an action that use the new VC for User Table
LINKTOFILTEREDVIEW("Eventi_Inline", IN([_THISROW].[CODICE FISCALE],LIST([CF Dipendenti],[CF Collaboratori],[PERSONALE IMPIEGATO 1],[PERSONALE IMPIEGATO 2])))
and it works properly, but if I select the row of a user in a dashboard view, the eventis are filtered only for [PERSONALE IMPIEGATO 2] column.

Why?

[Markus_Malessa]

I would look at how the dashboard view is set up to make sure it has the same inline view. Also if this is an interactive view there might be some additional settings that would need to be reconfigured with the introduction of the new VC.

[Espegg]

I showed to @Markus_Malessa the dashboard problem better and we were unable to understand the cause.
@Markus_Malessa can you explain here better 🙂

[Markus_Malessa]

From what I can tell the related columns are events related to a user. And yes you are correct it would return key columns, but from the event table if the key matches with any of the keys stored in all four columns. At least that is what I am expecting it to do. The unknown factor still is if any of those four columns in the user table are EnumList columns or something else. In that case it might require further tweaking. And you are correct, it does not currently address eliminating any fields, which in my mind is still uncertain what OP wants in that regard. Furthermore, the combined relationship should probably be stored in an actual column vs a virtual column and after everything is triple checked, the unneeded columns can probably be manually deleted in the table directly. There is still some unknowns that is why I indicated that my proposed solution may not in fact work. Only trying to provide some guidance at the moment to get them to the final solution.

[AleksiAlkio]

Instead of using 4 Ref fields from the same table, either use one EnumList base of Ref so you can select 4 at the same time, or just 4 Enum base of Ref. Then create your own virtual list column like SELECT(Table[KeyColumn],IN([_THISROW].[KeyColumn],LIST([Column1],[Column2],..))).

[RedVox]

A couple thoughts... The first question that comes to mind is why you have four different activity tables.  Why not one table with four different activity types.  Then you'd only have one relationship and all the activities for a given user would show up as a single child table.  Secondly, I'm not sure why you want to delete these relationships.  Understanding why you would want to create then delete relationships between records might help.