Adding Days to Date

Questions related to customising nuBuilder Forte with JavaScript or PHP.

Adding Days to Date

Unread postby pmjd » Mon Mar 29, 2021 9:38 pm

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
pmjd
 
Posts: 76
Joined: Fri Mar 12, 2021 7:08 pm
nuBuilder Version: 4.5

Re: Adding Days to Date

Unread postby kev1n » Mon Mar 29, 2021 9:54 pm

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#"]);
kev1n
nuBuilder Team
 
Posts: 2012
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Adding Days to Date

Unread postby pmjd » Tue Mar 30, 2021 11:21 am

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.
pmjd
 
Posts: 76
Joined: Fri Mar 12, 2021 7:08 pm
nuBuilder Version: 4.5

Re: Adding Days to Date

Unread postby kev1n » Tue Mar 30, 2021 4:04 pm

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)
kev1n
nuBuilder Team
 
Posts: 2012
Joined: Mon Oct 15, 2018 2:13 am
nuBuilder Version: 4.5

Re: Adding Days to Date

Unread postby pmjd » Wed Apr 28, 2021 9:02 pm

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.
pmjd
 
Posts: 76
Joined: Fri Mar 12, 2021 7:08 pm
nuBuilder Version: 4.5


Return to Custom Code

Who is online

Users browsing this forum: No registered users and 9 guests

cron