The following function can be used to fetch the data from different records using the query search.
function querySearch(reportAppliedFilter) {
let requisitions = [];
try {
var requisitionQuery = query.create({
type: 'customrecord_cs_other_costed_items'
});
var activityJoin = requisitionQuery.join({
fieldId: 'custrecord_cs_linked_activity'
});
var projectTaskJoin = activityJoin.join({
fieldId: 'custrecord_cs_as_linked_project_task'
});
var projectJoin = requisitionQuery.join({
fieldId: 'custrecord_cs_linked_proj'
});
var statusJoin = requisitionQuery.join({
fieldId: 'custrecord_cs_material_status'
});
var itemJoin = requisitionQuery.joinTo({
fieldId: 'custrecord_cs_ot_costed_item_code',
target: 'item'
});
var branchJoin = projectTaskJoin.join({
fieldId: 'custevent_cs_task_branch'
});
var firstCondition = projectTaskJoin.createCondition({
fieldId: 'startDateTime',
operator: query.Operator.ON_OR_AFTER,
values: [reportAppliedFilter.startDate]
});
var secondCondition = projectTaskJoin.createCondition({
fieldId: 'startDateTime',
operator: query.Operator.ON_OR_BEFORE,
values: [reportAppliedFilter.endDate]
});
var thirdCondition = projectTaskJoin.createCondition({
fieldId: 'endDate',
operator: query.Operator.ON_OR_AFTER,
values: [reportAppliedFilter.startDate]
});
var fourthCondition = projectTaskJoin.createCondition({
fieldId: 'endDate',
operator: query.Operator.ON_OR_BEFORE,
values: [reportAppliedFilter.endDate]
});
if (reportAppliedFilter.depId)
var depCondition = projectTaskJoin.createCondition({
fieldId: 'custevent_cs_task_branch',
operator: query.Operator.ANY_OF,
values: reportAppliedFilter.depId
});
var statusCondition = requisitionQuery.createCondition({
fieldId: 'custrecord_cs_material_status',
operator: query.Operator.ANY_OF,
values: ["3", "7", "9"]
});
var emptyStatusCondition = requisitionQuery.createCondition({
fieldId: 'custrecord_cs_material_status',
operator: query.Operator.EMPTY
});
if (reportAppliedFilter.depId)
requisitionQuery.condition = requisitionQuery.and(
requisitionQuery.or(requisitionQuery.and(firstCondition, secondCondition), requisitionQuery.and(thirdCondition, fourthCondition)), depCondition, requisitionQuery.or(statusCondition, emptyStatusCondition)
);
else
requisitionQuery.condition = requisitionQuery.and(
requisitionQuery.or(requisitionQuery.and(firstCondition, secondCondition), requisitionQuery.and(thirdCondition, fourthCondition)), requisitionQuery.or(statusCondition, emptyStatusCondition)
);
requisitionQuery.columns = [
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_linked_proj',
alias: 'projectId',
groupBy: true
}),
projectJoin.createColumn({
fieldId: 'entityTitle',
alias: 'projectName',
groupBy: true
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_ot_costed_item_code',
alias: 'itemId',
groupBy: true
}),
itemJoin.createColumn({
fieldId: 'itemId',
alias: 'itemName',
groupBy: true
}),
statusJoin.createColumn({
fieldId: 'name',
alias: 'status',
groupBy: true
}),
requisitionQuery.createColumn({
fieldId: 'custrecord_cs_ot_cost_qty',
alias: 'quantity',
aggregate: query.Aggregate.SUM
}),
projectTaskJoin.createColumn({
fieldId: 'title',
alias: 'projectTask',
groupBy: true
}),
branchJoin.createColumn({
fieldId: 'name',
alias: 'projectTaskBranch',
groupBy: true
}),
projectTaskJoin.createColumn({
fieldId: 'id',
alias: 'projectTaskId',
groupBy: true
}),
itemJoin.createColumn({
fieldId: 'costEstimate',
alias: 'itemCost',
aggregate: query.Aggregate.MAXIMUM
}),
];
requisitionQuery.sort = [
requisitionQuery.createSort({
column: requisitionQuery.columns[0],
ascending: true
})
];
let pagedData = requisitionQuery.runPaged({
pageSize: 1000
});
pagedData.pageRanges.forEach((pageRange) => {
let page = pagedData.fetch({ index: pageRange.index });
page.data.results.forEach((result) => {
requisitions.push(result.asMap());
});
});
return requisitions;
} catch (e) {
log.error('error@querySearch', e);
return requisitions;
}
}