Automatically create related records based on date fields?

[CharlesR1]

Hello all,

On our Purchase Order table(proc_po), we will be creating PO's for service provisions (not physical products), and these will be charged on a per month basis.

We have been asked to create PO Line Items (on the table 'proc_po_line_items') based automatically on the start and end time fields of the PO.

For example, if we save a new PO with the dates 01-01-2017 to 31-12-2017, we need 12 Purchase Order Line Items to be created automatically, one for each month with the correct dates showing on Start and End date fields on the line items table. (i.e.   01-01-2017 > 31-01-2017 on the first line item, 01-02-2017 > 28-02-2017 on the second etc...)

To complicate thing a little, the start date may not be from 1st of the selected month. for example, if we have the dates 12-01-2017 - 11-01-2018 then the dates on the Line Item records would need to be 12-01-2017 > 11-02-2017 on the first line item, 12-02-2017 > 11-03-2017 on the second etc... .

Can anyone point me in the right direction for creating a business rule that would achieve this?

Thanks very much,

Charles

[CharlesR1]

Hello Laurent,

Thanks again for your help with the above request - I wonder if I could pick your brains about a new requirement along the same lines?

There is a new request to create the Line Items not on the basis of day/month & year schedule, but on a month basis based only on the start & end dates.

For example, if a record starts on 1st Jan and runs to 15th April, we need to create four line items - one for Jan, Feb, March and April. Basically, if any of the time frame crosses over into another month, then we need a line item.

Any ideas?

Thanks so much,

Charles

[LaurentChicoine]

Hi Charles,

Will these line items, representing the different months, be represented with Start: first day of the month, End: last day of the month? Or will it be a new field (string or choice field) that will have the month this line is for stored inside of it?

[CharlesR1]

Hi Laurent - thanks for getting back to me.

Yes so we will be updating the existing line.u_start_day and line.u_end_day fields with the start of the month (01) and end of the month (28,30,31), for all those months that are complete. We will also be looking to create a new string field with the appropriate name of the month, if this is possible.

So for example, if we save a new PO with the dates 15-01-2017 to 25-03-2017, we need 3 Purchase Order Line Items to be created automatically, one for each calendar month with the correct dates showing on Start and End date fields on the line items table. (i.e.   15-01-2017 > 31-01-2017 on the first line item (January), 01-02-2017 > 28-02-2017 on the second (February) and 01-03-2017 > 25-03-17 on the last (March)).

I've reduced your code to that shown below, but these still create the records with the dates 15-01-2017 > 14-02-2017, 15-02-2017>14-03-2017, 15-03-2017>25-03-2017 - i.e. the full month from the start date, not split into the actual calendar months.

function createLinesServer(){

var startGDT = new GlideDateTime(current.u_start_date);

      var finalEndGDT = new GlideDateTime(current.u_end_date);

      while(startGDT.compareTo(finalEndGDT) <= 0){ //Final end date is after start date

              var line = new GlideRecord('proc_po_item');

      line.initialize();

      var endGDT = new GlideDateTime(startGDT);

          endGDT.addMonthsUTC(1);

      if(finalEndGDT.compareTo(endGDT) == -1){

              //End date is after final end date

              //Should use the final end date

              line.u_end_day = finalEndGDT;

      }

      else{

              line.u_end_day = endGDT;

      }

      line.insert();

              startGDT.addMonthsUTC(1);

}

    action.setRedirectURL(current);

    current.update();

}

Thanks so much again for all your help

Charles

[LaurentChicoine]

Hi Charles,

Here you go, I've fully adapted the code for it to generate what you asked. If you want to offer both maybe add a parameter in a field or create a second UI Action.

I've added a choice field on the proc_po_item table to store the month (integer field with choices with the value of the month number and the name of the month as a label, script only work with month number so it is easier that way). The advantage of an integer field is that when sorting by the column, the order will be the order of the month instead of alphabetical order. For this example I named the field u_month.

function createLinesServer(){
       var startGDT = new GlideDateTime(current.u_start);
       var finalEndGDT = new GlideDateTime(current.u_end);

       while(startGDT.compareTo(finalEndGDT) <= 0){ //Final end date is after start date
               var line = new GlideRecord('proc_po_item');
               line.initialize();

               line.purchase_order = current.getUniqueValue();

               line.u_month = startGDT.getMonthUTC();

               startGDT.setDayOfMonthUTC(1);
               line.u_start = startGDT;

               var endGDT = new GlideDateTime(startGDT);
               endGDT.setDayOfMonthUTC(endGDT.getDaysInMonthUTC());
               line.u_end = endGDT;

               line.insert();
               startGDT.addMonthsUTC(1);
       }
       action.setRedirectURL(current);
       current.update();
}

[CharlesR1]

Hi Laurent - so easy when you know how! Thank you again so much - this is superb.

Charles