Output Filter results to Data-Cell

Chri-c1e0 · June 30, 2020, 11:24am

Hey,

I'm setting up a small desk booking system with honeycode.
I want to display on each List entry, which people have booked a desk at a given day.

I filter the the people like this and get back two rows. But how can I access the information in the rows to display in my Data cell?
=FILTER(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date])

Let's say in the database are three columns (name, table, date)

John Doe, Table1, 06/30/20
Mary Joe, Table1, 06/30/20

I want to write in my cell: "John Doe, Mary Joe"

AndrewB · June 30, 2020, 1:44pm

=filter(...)[column name]

A filter returns a number of rows, so you just need to add a reference to a column after it.

Chri-c1e0 · June 30, 2020, 1:47pm

Hey thanks,

yeah I tried that. I only get "#VALUE!" as return though.

AndrewB · June 30, 2020, 1:49pm

Is this data cell within a list?

Chri-c1e0 · June 30, 2020, 1:50pm

Yes, but it's the same if I try it within a spreadhsheet cell

AndrewB · June 30, 2020, 2:03pm

Try setting the list’s source to the filter formula, and then changing the data cell in the list to just =[column name].

That should get you the data from that column for all rows returned.

Chri-c1e0 · June 30, 2020, 2:24pm

Bit more complicated. The List Source is from a different table then the Filter result I want to show.
I came up with a walkaround though, because you mentioned the filter settings.
I just use a Picklist now to display the content

nottheherb · December 4, 2020, 3:06am

I am having effectively the same issue - were you able to discover a fix?

RajeshGoli · December 7, 2020, 7:36pm

Hi @nottheherb welcome to the forum and thanks for asking!

While it isn't natively possible to construct a string that looks like "John Doe, Mary Joe and 3 more" using filters, I can think of a formula using basic Honeycode principles.

To start with, we can use the FindRow() function to find the first row in the above example, like so:

FindRow(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date])

This would give us the first row where this filter matches. Assuming we want to get the Name column, we can extend this formula using the data link concept explained in the FindRow article to something like

FindRow(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date])[Name]

This should give us something like "John Doe". We can also make sure that if there's an error then an appropriate string is displayed by putting an Iferror around the above formula like so:

IfError ( FindRow(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date])[Name] , "No bookings")

To get the second row, we can use the GetRow function, by passing a filter as the first parameter, like so:

GetRow(Filter(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date]), 1, FindRow(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date]))

This should give us the second row. Please note that while this formula looks long and complicated, most of it is just copy and paste of the filter string we incrementally built above.

Now we can make sure that this shows the second name by appending [Name] to the end, and ensuring nothing shows up if it's an error, by putting an IfError around it like so, like so:

IfError ( GetRow(Filter(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date]), 1, FindRow(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date]))[Name] , "")

This should give us something like "Mary Joe", and if the second row does not exist, it should give us "".

The last thing we want to do is construct something like "and 3 more", if there are 5 rows. We do this by getting a count of rows and subtracting 2 for the two rows for John and Mary. We do this by using Rows() function to count the number in a filter -

Rows(Filter(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date]))-2

Now, we can create a string to say something like "and 3 more" using an IF expression to check if the rows are greater than 0 to see if there are any rows matching this criteria at all, like so:

IF( Rows(Filter(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date]))-2 > 0 , "and " & Rows(Filter(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date]))-2 & " more", "")

Finally we can put all of these together to get a formula that gets you the string you're looking for:

=IfError(FindRow(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date])[Name], "No bookings") & " " & IfError(GetRow(Filter(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date]), 1, FindRow(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date]))[Name], "") & IF(Rows(Filter(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date]))-2, " and " & Rows(Filter(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date]))-2 & " more", "")

Note that while this expression is long, most of it just repeating the filter string. So, if we had saved some of these results in a screen variable, we could have made the expression much smaller and easier to read. I chose to create it this way to show it working independently.

Edit: to expand on the point above, if we had saved the filter =Filter(Bookings,"Bookings[table]=% AND Bookings[date]=%",$[table_title],$[Date]) in a variable MyBookings, the the above expression would become something like:

=IfError(FindRow($[MyBookings])[Name], "No bookings") & " " & IfError(GetRow($[MyBookings], 1, FindRow($[MyBookings])[Name], "") & IF(Rows($[MyBookings])>2, " and " & Rows($[MyBookings])-2 & " more", "")

Hope this helps!