Before Insert Business Rule not working as expected

SnowSB · ‎11-25-2023

Hi,

I am trying to populate the 'fiscal period' on Requested allocation based on start date and end date through before Insert/Update BR but it's not working. This BR is not running at all. Can someone please help me with this ?

Requested allocation record gets created when we submit resource plan.

Brad Bowman · ‎12-04-2023

This is likely related to the timezone offset pointed out below. To work with the other field formats, the script would look like this:

(function executeRule(current, previous /*null when async*/ ) {
	var AllocStartDate = new GlideDateTime(current.start_date)
	AllocStartDate = AllocStartDate.getDate().toString().replace("-","") + "T000000";
    var AllocEndDate = new GlideDateTime(current.end_date)
	AllocEndDate = AllocEndDate.getDate().toString().replace("-","") + "T235959";
    var fsp = new GlideRecord('fiscal_period');
    fsp.addEncodedQuery("start_date_time<=" + AllocStartDate + "^end_date_time>=" + AllocEndDate + "^fiscal_type=quarter");
    fsp.query();
	gs.addInfoMessage("Allocation Date Range " + AllocStartDate + ' - ' + AllocEndDate)
    if (fsp.next()) {
        current.u_fiscal_period = fsp.sys_id;
        gs.addInfoMessage("Inside if : " + current.number);
    } else {
        gs.addInfoMessage("Inside else : " + current.number + " : " + current.start_date + " : " + current.end_date);
        //gs.addErrorMessage("Due to allocation mismatch, could not populate fiscal period.");
    }
})(current, previous);

If this still isn't working for all fiscal periods in your environment, I'll check it out in your PDI and likely go back to the other date fields with a timezone offset, if that's the issue.

View solution in original post

SnowSB · ‎12-01-2023

Thanks Brad,

I have updated my code as below but the issue is if there are 2 requested allocation records, then system is populating fiscal period. But if it is more than 2, system is always leaving 1 record blank for fiscal period.

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

    // Add your code here

    var ra = new GlideRecord('requested_allocation');
    ra.addQuery('resource_plan', current.sys_id);
    ra.query();
    while(ra.next()) {
		var AllocStartDate = new GlideDateTime(ra.start_date) + " 00:00:00";
                var AllocEndDate = new GlideDateTime(ra.end_date) + " 23:59:59";
                var fsp = new GlideRecord('fiscal_period');
                fsp.addEncodedQuery("fiscal_start_date_time<=" + AllocStartDate + "^fiscal_end_date_time>=" + AllocEndDate + "^fiscal_type=quarter");
                fsp.query();
                if (fsp.next()) {
                    ra.u_fiscal_period = fsp.sys_id;
					ra.update();
                }
gs.addInfoMessage("New BR :  Fiscal Period : " + ra.u_fiscal_period);
        gs.log("Resource Plan Test : " + ra.number);
        gs.addInfoMessage("New BR Inside");

})(current, previous);

(BR on Resource Plan table)

Running After Insert at order 999

SnowSB · ‎12-01-2023

Brad Bowman · ‎12-02-2023

Your test cases may have lead you to the wrong conclusion as in both cases the same date range is the one without a Fiscal Period attached. Is your Fiscal Period FY24:Q1 configured with the correct Start date time? If you manually filter the Fiscal Periods list view do you get a record?

Can you re-create this issue with a Resource Plan 2024-02-01 to 2024-04-30?

SnowSB · ‎12-02-2023

Hi Brad,

Yes, from the above screenshot it looks like the issue is with Fiscal period, but when I applied that filter on the table it looks fiscal period FY24:Q1 is set correctly and returning the result.

I did another test with your suggested period but same issue. 1 out of 3, requested allocation fiscal period is blank.

Brad Bowman · ‎12-03-2023

Your new script on the resource_plan BR is probably fine, but in my instance I just added one line to set the fiscal period to one that I knew was wrong, then the update causes the other BR to set it to the right one.

(function executeRule(current, previous /*null when async*/) {
	var ra = new GlideRecord('requested_allocation');
    ra.addQuery('resource_plan', current.sys_id);
	ra.query();
	while (ra.next()) {
		ra.u_fiscal_period = '2bf220aa745b4510f877f61483c39911'; //a sys_id of a real record that can't be right, like FY21
		ra.update();
	}
})(current, previous);

The main advantage to this approach is that you don't have 2 scripts to maintain if you end up changing the logic in the GR query or whatever. You could also confirm if one of them gets populated to the wrong one and stays that way, or is still empty. In my instance all of the allocation records are updated to the correct one.