|
While creating a custom record we may be required to check if the newly created record overlaps the time period with any existing custom record of same type. In Custom record we have 2 fields called “Start Date” and “End Date” which specifies the time period. |
|
So on save record, we need to check these two fields. We need to create a saved search for that custom record type which will include “Start Date” and “End Date” along with other required fields as the result. Now in “On Record Save” client event we need to filter that saved search by new “Start Date” and “End Date” values. But in filter we can’t use syntax like: //This is start date value in new record var startDate=nlapiGetFieldValue(“custrecord_start_date”); var filters = new Array(); filters[0] = new nlobjSearchFilter( ‘custrecord_start_date’, null, ‘before’, startDate ); as we need the same filed “custrecord_start_date” multiple time in filter. Because for overlapping there can be multiple cases like: Existing Date: 18/02/2013(Start Date) – 27/08/2013 Possible overlapping new dates: Case 1: 26/01/2013 – 20/09/2013 (new time period is a super set of existing period) Case 2: 17/05/2013 – 25/12/2013 (Start Date is within the period and End Date is after existing period) Case 3: 01/01/2013 – 20/07/2013 (End Date is within the period and Start Date is before existing period) Case 4: 03/05/2013 – 20/07/2013 (new period is subset of existing period) Case 5: it includes if any of the date is on the same date(any of Start/End date) of any existing record. So the approach is like: |
//Get Start Date and End Date field value
var startDate = nlapiGetFieldValue("custrecord_strt_date");
var endDate = nlapiGetFieldValue("custrecord_end_date_custom");
//Set Filter expression to filter any record which overlaps the contrat period
var filterExpression = [
[
//Case 1
[
['custrecord_strt_date', 'within', startDate, endDate],
'and',
['custrecord_end_date_custom', 'within', startDate, endDate]
],
'or',
//Case 2
[
['custrecord_strt_date', 'within', startDate, endDate],
'and',
['custrecord_end_date_custom', 'notwithin', startDate, endDate]
],
'or',
//Case 3
[
['custrecord_strt_date', 'notwithin', startDate, endDate],
'and',
['custrecord_end_date_custom', 'within', startDate, endDate]
],
'or',
//Case 4
[
[
['custrecord_strt_date', 'before', startDate],
'and',
['custrecord_strt_date', 'before', endDate]
],
'and',
[
['custrecord_end_date_custom', 'after', startDate],
'and',
['custrecord_end_date_custom', 'after', endDate]
]
],
//Case 5
'or',
['custrecord_strt_date', 'on', startDate],
'or',
['custrecord_strt_date', 'on', endDate],
'or',
['custrecord_end_date_custom', 'on', startDate],
'or',
['custrecord_end_date_custom', 'on', endDate]
],
'and',
['internalidnumber', 'notequalto', recordId],
'and',
['custrecord_customer', 'is', customer]];
//Search contract record with above criteria
var contractResults = nlapiSearchRecord("customrecord_contract_pricing_custom_rec", null, filterExpression, null);