Choice filtering not working as expected in Pages

Hey everyone,

I am trying to do some simple filtering but it is not working as expected and it is driving me crazy. Below is a simplified example:

I have 4 tables.

Table 1 has two columns:

  • user ID
  • Name

user ID -|- Name
1234 -|- John
2345 -|- Mary

Table 2 has four columns:

  • user ID
  • a relation column to table 1 matching on user ID
  • a lookup column that gets the name of the user from the relation
  • an array column with Months

user ID -|- Relation -|- Lookup Name -|- Months Array
1234 -|- (John) -|- John -|- Jan, Feb
2345 -|- (Mary) -|- Mary -|- Feb, Mar

Table 3 has three columns:

  • months column where each row is a month
  • a relation column relating the months to the array column in table 2
  • a lookup column to get the name of the users for each month

Month -|- Relation -|- Lookup Name
Jan -|- (John) -|- John
Feb -|- (John) (Mary) -|- John, Mary
Mar -|- (Mary) -|- Mary
Apr -|- -|-

Table 4 has 2 columns which is the one I want to write to:

  • user id
  • month

On the screen i have a collection that shows all the names. When i click on one of the names, the one of the values on the screen is the Name. Then there is an Add button that has a choice based on the Months. What I want to do, is to filter the months shown in the choices. For example:

Let’s say that i click on John. The name on screen is now John. When I click Add and the form opens the choices without any filter are: Jan, Feb, Mar, Apr. When i add in the filters “Relation is not empty” then I am left with just: Jan, Feb, Mar.

Then, I want to add another filter to only see John’s months. So, when i add to the filter: … and the Lookup Name (from table 3) contains value from screen Name, the whole choice component disappears instead of only showing Jan & Feb which is what i am after. Not sure why this is happening but anw.

Then I tried creating another column in table 3, where i join the text in lookup name. This now allows me to use “includes” instead of using “contain” in the filter. So, the filter now is: “relation col” is not empty and “lookup names as csv col” includes “value from screen name”. So, I am expecting to only see Jan and Feb but instead I see Jan, Feb, Mar. What is even more confusing is that when I replace the “value from screen name” with a custom value and type “John” the choices are correct and I see Jan and Feb.

I am not sure if this is bug but I have tried many things and I haven’t been able to make it work as expected. Any help would be greatly appreciated.

Thanks a lot in advance :slight_smile:

So let me summarize it, you have a list of names in a choice component, you want that when you choose a name, then the next choice of months would be filtered based on the chosen name?

It is even simpler than that!

I have a list of names and I click on the name and visit their “profile”. Then I have add button that opens a form where there is a list of months in a choice component and I want the to be filtered based on the name on the name of the profile I visited.

It sounds very simple but the name on screen filter is not working as expected. After doing some digging, I think the issue I’m facing is similar or the same to this. I read the solution but I was not able to follow.