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

Brad Bowman · ‎11-25-2023

I'd love to help, but your screenshots are extremely tiny and illegible. Post the script using the insert code </> icon. Is this Business Rule running on the requested_allocation table?

SnowSB · ‎11-25-2023

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

    // Add your code here
	
    var AllocStartDate = new GlideDateTime(current.start_date) + " 00:00:00";
    var AllocEndDate = new GlideDateTime(current.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=445period");
    fsp.query();
    if (fsp.next()) {
        current.u_fiscal_period = fsp.sys_id;
      
    } 
})(current, previous);

SnowSB · ‎11-25-2023

Thanks, I have pasted the code here. This BR is on Requested_allocation table. fiscal period is custom field. Start date/End date is OOB field.

This is Before Insert/Update BR and the strange thing is this BR is executing well on Before update. But I want to populate Fiscal period while creation of this record. But it's not working on Insert. I have also tried to change it after insert/update. Same issue. After update is working but after insert is NOT working.

Is there any other way I can achieve this requirement ?

Sandeep Rajput · ‎11-25-2023

@SnowSB Try updating the script as follows and see if anything changes.

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

    // Add your code here
	
    var AllocStartDate = new GlideDateTime(current.start_date) + " 00:00:00";
    var AllocEndDate = new GlideDateTime(current.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=445period");
    fsp.query();
    if (fsp.next()) {
        current.u_fiscal_period = fsp.getValue('sys_id');      
    } 
})(current, previous);