> ## Documentation Index
> Fetch the complete documentation index at: https://docs.arc.cdata.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Sage Intacct Lookup

> Retrieve values from Sage Intacct and insert them into an Arc message in the flow.

The Lookup action retrieves values from Sage Intacct and inserts those values into an existing
Arc message in the flow.

<ol><li>In the CData Arc flow, drag an arrow from the connector that will provide input to the Sage Intacct connector.</li>
<li>Optionally, choose a **Sample File**. A sample file is recommended because it allows the connector to provide more context about the file structure and helps you build [filter](#filters) conditions.</li>
<li>Once you have completed the configuration steps below, in the Arc flow, drag an arrow from the Sage Intacct connector to the connector where you want your resulting XML or Header data to go.</li></ol>

## Lookup Configuration

Use this section to define the table and columns to include in your query.  [Designer Mode](#designer-mode) helps you pick the table and columns you want to target with your query, instead of requiring you to build the query free form. However, you can use [Query Mode](#query-mode) if you prefer to write your query from scratch.

### Designer Mode

Follow the steps below to use the Designer to configure a Lookup action for the Sage Intacct connector.

![Lookup designer](https://docs.arc.cdata.com/26.4/images/dbsa_lookup_designer.png)

<ol><li>Use the **Table** dropdown to select the table to include.</li>
<li>Choose the set of **Columns** that you want to include from the table.</li>
<li>**Output Destination**: select **XPath** to insert the query response values into an XML file or **Header** to insert the query response value into a message header.
<ul><li>XPath: When you choose XPath, the destination is in two parts. The **Parent-Level XPath** should already exist in the XML document. The **New Node Name** is the name of the node that is injected into the XML document to hold the result of the lookup. Each column in the query has its own XML element (leaf level) in the XML element provided here. The Parent-Level XPath can include [lookahead syntax](https://docs.arc.cdata.com/26.4/cloud/en/mapping/mapping-node-value-editor.md#lookahead) to target an element by a sibling condition; uploading a sample file makes it easier to build lookahead paths.</li>
<li>Header: When you choose Header, the connector adds one header to the output message for each column in the query. The values are comma-separated for each record that is returned from the database.</li></ul></li>
<li>Use **Record Limit** to specify what should happen when the query returns multiple results.
<ul><li>If you choose **Use First Result** from the dropdown, you can use the **Error if multiple results** to see an error when multiple results are returned.</li>
<li>If you choose **Custom Limit**, you must specify the limit in the field that appears.</li></ul></li>
<li>Use **Order By** to choose which column to order the results by.</li>
<li>The [Filters](#filters) section lets you create filters to build the query WHERE clause, including references to headers or XML from the input file.</li></ol>

### Filters

You can build complex filters by defining individual filters and/or groups of filters. For readability, expand the **Filters** section by clicking the expander icon ![expander](https://docs.arc.cdata.com/26.4/images/validate_expander.png) on the right side of the bar.

To add a filter, click **Add Filter** or **Add Group**.

When you build your filters, use the dropdowns to make your selections and choose your operator. Then specify the value to filter by. You can choose from the following:

<ul><li>Message Header</li>
<li>Input XML</li>
<li>Static Value</li></ul>

When you select **Input XML** for a filter, if you have uploaded a sample file, the editor displays a tree representation of the document so you can easily find the XPath you need, as shown in the image below.

![Lookup filter configuration](https://docs.arc.cdata.com/26.4/images/dbsa_lookup_filter.png)

The XPath can also include [lookahead syntax](https://docs.arc.cdata.com/26.4/cloud/en/mapping/mapping-node-value-editor.md#lookahead) to target an element by a sibling condition. This is useful for EDI qualifier-value pairs. For example: `/Interchange/.../REF[REF01='IA']/REF02` targets the REF02 value only when its sibling REF01 equals `IA`. Enter the brackets unescaped here, unlike the backslash-escaped form used inside the *xpath()* formatter.

> [!NOTE]
>
> <ul><li>**Supported forms:** existence checks, equality checks (against child paths, attributes, current text, or quoted literals), and multiple conditions combined with AND. A standalone `[last()]` positional selector is also supported.</li>
> <li>**Unsupported forms are rejected**, not silently reinterpreted, so simplify the XPath if Lookup rejects it. Unsupported forms include: arbitrary XPath functions and operators; `[last()]` combined with another condition on the same node; multiple `[last()]` selectors in one path; a numeric selector combined with another condition on the same node; and a condition attached directly to a terminal attribute (rewrite as a parent filter instead, for example `/actor[@type='lead']/@type`).</li>
> <li>**Performance:** lookahead syntax causes Lookup to load the document into memory instead of streaming it, which uses more memory. For very large XML input, prefer an XPath with no lookaheads or one that uses a numeric position, or preprocess the input upstream, to keep Lookup in streaming mode.</li></ul>

You can reorder filters and groups. Click the handle ![handle](https://docs.arc.cdata.com/26.4/images/xmlmap_condition_handle.png) and drag the object to a new location.

### Query Mode

Follow the steps below to use the Query mode to configure a Lookup action for the Sage Intacct connector.

![Lookup query mode](https://docs.arc.cdata.com/26.4/images/dbsa_lookup_query.png)

<ol><li>**Output Destination**: select **XPath** to insert the query response values into an XML file or **Header** to insert the query response value into a message header.
<ul><li>XPath: When you choose XPath, the destination is in two parts. The **Parent-Level XPath** should already exist in the XML document. The **New Node Name** is the name of the node that is injected into the XML document to hold the result of the lookup. Each column in the query has its own XML element (leaf level) in the XML element provided here. The Parent-Level XPath can include [lookahead syntax](https://docs.arc.cdata.com/26.4/cloud/en/mapping/mapping-node-value-editor.md#lookahead) to target an element by a sibling condition; uploading a sample file makes it easier to build lookahead paths.</li>
<li>Header: When you choose Header, the connector adds one header to the output message for each column in the query. The values are comma-separated for each record that is returned from the database.</li></ul></li>
<li>In **Query**, enter the SQL query to retrieve data from Sage Intacct tables.
<ul><li>XPath: Format the path as `${XPATH:/path/to/element}`. For example, `SELECT CustomerID FROM Customers WHERE CustomerName = ${XPATH:/Items/Invoices/Customer}`. The XPath can also include [lookahead syntax](https://docs.arc.cdata.com/26.4/cloud/en/mapping/mapping-node-value-editor.md#lookahead) to target an element by a sibling condition. For example, `${XPATH:/Interchange/.../REF[REF01='IA']/REF02}` targets the REF02 value only when its sibling REF01 equals `IA`. For supported lookahead forms and performance considerations, see [Filters](#filters).</li>
<li>Header: Format the path as `${HEADER:HeaderName}`.</li></ul></li>
<li>The two checkboxes let you configure what should happen when the query returns multiple results.
<ul><li>Check **Use First Result** to always use the first result.</li>
<li>Check **Error if multiple results** to see an error when multiple results are returned.</li></ul></li></ol>

## Advanced

![Lookup advanced settings](https://docs.arc.cdata.com/26.4/images/dbsa_lookup_advanced.png)
<ol><li>Use **No Matching Record Found Action** to specify what should happen if no record is found that matches the query.</li>
<li>The Sage Intacct connector can perform Lookup queries against a local cache instead of querying Sage Intacct directly for each Lookup. This can improve query performance, especially with larger databases. To enable this, select **Enabled** under **Lookup Caching** and configure the rest of the fields.
<ul><li>**Lookup Cache Provider**: Choose which driver to use to facilitate the connection to the cache database.</li>
<li>**Lookup Cache Connection String**: When set, the connector uses the database specified here for caching.</li>
<li>**Lookup Cache Tolerance**: Controls how often the connector issues a query to the data source to refresh the cache.</li>
<li>**Lookup Cache Tolerance Unit**: The **Lookup Cache Tolerance** unit. Choose Minute, Hour, or Day.</li>
<li>**Reset Cache**: Clears all cached data and forces the connector to re-query Sage Intacct on the next Lookup.</li></ul></li></ol>

> [!NOTE]
>
> <ul><li>If a query returns no results from the cache, the connector queries Sage Intacct directly.</li>
> <li>If your query statement contains a `limit`, `offset`, or `groupby` parameter, the cache function does not work. These queries go directly to Sage Intacct.</li></ul>

## Test Lookup

Use the **Test Lookup** button to check your configuration and see the result
without having to process files manually. Use the **Save Sample Data** button to
save the output as a sample for mapping later in the flow.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.