Learn How To Create a Real Time Three-Way Match Customization

Introduction

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.

Step 1: Create a Client Script for the Vendor Bill Record

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
	};
});        

Step 2: Create a Suitelet

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.

/**
 * @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 };
});        

Let’s test the code by creating a purchase order, item receipt and vendor bill.

Step 3: Create a Purchase Order

I’ve created a purchase order with two items.

Step 4: ?Receive the Purchase Order to create the Item Receipt

Step 5:? Bill the Purchase Order to create Vendor Bill

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.

Enhancements

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:

1. Custom Configuration Record: Consider implementing a custom record to store configuration settings. This record could house various parameters, including thresholds for detecting out-of-bounds matches and other settings tailored to your unique business requirements.
2. Handling Multiple Item Receipts: The current customization assumes a single item receipt per transaction. To make it more versatile, you can extend the code to handle scenarios involving multiple item receipts for a single transaction.

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.

Conclusion

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.

If you've enjoyed the insights and discussions in this newsletter, I invite you to subscribe and follow my LinkedIn profile for more updates. By subscribing, you'll get the latest editions delivered straight to your inbox, ensuring you never miss out on valuable content tailored to your interests and professional growth.

Following me on LinkedIn will also keep you in the loop with my regular posts and industry insights. Let's continue this journey of learning and growth together.

You can also view this article on my website blog here .

If you need help customizing or optimizing NetSuite please contact Suite Tooth consulting?here ?to set up a free consultation.?