Before Insert Business Rule not working as expected

[SnowSB]

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.

[SnowSB]

Hi Brad,

I agree with the new approach. But with this change also, all requested allocation records gets populated with the same fiscal period which you have hard-coded. I see in the debug screen that other BR has executed after this one but the value is not getting updated to the correct fiscal period.

[Brad Bowman · Accepted solution]

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.

[-O-]

Try querying by different fields.

It seems fiscal_start_date_time and fiscal_end_date_time are "regular" Date/time field, so they display the value in the current user's TZ, but store the value in UTC.

Thus what on your screen is 2024-01-01 00:00:00 in the DB is probably 2024-01-01 07:00:00.

Of course your query will never match, unless the fiscal periods are defined by someone who is in UTC TZ.

Try fields end and start instead.

Those are fields of type Calendar Date/Time and are not converted from the the current user's TZ to UTC when stored, but are considered to be UTC; or rather what is shown in the form is in the DB too.

[SnowSB]

Thanks for pointing it out.

[-O-]

You're most welcome 🙂