The Lookup Table Validator action allows you to validate data against data in another Record Type

If you have a Record Type that has master data in it you an use this action to check data against that Record Type. In Accounts Payable this is commonly used for Vendor data matching. Vendor names are often read from invoices using OCR which is not 100% perfect. When the vendor has been read you can use this action to check the vendor name against the master list to see if there is a match.


Field to Validate


    • Field to Validate - The field in the current Record that needs checked. In the AP example above this might be "Vendor."


Lookup Table


    • Lookup Table - The Record Type that contains the master list of correct values to check against. In the AP example above this might be the "Vendors" Record Type.


    • Lookup Limits - There are times when your master list contains data for more than one type of information or for more than one department. If you need to limit the records you are matching against, add the additional limits here. For instance, you might limit the vendor names to match against by the department stored in the invoice's DEPT field. For this you would set something like Department = [Dept] in the filters.


    • Sort By - If more than one record matches the validation, you can use the sort order to make sure the most likely ones are found first. The first match found is used.


    • Match Field - This is the field name in the lookup table that is used to find the match value. In the AP example above this might be the "Vendor Name" field.



Validation Methods



    • Exact Match - The value in the lookup table must exactly match that of the current record to be considered correct. 


    • Exact w/out Spaces - All spaces are removed from the values and then compared. The value in the lookup table must exactly match that of the current record (with spaces removed) to be considered correct.


    • Starts With - The value in the lookup table must start with that of the current record to be considered correct. 


    • Contains - The value in the lookup table must contain that of the current record to be considered correct. 


    • Each Word - This is more advanced matching and in some scenarios can result in too many false matches so use this with caution. Each word in the current record's value is required to be in the lookup table field and in the same order. For instance, if the current record has "acme vendor" then that would match any of the follow lookup table values:
      • Matching Values
        • ACME VENDOR NAME HERE
        • ACME VENDORING COMPANY
        • ACME IS THE NAME OF THIS VENDOR
      • Non matching values
        • VENDOR NAME IS ACME
        • VENDOR ACME
        • ACME Company


Field Updates

    • Update if Matched - You can update the current Record's data fields here IF AND ONLY IF you find a match in the lookup table. This is a great way to pull in address info or ID numbers.


    • Update if NOT Matched - You can update the current Record's data fields here IF AND ONLY IF you DO NOT find a match in the lookup table. This is a great way to mark the record as having no match in the lookup table. For instance, you might set VENDORID = NOMATCH here then in another workflow trigger you look for invoices where VENDORID = NOMATCH and handle the exceptions there.