Query interaction with Boolean inconsistent

Wonder if I’m doing it wrong, or this is just the way it works…

I have a two Google Sheets: Sheet A is the target of a Glide FORM. I’ve added a column (Column K) that is not filled by the form.

Sheet B has a simple Query that pulls all the data from Sheet A where Column K <> ‘TRUE’.

Works fine on the Sheets side… I type TRUE into column K and that row disappears from Sheet B.

In the App, users can essentially delete a FORM submission from Sheet B by checking a box and submitting with a Button Bar (there’s logic in the ACTION to ensure the box is checked, as a sort of safety against unwanted deletions).

Upon submitting, the COLUMN TYPE, the DATA editor converts itself from Text to Boolean. That puts a checkbox into Column K in Sheet A and the query returns #NA (Query completed with an empty output).

Google Sheets shows TRUE in the formula bar, so clearly it’s interpreting the Boolean correctly. But All of the other rows appear to be getting something in them that’s causing the query to trip.

Here are the relative screen shots.

Screen Shot 2021-02-09 at 9.20.07 PM Screen Shot 2021-02-09 at 9.22.34 PM

What happens if you remove the single quotes from TRUE or make TRUE lowercase (true)?

1 Like

Boom! That did it. And I see why… thanks so much!

I’ll just add that Column K received some Data Validation rule - which I believe must have come from Glide. So when I unselect the checkbox in the Google Sheet, I get a FALSE, but it also throws a Data Validation error. That error is of no consequence to me, but I removed Validation from the column and it reappears next time the app writes to it. Sheets seems to think it’s getting values from a drop-down list, but it’s really a checkbox, so something’s a little goofy on that side. No matter though, its working correctly for me.

1 Like

Final word on this: Don’t use TRUE/FALSE in query formulas fed by a boolean column.

I changed the logic so that my ACTION inserts the word “DELETE” rather than “TRUE” - by doing this, I can avoid letting Glide change the column to Boolean, which doesn’t work well with Query in Sheets.

Now Glide just sees a text string and leaves it alone. And Query is happy to pass judgement on that string. Half a day later, my phantom-delete (without really deleting) works. I can suppress a form entry yet, still recover it and track who did what.