SuiteCRM Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8804 of the connector.

Get Started

SuiteCRM Version Support

The connector enables real-time, bidirectional SQL access to SuiteCRM instances version 2 and later.

Establish a Connection

Connect to SuiteCRM

Requirements for connecting to SuiteCRM differ, depending on which version of the API your site is running.

SuiteCRM V4.1 API

To connect to SuiteCRM data via the V4.1 API, set these connection properties:

• Schema: suitecrmv4.
• URL: The URL associated with the SuiteCRM application. For example, http://suite.crm.com.
• User: The user associated with the SuiteCRM account.
• Password: The password associated with the user of the SuiteCRM account.

SuiteCRM V8 API

SuiteCRM V8 uses the OAuth2 authentication standard. Before you connect to SuiteCRM V8 API, you must ensure that is it properly configured to provide the OAuth2 private and public keys. For further information, see the SuiteCRM Developer Guide API V8 setup instructions.

Authenticate to SuiteCRM V8 API

The SuiteCRM V8 API uses OAuth2.0 as its authentication mechanism, based on either of two grant types: PASSWORD and CLIENT.

Authentication via OAuth requires the Schema to be set to suitecrmv8, and the creation of a custom OAuth application, as described in Creating a Custom OAuth Application.

Client Credentials Grant

To connect to SuiteCRM V8 API, set these properties:

• Schema: suitecrmv8.
• AuthScheme: OAuthClient.
• OAuthClientId: The client key returned during custom OAuth application creation. (See Creating a Custom OAuth Application.)
• OAuthClientSecret: The client secret returned during custom OAuth application creation (See Creating a Custom OAuth Application.)
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
• URL: The base URL of your SuiteCRM system. For example, https://suitecrmhost/.

Password Grant

To connect to SuiteCRM V8 API, set these properties:

• Schema: suitecrmv8.
• AuthScheme: OAuthPassword.
• OAuthClientId: The client key returned during custom OAuth application creation. (See Creating a Custom OAuth Application.)
• OAuthClientSecret: The client secret returned during custom OAuth application creation. (See Creating a Custom OAuth Application.)
• User: The user's username.
• Password: The user's password.
• InitiateOAuth: GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
• URL: The base URL of your SuiteCRM system. For example, https://suitecrmhost/.

Create a Custom OAuth Application

Create a Custom OAuth Application

SuiteCRM V8 API uses OAuth2.0 as its main method of authentication, in conjunction with either CLIENT credentials grant or PASSWORD grant. A custom OAuth application is used to secure the private and public OAuth keys.

OAuth authentication requires a SuiteCRM admin to create and register a custom OAuth application, and to ensure that users of both grant types have appropriate roles to access the API.

Register an Application

In the SuiteCRM admin console:

1. Log in to your admin account.
2. In the Profile dropdown, select Admin > OAuth2 Clients and Tokens.
3. Click New Password Client or New Client Credentials Client.
4. Enter a name and a secret.
5. Click Save.

Note the OAuth client credentials, the consumer key, and consumer secret for future reference.

Assign Roles for API Access

Normally, users who authenticate with the CLIENT credentials grant type have full access to the API; users who authenticate

with the PASSWORD grant type require permissions for each module or table.

To set user access to particular resources as needed, configure REST roles with the necessary privileges, and then assign users to those roles.

1. In the Profile dropdown, select Admin > Role Management.

2. Click Create Role.

3. Enter a name and description for the new role, and click Save.

   The Admin console redirects you to the Role Configuration menu.

4. Select the permissions required for the new role.

5. Click Save.

Now assign the new role to one or more users:

1. In the Profile dropdown, select Admin > Role Management.
2. Click on the role you want to assign to a user.
3. Scroll down to the bottom and click Select User.
   The Admin console displays a user sesarch window.
4. Select the users to whom you want to assign the role.
   For each user you select, click Select, then Save.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit 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 SuiteCRM 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 SuiteCRM and then processes the rest of the query in memory (client-side).

User Defined Views

The SuiteCRM 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

SuiteCRM connector models SuiteCRM modules as relational .

Tables

Table definitions are dynamically retrieved. The connector connects to SuiteCRM and gets the list of tables and the metadata for the tables by calling the appropriate Web services.

Any changes you make to your SuiteCRM account, such as adding a new table, or adding new columns, or changing the data type of a column, will immediately be reflected when you connect using the connector.

The connector can also expose custom modules on your SuiteCRM account that are not mentioned in the . You can query against these custom modules as with any other table. Additionally, you can query against custom fields of standard modules.

This section shows the sample table definitions that are included in the SuiteCRM development environment.

Key Features

• The connector models SuiteCRM Products, Customers, Inventory, and more as relational tables, allowing you to write SQL to query SuiteCRM data.
• Stored procedures allow you to execute operations to SuiteCRM, including downloading and uploading objects.
• Live connectivity to these objects means any changes to your SuiteCRM account are immediately reflected when using the connector.

SuiteCRM V4.1 Data Model

See SuiteCRM V4.1 Data Model for the entities available when connecting to SuiteCRM 1 instances.

SuiteCRM V8 Data Model

See SuiteCRM V8 Data Model for the entities available when connecting to SuiteCRM 2 instances.

SuiteCRM V4.1 Data Model

SuiteCRM connector models SuiteCRM modules as relational database.

Tables

Table definitions are dynamically retrieved. The connector connects to SuiteCRM and gets the list of tables and the metadata for the tables by calling the appropriate Web services.

Any changes you make to your SuiteCRM account, such as adding a new table, or adding new columns, or changing the data type of a column, will immediately be reflected when you connect using the connector.

The connector can also expose custom modules on your SuiteCRM account that are not mentioned in the . You can query against these custom modules as with any other table. Additionally, you can query against custom fields of standard modules.

This section shows the sample table definitions that are included in the SuiteCRM development environment.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to SuiteCRM and then processes the rest of the query in memory; API limitations and requirements are documented in this section.

Tables

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

SuiteCRM Connector Tables

Accounts

Create, update, delete, and query accounts created in SuiteCRM

Table Specific Information

Select

You can query the Accounts table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Accounts WHERE Name LIKE '%test%' AND [Date Created] > '2017-10-09'

Insert

Create a SuiteCRM Account by specifying any writable column.

INSERT INTO Accounts (Name, [Email Address], Industry) VALUES ('Test Account', 'example@test.com', 'Energy')

Update

You can update any Account column that is writable, by specifying the Id.

UPDATE Accounts SET Description = 'Updated', Employees = '20-50' WHERE ID = 'Test123'

Delete

Remove an Account by specifying the Account's Id.

DELETE FROM Accounts WHERE ID = 'account21'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

ACLRoles

Create, update, delete, and query the ACLRoles table in SuiteCRM

Table Specific Information

Select

You can query the ACLRoles table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM ACLRoles WHERE Name LIKE '%test%'

Insert

Create an ACLRole by specifying any writable column.

INSERT INTO ACLRoles (Name, Description) VALUES ('New Role', 'Role description')

Update

You can update any ACLRole column that is writable, by specifying the Id.

UPDATE ACLRoles SET Name = 'Updated' WHERE ID = 'Test123'

Delete

Remove an ACLRole by specifying the Id.

DELETE FROM ACLRoles WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Alerts

Create, update, delete, and query the Alerts of your SuiteCRM account

Table Specific Information

Select

You can query the Alerts table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Alerts WHERE Name LIKE '%test%'

Insert

Create Alerts by specifying any writable column.

INSERT INTO Alerts (Name, Type, [Target Module]) VALUES ('Urgent', 'Important', 'Opportunities')

Update

You can update any writable Alert column, by specifying the Id.

UPDATE Alerts SET [Is read] = true WHERE ID = 'Test123'

Delete

Remove an Alert specifying the Id.

DELETE FROM Alerts WHERE ID = '1000'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Bugs

Create, update, delete, and query the bugs in SuiteCRM

Table Specific Information

Select

You can query the Bugs table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Bugs WHERE Subject LIKE '%test%'

Insert

Create a Bug by specifying any writable column.

INSERT INTO Bugs (Subject, Status) VALUES ('Critical bug', 'Open')

Update

You can update any Bug entry column that is writable, by specifying the Id.

UPDATE Bugs SET Status = 'Closed' WHERE ID = 'Test123'

Delete

Delete a Bug by specifying the Id.

DELETE FROM Bugs WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

BusinessHours

Create, update, delete, and query business hours information in SuiteCRM

Table Specific Information

Select

You can query the Business Hours table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM [Business Hours] WHERE [Closing Hours] LIKE '23:%'

Insert

Create a Business Hours entity by specifying any writable column.

INSERT INTO [Business Hours] (Name, [Opening Hours], [Closing Hours]) VALUES ('Part Timer Hours', '9:00', '16:00')

Update

You can update any Business Hours column that is writable, by specifying the Id.

UPDATE [Business Hours] SET [Opening Hours] = '13' WHERE ID = 'Test123'

Delete

Delete a Business Hours entry by specifying the Id.

DELETE FROM [Business Hours] WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Calls

Create, update, delete, and query calls in SuiteCRM

Table Specific Information

Select

You can query the Calls table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Calls WHERE [Duration Minutes] < 35

Insert

Create a Call by specifying any writable column.

INSERT INTO Calls (Name, [Duration hours], [Duration minutes]) VALUES ('CISCO Call', 1, 12)

Update

You can update any Call column that is writable, by specifying the Id.

UPDATE Calls SET [Repeat Count] = '2' WHERE ID = 'Test123'

Delete

Delete a Call by specifying the Id.

DELETE FROM Calls WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CallsReschedule

Create, update, delete, and query rescheduled call information for the SuiteCRM calls

Table Specific Information

Select

You can query the Calls Reschedule table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM [Calls Reschedule] WHERE Reason LIKE '%Delay%'

Insert

Create a Calls Reschedule by specifying any writable column.

INSERT INTO [Calls Reschedule] (Name, [Call Id], [Reason]) VALUES ('CISCO Call Rescheduled', '2420', 'Unresponsive')

Update

You can update any Calls Reschedule column that is writable, by specifying the Id.

UPDATE [Calls Reschedule] SET Reason = 'Delay' WHERE ID = 'Test123'

Delete

Delete a Calls Reschedule by specifying the Id.

DELETE FROM [Calls Reschedule] WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CampaignLog

Create, update, delete, and query logs related to SuiteCRM campaigns

Table Specific Information

Select

You can query the Campaign Log table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM [Campaign Log] WHERE Campaign_Id = 'campaign1'

Insert

Create a Campaign Log by specifying any writable column.

INSERT INTO [Campaign Log] (Name, Campaign_Id) VALUES ('Test Log', 'Campaign2')

Update

You can update any Campaign Log column that is writable, by specifying the Id.

UPDATE [Campaign Log] SET Archived = true WHERE ID = 'Test123'

Delete

Delete a Campaign Log by specifying the Id.

DELETE FROM [Campaign Log] WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Campaigns

Create, update, delete, and query the SuiteCRM project's campaigns

Table Specific Information

Select

You can query the Campaigns table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Campaigns WHERE Budget < 10000

Insert

Create a Campaign by specifying any writable column.

INSERT INTO Campaigns (Name, [Expected Cost], [Actual Cost]) VALUES ('Suite Campaign', 30000, 29400)

Update

You can update any Campaign column that is writable, by specifying the Id.

UPDATE Campaigns SET Budget = 30000 WHERE ID = 'Test123'

Delete

Delete a Campaign by specifying the Id.

DELETE FROM Campaigns WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CaseEvents

Create, update, delete, and query the events related to the SuiteCRM cases

Table Specific Information

Select

You can query the Case Events table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM [Case Events] WHERE LBL_CASE_ID = 'caseId1'

Insert

Create a Case Event by specifying any writable column.

INSERT INTO [Case Events] (Name, LBL_CASE_ID) VALUES ('Major Event', 'caseId4')

Update

You can update any Case Events column that is writable, by specifying the Id.

UPDATE [Case Events] SET LBL_CASE_ID = 'caseId2' WHERE ID = 'Test123'

Delete

Delete a Case Event by specifying the Id.

DELETE FROM [Case Events] WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Cases

Create, update, delete, and query the SuiteCRM cases

Table Specific Information

Select

You can query the Cases table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Cases WHERE Priority LIKE 'P2'

Insert

Create a Case by specifying any writable column.

INSERT INTO Cases (Subject, Type, Number) VALUES ('New Tracker', 'Adminitration', 71)

Update

You can update any Case column that is writable, by specifying the Id.

UPDATE Cases SET Status = 'Closed_Closed' WHERE ID = 'Test123'

Delete

Delete a Case by specifying the Id.

DELETE FROM Cases WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

CaseUpdates

Create, update, delete, and query updated made to the SuiteCRM cases

Table Specific Information

Select

You can query the Case Updates table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM [Case Updates] WHERE LBL_CASE_ID = 'caseId2'

Insert

Create a Case Update by specifying any writable column.

INSERT INTO [Case Update] (Name, LBL_CASE_ID, LBL_CONTACT_ID) VALUES ('Minor Update', 'caseId4', 'contactID44')

Update

You can update any Case Events column that is writable, by specifying the Id.

UPDATE [Case Update] SET Internal_Update = 'Some minor changes' WHERE ID = 'Test123'

Delete

Delete a Case Update by specifying the Id.

DELETE FROM [Case Updates] WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Contacts

Create, update, delete, and query the contacts in SuiteCRM

Table Specific Information

Select

You can query the Contacts table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Contacts WHERE Title LIKE 'PhD'

Insert

Create a Contact by specifying any writable column.

INSERT INTO Contacts ([First name], [Last name]) VALUES ('Heisen', 'Schulz')

Update

You can update any Contact column that is writable, by specifying the Id.

UPDATE Contacts SET Home = '004284294' WHERE ID = 'Test123'

Delete

Delete a Contact by specifying the Id.

DELETE FROM Contacts WHERE ID = 10003

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Contracts

Create, update, delete, and query contracts in SuiteCRM

Table Specific Information

Select

You can query the Contracts table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Contracts WHERE [Contract Value] < 20000

Insert

Create a Contract by specifying any writable column.

INSERT INTO Contracts ([Contract Title], [Contract Value], Discount) VALUES ('Sample Contract', 45000, 3)

Update

You can update any Contract column that is writable, by specifying the Id.

UPDATE Contracts SET Currency = 'GBP' WHERE ID = 'Test123'

Delete

Delete a Contract by specifying the Id.

DELETE FROM Contracts WHERE ID = 10003

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Currencies

Create, update, delete, and query currencies used in SuiteCRM financial trackings

Table Specific Information

Select

You can query the Currencies table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Currencies WHERE [Conversion Rate] > 2.34

Insert

Create a Currency by specifying any writable column.

INSERT INTO Currencies ([Iso 4217 Code], [Currency Name]) VALUES ('CAD', 'Canadian Dollar')

Update

You can update any Currency column that is writable, by specifying the Id.

UPDATE Currencies SET [Conversion Rate] = 1.18 WHERE ID = 'Test123'

Delete

Delete a Currency by specifying the Id.

DELETE FROM Currencies WHERE ID = 'Test123'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

DocumentRevisions

Query and delete revisions to uploaded SuiteCRM documents

Table Specific Information

Select

You can query the DocumentRevisions table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM [Document Revisions] WHERE [Related Document] = 'DocId'

Insert

INSERT is not supported for this table.

Update

UPDATE is not supported for this table.

Delete

Delete a Document Revision by specifying the Id.

DELETE FROM [Document Revisions] WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Documents

Create, update, delete, and query SuiteCRM documents

Table Specific Information

Select

You can query the Documents table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Documents WHERE [Document Name] LIKE '%.txt'

Insert

Create a Document by specifying any writable column.

INSERT INTO Documents (Name, [Document Source URL]) VALUES ('New Document', 'http://fileorigin.com/testfile.txt')

Update

You can update any Document column that is writable, by specifying the Id.

UPDATE Documents SET [Status Id] = 'Expired' WHERE ID = 'Test123'

Delete

Delete a Document by specifying the Id.

DELETE FROM Documents WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

EAPM

Create, update, delete, and query the EAPM (External API Module) entries in SuiteCRM

Table Specific Information

Select

You can query the EAPM table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM EAPM WHERE Application LIKE 'Suite%'

Insert

Create an EAPM entry by specifying any writable column.

INSERT INTO EAPM ([App User Name], Application) VALUES ('Saved app', 'Test Application')

Update

You can update any EAPM column that is writable, by specifying the Id.

UPDATE EAPM SET Connected = True WHERE ID = 'Test123'

Delete

Delete an EAPM entry by specifying the Id.

DELETE FROM EAPM WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

EmailAddress

Create, update, delete, and query email addresses saved in SuiteCRM

Table Specific Information

Select

You can query the Email Address table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM [Email Address] WHERE [Invalid Email] = True

Insert

Create an Email Address by specifying any writable column.

INSERT INTO [Email Address] ([Email address]) VALUES ('example@email.com')

Update

You can update any Email Address column that is writable, by specifying the Id.

UPDATE [Email Address] SET [Opted out] = True WHERE ID = 'Test123'

Delete

Delete an Email Address by specifying the Id.

DELETE FROM [Email Address] WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Emails

Create, update, delete, and query sent or received emails

Table Specific Information

Select

You can query the Emails table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Emails WHERE Intent LIKE '%Testing%'

Insert

Create an Email by specifying any writable column.

INSERT INTO Emails (Name, From_Addr_Name, CC_Addrs_Names) VALUES ('Example', 'Contact', 'Included')

Update

You can update any Email column that is writable, by specifying the Id.

UPDATE Emails SET Flagged = True WHERE ID = 'Test123'

Delete

Delete an Email by specifying the Id.

DELETE FROM Emails WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

EmailTemplates

Create, update, delete, and query email templates to be used for emails

Table Specific Information

Select

You can query the Email Templates table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM [Email Templates] WHERE Published = True

Insert

Create an Email Template by specifying any writable column.

INSERT INTO [Email Templates] (Name, Description) VALUES ('Example', 'Creating an example email')

Update

You can update any Email Template column that is writable, by specifying the Id.

UPDATE [Email Templates] SET [Text Only] = True WHERE ID = 'Test123'

Delete

Delete an Email Template by specifying the Id.

DELETE FROM Emails WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Employees

Create, update, delete, and query employees registered in the SuiteCRM project

Table Specific Information

Select

You can query the Employees table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Employees WHERE Department = 'HR'

Insert

Create an Employee by specifying any writable column.

INSERT INTO Employees ([First Name], [Last name], Title) VALUES ('Trucie', 'Dart', 'Eng.')

Update

You can update any Employee column that is writable, by specifying the Id.

UPDATE Employees SET Description = 'Updated description' WHERE ID = 'Test123'

Delete

Remove an Employee by specifying the Id.

DELETE FROM Employees WHERE ID = 10003

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Events

Create, update, delete, and query events registered in the SuiteCRM project

Table Specific Information

Select

You can query the Events table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Events WHERE Locations LIKE '%San Francisco%' AND [Start Date] < '2017-02-02'

Insert

Create an Event entry by specifying any writable column.

INSERT INTO Events (Name, [Start Date], [End Date]) VALUES ('Inauguration', '2017-06-13', '2017-06-14')

Update

You can update any Event column that is writable, by specifying the Id.

UPDATE Events SET Budget = 30000 WHERE ID = 'Test123'

Delete

Delete an Event by specifying the Id.

DELETE FROM Events WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

InboundEmail

Create, update, delete, and query SuiteCRM inbound emails

Table Specific Information

Select

You can query the Inbound Email table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM [Inbound Email] WHERE [Monitored Folders] LIKE '%MANAGEMENT%'

Insert

Create an Inbound Email by specifying any writable column.

INSERT INTO [Inbound Email] (Name, [Group Folder Id]) VALUES ('Fast replies', 'groupId123')

Update

You can update any Inbound Email column that is writable, by specifying the Id.

UPDATE [Inbound Email] SET [Auto-Reply Template] = 'emailTemplateId2' WHERE ID = 'Test123'

Delete

Delete an Inbound Email by specifying the Id.

DELETE FROM [Inbound Email] WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Index

Create, update, delete, and query the available indexes in SuiteCRM

Table Specific Information

Select

You can query the Index table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Index WHERE Location LIKE '%ACCOUNT%'

Insert

Create an Index by specifying any writable column.

INSERT INTO Index (Name, Location) VALUES ('Empty Index', 'Home')

INSERT INTO Index (Name, Location) VALUES ('Empty Index', 'Home')

Update

You can update any Index column that is writable, by specifying the Id.

UPDATE Index SET Location = 'Index' WHERE ID = 'Test123'

Delete

Delete an Index by specifying the Id.

DELETE FROM Index WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

IndexEvent

Create, update, delete, and query the Index Event entries in SuiteCRM

Table Specific Information

Select

You can query the Index Event table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM [Index Event] WHERE Name LIKE '%ERROR%'

Insert

Create an Index Event entry by specifying any writable column.

INSERT INTO [Index Event] (Name, [Record Module]) VALUES ('Created first index', 'indexId2', 'Index Name 2')

Update

You can update any Index Event column that is writable, by specifying the Id.

UPDATE [Index Event] SET Success = false, Error = 'Unexpected error' WHERE ID = 'Test123'

Delete

Delete an Index Event entry by specifying the Id.

DELETE FROM [Index Event] WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Invoices

Create, update, delete, and query the invoices saved in the SuiteCRM

Table Specific Information

Select

You can query the Invoices table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Invoices WHERE [Grand Total] < 3200

Insert

Create an Invoice by specifying any writable column.

INSERT INTO Invoices (Title, Account, Tax,[Due Date]) VALUES ('Major purchase', 'BillingAccount Of Employee', 20, '2017-04-07')

Update

You can update any Invoice column that is writable, by specifying the Id.

UPDATE Invoices SET Shipping = 140 WHERE ID = 'Test123'

Delete

Delete an Invoice by specifying the Id.

DELETE FROM Invoices WHERE ID = '10003'

Columns

Pseudo-Columns

Pseudo column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Leads

Create, update, delete, and query the registered Leads

Table Specific Information

Select

You can query the Leads table using any criteria in the WHERE clause. The connector will use the SuiteCRM API to filter the results.

SELECT * FROM Leads WHERE [Lead Source] = 'Word of Mouth'

Insert

Create a Lead by specifying any writable column.

INSERT INTO Leads ([First Name], [Last Name], [Referred by]) VALUES ('New', 'Lead', 'Adminitrator')

Update

You can update any Lead column that is writable, by specifying the Id.

UPDATE Leads SET Converted = True  WHERE ID = 'Test123'

Delete

Remove a Lead by specifying the Id.

DELETE FROM Leads WHERE ID = 10003

Columns