This page provides a guide to Establishing a Connection to Acumatica in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to Acumatica

Establishing a Connection shows how to authenticate to Acumatica and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from Acumatica through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to Acumatica by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Connecting to Acumatica

In order to connect to the Acumatica data source, you must specify the following connection properties.

• URL: (Required) The base URL for the Acumatica ERP instance. For example: https://domain.acumatica.com/.
• Schema: (Optional) There are two schemas that contain different data. The default one is REST, which uses the Acumatica REST Contract-Based API, and the OData schema, which uses the Acumatica OData API. The OData schema is used to query Acumatica Generic Inquiries.
• Company: (Partially required) Set this to the name of your company or tenant. It is required if Schema is set to OData.
• EndpointVersion: (Optional) The version of the Web Services endpoint. For example: 24.200.001. This applies only to the REST schema.
• EndpointName: (Optional) The name of the Web Services endpoint. For example: Default. This applies only to the REST schema.

To find out the EndpointVersion and EndpointName for your Acumatica instance, log into Acumatica in a web browser, and then navigate to the Web Service Endpoints page. If necessary, navigate to this page by editing the web browser URL and replacing ScreenId=00000000 (the homepage) with ScreenId=SM207060. If you are redirected back to the homepage, this means your user does not have the necessary permissions to access web services. Under Endpoints properties get the Endpoint Name and Endpoint Version.

Exposing Generic Inquiries

The Cloud is capable of discovering generic inquiry data as tables and views. However, you must first extend the Web Services Endpoint to expose them.

The process for exposing generic inquiries is different depending on the schema specified in the Schema connection property.

REST Schema

Generic Inquiries are not exposed via REST by default. To expose them:

1. Navigate in the Acumatica UI to Integration > Show All > Web Service Endpoints.
2. Click the "Default" endpoint with the most recent Endpoint Version.
3. From the available action buttons (in the button bar below the endpoint name at the top of the page), click EXTEND ENDPOINT.
   • You may have to find this action in the full action list, which you can view by clicking the "..." button.
4. Use Endpoint Name to name the extended endpoint and Endpoint Version to specify the endpoint version you want it to have, then click OK.
5. On the page for your new extended endpoint, click the + Insert button (above the ENDPOINT tree structure).
6. Create an entity definition for a new custom object as follows.
   • Name the object in the Object Name field.
   • Leave the Object Type field set to the default -- Top-Level.
   • In the Screen Name field, click the magnifying glass icon to search for the screen associated with your generic inquiry. Select the screen matching the Site Map Title field of your generic inquiry.
     • Ensure that the autopopulated Screen ID field's value matches that of your generic inquiry.
   • Click OK.
7. Create a new "Results" entity under your custom object as follows:
   • Select the newly created object in the ENDPOINT tree structure, then click the + Insert button.
   • Set Field Name to "Result".
   • Specify a name for this object in Object Name.
   • Set Object Type to Detail.
   • Click OK.
8. In the ENDPOINT tree structure, expand the node for your custom object, select the new Results object you just created, then click FIELDS > POPULATE.
9. In the Populate Field window's Object field, select "Result" (the object you just created).
10. Select the check box for each field you want to expose from the general inquiry, then click OK.
11. To see the exposed general inquiry in your table list, set EndpointName to the name of your extended endpoint and set InquiryTables to the name of your top-level custom object from step 6 above.

OData Schema

Generic Inquiries are not exposed via OData by default. To expose them, navigate in the Acumatica UI to Customization > Profiles > Generic Inquiry and select the Expose via OData checkbox.

Authenticating to Acumatica

There are two authentication methods available for connecting to Acumatica data source, Basic and OAuth.

User Credentials

Set the AuthScheme to Basic and set the User and Password to your login credentials.

OAuth

Web Applications

When connecting via a Web application, you need to create and register a custom OAuth application with Acumatica. See Creating a Custom OAuth App for more information about custom applications. You can then use the Cloud to acquire and manage the OAuth token values.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the client Id in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the callback URL you specified in your application settings. If necessary, set the Scope parameter to request custom permissions.

   The stored procedure returns the URL to the OAuth endpoint.

2. Open the URL, log in, and authorize the application. You are redirected back to the callback URL.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the "code" parameter in the query string of the callback URL. If necessary, set the Scope parameter to request custom permissions.

Once you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the driver automatically refresh the OAuth access token, set the following for the first data connection:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the client Id in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where the Cloud saves the OAuth token values, which persist across connections.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token.

Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the client Id in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After retrieving the new tokens, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

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. Mostly the Navigation Views break the optimization because Acumatica is trying to get data from the linked tables, because of this the SELECT * queries from Navigation Views without specifying the key of the parent table are not possible. Our drivers tries to remove the columns that break the optimization from the SELECT * queries.

Some of the Views that break the optimization when selected without specifying the keyfields are JournalVoucher_Details. In order to qyery the JournalVoucher_Details the ParentBatchNbr should be specified.

 SELECT * FROM JournalVoucher_Details where ParentBatchNbr = '0001'

The view can be queried by geting the parent key field in this case ParentBatchNbr from the parent table JournalVoucher.

 SELECT * FROM JournalVoucher_Details where ParentBatchNbr IN (SELECT BatchNbr FROM JournalVoucher)

More information on how to bypass the Acumatica optimization can be found here: Limitations.

Customizing the SSL Configuration

To enable TLS, set the following:

• URL: Prefix the connection string with https://

With this configuration, the Cloud attempts to negotiate TLS with the server. The server certificate is validated against the default system trusted certificate store. You can override how the certificate gets validated using the SSLServerCert connection property.

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

• ProxyServer: the hostname or IP address of the proxy server that you want to route HTTP traffic through.
• ProxyPort: the TCP port that the proxy server is running on.
• ProxyAuthScheme: the authentication method the Cloud uses when authenticating to the proxy server.
• ProxyUser: the username of a user account registered with the proxy server.
• ProxyPassword: the password associated with the ProxyUser.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The Cloud models the Acumatica API as relational tables.

When you connect, the Cloud connects to Acumatica and gets the list of tables and the metadata for the tables and views.

Since the table definitions are dynamically retrieved, any changes to the remote data are immediately reflected in your queries;.

Generic Inquiries

The Cloud can discover generic inquiry data as tables and views when the Web Services Endpoint has been extended to expose them.

See for a guide to extending the Web Services Endpoint and exposing your generic inquiries.

Generic inquiries can also be configured to support server-side filtering. Server-side Filtering for Generic Inquiries describes this procedure.

Limitations

Limitations describes the limits of the Acumatica REST API and how they affect the operation of the Cloud.

Tables

Tables shows default table definitions from Acumatica. Your actual tables may differ depending on your customizations. Set the URL connection property to the latest version to get the changes.

The CData Cloud reads table and column metadata dynamically, so it will include both your customizations and any new features that are added to standard Acumatica entities in the future.

Views

Views are read-only tables representing Acumatica entities such as child tables.

Stored Procedures

Stored Procedures are function-like interfaces to Acumatica. They can be used to perform extra tasks that do not fit into the standard operation of directly interacting with a table.

Server-side Filtering for Generic Inquiries

By default, queries that filter on general inquiry columns will not be processed server-side. In order to enable server-side filtering for these columns, you must first explicitly set them as filter columns in the Acumatica UI.

To expose a general inquiry column as a filter:

1. Navigate in the Acumatica UI to the sidebar and select More Items > Customization > Generic Inquiry. The configuration page for your generic inquiry appears.
2. Click the magnifying glass icon in the Inquiry Title field. Select the generic inquiry that you have exposed as a table.
3. Click PARAMETERS. Perform the following steps for each column for which you want to enable server-side filtering.
   1. Click + (Add Row).
   2. Ensure the checkbox in the Active column is checked.
   3. Provide a name for the parameter in the Name column.
   4. Click the dropdown in the Schema Field column. Choose the general inquiry column for which you want to enable server-side filtering.
4. Click CONDITIONS. Perform the following steps for each column for which you want to enable server-side filtering.
   1. Click + (Add Row).
   2. Ensure the checkbox in the Active column is checked.
   3. Leave the Condition column set to Equals.
   4. Click the dropdown in the Data Field column. Choose the general inquiry column for which you want to enable server-side filtering. This should match the column chosen in the Schema Field column on the PARAMETERS tab.
   5. Click the dropdown in the Value 1 column. Choose the parameter you created earlier for this column.
5. Click the Save icon to submit these changes.
6. Navigate in the sidebar to Integration > Web Service Endpoints (expand the Integration page with Show More to find this, if needed).
7. Select the endpoint in which you have exposed the general inquiry as an object.
8. Expand the node for the general inquiry object's endpoint in the ENDPOINT tree structure, then select its child entity (Result), which you created earlier.
9. Click the FIELDS tab. Perform the following steps for each column for which you want to enable server-side filtering.
   1. Select the Mapped Object field associated with the column, then click its magnifying glass icon.
   2. Double-click Filter to set "Filter" as the mapped object.
10. Click the Save icon to submit these changes. Server-side filtering for these columns is now enabled.

Custom Fields

By default, the table and view schemas describe the column behavior of system fields as well as custom fields. You can set the IncludeCustomFields property to false if you want to work only with system fields. On a SELECT * FROM EntityName query, the custom fields will not be returned even though 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

Linked and Detail Entities

The Linked and Detail entities to main entity are exposed in the main entity table as aggregate columns starting with the 'Linked' prefix. They are also exposed as separate views using the naming format 'MainEntityName_LinkedEntityName'. For example, the Customer table exposes the linked entity 'Contacts' as the aggregate field 'LinkedContacts'. The linked entity Contacts is also exposed as the separate view Customer_Contacts.

Due to Acumatica REST API limitations, the Linked and Detail entities cannot be retrieved when requesting multiple main entity records. For this reason, when querying the main entity, without specifying the primary key in the WHERE clause conditions, the linked aggregate fields are returned as null. For example, in order to retrieve the aggregate values of the linked entity Contacts in the Customer table, you can execute one of the queries below.

SELECT CustomerID, LinkedContacts FROM Customer WHERE customerId = '100009'

You can also make use of the IN operator and subqueries as below, in order to retrieve the Linked and Detail entities for multiple parent records.

SELECT CustomerID, LinkedContacts FROM Customer WHERE CustomerId IN (SELECT CustomerId FROM Customer)

Additionally, to query the exposed Linked and Detail entities, specifying the parent entity primary key in the WHERE clause conditions, is required. For example:

SELECT * from Customer_Contacts where ParentCustomerID = '100009'

Excluded Columns

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 return 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'

The Cloud models the data in Acumatica as a list of tables in a relational database that can be queried using standard SQL statements.

CData Cloud - Acumatica Tables

Account is an auto-generated table.

Columns

AccountByPeriodInquiry is an auto-generated table.

Columns

AccountBySubaccountInquiry is an auto-generated table.

Columns

AccountDetailsInquiry is an auto-generated table.

Columns

AccountLocation is an auto-generated table.

Columns

AccountSummaryInquiry is an auto-generated table.

Columns

Adjustment is an auto-generated table.

Columns

AttributeDefinition is an auto-generated table.

Columns

Bill is an auto-generated table.

Columns

BusinessAccount is an auto-generated table.

Columns

Carrier is an auto-generated table.

Columns

Case is an auto-generated table.

Columns

CashSale is an auto-generated table.

Columns

Check is an auto-generated table.

Columns

Contact is an auto-generated table.

Columns

Currency is an auto-generated table.

Columns

Customer is an auto-generated table.

Columns

CustomerClass is an auto-generated table.

Columns

CustomerLocation is an auto-generated table.

Columns

CustomerPaymentMethod is an auto-generated table.

Columns

CustomerPriceClass is an auto-generated table.

Columns

Discount is an auto-generated table.

Columns

DiscountCode is an auto-generated table.

Columns

Email is an auto-generated table.

Columns

Employee is an auto-generated table.

Columns

Event is an auto-generated table.

Columns