Hello there. Here’s the formula I am using to create a select control which is a list of companies filtered for just the company projects that have a checkmark in an “Initiate Invoce” column:
The idea is to use the checkmarks to identify which projects I want to crank out an invoice for (because there are unpaid hours over a certain amount). I simply check that box in the projects table and then go over to an invoice generator and apply the select control to winnow down all customers to just the ones that have a project with a check.
It seems to actually produce the right result BUT I get an error nonetheless. See attached screenshot.
Thanks very much.
Well, I was wrong. It coincidentally produced the right answer at first, but fails in subsequent testing…
I saw that you have a little problem with your filtering formula. Don’t worry, I’m here to help!
It looks like you’re trying to filter the projects to show only those that have “Initiate Invoice” set to “true”, and then sorting them in reverse order.
The formula you used is almost correct, but there are some syntax errors. Here is the correct formula:
filter(sort(filter(Projects, Projects.[Initiate Invoice] = true), Projects.[Company], descending), Projects.[Company], descending)
What happens here is that we use the “filter” function to select only those projects that have “Initiate Invoice” set to “true”. Then we use the “sort” function to sort these projects by company name in descending order. And finally, we use the “filter” function again to select only those projects that have “Company” set.
I hope this helps you solve your problem! If you have any other questions, please let me know.
Have fun with Coda!
@codafrench - Thierryvm
Hi Thierry. Thanks so much! I still can’t get this to work though.
To keep it a little simpler, let’s ignore the sorts completely. I can add that back in later.
Don’t I need to start with the list I want to use in the control as selectors? That would be Projects.[Company]
That would be, in my Company Selector control:
So it would look like: Projects.Company.Filter(Projects.[Initiate Invoice]=true)
But this doesn’t work either. I feel like I’m missing something really obvious. The dot operators get a little confusing to me, so I may be misusing them here.
Thierry, I think I just figured it out. The following seems to work, and it’s really simple:
So your problem is solved? Please share your progress with the community, as it will help others