let invoiceSearchObj = search.create({
type: ‘invoice’,
filters: [
[‘internalid’, ‘anyof’, invoiceArray], // Handling multiple invoices
“AND”,
[“cogs”, “is”, “F”],
“AND”,
[“taxline”, “is”, “F”],
“AND”,
[“shipping”, “is”, “F”],
],
columns: [
search.createColumn({
name: “type”,
join: “item”,
label: “Type”
}),
search.createColumn({ name: “tranid”, label: “Document Number” }),
search.createColumn({ name: “internalid”, label: “Internal ID” }),
search.createColumn({ name: “datecreated”, label: “Date Created” }),
search.createColumn({ name: “lastmodifieddate”, label: “Last Modified” }),
search.createColumn({ name: “subsidiary”, label: “Subsidiary” }),
search.createColumn({ name: “location”, label: “Location” }),
search.createColumn({ name: “currency”, label: “Currency” }),
search.createColumn({ name: “shipaddress”, label: “Shipping Address” }),
search.createColumn({ name: “exchangerate”, label: “Exchange Rate” }),
search.createColumn({ name: “saleseffectivedate”, label: “Sales Effective Date” }),
search.createColumn({ name: “billaddress”, label: “Billing Address” }),
search.createColumn({ name: “shippingcost”, label: “Shipping Cost” }),
search.createColumn({ name: “fxamount”, label: “Amount (Foreign Currency)” }),
search.createColumn({ name: “fxamountremaining”, label: “Amount Remaining (Foreign Currency)” }),
search.createColumn({ name: “giftcert”, label: “Gift Certificate” }),
search.createColumn({ name: “item”, label: “Item” }),
search.createColumn({ name: “internalid”, join: “customer” }),
search.createColumn({ name: ’email’, join: ‘customer’ }),
search.createColumn({ name: “type”, join: “item”, label: “Type” }),
search.createColumn({ name: “statusref”, label: “Status” }),
search.createColumn({ name: “salesrep”, label: “Sales Rep” }),
search.createColumn({ name: “duedate”, label: “Due Date/Receive By” }),
search.createColumn({ name: “formulanumeric”, formula: “{taxtotal}”, label: “Tax Total” }),
]
});
let searchResultCount = invoiceSearchObj.runPaged().count;
let searchResults = invoiceSearchObj.run().getRange({ start: 0, end: 1000 });
let invoicesMap = {};
if (searchResultCount > 0) {
searchResults.forEach(function (result) {
let invoiceId = result.getValue({ name: ‘internalid’ });
if (!invoicesMap[invoiceId]) {
invoicesMap[invoiceId] = {
internalId: result.getValue({ name: ‘internalid’ }),
invoice_number: result.getValue({ name: ‘tranid’ }),
dateCreated: result.getValue({ name: ‘datecreated’ }),
dateModified: result.getValue({ name: ‘lastmodifieddate’ }),
status: result.getText({ name: ‘statusref’ }),
location: result.getText({ name: ‘location’ }),
salesRep: result.getText({ name: ‘salesrep’ }),
dueDate: result.getValue({ name: ‘duedate’ }),
customer_email: result.getValue({ name: ’email’, join: ‘customer’ }),
items: [], // Store all item names
shipping: result.getValue({ name: ‘shipaddress’ }),
currency: result.getText({ name: ‘currency’ }),
exchange_rate: result.getValue({ name: ‘exchangerate’ }),
sales_effective_date: result.getValue({ name: ‘saleseffectivedate’ }),
billing: result.getValue({ name: ‘billaddress’ }),
shipping_cost: result.getValue({ name: ‘shippingcost’ }),
gift_certificate: result.getValue({ name: ‘giftcert’ }),
customer_id: result.getValue({ name: ‘internalid’, join: ‘customer’ }),
sub_total: 0,
tax_total: result.getValue({ name: “formulanumeric”, formula: “{taxtotal}”, label: “Tax Total” }),
gift_certificate_applied: 0,
discount_total: 0,
discount_item: “”,
total: result.getValue({ name: ‘fxamount’ }),
amount_due: result.getValue({ name: ‘fxamountremaining’ })
};
} else {
invoicesMap[invoiceId].dateModified = result.getValue({ name: ‘lastmodifieddate’ });
invoicesMap[invoiceId].subsidiary = result.getText({ name: ‘subsidiary’ }) || “”;
invoicesMap[invoiceId].status = result.getText({ name: ‘statusref’ });
invoicesMap[invoiceId].location = result.getText({ name: ‘location’ });
invoicesMap[invoiceId].salesRep = result.getText({ name: ‘salesrep’ });
invoicesMap[invoiceId].dueDate = result.getValue({ name: ‘duedate’ });
invoicesMap[invoiceId].shipping = result.getValue({ name: ‘shipaddress’ });
invoicesMap[invoiceId].currency = result.getText({ name: ‘currency’ });
invoicesMap[invoiceId].exchange_rate = result.getValue({ name: ‘exchangerate’ });
invoicesMap[invoiceId].sales_effective_date = result.getValue({ name: ‘saleseffectivedate’ });
invoicesMap[invoiceId].billing = result.getValue({ name: ‘billaddress’ });
invoicesMap[invoiceId].shipping_cost = result.getValue({ name: ‘shippingcost’ });
invoicesMap[invoiceId].gift_certificate = result.getValue({ name: ‘giftcert’ });
}
let itemType = result.getValue({
name: “type”,
join: “item”,
label: “Type”
});
let itemAmount = parseFloat(result.getValue({ name: ‘fxamount’ })) || 0;
let itemName = result.getText({ name: ‘item’ });
if (itemName && !invoicesMap[invoiceId].items.includes(itemName)) {
invoicesMap[invoiceId].items.push(itemName);
}
if (itemType) {
let typeLower = itemType.toLowerCase();
if (typeLower.includes(“giftcert”)) {
invoicesMap[invoiceId].gift_certificate_applied += itemAmount;
} else if (typeLower.includes(“discount”)) {
invoicesMap[invoiceId].discount_total += itemAmount;
let discountName = result.getText({ name: ‘item’ });
if (discountName) {
if (invoicesMap[invoiceId].discount_item) {
invoicesMap[invoiceId].discount_item = discountName;
} else {
invoicesMap[invoiceId].discount_item = discountName;
}
}
} else {
invoicesMap[invoiceId].sub_total += itemAmount;
}
}
});