Approvals - Simple 2-Way Match with Workflows


Overview 

This article covers a recipe for creating a lightweight two way match between POs and Vendor Bills using Saved Searches and Workflows on NetSuite. This can be used as a component of the approvals bundle to trigger approval processes on Vendor Bills based on match exceptions. 

Time to Setup: Approximately 45 Minutes
Requirements: NetSuite Administrator Login

Caveats

This simple two-way match solution has a limitation in that it can only be used where Vendor Bills contain items from only one PO. NetSuite natively supports combining lines from multiple POs on a single Vendor Bill (linkage occurs at the line level) however, most vendors tend to send out invoices per PO and thus in most cases this recipe will work just fine. 

Simple 2-Way Match Recipe

This solution depends on two simple mechanisms on NetSuite:

a) If a transaction body field is set on a PO, when a Vendor Bill is created from the PO it will carry the value over to the Bill. 

b) In NetSuite it is possible to create body fields on transactions which derive their value from a saved search result and "calculate" it live each time the field value is referenced in a workflow. 

Setup Steps

For this solution we will create:

  • A transaction body field called "Original PO"  (List/Record lookup to Transaction)
  • A saved search called "PO/Bill Total Difference (Used on Transaction Body Field)"
  • A transaction body field called "PO/Bill Variance Exception"  (Checkbox)
  • A transaction body field called "PO/Bill Difference"  (Currency)
  • A workflow called "Set PO/Bill Variance Exception"

Note: Feel free to change the Labels and IDs of the fields to suit your own naming conventions. Just make sure when an ID is used in a formula in this recipe that your changes are kept consistent. Here the prefix "ngcu" has been used to stand for "Netgain Customization". 

 Field - Original PO

Create a new transaction body field:

  • Name: "Original PO"
  • ID:  custbody_ngcu_original_po
  • Type: List/Record
  • List/Record: Transaction
  • Store Value: True
  • Applies To: Purchase
  • Help Text: This value will be automatically populated on the PO record and carry over to the Vendor Bill when it is created.
  • Sourcing & Filtering:
    • Type = Purchase Order
  • Save & Apply to Forms  - show on only PO and Vendor Bill forms

Saved Search - PO/Bill Total Difference

Create a new saved search and on the page to select the type of search, choose "Transaction":

  • Name: "PO/Bill Total Difference (Used on Transaction Body Field)"
  • ID: custbody_ngcu_po_bill_total_diff

Criteria: 

There are three criteria lines for this search: 

  • Type: is Bill
  • Main Line: Is True
  • Original PO Fields.... Main Line: Is True

Column 1:

Note that there is to be only one column in this search.

  • Field: Formula Currency
  • Summary Type: Sum
  • Public: True
  • Formula:  
sum({amount}) - max({custbody_ngcu_original_po.amount})

Available Filters:

  • Original PO

Field - PO/Bill Variance Exception

Create a new transaction body field:

  • Name: "PO/Bill Variance Exception"
  • ID: custbody_ngcu_po_bill_exception
  • Type: Checkbox
  • Store Value: True
  • Applies To: Purchase
  • Help Text: This box will be checked by a workflow if there is variance exception between the PO and Vendor Bill totals.
  • Save & Apply to Forms  - Show on only Vendor Bill forms

Field - PO/Bill Difference

Create a new transaction body field:

  • Name: "PO/Bill Difference"
  • ID: custbody_ngcu_po_bill_diff
  • Type: Currency
  • Store Value: False
  • Applies To: Purchase
  • Display Type: Inline Text
  • Help Text: This field has a calculated value which is not stored but is calculated when the field is viewed via forms or accessed via a workflow. It will show the difference between the PO and Vendor Bill totals. The value is driven by a saved search result. 
  • Validation & Defaulting
    • Search: "PO/Bill Total Difference (Used on Transaction Body Field)"
    • Field: "Original PO"
  • Save & Apply to Forms  - Show on only PO forms

Workflow - Set PO/Bill Variance Exception

Create a new Workflow under the Customization -> Workflow -> Workflows menu:

Workflow General Setups

  • Name "Set PO/Bill Variance Exception"
  • ID: customworkflow_ngcu_set_po_bill_variance
  • Record Type: Transaction
  • Sub-Types: Purchase Order, Vendor Bill
  • Execute As Admin: True
  • Release Status: Released (note that you can start with this in Testing but only the script owner will be able to test)
  • Initiation: Event Based
  • On Create: True
  • On View or Update: True
  • Trigger Type: All
  • Condition: Optional if you want to limit by subsidiary or applicable sub-set of transactions, recommended after initial testing

Workflow State Setups

Note that this workflow has only one state. There are two workflow actions in this state:


Action 1 - Set Field Value - Original PO:

This action sets the "Original PO" field on the PO so that the Vendor Bill is linked back to the PO.

  • Type: Set Field Value
  • Trigger On: Before Record Submit
  • Event Types: Approve, Edit, Direct List Edit
  • Condition:
    • Type = Purchase Order
  • Parameters: 
    • Field: Original PO
    • Value: Formula:
 {id}

Action 2 - Set Field Value - Original PO:

This action sets the "PO/Bill Variance Exception" field to true if there is a mis-match. 

  • Type: Set Field Value
  • Trigger On: After Record Submit
  • Event Types: All 
  • Condition:
    • Type = Purchase Order
  • Parameters: 
    • Field: "PO/Bill Variance Exception"
    • Value: Formula: See below options for different example formulas
 case when {custbody_ngcu_po_bill_diff} > 50  then 'T' else 'F' end

This formula is a simple test to start with, it checks for a currency difference of greater than 50 and then sets a match exception. Test this formula first to ensure the workflow is working and then look for different possible variations below. 

Testing

Once the workflow has been activated the process can be tested by using the following steps:

Create a new PO with a quantity of 2 and line amount of 2,000
Save the PO
Make sure that the "Original PO" field has been set and points to its self. 


Convert the PO to a vendor bill and change the quantity to 1 and the line amount to 900
Save the Vendor Bill
Make sure that the "Original PO" field points back to the original PO

Navigate back to the PO
Make sure that the "PO/Bill Difference" field shows a negative number (-1,100)
Make sure that the "PO/Bill Variance Exception" check box is not checked  (Pass on 1st Scenario) 


Create a new Vendor Bill from the PO to bill the remaining quantity
Adjust the amount to be 1,500
Save the vendor bill
Make sure that the "Original PO" field points back to the original PO

Navigate back to the Original PO
Make sure that the "PO/Bill Difference" field shows a positive number (400)
Make sure that the "PO/Bill Variance Exception" check box is checked (Pass on 2nd Scenario)

Edit the 2nd Vendor Bill and set the amount on the line from 1,500 to instead be 100
Save the vendor bill

Navigate back to the Original PO
Make sure that the "PO/Bill Difference" field shows a negative number (-1,000)
Make sure that the "PO/Bill Variance Exception" check box is not checked (Pass on 3rd Scenario)

If all of these three scenarios worked correctly, the check box can be used in approval workflows and formulas to trigger approvals on Vendor Bills. The formula to set this box to true can be adjusted as needed in the workflow and re-tested. 

Formula Options

The formula in the workflow to set the "PO/Bill Variance Exception" check box can be adjusted as needed. Here are some other examples of what is possible:

No Overage Allowed

case when {custbody_ngcu_po_bill_diff} > 0 then 'T' else 'F' end

Fixed Amount Allowed

case when {custbody_ngcu_po_bill_diff} > 200 then 'T' else 'F' end

Up To 10% Variance Acceptable 

case when {custbody_ngcu_po_bill_diff} /  {total} * 100  > 10 then 'T' else 'F' end
Was this article helpful?