Tableau Connector for Acumatica

Build 20.0.7606

Custom Fields

By default, the table and view schemas describe the column behavior of only system fields. To access custom fields as well as system fields, you can set IncludeCustomFields to true in the connection string. On SELECT * FROM EntityName the custom fields will not be returned even though the IncludeCustomFields is set to true. The Custom fields will be returned only when they are specified on projections. For example:

SELECT Custom_FieldA, Custom_FieldB, Custom_FieldC FROM EntityName

The requirement to explicitly select the custom fields is due to a limitation with the Acumatica API. It will not return results when too much overall data is selected per record. It is not possible to dynamically calculate at what point Acumatica will cut off results. You should only select the columns you need and not everything to keep things working smoothly.

In case a group of custom fields are important to your use case and the Acumatica API cannot return them in a single response, it is recommended to setup an extended endpoint and include the custom fields in it. After this change, you should be able to retrieve them as non-custom fields from the extended endpoint.

An alternative to extending an existing endpoint is to split the main query into two or more subqueries and distribute the selected custom fields between them. Lastly, you can join the subqueries on the primary key column as in the example below.

SELECT Customer_1.CustomerID, Customer_1.Custom_Timezone, Customer_1.Custom_ProductGroup, Customer_2.Custom_ContactCategory, Customer_2.Custom_DiscountGroup 
FROM Customer Customer_1 JOIN Customer Customer_2 
ON Customer_1.CustomerID = Customer_2.CustomerID

Extending Schemas

If you are using the default endpoint and the 17.200.001 version of the Acumatica API the driver works out of the box with all tables, views, and columns provided by the Acumatica Contract API.

If you have extended the API and now have a new version with new entities that didn't exist in version 17.200.001, you may need to make some changes to the metadata schema files. In general things will still work without these changes, but due to the limitations of the API there are some metadata values you can manually mark up to make things work better.

Table and view schemas are defined in .rsd configuration files with a simple format that makes them easy to customize. You can call CreateSchema to save a schema file. Before executing CreateSchema, make sure to set the Location connection property. You may also wish to set IncludeCustomFields connection property to true. For example:

EXEC CreateSchema @TableName='Customer', @FileName='Customer.rsd'

After calling CreateSchema, an .rsd file will be written to the folder defined in your Location. This is a simple xml file that will contain the definition for the table.

To use to the custom schemas instead of reading table and column metadata dynamically, set the Location property to the folder containing the schema files. Any table definitions found in the Location will be read from that file instead of dynamically. All other table definitions will be read dynamically.

If you need to work with a lot of files, set Location and set GenerateSchemaFiles to OnStart. Then execute a simple column listing from one table. This will cause .rsd files to be written for all tables to the Location instead of having to create them one at a time.

Adding Additional Parameters

Acumatica has introduced an optimization in version 3 of the Contract Based API. When multiple records are retrieved from the Acumatica ERP through an endpoint, the system tries to optimize the retrieval of the records and obtain all needed records in one request to the database. If the optimization fails, the system returns an error, specifying the entities or fields that caused the failure of the optimized request. To prevent the error from occurring, we need to do one of the following:

  • Get all the records one by one by specifying the key fields.
  • Get only the fields that don't break the optimization.

Create the schema by calling the CreateSchema if you have not already done so.

Add the additional parameters in the .rsd schema files below.

  • key="true"
  • other:exclude="true"

These two parameters are needed so the new entities don't throw an error in SELECT * FROM Table queries. The example below is a rsd snippet that shows how these parameters are used.

In the example Contact.rsd, Duplicate and AddressValidated are fields that break the Acumatica optimization so they have the additional parameter other:exclude="true". When a SELECT * FROM Contact query is done, the driver will get all fields except those with the other:exclude="true" parameter. However, the omitted fields will still appear on your result set as null. The data provider will request the excluded fields from the endpoint only if they are explicitly stated in the SELECT clause or if the primary key column/s are specified.

In the example Contact.rsd, ContactID is the primary key so the additional parameter key="true" is added. When a SELECT * FROM Contact WHERE ContactID='contactid' is done, the driver will get all fields from the API including the Duplicate and AddressValidated.

<rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2">
  <rsb:info title="Contact" description="Contact is an auto-generated table." other:tablehref="https://try.acumatica.com/ISV/entity/Default/17.200.001/Contact" other:tablekind="EntitySet"/>
    <attr name="LanguageOrLocale" xs:type="string" columnsize="2000" readonly="false" description="null" other:entityname="Contact" other:schemanamespace="Contact" other:internalcolumnname="LanguageOrLocale"/>
    <attr name="DuplicateFound" xs:type="bool" readonly="false" description="null" other:internalcolumnname="DuplicateFound"/>
    <attr name="JobTitle" xs:type="string" columnsize="2000" readonly="false" description="null" other:internalcolumnname="JobTitle"/>
    <attr name="ContactID" xs:type="int" key="true" readonly="false" description="null" other:queue="0" other:internalkey="ContactID" other:internalcolumnname="ContactID"/>
    .......
    <attr name="Duplicate" xs:type="string" columnsize="2000" readonly="false" description="null" other:exclude="true" other:internalcolumnname="Duplicate"/>
    .......
    <attr name="AddressValidated" xs:type="bool" readonly="false" description="null" other:exclude="true" other:internalcolumnname="AddressValidated"/>
    .......
      <rsb:set attr="other:tablekind" value="EntitySet" />
  <rsb:set attr="tablename" value="Contact" />
  <rsb:set attr="description" value="Contact is an auto-generated table." />
  <rsb:set attr="entity" value="Contact" />
  <rsb:set attr="other:tablehref" value="https://try.acumatica.com/ISV/entity/Default/17.200.001/Contact" />
  <rsb:set attr="supportgetdeleted" value="true" />

  <rsb:script method="GET">
    <rsb:call op="acumaticaadoExecuteSearch">
      <rsb:push/>
    </rsb:call>
  </rsb:script>

  <rsb:script method="POST">
    <rsb:call op="acumaticaadoExecuteInsert">
      <rsb:push/>
    </rsb:call>
  </rsb:script>

  <rsb:script method="MERGE">
    <rsb:call op="acumaticaadoExecuteUpdate">
      <rsb:push/>
    </rsb:call>
  </rsb:script>

  <rsb:script method="DELETE">
    <rsb:call op="acumaticaadoExecuteDelete">
      <rsb:push/>
    </rsb:call>
  </rsb:script>
</rsb:script>
    

Creating User Defined Views

A simple and effective way to automate the process of getting all records one by one by specifying the key fields are the User Defined Views. User Defined Views are predefined queries stored in a local JSON file, which can be queried from the driver just as the existing views.

As stated in the above section, the excluded fields will be retrieved if the primary key is specified. You can use the driver's enhanced SQL support to automatically do this for each record by appending an IN subquery to your main query. From the subquery, you should be selecting only the primary key column as in the example below.

SELECT * FROM Contact WHERE ContactId IN (SELECT ContactId FROM Contact)

You can then add this query under the "query" field of a local created JSON file (ex. C:\Users\user\Desktop\CustomViews.json).

{
    "AllContacts": {
        "query": "SELECT * FROM Contact WHERE ContactId IN (SELECT ContactId FROM Contact)"
    },
    "AllCustomers": {
        "query": "SELECT * FROM Customer WHERE CustomerId IN (SELECT CustomerId FROM Customer)"
    }
}

Lastly, you can directly query the defined views (AllContacts and AllCustomers) after stating the Custom Views file location in the UserDefinedViews property under the Other connection parameter.

Other='UserDefinedViews=C:\Users\User\Desktop\CustomViews.json;'

You can also execute a query to the sys_tables view to verify if the custom views are added. You should be able to find them under the UserViews schema.

SELECT * FROM sys_tables where TableType='view'

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7606