Set a future date for a column

💬 App Building
1

I currently have an app that I am building for a client, it's a defect tracking system that includes a ticket stage for all of the records as well as a review date once the ticket has been closed.

Once the ticket enters the closed state the review date needs to be updated to 3 weeks or 3 months in the future to ensure that it is revisited one the fix has been fixed and release to the live environment.

I can set a column to a date format but it defaults to now()

How can I dynamically update the column to be set to a future date?

2

Hi @James_Gould,

You can do it a few ways depending on how your app is set up and what DB you're using. I'll make an assumption that you're resolving the ticket but updated a field in the DB and the field is called resolved. When you mark it as resolved via a button or something in your app, you are running a query against the DB to say UPDATE tbl_tickets SET resolved = TRUE WHERE ticket_id = 123. You can expand the query at this point to something like

UPDATE tbl_tickets
SET
    resolved = TRUE,
    review_date = NOW() + INTERVAL '3 weeks'
WHERE
   id = 123

You could also adjust it to be INTERVAL '3 months' if that's what you needed. If you're using MySQL instead of Postgres it would just be INTERVAL 3 WEEK or INTERVAL 3 MONTH (without quotes, in caps)

1 Like
3

Hi Mike,

This does set the review_date correctly, however is only updating that one id how can I get it to use my selected record (I'm using forms to update each of my records)

Thanks in advance

4

Hi @James_Gould,

Is the ID (or some unique value) stored in your form data somewhere? You can use that to set the Update WHERE property dynamically.