Newbie looking for help with basic database querying

💬 Mobile
1

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.

Screenshot 2023-04-04 at 10.49.52 AM
Screenshot 2023-04-04 at 10.49.52 AM419×857 46.9 KB

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!

2

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

3

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!

4

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?

5

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 :slight_smile: So let me know how it goes for you!