Requirement – The purpose of this saved search is to run a report on all our contacts and bring in their spend in the past 12 months. The search should display the primary contact of the customer if there is no contact added in the invoice.
Solution – Create an invoice saved search including the details of the contact specified in the invoice record. And include the Primary contact of the corresponding customer if there is no contact specified in the invoice.
var invoiceSearchObj = search.create({
type: “invoice”,
filters:
[
[“trandate”,”within”,”previousrollingyear”],
“AND”,
[“type”,”anyof”,”CustInvc”],
“AND”,
[“customer.subsidiary”,”anyof”,”1″],
“AND”,
[“customer.custentity_exclude_from_nps_survey”,”is”,”F”],
“AND”,
[[[“custbody_po_contact”,”isnotempty”,””]],”OR”,[[“customer.contact”,”isnotempty”,””]]]
],
columns:
[
search.createColumn({
name: “internalid”,
summary: “GROUP”,
label: “Invoice internal ID”
}),
search.createColumn({
name: “internalid”,
join: “customer”,
summary: “GROUP”,
label: “Customer Internal ID”
}),
search.createColumn({
name: “firstname”,
join: “contactPrimary”,
summary: “GROUP”,
label: “First Name”
}),
search.createColumn({
name: “lastname”,
join: “contactPrimary”,
summary: “GROUP”,
label: “Last Name”
}),
search.createColumn({
name: “entityid”,
join: “contactPrimary”,
summary: “GROUP”,
label: “Name”
}),
search.createColumn({
name: “email”,
join: “contactPrimary”,
summary: “GROUP”,
label: “Email”
}),
search.createColumn({
name: “custbody_ozlink_entity_name”,
summary: “GROUP”,
label: “Company”
}),
search.createColumn({
name: “title”,
join: “contactPrimary”,
summary: “GROUP”,
label: “Job Title”
}),
search.createColumn({
name: “salesrep”,
join: “customer”,
summary: “GROUP”,
label: “Sales Rep”
}),
search.createColumn({
name: “custentity1”,
join: “customer”,
summary: “GROUP”,
label: “Customer Primary Group”
}),
search.createColumn({
name: “formulatext”,
summary: “GROUP”,
formula: “CASE WHEN {custbody_po_contact} IS NULL THEN {customer.contact} ELSE ‘ ‘ END”,
label: “Customer primary contact”
}),
search.createColumn({
name: “netamountnotax”,
summary: “SUM”,
label: “Amount (Net of Tax)”
})
]
});
var searchResultCount = invoiceSearchObj.runPaged().count;
log.debug(“invoiceSearchObj result count”,searchResultCount);
invoiceSearchObj.run().each(function(result){
// .run().each has a limit of 4,000 results
return true;
});
Limitation – In this search, it is only available the name of the customer’s primary contact if there are no contacts added in the invoice. The First Name, Last Name, and Email ID will not be available. To get these fields you must add contact details to the invoice.
Also in the case that no contact is added in the invoice and the contact in the customer record is not specified as primary contact there will be no results in the search.