Ah, that makes sense. Some of these solutions seem so easy that I want to kick myself for missing them.
If I may, I have one other question. I have been working with the following SQL statement and for some reason I keep getting a "cannot cast type boolean to date" error.
SELECT *
FROM shipping
WHERE CASE
WHEN {{ time.value }} = 'day'
THEN ({{ !date79.value }} OR ship_date = {{ date79.value }})
WHEN {{ time.value }} = 'week'
THEN ship_date BETWEEN {{ moment(new Date()).startOf('day') }} AND {{ moment(new Date()).add(7, 'days').endOf('day') }}
WHEN {{ time.value }} = 'month'
THEN EXTRACT(MONTH FROM shipped::date) = {{ select62.value }}
END;
So SQL is not my strenght, but I think the error may be ocurring because of this condition:
({{ !date79.value }} OR ship_date = {{ date79.value }})
From a quick ChatGPT prompt, I got the following:
The expression {{ !date79.value }} evaluates to a boolean (true/false), and SQL cannot compare or mix booleans with dates in a valid expression. Specifically:
{{ !date79.value }} negates the date79.value value (likely a boolean), which is not directly compatible with the rest of the SQL logic.
SQL expects all expressions in the THEN clause to produce valid, comparable conditions, not mixed data types.
How to Fix
You need to refactor the condition to properly handle whether date79.value is empty or has a valid value. Assuming date79.value is a date or null:
SELECT *
FROM shipping
WHERE CASE
WHEN {{ time.value }} = 'day' THEN
({{ date79.value ? `ship_date = '${date79.value}'` : 'TRUE' }})
WHEN {{ time.value }} = 'week' THEN
ship_date BETWEEN {{ moment(new Date()).startOf('day') }} AND {{ moment(new Date()).add(7, 'days').endOf('day') }}
WHEN {{ time.value }} = 'month' THEN
EXTRACT(MONTH FROM ship_date::date) = {{ select62.value }}
END;
I have been trying to set up my table to use the built in add row function and also make the cells editable. So I have a query set up in the table Add-Ons, under Save Action as in the screenshot below.
I have added the Add Row button to the table toolbar.
Under Save Actions, I have the query for editing cells set up like this:
And I have the query for adding a row set up like this:
When I tested the Add Row function, absolutely nothing happened. No record got inserted. The little blue triangles in the corners of the cell did not even disappear. I tested editing cells and that did not work either.