Row Filter Condition to match "REF" values

[gcor71]

I have a FILTER table and a DATA table where I can successfully slice the DATA table using:
if(isnotblank(FiltersForCurrentUser[F_group]), in([Group Name], FiltersForCurrentUser[F_group]), True)

The DATA table has another column, ‘v_nearest’, which is a REF to a list of other Group Names.
What I want to do is use an expression like below to match Group Name against v_nearest Group Names:
if(isnotblank(FiltersForCurrentUser[F_group]), in([v_nearest], FiltersForCurrentUser[F_group]), True)

However, because v_nearest is a REF type as the result of a formula against a third table, the expression always returns FALSE.

How can I write a condition that will “pull” the Group Name from the REF to allow matching in this second situation?

To clarify - the result should be to show any row where ‘5th Portsmouth’ is in the Nearest Group column.

[Suvrutt_Gurjar]

Thank you for a fairly detailed input with your question. However, I think you may want to elaborate little more for a still better understanding of your data structure.

1. What is the difference between [Group Name] , [V_Nearest] Group names

2. The table Group_Location_Data seems to have the [Group Name] as label. So is this [Group Name] same as the [Group Name] in OSM_DATA table?

3. You may want to similarly share the screenshot of OSM_DATA table columns, the way you have shared that of Group_Location_Data table

[gcor71]

Some additional context.
OSM_DATA is a consolidated list of people who want to join Scouts. Here [Group Name] reflects the group that holds the waiting list that person is on.

Group_Location_Data is a simple lookup table, one row per [Group Name] recording the Lat/Long for the Group.

The objective if the App is to allow a user to filter the OSM_DATA table to show people
a) names from OSM_DATA where OSM_DATA.[Group Name] = Filter_Group; (ie a simple list of who is on the ‘Filter_Group’ waiting list)
and also
b) names from OSM_DATA where OSM_DATA.[v_nearest] = Filter_Group; (ie a list of who has ‘Filter_Group’ as v_nearest).

1. What is the difference between [Group Name] , [V_Nearest] Group names

In the OSM_DATA table [Group Name] identifies which (group) waiting list the member is currently held on.
[v_nearest] is the nearest (group) location to the [Postcode] in the data row, calculated against a reference/lookup table “Group_Location_Data”.

2. The table Group_Location_Data seems to have the [Group Name] as label. So is this [Group Name] same as the [Group Name] in OSM_DATA table?

The content is the same (ie 5th Portsmouth, 33rd Portsmouth etc) but their intent is different. In OSM_DATA [Group Name] records the waiting list the members is on, [Group Name] can appear multiple times in this table. In Group_Location_Data [Group Name] is the label/name of the group listed, each [Group Name] only appears once in this table.

3. You may want to similarly share the screenshot of OSM_DATA table columns, the way you have shared that of Group_Location_Data table

Source data example

I hope this helps.

[Suvrutt_Gurjar]

Thank you very much. And how the Filter table populates the [F_group] column options for user to select. Also are you intending to allow the selection based on [V_Nearest] as an alternative to [_F_Group] to the user in the Filter table?

[Suvrutt_Gurjar]

In the meantime, if I understand the structure based on your latest shared data, please create a virtual column called [V_Nearest_Group] in the OSM_DATA table, with an expression something like [V_nearest].[Group_Name]

Then the filter expression can be something like

IF(ISNOTBLANK(FiltersForCurrentUser[F_group]), IN([V_Nearest_Group], FiltersForCurrentUser[F_group]), TRUE)

[gcor71]

how the Filter table populates the [F_group] column options for user to select.

My implementation is drawn directly from this question asked by @Colin_Lough back in 2020 https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Dashboard-filter-based-on-user-input-multiple-filters/m-p/307887

There is a FILTERS table and a slice against it that lookup the filters set by a given user.

The slice of OSM_DATA refers to F_group and F_section retrieved from the FILTERS table… and so applies them. The UX then shows the resulting slice.

The options for the user to select from (to populate F_group) are a manually defined ENUM list in the UX form. It would be nice to have this dynamic, based on the available options in OSM_DATA.

are you intending to allow the selection based on [V_Nearest] as an alternative to [_F_Group] to the user in the Filter table?

I’m not sure what you mean, but to say that v_nearest and f_group are (to the user) the same thing. THey are saying “for this group (f_group) show me all the people waiting for a place”. The app returns people who are nearest to the group HQ (v_nearest), next nearest (v_2ndNearest) and third nearest (v_3rdNearest).

[gcor71]

create a virtual column called [V_Nearest_Group] in the OSM_DATA table, with an expression something like [V_nearest].[Group_Name]

Thank you! This is just what was needed - to convert the rowID returned by the REF into the relevant Group column.

I have some tinkering to do to get the ‘row filter condition’ to behave consistently - but your help has moved me on a long way.

[Suvrutt_Gurjar]

Great. Thank you for the update.

As you would appreciate the information in the second post was useful. The following screenshot helped in knowing how the [V_Nearest] column is referenced and paved the way for suggesting an approach.

At times, a community member may wonder why more table , expression details are needed but the person answering the question has no way of knowing how app is configured unless the relevant requested details to the question are provided.

I also thank you for being cooperative in giving the relevant app configuration details in a very systematic manner.

All the best with your app.