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
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
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