Adding Days to Date

[pmjd]

Hello,

I know there is some code on the nuBuilder github pages but I'm not sure what to do with it/where it is meant to go.

I have an input from with a date field "date_prep" for when something was made. On record save I want to add x days (the days vary and are dependant on the item made, which is from another reference table that I access via a lookup on the form to pull through item name and a couple of related bits of info, these are copied across to the form and saved as part of it to new fields on the form via the nuSetFormValue function) take the prep_date and add the days to it to make expiry_date.

Can anyone pint me in the right direction?

Thanks,
Paul

[kev1n]

Hi Paul,

Run an update query after saving the record. The Hash Cookie #add_days# would contain the number of days to be added.

Add this code to the AS (After Save) PHP event (replace the table name, columns, hash cookies with yours)

Code: Select all

$update = "UPDATE your_table SET expiry_date = DATE_ADD('#date_prep#', INTERVAL #add_days# DAY) WHERE your_table_id = ?";

nuRunQuery($update,["#RECORD_ID#"]);

[pmjd]

Hi kev1n,

Thanks for replying but can't get it to work.

The After Save has the following code (the first $sql part from your previous help)

Code: Select all

$sql = "
   UPDATE sln_prepared
   SET sln_prepared_slnumber = CONCAT('SL',LPAD(sln_prepared_autonumber,4,'0'))
   WHERE sln_prepared_slnumber IS NULL AND sln_prepared_id = ?
";

nuRunQuery($sql, ["#RECORD_ID#"]);

$update = "UPDATE sln_prepared SET sln_prepared_expiry = DATE_ADD('#sln_prepared_date#', DATEINTERVAL #sln_ref_info_expirydays# DAY) WHERE sln_prepared_id = ?";

nuRunQuery($update,["#RECORD_ID#"]);

For the $update here's the complete reference info
Table sln_ref_info holds the reference data, with sln_ref_info_expirydays holding how many days need to be added to the preparation date to make the expiry date.

Table sln_prepared is where the data is entered for each new solution prepared. sln_prepared_date is a nuDate field to enter the date, and sln_prepared_expiry is the field that needs to be updated with sln_prepared_date + sln_ref_info_expirydays.

I also tried

Code: Select all

WHERE sln_ref_info_id = ?

in case I had the wrong table.

It's probably something simple I'm missing.

[kev1n]

As a general debug technique add some nuDebug() calls to your code.

Add

Code: Select all

nuDebug($sql, "#RECORD_ID#");

after each nuRunQuery() and then view the nuDebug Results (CTRL+SHIFT+D) after the queries have run.

Then you can also verify/run the queries in phpMyAdmin ( replace ? with the record id)

[pmjd]

Just incase anyone else is stuck on this, I wasn't able to pull data through from another table as the Hash Cookie way didn't work (the info I was after was not listed as an available Hash Cookie). So instead I pulled the number of days I wanted to add using the nuSetFormValue function, added it to form. There after I was able to get things working. Also DATEINTERVAL didn't work but INTERVAL did. The code below is different from the original because I was testing it out on a seperate table.

Table is date_test, field00 is the original date, field01 is the calculated date, field02 is the number of days to add.

Code: Select all

$update = "UPDATE date_test  SET field01 = DATE_ADD(field00, INTERVAL field02 DAY) WHERE date_test_id= ? ";

nuRunQuery($update,["#RECORD_ID#"]);

Hope this helps anyone else out.