Scripting help
Hi,
I’m trying to take custom records and add them to expense lines on a vendor bill based on their invoices. I need a bill created for each invoice # with the coordinating custom records on the expense lines. This is the code I have so far, and I’m having trouble combing them based on their invoice #s. Does anyone know what looks wrong or have any other suggestions I could try? I’ve considering trying a group search.
/**
* This script looks for Fedex billings with "Include in Fedex Bill" checked.
* Fedex billings with the same invoice #s are combined into one vendor bill,
* and the Fedex billings are inactivated.
*
* @NApiVersion 2.x
* @NScriptType ScheduledScript
* @NModuleScope SameAccount
*/
define(['N/search', 'N/log', 'N/record', 'N/format'], function (search, log, record, format) {
function fedexBillSearch() {
return search.create({
type: 'customrecordfedexbilling',
filters: [
['custrecord72', 'is', 'T'],
'AND',
['isinactive', 'is', 'F']
],
columns: [
'custrecordfedexsoshipamount',
search.createColumn({
name: 'internalid',
join: 'CUSTRECORDFEDEXCUSTOMER'
}),
'custrecordfedexlocation',
'custrecordfedexso',
search.createColumn({
name: 'formulatext',
formula: "CONCAT(CONCAT(TO_CHAR({today}),' - '), {custrecordfedexinvoice})"
}),
search.createColumn({
name: 'custrecordfedexinvoice',
sort: search.Sort.ASC
}),
search.createColumn({
name: 'custrecordfedexinvdate',
sort: search.Sort.ASC
}),
'internalid'
]
});
}
function getAllResults(searchObj) {
var start = 0;
var allResults = [];
var results = [];
var resultSet = searchObj.run();
results = resultSet.getRange({
start: start,
end: start + 1000
});
allResults = allResults.concat(results);
while (results.length) {
start += 1000;
results = resultSet.getRange({
start: start,
end: start + 1000
});
allResults = allResults.concat(results);
}
return allResults;
}
/**
* This function groups Fedex billings based on invoice #s
* @param {N/search.Result[]} results - Array of search results
* @param {string} fedexinvoice - Name of the field used for grouping
* @returns {Object} - Grouped invoice records
*/
function groupInvoices(results, fedexInvoice) {
var groupedRecords = {};
for (var i = 0; i < results.length; i++) {
var fedexInvoice = record.getValue('custrecordfedexinvoice');
var record = results[i];
var propertyValue = record[fedexInvoice];
if (!groupedRecords[propertyValue]) {
groupedRecords[propertyValue] = [];
}
groupedRecords[propertyValue].push(record);
}
return groupedRecords;
}
/**
* This function creates an array of order information from the search results
* @param {N/search.Result} result - Result object from the search
* @returns {Object} - Order information
*/
function getOrderInfo(results) {
var orderInfo = {};
orderInfo.fulfillid = results.id;
orderInfo.amount = results.getValue('custrecordfedexsoshipamount');
orderInfo.customer = results.getValue({
name: 'internalid',
join: 'CUSTRECORDFEDEXCUSTOMER'
});
orderInfo.location = results.getValue('custrecordfedexlocation');
orderInfo.salesOrder = results.getValue('custrecordfedexso');
orderInfo.externalId = results.getValue({
name: 'formulatext',
formula: "CONCAT(CONCAT(TO_CHAR({today}),' - '), {custrecordfedexinvoice})"
});
orderInfo.invoiceDate = results.getValue('custrecordfedexinvdate');
orderInfo.internalId = results.getValue('internalid');
return orderInfo;
}
function createFedexVendorBill(orderInfo) {
var newFedexVendorBill = record.create({
type: record.Type.VENDOR_BILL,
isDynamic: true
});
var formattedDate = format.parse({
value: orderInfo.invoiceDate,
type: format.Type.DATE
});
newFedexVendorBill.setValue({
fieldId: 'entity',
value: 341
});
newFedexVendorBill.setValue({
fieldId: 'customform',
value: 135
});
newFedexVendorBill.setValue({
fieldId: 'memo',
value: orderInfo.externalId
});
newFedexVendorBill.setValue({
fieldId: 'trandate',
value: formattedDate
});
newFedexVendorBill.setValue({
fieldId: 'account',
value: 112
});
newFedexVendorBill.selectNewLine({
sublistId: 'expense'
});
newFedexVendorBill.setCurrentSublistValue({
sublistId: 'expense',
fieldId: 'account',
value: 291
});
newFedexVendorBill.setCurrentSublistValue({
sublistId: 'expense',
fieldId: 'amount',
value: orderInfo.amount
});
newFedexVendorBill.setCurrentSublistValue({
sublistId: 'expense',
fieldId: 'location',
value: orderInfo.location
});
newFedexVendorBill.setCurrentSublistValue({
sublistId: 'expense',
fieldId: 'department',
value: 1
});
newFedexVendorBill.setCurrentSublistValue({
sublistId: 'expense',
fieldId: 'custcolrelcustomer',
value: orderInfo.customer
});
newFedexVendorBill.setCurrentSublistValue({
sublistId: 'expense',
fieldId: 'custcolrelsalesorderline',
value: orderInfo.salesOrder
});
newFedexVendorBill.commitLine({
sublistId: 'expense'
});
var newFedexVendorBillId = newFedexVendorBill.save();
return newFedexVendorBillId;
}
function vendorBillSearch(orderInfo) {
var checkForDupe = search.create({
type: 'vendorbill',
filters: [
['externalidstring', 'contains', orderInfo.externalId]
],
columns: [
'internalid'
]
});
return getAllResults(checkForDupe);
}
function inactivateFedexBills(orderInfo) {
record.submitFields({
type: 'customrecordfedexbilling',
id: orderInfo.internalId,
values: {
isinactive: true
}
});
}
function execute(scriptContext) {
var fedexBillSearchObj = fedexBillSearch();
var results = getAllResults(fedexBillSearchObj);
log.debug('search', fedexBillSearchObj);
log.debug('results', results);
var invoiceGroup = groupInvoices(results, 'custrecordfedexinvoice');
log.debug('invoice groups', invoiceGroup);
for (var invoiceNumber in invoiceGroup) {
var customRecords = invoiceGroup[invoiceNumber];
for (var i = 0; i < customRecords.length; i++) {
var customRecord = customRecords[i];
var orderInfo = getOrderInfo(customRecord);
log.debug('orderInfo', orderInfo);
var billResults = vendorBillSearch(orderInfo); // Call vendorBillSearch function
log.debug('billResults', billResults);
if (billResults.length === 0) { // Checking if the vendor bill is a duplicate
var newFedexVendorBillId = createFedexVendorBill(orderInfo);
log.debug('Created Vendor Bill', newFedexVendorBillId);
inactivateFedexBills(orderInfo); // Call inactivateFedexBills function
}
}
}
}
return {
execute: execute
};
});
Hi,
What’s happening currently and what do you expect?
Thanks,
Chris
Thanks for the help. The records aren’t combining based on their invoice #s onto the expense lines of the bills, and instead it’s still making individual bills for each record. For example: if 3 of the custom records have the same invoice #, they would be added as three expense lines on the same bill. I thought by grouping them before creating an array of the search results would do it, but I think I am missing a step somewhere.
