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.
Solved! Go to Solution.
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)
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
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.
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?
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-f...
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).
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)
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.
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.
User | Count |
---|---|
41 | |
30 | |
29 | |
16 | |
14 |