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

};

});
Rookie Asked on June 28, 2023 in SuiteScript.
Add Comment
1 Answer(s)

Hi,

What’s happening currently and what do you expect?

Thanks,

Chris

Advanced Answered on June 29, 2023.

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.

on June 29, 2023.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Become a Sponsor   Become a Sponsor