Greetings all. New Retool app builder here...and rusty coder. You've been warned!
I'm working on a filter connected to a table driven by a Query JSON with SQL linked to a Google Sheet.
The filter (embedded in a container) includes a text search box (which works fine) and a checkbox called "FundedCheckbox." When the box is unchecked, all the records should display. When it's checked, all the "Funded" items should display.
For some reason, it's refusing to cooperate and I can't figure out why. All the records show regardless of the checkbox.
The problem seems to be embedded in the _.isEmpty function I've deployed to test if the checkbox is checked; if I delete that and just tell the SELECT to show records that are funded, it works fine. So...my syntax is messed up?
Here's the SQL:
SELECT * FROM {{All_UAccess_COH.data}}
WHERE ({{_.isEmpty(searcher.value)}} OR [Proposal_Title] ILIKE {{'%' + searcher.value + '%'}} OR [Sponsor_Name] ILIKE {{'%' + searcher.value + '%'}})AND ({{_.isEmpty(FundedCheckbox.value)}} OR Proposal_Status_Description = 'Funded')
One notable detail: If the checkbox is unchecked and I mouseover the _.isEmpty(FundedCheckbox.value code, the popup tells me the value is "false." If I check the box, it switches to "true." Counterintuitive but a google search suggests I'm not the first person to be confused by how _.isEmpty works.
Curiously, though, if I click in the SQL code box and pop up the nifty SQL Prepared Statement, it tells me that {{_.isEmpty(FundedCheckbox.value)}} is evaluating to always "true" whether or not the box is checked.
I have a feeling I'm missing something obvious, but I'm not seeing what it is. Any pointers would be most appreciated.
Cheers,
ken.