I’m using an action button to change some data in a table. There are 4 fields. I want 3 of them to be optionally updated. If no entry in on of the prompts, I want the value left unchanged.
I’ve set the 3 fields as optional.
Then I use this as my update statement:
UPDATE schedule.schedule_jobs
SET email= (case when coalesce({{email}},'') <> '' then {{email}} else email end),
email_subject =(case when coalesce({{subject}},'') <> '' then {{subject}} else email_subject end),
email_body = (case when coalesce({{body}},'') <> '' then {{body}} else email_body end) ,
include_dashboard_link = {{include_link}}
WHERE id = {{id}}
This is the UI with the error:
Is it possible to do this?
Frustratingly, it keeps removing the optional bits making testing harder.
You need to surround the stanzas that are optional with [[double square brackets]].
Try rewriting the statement like this:
UPDATE schedule.schedule_jobs
SET include_dashboard_link={{include_link}}
[[, email={{email}}]]
[[, email_subject={{subject}}]]
[[, email_body={{body}}]]
WHERE id = {{id}}
That worked - thanks.
This has all become quite sophisticated. We now have users being able to schedule dashboards to be emailed as pdf attachments and set the filters as needed.
Just need to move their Postgres DB to Docker so I can use something better than Windows Task Scheduler to do the scheduling.
Spent part of the weekend testing a dozen different community tips options to see what still works without annoying limits. The only one I ended up keeping in my bookmarks is dbbii．uspiix．com/p/d6moG. Been checking it for 9 months and it stays consistent without nagging you every two clicks.
