Filtering with combining lists

[mrbonnell]

Hi All,

I would like to filter a dataset based on the [Row ID] (which on its own works just fine) but I'd like to return the data from a specific column [Confirmed Learners] (column type List), then combine the returned lists into a larger list.

I'm trying to get a combined list of all Parent Permission[Confirmed Learners] that are being filtered

[Confirmed Learners] is a virtual column list of all learners in that row

FILTER("Parent Permission", ([Trip Name] = [_THISROW].[Row ID]))

Thanks in advance

Matthew

[baba_sawane]

Hi,

Can you provide an example on what you are aiming ? I'm not sure. You might need select()

If you want to combine list, use list(list1, list2...) , like : list(select(Parent Permission[Confirmed Learners], ([Trip Name] = [_THISROW].[Row ID])) , list2 )

[Mjbonnell]

Hi

For an example this is the second table I’m trying to filter the data from:

row1        Trip1         {Danny,Clair,John}

row2       Trip2        {sally,Jessie}     

row3       Trip1          {mark,Bobby,fran}

I want to create a list from row1 and row3 (as they are both connected to Trip1) and save the combined list into a cell in the Trip1 row of my Trips table.
{Danny,Clair,John,mark,Bobby,fran}

(Trip1 is a ref  to the Trips table)

select seems to only allow one row to be selected, I will have potentially hundreds of rows

cheers

Matt

[baba_sawane]

Thanks for providing an exemple.

1. What you can do is creating an Enumlist column of base type text that i'll name [combined list]. 

2. Then create a second column, virtual, set it as List of ref with the formula [combined list], and reference to the table "Parent Permissions".

3. In the enumlist, you can construct the combined list of rows, then concatenate them into text, with a formula like this  : 

concatenate(unique(select(Parent Permissions[keycolumn],contains([Trip],[_THISROW].[Trip]))))

You can hide this column and only display the List of ref, that will show every item from your combined list, with reference.

I added unique() to remove duplicates

The list of ref needs to be, a list of ref of base type Text. This is why we first convert it into text using concatenate and EnumList of base type Text, then convert it into a list of ref.

You might want to check the select() function as it is pretty useful in AppSheet. 

This process is detailed in this video by @MultiTech in the Report Output select() section.

Tell me if you encounter any issues !

[mrbonnell]

Hi,

With this method the Enumlist is only being populated by the first instance of the list in the table, it is not combining the lists. In my example this would be only {Danny,Clair,John} and not the second list.

I think SELECT is only bringing in one row (the first one) from the second table.

Any other ways? Using filter I can get all the row ref, but then how from that list of ref can I extract the column lists and combine them into one?

Thanks for helping

Matt

[baba_sawane]

Your welcome Matt,

I think SELECT is only bringing in one row (the first one) from the second table.

>> Select is selecting rows based on your filter. This is the filter I suggested : contains([Trip],[_THISROW].[Trip]))

Logically, it will select every rows that contains in Trip the value of the actual Trip.  You might want to check that you select the key column in select(Parent Permissions[keycolumn] (the key column of the parent permissions table) and that every data type is corresponding to my explanation above.

It should returns the combined list :

>> You can see columns types of every column included in the formula, and check if it matches your app, if it is it can be replicated

If an error message appear >  there's an issue in data types definition

If 0 or only 1 value appear > it's a filter or a reference of key issue