I'm trying to use a table column with FILTER() formula as the data source of a List in the app, but I can't get it to work, I can't dereference the resulting rowlink in the Column List.
I have 2 tables, Clients and Items and I have a column called Assigned Items in the Clients table that contains a formula to show all Items assigned to that Client: =FILTER(Items,"Items[Client Name]=THISROW()").
When trying to add a Column List in the app and I set the source to =Clients[Assigned Items] or =FILTER(Clients,"Clients[Assigned Items]>0"), I can't dereference the resulting rowlinks to get the values of each column of the rowlink.
The Set source field shows Evaluates to: 3 but if I try to dereference it by doing =FILTER(Clients,"Clients[Assigned Items]>0")[Assigned Items] and using =[Item Name] or =[Quantity] in the List, I get #VALUE!. Without dereferencing I get #NAME? in each column.
Basically, if you look at the last screenshot in the Filter( ) example, I'm trying to use the Tasks column as my List data source but I can't get dereference the columns of the rowlink. Sure, I could use the FILTER() formula again as the List source but that's exactly what I want to avoid due to other complexities.
I tried doing this in the Table itself, by creating a new column and trying to dereference the FILTER() column using =[Assigned Items][Quantity] formula but it shows #VALUE!.
In your [Assigned Items] column, the =FILTER(Items, "Items[Assignee]=THISROW()") is generating a filtered view of rows symbolized by the blue funnel icon. A filtered view of rows is different from what we refer to as "rowlinks" in Honeycode. While a filtered view of rows cannot be dereferenced in the way you're trying to, rowlinks can.
If your use case would benefit from dereferencing a rowlink's column, I would recommend taking a look at this article, which will walk you through it step-by-step.
Thanks for your reply. Is there a way to iterate through the filtered rows in a List?
My use case is that I have a long and complex filter, for example =FILTER(Items,"TODAY()+7>=Items[Next Pickup] AND TODAY()-7<Items[Next Pickup] ORDER BY Items[Next Pickup]") that I want to use on various app screens in lists. I thought I could add this filter in the Table to a column and just the results instead of defining the same filter again and again as the source of each list.
The reason for doing this in the Table is performance and not needing to remember to update 3-4 List objects if the FILTER() formula needs changing, also I could use variables inside the FILTER() more easily if it was done in the Table.
It's interesting that the same thing works with an $[InputRow] style Picklist:
So, effectively we did [Assigned Items][Item Name] but it only works with a picklist, not directly referencing the column in the List source. Is this expected or is this a bug in Honeycode?
Is there any way to filter in the table and iterate the results or somehow dereference them in a List in the app?
As you correctly observed, the $[InputRow] variable can be dereferenced. The $[Inputrow] returns a rowlink(s) (i.e., an entire row(s) of data) from a table enabling you to access and deference column data. Picklists are also rowlinks, but in the form of a dropdown menu.
Filtered views in a table are not the same as rowlinks. My understanding is that a table column formatted as a filtered view of rows cannot be dereferenced through a List object. As for being able to iterate the filter throughout your app, I would recommend to maintain the filter on each of the screens, as you are currently doing.
I will continue to ask around if there is some way to achieve what you're seeking; if I identify a better solution, I will make sure to follow up with you. Being able to iterate the filter throughout the app from a central location is an interesting idea, and I will share this with the team. Thank you for the feedback!
Thanks for your response and confirmation @Dan. At least I know it's expected
It would be great to have a central or Table-based filter, so that I don't need to add the same filter multiple times on various apps or app screens. It would be handy for automation too because sometimes I want to apply conditional formatting for a specific formula (some deadline is within 7 days, etc), and also send a notification. For this currently I need to add the same FILTER formula in 2-3 or more places. If I could do this in the Table, then I could just use the filtered column and match on it if its value is 0 or 1.
As a workaround, I currently create an additional column with Checkbox format (effectively a TRUE or FALSE boolean) and test if the FILTER column is bigger than 0 with the =[Assigned Items]>0 formula. In conditional formatting and automation triggers I can just match on the Checkbox being TRUE or FALSE. For Lists, I still have to enter the full FILTER formula.
