Solution:
Create a User Event script and deploy it on the Bill Payment record. Also, create a Map Reduce script to run in the background. The User Event script will trigger the Map Reduce script. The internal ID of the Bill Payment record is obtained using the User Event script, and further processing is done in the Map Reduce script.
There will be a delay in viewing the attachments after saving the Bill Payment record. The delay will increase based on the number of attachments.
The files are fetched from the File Cabinet. For identification purposes, the reference number in the Bill and Bill Credit will exist in the PDF file name. Files containing the reference number in the file name and attached to the corresponding Bill or Bill Credit will be attached to the Bill Payment record in both create and edit contexts.
Additionally, for easy access, the files are stored in different folders based on the naming format. For storing files related to Bill records, the folder naming format is “Vendor Name Invoices.” For Bill Credit files, the folder naming format is “Vendor Name Credit Memos.
The removal context is not considered. If any PDF file attachments are removed from the Bill or Bill Credit and those attachments are already added to the Bill Payment record, we need to manually remove those PDF attachments from the Bill Payment if required.
During the edit and save of the Bill Payment record, if any new Bill is attached to the Bill Payment record and the Bill contains a PDF file with the reference number in the file name, then that PDF will also be attached to the Bill Payment record.
User event script:
define([‘N/record’, ‘N/search’, ‘N/log’, ‘N/task’],
/**
* @param{record} record
* @param{search} search
* @param{log} log
* @param{task} task
*/
(record, search, log, task) => {
/**
* Defines the function definition that is executed after record is submitted.
* @param {Object} scriptContext
* @param {Record} scriptContext.newRecord – New record
* @param {Record} scriptContext.oldRecord – Old record
* @param {string} scriptContext.type – Trigger type; use values from the scriptContext.UserEventType enum
* @since 2015.2
*/
const afterSubmit = (scriptContext) => {
try {
if (scriptContext.type !== scriptContext.UserEventType.CREATE && scriptContext.type !== scriptContext.UserEventType.EDIT) {
return;
}
let billPaymentRecord = scriptContext.newRecord;
let billPaymentId = billPaymentRecord.id;
log.debug(“billPaymentId”, billPaymentId)
let mrTaskId = createMapReduceTask(billPaymentId);
} catch (e) {
log.error(‘Error Retrieving Applied Records’, e);
}
}
/**
* Creates a Map/Reduce task to attach files to the bill payment record.
*
* @param {Array<Object>} appliedRecords – An array of objects containing the internal ID and type of each applied record.
* @param {string} billPaymentId – The internal ID of the bill payment record.
* @returns {string} The task ID of the submitted Map/Reduce task.
*/
const createMapReduceTask = (billPaymentId) => {
try {
let mrTask = task.create({ taskType: task.TaskType.MAP_REDUCE });
mrTask.scriptId = “customscript_jj_mr_attach_file_payment”;
mrTask.deploymentId = “customdeploy_jj_mr_attach_file_payment”;
mrTask.params = {
“custscript_jj_bill_payment_id”: billPaymentId
};
return mrTask.submit();
} catch (e) {
log.error(‘Error in createMapReduceTask’, e);
return null;
}
};
return { afterSubmit }
});
Map Reduce script:
define([‘N/file’, ‘N/record’, ‘N/runtime’, ‘N/search’, ‘N/log’, ‘N/query’],
/**
* @param{file} file
* @param{record} record
* @param{runtime} runtime
* @param{search} search
* @param{log} log
* @param{query} query
*/
(file, record, runtime, search, log, query) => {
/**
* Defines the function that is executed at the beginning of the map/reduce process and generates the input data.
* @param {Object} inputContext
* @param {boolean} inputContext.isRestarted – Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {Object} inputContext.ObjectRef – Object that references the input data
* @typedef {Object} ObjectRef
* @property {string|number} ObjectRef.id – Internal ID of the record instance that contains the input data
* @property {string} ObjectRef.type – Type of the record instance that contains the input data
* @returns {Array|Object|Search|ObjectRef|File|Query} The input data to use in the map/reduce process
* @since 2015.2
*/
const getInputData = (inputContext) => {
try {
let billPaymentRecordId = runtime.getCurrentScript().getParameter({ name: ‘custscript_jj_bill_payment_id’ });
let billAppliedId = getAppliedRecords(billPaymentRecordId);
let creditAppliedId = getBillPaymentCredits(billPaymentRecordId);
return [
JSON.stringify({
billAppliedId: billAppliedId,
creditAppliedId: creditAppliedId
})
];
} catch (e) {
log.error(‘Error in getInputData’, e.message);
return [];
}
}
/**
* Defines the function that is executed when the reduce entry point is triggered. This entry point is triggered
* automatically when the associated map stage is complete. This function is applied to each group in the provided context.
* @param {Object} reduceContext – Data collection containing the groups to process in the reduce stage. This parameter is
* provided automatically based on the results of the map stage.
* @param {Iterator} reduceContext.errors – Serialized errors that were thrown during previous attempts to execute the
* reduce function on the current group
* @param {number} reduceContext.executionNo – Number of times the reduce function has been executed on the current group
* @param {boolean} reduceContext.isRestarted – Indicates whether the current invocation of this function is the first
* invocation (if true, the current invocation is not the first invocation and this function has been restarted)
* @param {string} reduceContext.key – Key to be processed during the reduce stage
* @param {List<String>} reduceContext.values – All values associated with a unique key that was passed to the reduce stage
* for processing
* @since 2015.2
*/
const reduce = (reduceContext) => {
try {
reduceContext.values.forEach(value => {
let inputData = JSON.parse(value);
log.debug(“inputData”, inputData);
if (inputData.billAppliedId) {
processAppliedIds(inputData.billAppliedId, ‘VendBill’);
} else {
log.debug(“billAppliedId not found in inputData”);
}
if (inputData.creditAppliedId) {
processAppliedIds(inputData.creditAppliedId, ‘VendCred’);
} else {
log.debug(“creditAppliedId not found in inputData”);
}
});
} catch (e) {
log.error(‘Error in reduce’, e.message);
}
};
/**
* Retrieves the internal IDs of applied records from a given bill payment record.
*
* @param {number} billPaymentRecordId – The internal ID of the bill payment record.
* @returns {Array<number>} An array of internal IDs of the applied records.
*/
const getAppliedRecords = (billPaymentRecordId) => {
try {
// Create the saved search
let appliedRecordsSearch = search.create({
type: search.Type.TRANSACTION,
filters: [
[‘type’, ‘anyof’, ‘VendPymt’], // Vendor Payment
‘AND’,
[‘internalid’, ‘is’, billPaymentRecordId],
‘AND’,
[‘mainline’, ‘is’, ‘F’],
‘AND’,
[“appliedtotransaction”, “noneof”, “@NONE@”]
],
columns:
[
search.createColumn({ name: “appliedtotransaction”, label: “Applied To Transaction” }),
search.createColumn({
name: “internalid”,
join: “appliedToTransaction”,
label: “Internal ID”
})
]
});
let appliedRecords = [];
// Run the search and process the results
let searchResultCount = appliedRecordsSearch.runPaged().count;
appliedRecordsSearch.run().each(result => {
let billId = result.getValue({
name: “internalid”,
join: “appliedToTransaction”,
label: “Internal ID”
});
appliedRecords.push(parseInt(billId, 10));;
return true;
});
return appliedRecords;
} catch (e) {
log.error(‘Error in getAppliedRecords’, e);
return [];
}
};
/**
* Retrieves the applied bill credit records from the bill payment record.
*
* @param {number} billPaymentId – The internal ID of the bill payment record.
* @returns {Array<number>} An array containing the internal IDs of the applied bill credit records.
*/
function getBillPaymentCredits(billPaymentId) {
let queryResults = query.runSuiteQL({
query: `
SELECT
Applied.nextdoc as billCreditId
FROM
AppliedCreditTransactionLineLink as Applied
WHERE
Applied.paymenttransaction = ${billPaymentId}
`
});
let results = queryResults.asMappedResults();
// Map the results to an array of billCreditId values
let billCreditIds = results.map(result => result.billcreditid);
return billCreditIds;
}
/**
* Processes applied IDs (either bill or credit) based on provided IDs and type.
* This function retrieves transaction details for each ID, checks the document name,
* retrieves file internal IDs, and attaches files to the corresponding bill payment record.
*
* @param {Array<number>} appliedIds – An array of internal IDs to process.
* @param {string} type – The record type, which can be either VendBill or VendCred.
*/
const processAppliedIds = (appliedIds, type) => {
appliedIds.forEach(id => {
let transactionDetails = getTransactionDetails(id, type);
transactionDetails.forEach(({ vendorName, documentName, referenceNumber, recType }) => {
if (referenceNumber) {
if (documentName.includes(referenceNumber)) {
let fileInternalIdArray = getFileInternalIds(vendorName, referenceNumber, recType);
let billPaymentRecordId = runtime.getCurrentScript().getParameter({ name: ‘custscript_jj_bill_payment_id’ });
attachFilesToRecord(fileInternalIdArray, billPaymentRecordId);
} else {
log.debug(“Document name does not contain reference number, skipping attachment”);
}
}
else {
log.debug(“No Reference Number in the record”);
}
});
});
};
/**
* Defines the function that retrieves transaction details for a given internal ID.
* This function creates a saved search to get the reference number and vendor name.
*
* @param {number} internalId – The internal ID of the transaction to retrieve details for.
* @param {string} type – The record type can be either VendBill or VendCred.
* @returns {Array} An array of search results containing the transaction details.
*/
const getTransactionDetails = (internalId, type) => {
try {
let typeFilter = type === ‘VendBill’ ? ‘VendBill’ : ‘VendCred’;
let vendorbillSearchObj = search.create({
type: search.Type.TRANSACTION,
filters: [
[‘type’, ‘anyof’, typeFilter],
‘AND’,
[‘internalid’, ‘anyof’, internalId],
‘AND’,
[‘mainline’, ‘is’, ‘T’]
],
columns: [
‘tranid’,
‘entity’,
search.createColumn({
name: ‘name’,
join: ‘file’,
label: ‘Name’
}),
search.createColumn({ name: “type”, label: “Type” })
]
});
let billSearchResultCount = vendorbillSearchObj.runPaged().count;
let vendorBillDetails = [];
vendorbillSearchObj.run().each(result => {
let vendorName = result.getText(‘entity’);
let referenceNumber = result.getValue(‘tranid’);
let documentName = result.getValue({
name: ‘name’,
join: ‘file’,
label: ‘Name’
});
let recType = result.getValue(“type”);
vendorBillDetails.push({
vendorName: vendorName,
documentName: documentName,
referenceNumber: referenceNumber,
recType: recType
});
return true;
});
return vendorBillDetails;
} catch (e) {
log.error(‘Error in getTransactionDetails’, e.message);
return [];
}
};
/**
* Defines the function that retrieves file internal IDs for a given vendor name and reference number.
* This function creates a saved search to get the file internal IDs.
*
* @param {string} vendorName – The name of the vendor.
* @param {string} referenceNumber – The reference number to search for in file names.
* @param {string} recType – The record type can be either VendBill or VendCred.
* @returns {Array<string>} An array of file internal IDs.
*/
const getFileInternalIds = (vendorName, referenceNumber, recType) => {
try {
let folderName = vendorName + ” ” + (recType === ‘VendBill’ ? “Invoices” : “Credit Memos”);
let fileInternalIdArray = [];
let folderSearch = search.create({
type: search.Type.FOLDER,
filters: [
[‘name’, ‘is’, folderName],
‘AND’,
[‘file.name’, ‘contains’, referenceNumber]
],
columns: [
search.createColumn({
name: ‘name’,
join: ‘file’,
label: ‘Name’
}),
search.createColumn({
name: ‘internalid’,
join: ‘file’,
label: ‘Internal ID’
})
]
});
folderSearch.run().each(result => {
let fileName = result.getValue({
name: ‘name’,
join: ‘file’,
label: ‘Name’
});
let fileInternalId = result.getValue({
name: ‘internalid’,
join: ‘file’,
label: ‘Internal ID’
});
fileInternalIdArray.push(fileInternalId);
return true;
});
return fileInternalIdArray;
} catch (e) {
log.error(‘Error in getFileInternalIdsBill’, e.message);
}
};
/**
* Defines the function that attaches files to a given bill payment record.
* This function iterates over the array of file internal IDs and attaches each file to the bill payment record.
*
* @param {Array<string>} fileInternalIdArray – An array of file internal IDs to attach.
* @param {string} billPaymentRecordId – The internal ID of the bill payment record to attach files to.
*/
const attachFilesToRecord = (fileInternalIdArray, billPaymentRecordId) => {
try {
fileInternalIdArray.forEach(fileId => {
record.attach({
record: { type: ‘file’, id: fileId },
to: { type: ‘vendorpayment’, id: billPaymentRecordId }
});
});
} catch (e) {
log.error(‘Error in attachFilesToRecord’, e.message);
}
};
return { getInputData, reduce }
});