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

[LaurentChicoine]

Hi Charles,

I did find your requirement interesting, so I decided to quickly build the Business rule for it.

So I installed the plugin and saw that there where no start and end time field. So I assumed they where custom Date field (not date/time, but date only). SO I created a Start (u_start) and End(u_end) fields on both tables.

I did not handle cases where the dates are updated and things like that, I mostly wanted to show you the script behind

Table: Purchase Order [proc_po]

When: After

Insert: true

Update: true

Condition: Start is not empty, End is not empty, (Start changes or End changes). The condition is not quite accurate as a time change would not delete or update previous PO but create new ones.

Script:

(function executeRule(current, previous /*null when async*/) {

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

       while(startGDT.compareTo(finalEndGDT) == -1){ //Final end date is after start date
               var line = new GlideRecord('proc_po_item');
               line.initialize();
               line.purchase_order = current.getUniqueValue();
               line.u_start = startGDT;

               var endGDT = new GlideDateTime(startGDT);
               endGDT.addMonthsUTC(1);
               endGDT.addDaysUTC(-1);
               if(finalEndGDT.compareTo(endGDT) == -1){ //Final end date is earlier than end date
                       //Should use the final end date
                       line.u_end = finalEndGDT;
               } 
               else{
                       line.u_end = endGDT;
               }

               line.insert();

               startGDT.addMonthsUTC(1);
       }

})(current, previous);

I used the GlideDateTime API to build the logic (GlideDateTime - ServiceNow Wiki )

With it I simply calculate the end date of the line by adding a month to the start date and then substract a day. After that I add a month to the start date and repeat the whole thing as long as the start date comes before the end date. To cover cases where a line could be for less than a full month, if the final end date is earlier than the calculated end date, than it is the final end date that is used instead of the calculated end date.

If you have any question, or the script is not fully working (only tested it very quickly), don't hesitate.

[CharlesR1]

Hello Laurent - that is fantastic - thank you so much for your help.

This works really well, however there are a couple of things - once the new line items are created, no other business rules seem to operate on the record. Not sure why this would be the case. If I then create additional new line items manually then those business rules do work.

Secondly, since I posted the question, we have been asked to add a new field 'Frequency' to the PO record. It turns out that not all the Line Items will be per month - some will be Daily and others Yearly. Is there a way for us to reference the new 'u_frequency' field and create the appropriate number of line items using the same script? (the values for u_reference are 'day', 'month' and 'year').

Thanks again for your help with this.

Charles

[LaurentChicoine · Accepted solution]

When you say no other business rules operate on the record, what do you mean, are they business rule on the proc_po table or on the proc_po_item table? The script I proposed was pretty simple in the sense that it only cared about the relation and the start and end date, so it did not populate other fields even if mandatory (default values should however be respected). I don't know if you added the value to these fields inside the script, but you should probably look to do so and check the conditions of the business rules running. It's kind of hard to troubleshoot without knowing what you have.

As for your frequency requirement, you could change it to this:

(function executeRule(current, previous /*null when async*/) {

       var startGDT = new GlideDateTime(current.u_start);
       var finalEndGDT = new GlideDateTime(current.u_end);
       var frequency = current.u_frequency;

       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_start = startGDT;

       var endGDT = new GlideDateTime(startGDT);

       if(frequency == 'month'){
               endGDT.addMonthsUTC(1);
       }

       else if(frequency == 'year'){
               endGDT.addYearsUTC(1);
       }

       if(endGDT.compareTo(startGDT) != 0){
               //PO line of more than one day
               endGDT.addDaysUTC(-1);
       }

       if(finalEndGDT.compareTo(endGDT) == -1){
               //End date is after final end date
               //Should use the final end date
               line.u_end = finalEndGDT;
       }
       else{
               line.u_end = endGDT;
       }

       line.insert();

       if(frequency == 'day'){
               startGDT.addDaysUTC(1);
       }

       else if(frequency == 'month'){
               startGDT.addMonthsUTC(1);
       }

       else if(frequency == 'year'){
               startGDT.addYearsUTC(1);
       }

}

})(current, previous);

I was not sure how you wanted to deal with daily, I assumed the PO had identical start date and end date.

I also modified the script to accept PO Line of a single day as before if you had start date 2016-01-01 and end date 2016-02-01, you would get a single PO of 2016-01-01 to 2016-01-31. Now you also get the PO for 2016-02-01 to 2016-01-01.

If you eventually have the need for weeks you could follow the pattern in the script and use the function addWeeksUTC(int). Also if you eventually have the need for a different occurrence lets say every 2 years then you could add a new field and that field could be used instead of the "1" in the addXXXXUTC(int) function.

[CharlesR1]

Hello Laurent - thank you so much for this. It works perfectly. You have made my day.

I will need to troubleshoot the other business rules to see why they wont run on Insert - however as you say I may just populate everything from this one business rule.

Thanks again,

Charles