What are Lookups?
Lookups describe a situation where you need to find a target piece of data, like an Account ID, but you only have a related public piece of data, like an email domain. You can reference some data store, like a database or SaaS platform, to “look up” the Account ID based on the email domain.
If you’ve ever “looked up” the definition of a word in a dictionary, then you already understand the concept -- the spelling of the word is your starting public data (equivalent to the email domain), the definition of the word is the target data (equivalent to the Account ID), and the dictionary itself is the data store.
Why are Lookups useful?
Lookups solve critical problems that may arise when integrating external data (like EDI documents) into internal systems (like CRM or ERP platforms). When you integrate data into an ERP system, you may need to reference internal identifiers or private values like an Account ID, a warehouse ID, or a contract number. Inbound EDI documents will contain public information, like the name of a company or an email, but they will not typically contain these internal values that your ERP system requires.
Lookups can bridge the gap between the public data provided in EDI documents and the internal values necessary for integrating these documents into your systems. By “looking up” an Account ID based on the email domain provided in an EDI document, you can facilitate integration into ERP systems without requiring that your business partner sends you these ID values each time.
How do I perform Lookups in Arc?
Each connector in Arc that connects to a data store or SaaS application (e.g. databases, CRM applications, ERP systems, accounting platforms, etc) can perform a Lookup. Simply set the Action setting to ‘Lookup’ within the connector to bring up the Lookup configuration UI.
Conceptually, Lookups work by querying the data store for a target value and injecting that value into the XML message that is currently passing through the flow. For example, an EDI document is converted into XML via an EDI connector, then that XML is passed along to a SQL Server connector that is configured with the Lookup action. The SQL Server connector performs the lookup query as configured, and the value returned from this query is inserted into a specified location (XPath) within the XML message. The XML message, now containing an XML element with the target value, then continues along the Arc flow path.
How do I configure a Lookup?
Lookups require 5 configuration parameters:
- Connection to the database/application
- Table or data object within the data store that contains the target value
- Column within the table/data object that represents the target value
- Filters to ensure only the desired target value is queried
- XPath within the Arc XML document where the target value should be inserted
The connection to the database/application is the same as other connections created to access this same database or application, and has often already been created (and can simply be used in the connector performing the lookup).
The table or data object is helps Arc know where to find the target value -- for example, the Account ID value is found in the Accounts table/object, so the appropriate configuration value would be ‘Accounts’. Arc lists the tables/objects it detects in the UI so that you can simply choose the appropriate table.
The column represents the value within the table that you are targeting -- for example, the ‘ID’ column contains the Account ID value, so the appropriate configuration value would be ‘ID’.
Filters ensure that you don’t accidentally return more values than the specific target value you have in mind. For example, if you want the Account ID for a specific company, then you need to use the company name as a filter for the Account ID query.
The Parent-Level XPath and New Node Name together determine where the target value should end up in the XML message currently being processed by the connector performing the lookup. The parent XPath should be an existing path in the document, and the new node name determines what node will be inserted at this path to contain the target value.
The Parent-Level XPath is made simpler by utilizing the optional configuration parameter called Sample File. The sample file should be an example XML document that will be passed along to the connector performing the lookup (e.g. if the lookup is being performed for an EDI document, then the sample file will be the XML translation of that EDI data). Setting the sample file enables the Arc UI to show a dropdown of all available Parent-Level XPaths to determine where in the document to insert the new target value. Without setting the sample file, users will need to manually type in this Parent-Level XPath, which requires viewing and understanding the XML message being processed.
Example configuration:
An example configuration of an Account ID lookup might look like this (connection to the data store not shown):
In this example, the AccountID is being “looked up” via the ‘ID’ column of the ‘Account’ table, and is being filtered to only grab the Account ID for the account whose name matches the ‘customer name’ provided in the input XML.
