Business rule to compare between two different fields in two different tables.

[test1231998]

I wanted to write a business rule to compare between 2 different fields on 2 different tables.

I have 2 tables table_1 and table_2 with fields total_days and working_days.

The total_days field in table_1 should be less than working_days field in table_2

So whenever I create new record from table_2 if the above condition is not met then the request shouldn't be submitted.

[Pranav Bhagat]

Write a before br

	var gr = new GlideRecord('table1');
gr.addQuery('', current.<unique value>); // add a unique value to identfy the correct record in table 1
gr.query();
if(gr.next()){
 if(gr.total_days <current.workingdays){
	 current.setAbortAction(true);
 }
}

Regards

Pranav

[Pradeep Sharma]

Hello Deepika,

Create a Before business rule in Table_2 with filter condition as total_days (**you can get total_days via dot-walking) is | greater than | working_days and under action tab select Add message and abort checkbox to true.

No scripting is required.

[sriram35]

Hi Deepika,

Please refer below thread it will help you,

https://community.servicenow.com/community?id=community_question&sys_id=a2801369dbdcdbc01dcaf3231f961...

Hope this helps!

Thanks,

Sriram

[reshmapatil]

Hi Deepika,

Create before insert business rule on Table2.

In Filter Conditions, add working_days is not empty.

Script:

var gr = new GlideRecord(Table1);

gr.addEncodedQuery('total_daysISNOTEMPTY');  //can add more filters as per your need

gr.query();

if(gr.next()){

// If working_days is greater than total_days field

if(gr.total_days>current.working_days)

current.setAbortAction(true);

else{

//set field values which you want to populate during the insertion on record in Table2

}

}

Have few Quetions: 

1. Do you have any field on Table2 that is referencing to Table1?

2. Which type are of total_days and working_days fields?

Please mark correct & helpful if that works for you.

Thanks.