Exact Online Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8803 of the connector.

Get Started¶

Exact Online Version Support

The Exact Online connector connects with V1 of the REST API.

Establish a Connection¶

Authenticate via OAuth Authentication¶

You can connect after setting required connection properties for your user credentials and obtaining required access tokens using a tool such as Postman.

• Division: Set to the division of the Exact Online administration.
• Region: Set to the region of the Exact Online service you want to connect to.

See Using OAuth Authentication for details of the OAuth authentication flow.

Create a Custom OAuth Application¶

Create a Custom OAuth Application¶

embeds OAuth Application Credentials with branding that can be used when connecting to Exact Online via a desktop application or a headless machine. If you want to use the embedded OAuth application, all you need to do to connect is to:

• set AuthScheme to OAuth,
• get and set the OAuthAccessToken, and
• set the necessary configuration parameters.

(For information on getting and setting the OAuthAccessToken and other configuration parameters, see the Desktop Authentication section of "Connecting to Exact Online".)

However, you must create a custom OAuth application to connect to Exact Online via the Web. And since custom OAuth applications seamlessly support all three commonly-used auth flows, you might want to create custom OAuth applications (use your own OAuth Application Credentials) for those auth flows anyway.

Custom OAuth applications are useful if you want to:

• control branding of the authentication dialog;
• control the redirect URI that the application redirects the user to after the user authenticates; or
• customize the permissions that you are requesting from the user.

Procedure¶

To create a custom OAuth application for use in a Desktop, Web, or Headless Machine auth flow, and obtain the appropriate connection properties:

1. If you have not already done so, create an Exact Online developer account.

2. Log into the App Center.

3. Navigate to Manage Apps > Add a New Application.

4. Enter a name for the application. This name will be displayed to users when they are prompted to grant permissions to connect.

5. Set the Redirect URI:

   • For connecting via either a Desktop or a Headless Machine, set the Redirect URI to https://oauth.cdata.com/oauth/.
   • For connecting via the Web, set the Redirect URI to a page where you would like the user to be returned after they have granted your application permission to connect.

6. Click Edit. The App Center displays your new application's client credentials,

   client Id, and client secret.

Set these OAuth credentials, plus Division and Region, before you connect.

Important Notes¶

Configuration Files and Their Paths¶

• All references to adding configuration files and their paths refer to files and locations on the Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features¶

This section details a selection of advanced features of the Exact Online connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Exact Online and then processes the rest of the query in memory (client-side).

User Defined Views¶

The Exact Online connector allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File¶

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Accounts WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements¶

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View¶

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View¶

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View¶

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views¶

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views¶

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration¶

Customize the SSL Configuration¶

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Data Model¶

The Exact Online connector models entities from the Exact Online API as relational tables and stored procedures. The Exact Online connector dynamically retrieves the table definitions. When you connect, the connector gets the list of tables and the metadata for the tables by calling the appropriate Web services.

Tables

Tables allow access to the data from the data source.

Stored Procedures

Stored Procedures are function-like interfaces to Exact Online. They can be used to search, update, and modify information

in Exact Online.

Tables¶

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

Exact Online Connector Tables¶

AcceptQuotation¶

Usage information for the operation AcceptQuotation.rsd.

Columns¶

AccountInvolvedAccounts¶

Usage information for the operation AccountInvolvedAccounts.rsd.

Columns¶

AccountOwners¶

Usage information for the operation AccountOwners.rsd.

Columns¶

Accounts¶

Usage information for the operation Accounts.rsd.

Columns¶

Addresses¶

Usage information for the operation Addresses.rsd.

Columns¶

BankAccounts¶

Usage information for the operation BankAccounts.rsd.

Columns¶

BankEntries¶

Usage information for the operation BankEntries.rsd.

Columns¶

BankEntryLines¶

Usage information for the operation BankEntryLines.rsd.

Columns¶

BillOfMaterialRoutings¶

Use this endpoint to create, read, update and delete routings in a bill of material version linked to a make item.

Columns¶

ByProductReceipts¶

Usage information for the operation ByProductReceipts.rsd.

Columns¶

ByProductReversals¶

Usage information for the operation ByProductReversals.rsd.

Columns¶

CashEntries¶

Usage information for the operation CashEntries.rsd.

Columns¶

CashEntryLines¶

Usage information for the operation CashEntryLines.rsd.

Columns¶

CommunicationNotes¶

Usage information for the operation CommunicationNotes.rsd.

Columns¶

Complaints¶

Usage information for the operation Complaints.rsd.

Columns¶

Contacts¶

Usage information for the operation Contacts.rsd.

Columns¶

Costcenters¶

Usage information for the operation Costcenters.rsd.

Columns¶

CostTransactions¶

Usage information for the operation CostTransactions.rsd.

Columns¶

Costunits¶

Usage information for the operation Costunits.rsd.

Columns¶

DepreciationMethods¶

Usage information for the operation DepreciationMethods.rsd.

Columns¶

DirectDebitMandates¶

Usage information for the operation DirectDebitMandates.rsd.

Columns¶

DocumentAttachments¶

Usage information for the operation DocumentAttachments.rsd.

Columns¶

DocumentFolders¶

Usage information for the operation DocumentFolders.rsd.

Columns¶

Documents¶

Usage information for the operation Documents.rsd.

Columns¶

DocumentTypeFolders¶

Usage information for the operation DocumentTypeFolders.rsd.

Columns¶

DropShipmentLines¶

To List, Create and Update DropShipmentLines.

Columns¶

DropShipments¶

To List, Create and Update DropShipments.

Columns¶

Events¶

Usage information for the operation Events.rsd.

Columns¶

ExchangeRates¶

Usage information for the operation ExchangeRates.rsd.

Columns¶

GeneralJournalEntries¶

Usage information for the operation GeneralJournalEntries.rsd.

Columns¶

GeneralJournalEntryLines¶

Usage information for the operation GeneralJournalEntryLines.rsd.

Columns¶

GLAccounts¶

Usage information for the operation GLAccounts.rsd.

Columns¶