Hello! I'm brand new to Retool and I'm having a heck of a time with some basics. I have a simple database of transactions, and I want to query it and display the items in a list collection. I have two filter fields, a checkbox for "Include Paid Items" and a multi-select list box to indicate the Accounts to include.
For the checkbox, I want it to return TRUE if checked, and FALSE if not checked (simple toggle).
For the Account multi-select, I want to fetch from the database all records where the account column is any of the items selected in the multi-select control. I think I need to use the ANY function but I can't get that to work.
This seems like super-basic stuff but I can't seem to get either filter control to work properly. Can you help?
Thanks in advance!
I got it to work using = ANY ({{...}}) for the multi-select box. Still struggling to have a checked checkbox give me TRUE and unchecked pass me FALSE...
Hey @JeremyNC!
If your checkbox is setup similar to mine (a single option), you could do something like:
{{ checkbox1.value[0] == "true" }}
to return true if checked or false if not checked.
If your value isn't "true", you could do something like:
{{ checkbox1.value[0] == "paid" }}
for the same effect!
Thanks for this! I'm not sure how I would apply it though I think I follow what you're doing. I'm probably not explaining what I'm trying to do very well. Let me try again...
In my database I have a boolean column called paid. By default, in my collection I only want to return rows where paid = false. But, I might want to see the others so I added a checkbox to "Include Paid Items." If checked, then I would want all rows returned, not just those where paid = false.
So I am trying to do something like this pseudocode:
SELECT * from purchases WHERE {{ if(checkbox.value == false) { echo "paid = false and "; } }} account = ANY( {{multiselectAccounts.value}}
So if unchecked, I want the WHERE clause to be:
WHERE paid = false and account = ...
If it's checked, I want the WHERE clause to be:
WHERE account = ...
Does that make sense?
Definitely!
The query should look something like this:
select * from purchases
where ( {{ checkbox1.value[0] }} OR paid = false )
This selects ALL purchases when the {{ checkbox1.value[0] }} evaluates to true (so basically, when "Include Paid Items" is checked) and only UNPAID purchases if the checkbox isn't checked.
It's working for me 
So let me know how it goes for you!
