Three-way matching is an accounts payable procedure that involves verifying the information on a purchase order, the supplier’s invoice, and the delivery receipt to ensure they align before initiating payment for an invoice. Manual verification of this process can be labor-intensive. In this blog, I’ll demonstrate how to create a fundamental Real Time Three-Way Match customization. Let’s get started.
The first step involves setting up a Client Script that triggers when you create a Vendor Bill record. This script plays a crucial role: it reaches out to a Suitelet to fetch essential details from the purchase order and item receipt.
Why use a Suitelet here? It’s all about access and permissions. Suitelets are more flexible in handling permissions, making them a safer choice for this task.
Once the Client Script retrieves the transaction data, it performs a critical comparison. It checks for any inconsistencies between the purchase order and item receipt data. If it finds discrepancies, a dialog box will pop up, highlighting these differences for the user. It allows the user to review and provide reasons for saving the record, even when there are noted discrepancies.
Add and deploy the script to the vendor bill record (code shown below). Reference my article “Quick Guide to Adding and Deploying a Script in NetSuite” if needed.
/**
* @NApiVersion 2.1
* @NScriptType ClientScript
**/
/*
* On save of vendor bill, call Suitelet to get purchase order and item receipt data.
* If differences exits, show differences to user and allow them to input a reason
* if they decide to save the vendor bill.
*/
define(['N/https', 'N/url','N/format'], (https, url, format) => {
let MODE = null;
let finalSubmit = false;
pageInit = (context) => {
MODE = context.mode;
console.log('MODE', MODE);
}
saveRecord = (context) => {
// only check on creation of vendor bill
// we are clicking "Bill" on purchase order which is MODE == 'copy'
if (MODE != 'copy') {
return true;
}
const LOGTITLE = "saveRecord " + context.currentRecord.id;
let curRecord = context.currentRecord;
if (finalSubmit) {
return true;
}
let poId = curRecord.getValue('podocnum');
if (!poId) {
// if not generating from a PO return
return true;
}
console.log('poId:' + poId);
// call Suitelet to get the purchase order and item receipt data
var response = getTransactionData(poId);
let vbData = getVendorBillData(curRecord);
setTimeout(function () {
log.debug(LOGTITLE, `response: ${response}`);
// compare the VB to PO and IR (if it exists)
let output = compareData(response, vbData);
if (output == 'success') {
log.debug(LOGTITLE, `Three Way Match Success`);
finalSubmit = true;
getNLMultiButtonByName('multibutton_submitter').onMainButtonClick(this);
}
else {
let resReason = 'Differences detected in Real-Time Three-Way Match for one or more items.<br><br>' + output + '<br>Please enter the reason for saving the record.';
Ext.Msg.prompt('Three-Way Match Validation', resReason, function (btn, text) {
if (btn == 'ok') {
// Click the save button
curRecord.setValue({
fieldId: 'custbody_threewaymatch_discrepancy',
value: text
});
finalSubmit = true;
getNLMultiButtonByName('multibutton_submitter').onMainButtonClick(this);
}
else {
finalSubmit = false;
}
});
}
}, 2000);
return false;
}
getTransactionData = (poId) => {
let stSuiteletUrl = url.resolveScript({
scriptId: 'customscript_sd_sl_threewaymatch',
deploymentId: 'customdeploy_sd_sl_threewaymatch'
});
let headerObj = {
'accept': 'application/json',
'content-type': 'application/json'
};
let params = {
'poId': poId
};
let response = https.post({
url: stSuiteletUrl,
body: JSON.stringify(params),
headers: headerObj
});
console.log('response:' + response.body);
return response.body;
}
getVendorBillData = (curRecord) => {
let vbData = [];
// Get the vendor bill data
let lineCount = curRecord.getLineCount({ sublistId: 'item' });
for (let i = 0; i < lineCount; i++) {
const line = curRecord.getSublistValue({sublistId: 'item',fieldId: 'line',line: i});
const itemId = curRecord.getSublistValue({sublistId: 'item',fieldId: 'item',line: i});
const itemQty = curRecord.getSublistValue({sublistId: 'item',fieldId: 'quantity',line: i});
const itemRate = curRecord.getSublistValue({sublistId: 'item',fieldId: 'rate',line: i});
const itemAmount = curRecord.getSublistValue({sublistId: 'item',fieldId: 'amount',line: i});
const itemName = curRecord.getSublistText({sublistId: 'item',fieldId: 'item',line: i});
vbData.push({
line: line,
item: itemId,
qty: itemQty,
rate: itemRate,
amount: itemAmount,
name: itemName
});
}
return vbData;
}
compareData = (response, vbData) => {
let reason = '';
let data = JSON.parse(response);
let poData = data.poData;
let irData = data.irData;
for (let i = 0; i < vbData.length; i++) {
let vbItem = vbData[i];
let itemNameArray = vbItem.name.split(':');
let itemName = itemNameArray[itemNameArray.length-1];
let poItem = poData.itemData[vbItem.item];
let irItem = null;
if (irData) {
irItem = irData.itemData[vbItem.item];
}
let itemHTML = `${getTextInBlue('Item:')} ${itemName} (Line ${vbItem.line})`;
// compare rate
if (vbItem.rate != poItem.rate) {
let comparison = getComparison(vbItem.rate,poItem.rate);
reason += `${itemHTML} ${getTextInBlue('VB Price')} ($${getCurrency(vbItem.rate)}) is ${comparison} ${getTextInBlue('PO Price')} ($${getCurrency(poItem.rate)})<br>`;
}
if (irData && vbItem.rate != irItem.rate) {
let comparison = getComparison(vbItem.rate,irItem.rate);
reason += `${itemHTML} ${getTextInBlue('VB Price')} ($${getCurrency(vbItem.rate)}) is ${comparison} ${getTextInBlue('IR ' + irData.tranid + ' Price')} ($${getCurrency(irItem.rate)})<br>`;
}
// compare quantity
if (vbItem.qty != poItem.qty) {
let comparison = getComparison(vbItem.qty,poItem.qty);
reason += `${itemHTML} ${getTextInBlue('VB Quantity')} (${vbItem.qty}) is ${comparison} ${getTextInBlue('PO Quantity')} (${poItem.qty})<br>`;
}
if (irData && vbItem.qty != irItem.qty) {
let comparison = getComparison(vbItem.qty,irItem.qty);
reason += `${itemHTML} ${getTextInBlue('VB Quantity')} (${vbItem.qty}) is ${comparison} ${getTextInBlue('IR ' + irData.tranid + ' Quantity')} (${irItem.qty})<br>`;
}
// compare amount
if (vbItem.amount != poItem.amount) {
let comparison = getComparison(vbItem.amount,poItem.amount);
reason += `${itemHTML} ${getTextInBlue('VB Amount')} ($${getCurrency(vbItem.amount)}) is ${comparison} ${getTextInBlue('PO Amount')} ($${getCurrency(poItem.amount)})<br>`;
}
if (irData && vbItem.amount != irItem.amount) {
let comparison = getComparison(vbItem.amount,irItem.amount);
reason += `${itemHTML} ${getTextInBlue('VB Amount')} ($${getCurrency(vbItem.amount)}) is ${comparison} ${getTextInBlue('IR ' + irData.tranid + ' Amount')} ($${getCurrency(irItem.amount)})<br>`;
}
}
if (reason) {
reason = `<font size="+1" color="green"><b>Purchase Order#${poData.tranid}</b></font><br>${reason}`;
}
else {
reason = 'success';
}
return reason;
}
getTextInBlue = (text) => {
return `<font color="blue">${text}</font>`;
}
getComparison = (value1, value2) => {
return value1 > value2 ? 'greater than': 'less than';
}
getCurrency = (amount) => {
return format.format({value:amount, type: format.Type.CURRENCY});
}
return {
pageInit: pageInit,
saveRecord: saveRecord
};
});This Suitelet simply gets the data from purchase order and item receipt and returns it.
Add and deploy the script to the vendor bill record (code shown below). You will need to use the ID “_sd_sl_threewaymatch” when creating both the script record and the script deployment record.
Let’s test the code by creating a purchase order, item receipt and vendor bill.
/**
* @NApiVersion 2.1
* @NScriptType Suitelet
*/
/*
Return purchase order and item receipt data.
*/
define(['N/http', 'N/search', 'N/record'],
(http, search, record) => {
onRequest = (context) => {
try {
if (context.request.method === http.Method.POST) {
let requestBody = context.request.body;
let poId = JSON.parse(requestBody).poId;
let poData = getTransactionData(record.Type.PURCHASE_ORDER, poId);
let itemReceiptId = getItemReceiptId(poId);
let irData = null;
if (itemReceiptId) {
irData = getTransactionData(record.Type.ITEM_RECEIPT, itemReceiptId);
}
let response = {
poData : poData,
irData : irData
}
context.response.write(JSON.stringify(response));
}
}
catch (e) {
log.error('suitelet error', JSON.stringify(e));
throw e.message;
}
}
getItemReceiptId = (poId) => {
let itemReceiptSearch = search.create({
type: search.Type.ITEM_RECEIPT,
filters: [
['createdfrom', 'anyof', poId]
],
columns: ['internalid']
});
var id = null;
itemReceiptSearch.run().each(function (result) {
id = result.id;
return true;
});
return id;
}
//Get the item list of record
getTransactionData = (recordType, recordId) => {
log.debug('recordType', recordType);
log.debug('recordId', recordId);
var curRecord = record.load({
type: recordType,
id: recordId,
isDynamic: true
});
let tranid = curRecord.getValue('tranid');
let lineCount = curRecord.getLineCount({ sublistId: 'item' });
let itemData = {};
for (let i = 0; i < lineCount; i++) {
const itemId = curRecord.getSublistValue({sublistId: 'item',fieldId: 'item',line: i});
const itemQty = curRecord.getSublistValue({sublistId: 'item',fieldId: 'quantity',line: i});
const itemRate = curRecord.getSublistValue({sublistId: 'item',fieldId: 'rate',line: i});
let itemAmount = 0;
if (curRecord.type == record.Type.PURCHASE_ORDER) {
itemAmount = curRecord.getSublistValue({sublistId: 'item',fieldId: 'amount',line: i});
}
else {
itemAmount = itemQty * itemRate;
}
itemData[itemId] = {
qty: itemQty,
rate: itemRate,
amount: itemAmount
}
}
return {
itemData : itemData,
tranid : tranid
}
}
return { onRequest };
});I’ve created a purchase order with two items.
I will change the quantity on the first line and the rate on the second to trigger the customization.
Now when I save the record, the dialog will show the differences and ask the user to enter a reason if they still want to save the record.
After the record saves you can see the reason which is saved in a custom transaction body field.
The Real-Time Three-Way Match customization discussed here serves as a solid foundation, but there’s room for improvement and customization to better align it with your specific business needs. Here are a couple of ways you can enhance it:
Keep in mind that these are just a couple of ideas, and there are likely many more ways to tailor and optimize this customization to suit your organization’s precise needs.
In conclusion, the Real-Time Three-Way Match customization presented here has the potential to significantly streamline your workflow and save valuable time otherwise spent on manual checks. A special thanks to our developer, Serhii, for his invaluable contribution in crafting this solution.
Jaime Requena is a seasoned NetSuite Consultant and Solutions Architect, known for delivering WHITE GLOVE service to businesses. With 15+ years of experience and 3x certifications in ERP, Developer, and Admin, Jaime specializes in highly customized NetSuite accounts, transforming operations for 200+ satisfied customers all across the globe.
05/16/2024
What is Netsuite supply chain management?
We aim to bring unmatched expertise and professionalism to your NetSuite initiatives. Let’s talk about how our NetSuite consultancy can make a difference!
It’s Been 4+ Years Now And We Have Worked With Hundreds Of Clients, Building Our Way To The Top, One Happy Client After Another! Their Voices Of Satisfaction Serve As A Testament To Our Success –
We can help you achieve as your dedicated Lead NetSuite Consultant. My team and I are experts in tailoring the NetSuite platform to meet the unique challenges of your business in this fast-paced digital era.
