Skip to Content

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

Note

This authentication method is only available for suitecrmv8 schema.

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
Name Description
Accounts Create, update, delete, and query accounts created in SuiteCRM
ACLRoles Create, update, delete, and query the ACLRoles table in SuiteCRM
Alerts Create, update, delete, and query the Alerts of your SuiteCRM account
Bugs Create, update, delete, and query the bugs in SuiteCRM
BusinessHours Create, update, delete, and query business hours information in SuiteCRM
Calls Create, update, delete, and query calls in SuiteCRM
CallsReschedule Create, update, delete, and query rescheduled call information for the SuiteCRM calls
CampaignLog Create, update, delete, and query logs related to SuiteCRM campaigns
Campaigns Create, update, delete, and query the SuiteCRM project's campaigns
CaseEvents Create, update, delete, and query the events related to the SuiteCRM cases
Cases Create, update, delete, and query the SuiteCRM cases
CaseUpdates Create, update, delete, and query updated made to the SuiteCRM cases
Contacts Create, update, delete, and query the contacts in SuiteCRM
Contracts Create, update, delete, and query contracts in SuiteCRM
Currencies Create, update, delete, and query currencies used in SuiteCRM financial trackings
DocumentRevisions Query and delete revisions to uploaded SuiteCRM documents
Documents Create, update, delete, and query SuiteCRM documents
EAPM Create, update, delete, and query the EAPM (External API Module) entries in SuiteCRM
EmailAddress Create, update, delete, and query email addresses saved in SuiteCRM
Emails Create, update, delete, and query sent or received emails
EmailTemplates Create, update, delete, and query email templates to be used for emails
Employees Create, update, delete, and query employees registered in the SuiteCRM project
Events Create, update, delete, and query events registered in the SuiteCRM project
InboundEmail Create, update, delete, and query SuiteCRM inbound emails
Index Create, update, delete, and query the available indexes in SuiteCRM
IndexEvent Create, update, delete, and query the Index Event entries in SuiteCRM
Invoices Create, update, delete, and query the invoices saved in the SuiteCRM
Leads Create, update, delete, and query the registered Leads
LineItemGroups Create, update, delete, and query the SuiteCRM line items groups
LineItems Create, update, delete, and query line items in SuiteCRM
Locations Create, update, delete, and query locations in SuiteCRM
MapAddressCache Create, update, delete, and query information on the Map Address saved in the server cache
MapAreas Create, update, delete, and query saved map areas in SuiteCRM
MapMarkers Create, update, delete, and query Google Maps Map Markers saved in SuiteCRM
Maps Create, update, delete, and query maps via Google Maps.
Meetings Create, update, delete, and query meeting information.
Notes Create, update, delete, and query notes in SuiteCRM
OAuthConsumerKeys Create, update, delete, and query information on OAuth keys distributed by the application.
OAuthTokens Query currently active OAuth tokens
Opportunities Create, update, delete, and query opportunities saved in SuiteCRM
OutboundEmailAccounts Create, update, delete, and query the outbound email accounts table
PDFTemplates Create, update, delete, and query PDFTemplates table.
ProcessAudit Create, update, delete, and query information on process audits
ProductCategories Create, update, delete, and query the product categories.
Products Create, update, delete, and query the products registered for the SuiteCRM project
Projects Create, update, delete, and query projects registered in SuiteCRM
ProjectTemplates Create, update, delete, and query any saved project template.
Quotes Create, update, delete, and query quotes saved in SuiteCRM
Releases Create, update, delete, and query the registered releases.
ReportCharts Create, update, delete, and query report charts.
ReportConditions Create, update, delete, and query report conditions.
ReportFields Create, update, delete, and query the saved report fields.
Reports Create, update, delete, and query information on reports made in SuiteCRM.
Roles Create, update, delete, and query the roles in SuiteCRM.
SavedSearches Query any saved searches
ScheduledReports Create, update, delete, and query information on SuiteCRM scheduled reports.
Schedulers Create, update, delete, and query schedulers.
SecurityGroupsManagement Create, update, delete, and query information on security groups
Spots Create, update, delete, and query the saved spots.
SuiteCRMFeed Create, update, delete, and query information on the activity feed in the SuiteCRM project.
TargetLists Create, update, delete, and query the saved target lists.
Targets Query targets saved in SuiteCRM
Tasks Create, update, delete, and query tasks in SuiteCRM.
TemplateSectionLine Create, update, delete, and query the template section line entries in SuiteCRM
Trackers Create, update, delete, and query the created trackers.
Users Create, update, delete, and query the SuiteCRM registered users.
WorkFlow Create, update, delete, and query the wokflow actions in SuiteCRM
WorkFlowActions Create, update, delete, and query the wokflow actions in SuiteCRM.
WorkFlowConditions Create, update, delete, and query the workflow conditions in SuiteCRM.
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
Name Type ReadOnly Description
ID [KEY] String True
Address String True Address from Google Maps of the account address.
AlternatePhone String False An alternate phone number.
AnnualRevenue String False Annual revenue for this account.
AnyEmail String True The email address for the account.
Assignedto String True The ID of the user assigned to the record.
AssignedUser String False The user name of the user assigned to the record.
BillingCity String False The city used for the billing address.
BillingCountry String False The country used for the billing address.
BillingPostalCode String False The postal code used for the billing address.
BillingState String False The state used for the billing address.
BillingStreet String False The second line of the billing address.
BillingStreet2 String True The third line of the billing address.
BillingStreet3 String True The fourth line of the billing address.
BillingStreet4 String True The street address used for the billing address.
Campaign String True The campaign that generated the account.
CampaignID String False The first campaign name for the account.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False The full text of the alert.
EmailAddress String False The alternate email address for the account.
EmailOptOut Bool True Whether the account has opted out of emails.
Employees String False Number of employees. Can be a number (100) or range (50-100)
Fax String False The fax phone number of this account.
GeocodeStatus String True Geocode from Google Maps of the account address.
Industry String False The industry that the account belongs in.
InvalidEmail Bool True Whether the email address of the account has been marked as invalid.
Latitude Double True Latitude from Google Maps of the account address.
Longitude Double True Longitude from Google Maps of the account address.
Memberof String False The name of the parent of this account.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False The name of the account.
NonPrimaryE-mails String True The nonprimary email addresses of the account.
OfficePhone String False The office phone number.
Ownership String True The ownership of the account.
ParentAccountID String False The ID of the parent of this account.
Rating String False An arbitrary rating for this account for use in comparisons with others.
ShippingCity String False The city used for the shipping address.
ShippingCountry String False The country used for the shipping address.
ShippingPostalCode String False The ZIP code used for the shipping address.
ShippingState String False The state used for the shipping address.
ShippingStreet String False The second line of the shipping address.
ShippingStreet2 String True The third line of the shipping address.
ShippingStreet3 String True The fourth line of the shipping address.
ShippingStreet4 String True The street address used for for shipping purposes.
SICCode String False SIC code of the account.
TickerSymbol String False The stock trading (ticker) symbol for the account.
Type String False The type of the account.
Website String False The URL of the website for the account.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the ACL role.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False The role description.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False The role name.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the alert.
Assignedto String False The ID of the user assigned to the record.
AssignedUserId String True The user name of the user assigned to the record.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Text of the alert
IsRead Bool False Whether the alert has been read
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the alert
TargetModule String False Which SuiteCRM module the alert is for
Type String False Type of alert
UrlRedirect String False The URL the alert redirects to
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the record.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True The user name of the user assigned to the record.
Category String False Where the bug was discovered (e.g., Accounts, Contacts, or Leads).
CreatedById String True The ID of the user who created the record.
CreatedByName String True The user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False Record deletion indicator.
Description String False The full text of the note.
FixedInReleaseId String False The software or service release that corrected the bug.
FixedInReleaseName String True The name of the software or service release that corrected the bug.
FoundInReleaseId String False The software or service release that manifested the bug.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The user name of the user who last modified the record.
Number Int False The visual unique identifier. Cannot be updated.
Priority String False An indication of the priority of the issue.
ReleaseName String True The release name linked with the bug
Resolution String False An indication of how the issue was resolved.
Source String False An indicator of how the bug was entered (e.g., via Web or email).
Status String False The status of the issue.
Subject String False The short description of the bug.
Type String False The type of issue (e.g., issue or feature).
WorkLog String False Free-form text used to denote activities of interest.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the business hour.
ClosingHours String False Time the business hours ends
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Day String False Which day of the week is the business hour applied for
Deleted Bool False The record deletion indicator.
Description String False Description for the business hour
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name given to the business hour
Open Bool False Whether the business hour is open
OpeningHours String False Time the business hour starts
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the call.
AcceptLink String True The accept status of the call.
Assignedto String False The user name of the user assigned to the record.
AssignedUser String True The ID of the user assigned to the record.
CallAttemptHistory String True Reschedule info of the call.
CallAttempts String True The Number of times call was rescheduled.
Contact String True The contact name for the call.
Contactid String True The ID of the contact for the call.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The user name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False Record deletion indicator.
Description String False The full text of the note.
Direction String False Indicates whether call is inbound or outbound.
DurationHours Int False The hours portion of the call duration.
DurationMinutes Int False The minutes portion of the call duration.
EmailReminder Bool True The checkbox indicating whether or not the email reminder value is set.
EmailRemindersent Bool False Whether the email reminder is already sent.
EmailReminderTime String False When a email reminder alert should be issued. -1 means no alert; otherwise, the number of seconds prior to the start.
EndDate Datetime False The date when the call ends.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The user name of the user who last modified the record.
Name String False A brief description of the call.
OutlookID String False When the Suite Plug-in for Microsoft Outlook syncs an Outlook appointment, this is the Outlook appointment item Id.
ParentType String False The type of the parent Sugar object related to the call.
RecurringSource String False The recurring source requesting the call
Relatedto String True The name of the parent Sugar object related to the call.
RelatedtoID String False The ID of the parent Sugar object identified by .
ReminderChecked Bool True The checkbox indicating whether or not the reminder value is set.
ReminderTime String False When a reminder alert should be issued. -1 means no alert; otherwise, the number of seconds prior to the start.
Reminders String False List of reminders for the call
RepeatCount Int False Number of recurrences.
RepeatDow String False The days of week in recurrence.
RepeatInterval Int False The interval of recurrence.
RepeatParentID String False The ID of the first element of recurring records.
RepeatType String False The type of recurrence.
RepeatUntil Date False Repeat until the specified date.
StartDate Datetime False The date when the call starts.
Status String False The status of the call (e.g., Held or Not Held).
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the call reschedule.
Assignedto String False The ID of the user assigned to the record.
AssignedUserId String True The user name of the user assigned to the record.
Calls String False Name of the rescheduled call
CallId String False Id of the rescheduled call
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the call reschedule
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name given for the reschedule
Reason String False Reason given for the reschedule
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the record.
ActivityDate Datetime False The date the activity occurred.
ActivityType String False The activity that occurred (e.g., Viewed Message, Bounced, or Opted out).
Archived Bool False Indicates if the item has been archived.
campaign_content String True The campaign content.
campaign_id String False The identifier of the campaign associated with the campaign log.
campaign_name String True The name of the campaign associated with the campaign log.
campaign_objective String True The campaign objective.
DateModified Datetime False The date the campaign log was last modified.
Deleted Bool False Record deletion indicator.
Hits Int False Number of times the item has been invoked (e.g., multiple click throughs).
LBL_MARKETING_ID String False The ID of the marketing email this entry is associated with.
marketing_name String False The marketing name.
MoreInformation String False More information about the campaign log.
Name String True The name of the campaign associated with the campaign log.
ProspectListID String False The prospect list from which the item originated.
recipient_email String True The email of the recipient.
recipient_name String True The name of the recipient.
RelatedId String False The ID of the related record.
RelatedType String False The type of the related record.
TargetID String False The identifier of the target record.
TargetTrackerKey String False The identifier of the tracker URL.
TargetType String False The target record type (e.g., Contact or Lead).
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the campaign.
ActualCost Double False Actual cost of the campaign.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True The user name of the user assigned to the record.
Budget Double False Budgeted amount for the campaign.
Content String False The campaign description.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Currency String False Currency in use for the campaign.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String True The description for the campaign.
EndDate Date False Ending date of the campaign.
ExpectedCost Double False Expected cost of the campaign.
ExpectedRevenue Double False Expected revenue stemming from the campaign.
Frequency String False Frequency of the campaign.
Impressions Int False Expected click throughs manually entered by the campaign manager.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False The name of the campaign.
Objective String False The objective of the campaign.
StartDate Date False Starting date of the campaign.
Status String False Status of the campaign.
Tracker Int False The internal ID of the tracker used in a campaign. 2. (See CampaignTrackers.)
TrackerCount Int False The number of accesses made to the tracker URL; no longer used as of 4.2. (See CampaignTrackers.)
TrackerLinkText String False The text that appears in the tracker URL. No longer used as of 4.2. (See CampaignTrackers.)
TrackerRedirectURL String False The URL referenced in the tracker URL. No longer used as of 4.2. (See CampaignTrackers.)
Type String False The type of the campaign.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the case event.
Assignedto String True The ID of the user assigned to the record.
AssignedUserId String True The user name of the user assigned to the record.
CaseId String False The ID of the case the event is logged for
Case String False The name of the case the event is logged for
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime False
DateModified Datetime False
Deleted Bool False The record deletion indicator.
Description String False Description of the case event
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name given for the case update
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier for the record.
AccountID String False The ID of the associated account.
AccountName String False The name of the associated account.
account_name1 String True A second account for the case
Address String True Address from Google Maps of the case.
AssignedUserId String False The user ID assigned to the record.
AssignedUserName String True The name of the user assigned to the record.
CaseAttachments String True A display of case attachments
CaseUpdatesThreaded String False A list of the case updates
Createdbycontact String True The user name of the user who created the case contact.
CreatedById String False The ID of the user who created the record.
CreatedByName String False The user name of the user who created the record.
DateCreated Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
Deleted Bool False Record deletion indicator.
Description String False The full text of the note.
GeocodeStatus String True Geocode from Google Maps of the case.
InternalUpdate Bool False Whether the update is internal.
Latitude Double True Latitude from Google Maps of the case.
LBL_CONTACT_CREATED_BY_ID String True The User that created the case's contact
Longitude Double True Longitude from Google Maps of the case.
ModifiedById String False The ID of the user who last modified the record.
ModifiedByName String False The user name who last modified the record.
Number Int False The user-visible unique identifier for the case.
Priority String False The priority of the case.
Resolution String False The resolution of the case.
State String False State the case is left in The allowed values are OPen, Closed.
Status String False The status of the case.
Subject String False The short description of the bug.
Suggestions String True Collection of suggestions left for the case.
Type String False The type of case.
Updateattachmentform String True The HTML text for the case's update attachment
UpdateText String True Text associated with an update on the case.
WorkLog String False Free-form text used to denote activities of interest.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the case update.
Assignedto String False The ID of the user assigned to the record.
AssignedUserId String True The user name of the user assigned to the record.
Case String True The name of the case related to the update
LBL_CASE_ID String False The name of the case related to the update
Contact String True Name of the contact related to the update
LBL_CONTACT_ID String False Id of the contact related to the update
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the case update
Internal_Update Bool False Whether the update is internal.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the case update
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier for the record.
AcceptStatus String True Id of the event status
AcceptStatusC String True The call accept status fields.
AcceptStatusId String True The ID of the accept status.
AcceptStatusM String True The meeting accept status fields.
AcceptStatusName String True The name of the accept status.
AccountDisabled Bool False Whether the portal account has been disabled for the contact.
AccountID String True The ID of the account associated with the contact.
AccountName String True The name of the account associated with the contact.
Address String True Address from Google Maps of the contact.
AlternateAddressCity String False The city for the alternate address.
AlternateAddressCountry String False The country for the alternate address.
AlternateAddressPostal_Code String False The postal code for the alternate address.
AlternateAddressState String False The state for the alternate address.
AlternateAddressStreet String False The street address for the alternate address.
AlternateAddressStreet2 String True The second line of the alternate address.
AlternateAddressStreet3 String True The third line of the alternate address.
AnyEmail String True The email for the contact.
Assignedto String True The name of the user assigned to the record.
AssignedUser String False The user ID assigned to the record.
Assistant String False The name of the assistant of the contact.
AssistantPhone String False The phone number of the assistant of the contact.
Birthdate Date False The birthdate of the contact.
Campaign String False The first campaign name for Contact.
CampaignID String False The campaign that generated the lead.
CreatedById String False The ID of the user who created the record.
CreatedByName String False The name of the user who created the record.
DateCreated Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
Deleted Bool False Record deletion indicator.
Department String False The department of the contact.
Description String False The full text of the note.
DoNotCall Bool False An indicator of whether the contact can be called.
Email String True The email and name of the contact.
EmailAddress String False The alternate email for the contact.
EmailOptOut Bool True Whether the contact has opted out of emails.
Fax String False The contact fax number.
FirstName String False The first name of the contact.
Fullname String True The full name of hte contact.
GeocodeStatus String True Geocode from Google Maps of the contact.
Home String False Home phone number of the contact.
InvalidEmail Bool True Whether the contact email has been marked as invalid.
JoomlaAccountID String False Id of the contact's Joomla account.
LastName String False The last name of the contact.
Latitude Double True Latitude from Google Maps of the contact.
LBL_CONT_ACCEPT_STATUS String True The event accept status fields.
LBL_CONT_INVITE_STATUS String True The event invite status fields.
LBL_JOOMLA_ACCOUNT_ACCESS String True Access point for the contact's Joomla account.
LBL_LIST_ACCEPT_STATUS_EVENT String True Accept status for the event
LBL_LIST_INVITE_STATUS String True Id for the event invite
LBL_LIST_INVITE_STATUS_EVENT String True Invite status of the event
LeadSource String False The lead source for the contact.
Longitude Double True Longitude from Google Maps of the contact.
Mobile String False Mobile phone number of the contact.
ModifiedById String False The ID of the user who last modified the record.
ModifiedByName String True The user name of the user who last modified the record.
Name String True The name of the contact.
NonPrimaryEmails String True The nonprimary email addresses for the contact.
OfficePhone String False Work phone number of the contact.
OpportunityRole String True The opportunity role.
OpportunityRoleFields String True The opportunity role fields.
OpportunityRoleID String True The ID of the opportunity role.
OtherEmail String True The alternate email for the contact.
OtherPhone String False Other phone number for the contact.
Photo String False The avatar for the contact.
PortalUserType String False Type of the contact's portal account.
PrimaryAddressCity String False The city for the primary address.
PrimaryAddressCountry String False The country for primary address.
PrimaryAddressPostalCode String False The postal code for the primary address.
PrimaryAddressState String False The state for the primary address.
PrimaryAddressStreet String False The street address for the alternate address.
PrimaryAddressStreet2 String True The second line of the alternate address.
PrimaryAddressStreet3 String True The third line of the alternate address.
ReportsTo String True The name of the contact this contact reports to.
ReportstoID String False The ID of the contact this contact reports to.
Salutation String False The contact salutation (e.g., Mr. or Ms.).
SynctoOutlook_reg\_ Bool True Whether the lead is synced to Outlook.
Title String False The title of the contact.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the contract.
Account String False Account opened for the contract
AssignedUserId String True The ID of the contract manager.
ContractManager String False Name of the account manager
Call_ID String False Id of the call linked to the contract
CompanySignedDate Date False Date the company signed the contract
Contact String True Name of the contact linked to the account
Contact_Id String False Id of the contact linked to the account
ContractTitle String False Title given to the contract
ContractType String False Type of the contract
ContractValue Double False Value of the contract
ContractValue(DefaultCurrency) Double False Value of the contract calculated in the system's default currency
Contract_Account_Id String True Id of the Account linked to the contract
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Currency String False Id of the currency used in the contract
CustomerSignedDate Date False Date the customer signed the contract
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the contract
Discount Double False Discount value for the account
Discount(DefaultCurrency) Double False Discount value for the account in the system's default currency
EndDate Date False Date until the contract expires
GrandTotal Double False Grand total of the account
GrandTotal(DefaultCurrency) Double False Grand total of the account in the system's currency
LineItems String True Line items of the contract
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Opportunity String True Name of the opportunity linked to the contract
Opportunity_Id String False Id of the opportunity linked to the contract
ReferenceCode String False Reference code of the contract
RenewalReminderDate Datetime False Date to remind for the contract renewal
Shipping Double False Shipping costs of the contract
Shipping(DefaultCurrency) Double False Shipping costs of the contract in the system's default currency
ShippingTax String False Shipping tax costs
ShippingTax(DefaultCurrency) Double False Shipping tax costs in the system's default currency
ShippingTaxAmount Double False Amount of the shipping tax
StartDate Date False Date the contract is valid from
Status String False Status of the contract
Subtotal Double False Subtotal amount
Subtotal(DefaultCurrency) Double False Subtotal amount in the system's default currency
Tax Double False Tax costs
Tax(DefaultCurrency) Double False Tax costs in the system's default currency
Total Double False Total value of the contract
Total(DefaultCurrency) Double False Total value of the contract in the system's default currency
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
Id [KEY] String False The unique identifer of the currency.
ConversionRate Double False Conversion rate factor (relative to stored value).
CreatedById String False Id of the user who created the record.
CreatedByName String False Id of the user who created the record.
CurrencyName String False Name of the currency.
CurrencySymbol String False Symbol representing the currency.
DateCreated Datetime False Date the record was created.
DateModified Datetime False Date the record was last modified.
Deleted Bool False Record deletion indicator.
hide String True Hide status of the currency
ISO4217Code String False 3-letter identifier specified by ISO 4217 (e.g., USD).
Status String False Currency status.
unhide String True Unhide status of the currency
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
RevisionNumber [KEY] String False The unique identifier for the document revision.
ChangeLog String False The change log for the document revision.
CreatedById String True The ID of the user who created the document.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the revision was entered.
DateModified Datetime True The date the record was last modified.
Deleted Bool False Whether the document revision is deleted.
DocumentName String True The name of the document.
DocumentSourceID String False The document ID from the Web service provider for the document.
DocumentSourceURL String False The document URL from the Web service provider for the document.
File String False The file name of the document.
FileExtension String False The file extension of the document.
LastRevisionId String True The ID of the latest revision.
LatestRevision String True The latest revision.
MimeType String False The MIME type of the document.
RelatedDocument String False The ID for the associated document.
Revision String False The revision number.
Source String False The document type (e.g., Google, box.net, or IBM SmartCloud).
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier for the record.
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
Category String False The ID for the category of the document.
Contractname String True The name of the document's contract
Contractstatus String True The document's contract status.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The user name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False Record deletion indicator.
Description String False The full text of the note.
DocumentName String False The document name.
DocumentRevisionId String False The ID of the document revision.
DocumentSourceID String False The document ID from the Web service provider for the document.
DocumentSourceURL String False The document URL from the Web service provider for the document.
DocumentType String False The template type of the document.
ExpirationDate Date False The date the document expires.
FileName String True The file name of the document attachment.
IsaTemplate Bool False Whether the document is a template.
LastRevisionCreateDate Date True The date the last revision was created.
LastrevisionMIMEtype String True The MIME type of the last revision.
Lastestrevisionname String True The name of the latest revision.
LatestRevision String True The latest revision.
LatestRevisionId String True The ID of the latest revision.
Linkedid String True The ID of the linked record.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The user name of the user who last modified the record.
Name String True The name of the document.
PublishDate Date False The date the document is active.
RelatedDocument String True The related document name.
RelatedDocumentID String False The ID of the related document.
RelatedDocumentRevision String True The related document version number.
RelatedDocumentRevisionID String False The ID of the related document revision.
Revision String True The revision number.
RevisionCreatedBy String True The name of the user who created the last revision.
Selectedrevisionfilename String True The filename of the selected revision.
Selectedrevisionid String True The ID of the selected revision.
Selectedrevisionname String True The name of the selected revision.
Source String False The document type (e.g., Google, box.net, or IBM SmartCloud).
Status String True The document status.
StatusId String False The ID of the document status.
SubCategory String False The ID of the subcategory of the document.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False Unique identifier for the external API.
APIData String False Any API data that the external API may wish to store on a per-user basis.
AppPassword String False The password of the external API.
AppUserName String False The name of the external API.
Application String False The application name of the external API.
AssignedUserId String False The ID of the user assigned to the record.
Connected Bool False Whether the external API has been validated.
ConsumerKey String False The consumer key for the external API.
ConsumerSecret String False The consumer secret for the external API.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False The full text of the note.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The user name of the user who last modified the record.
OAuthToken String False The OAuth access token for the external API.
PleaseNote String True The note for the external API.
SuiteCRMUser String True The name of the user assigned to the record.
URL String False The URL of the external API.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the email address.
DateCreate Datetime True The date the email address was created.
DateModified Datetime True The date the email address was last modified.
Delete Bool False Whether the email address is deleted.
EmailAddress String False The email address.
EmailAddresscaps String False The email address in uppercase.
InvalidEmail Bool False Whether the email address is marked as invalid.
OptedOut Bool False Whether the email address is marked as opt out.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the email.
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
bcc_addrs_names String True The bcc addresses in the email.
CC_Addrs_names String True The cc addresses in the email.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The user name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
DateSent Datetime False The date the email was sent.
Deleted Bool False The record deletion indicator.
description String True The description for the email.
description_html String True The HTML description for the email.
EmailStatus String False The status of the email.
Flagged Bool False The flagged status of the email.
from_addr_name String True The from address in the email.
Intent String False The target of the action used in the Inbound Email assignment.
LBL_MAILBOX_ID String False The ID of the mailbox associated with the email.
MessageID String False The ID of the email item obtained from the email transport system.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The user name of the user who last modified the record.
Name String False The subject of the email.
Parent_ID String False The ID of the Sugar module associated with the email. (Deprecated as of 4.2.)
Parent_Name String True The name of the Sugar module associated with the email.
Parent_Type String False The type of the Sugar module associated with the email. (Deprecated as of 4.2.)
raw_source String True The raw source for the email.
ReplyToStatus Bool False The reply-to status of the email. If you reply to an email then the reply-to status of original email is set.
reply_to_addr String True The reply-to address in the email.
to_addrs_names String True The to addresses in the email.
Type String False The type of the email (e.g., draft).
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the email template.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String False The user name of the user assigned to the record.
Body String False Plaintext body of the resulting email.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False The description for the email template.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False The name of the email template.
PlainText String False The HTML-formatted body of the resulting email.
Published Bool False The published status of the record.
Subject String False The subject of the resulting email.
TextOnly Bool False Whether the email template is to be sent in text only.
Type String False The type of the email template.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the employee.
AcceptStatusC String True The status fields for the call accept status of the employee.
AcceptStatusId String True The ID of the accept status of the employee.
AcceptStatusM String True The status fields for the meeting accept status of the employee.
AcceptStatusName String True The name of the accept status of the employee.
AddressCity String False The city in the address of the employee.
AddressCountry String False The country in the address of the employee.
AddressPostalCode String False The postal code in the address of the employee.
AddressState String False The state in the address of the employee.
AddressStreet String False The street address of the employee.
AuthenticationId String False The ID used in authentication.
CreatedBy String True The ID of the user who created the record.
CreatedByName String True The user name of the user who created the record.
DateEntered Datetime True The date the employee record was entered into the system.
DateModified Datetime True The date the employee record was last modified.
Deleted Bool False Whether the employee is deleted.
Department String False The department of the employee.
Description String False The description for the employee.
DisplayEmployeeRecord Bool False Whether to show the employee.
EmailAddress String True The alternate email address of the employee.
EmailClient String True The link type of the email for the employee.
EmployeeStatus String False The status of the employee.
ExternalAuthentication Bool False Whether the employee only has external authentication available.
Fax String False The fax of the employee.
FirstName String False The first name of the employee.
Fullname String True The full name of the employee.
GroupUser Bool False Whether the employee is a group user.
HomePhone String False The home phone number of the employee.
IMName String False The ID of the instant messenger service used by the employee.
IMType String False The type of the instant messenger service used by the employee.
IsAdministrator Bool False Whether the employee is an admin.
IsUser Bool False Whether the employee has a Sugar login.
LastName String False The last name of the employee.
LBL_PRIMARY_GROUP Bool True The primary security group the employee is assigned to.
LBL_REPORTS_TO_ID String False The ID of who the employee reports to.
LBL_securitygroup_noninherit_id String True The security group's non-inheritance id.
LBL_SECURITYGROUP_NONINHERITABLE Bool True Whether the group is non inheritable.
Mobile String False The mobile phone number of the employee.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The user name of the user who last modified the record.
Name String False The name of the employee.
NotifyonAssignment Bool False Whether the employee can receive notifications.
Other String False The alternate phone of the employee.
Password String False The user hash of the employee.
Password_Last_Changed Datetime False The date the password of the employee was last changed.
Photo String False The ID of the picture.
PortalAPIUser Bool False Whether the employee is a portal-only user.
Reportsto String True The user name of who the employee reports to.
SecurityGroup String True The security group's fields that are selected for non-inheritance.
Status String False The status of the employee.
SystemGeneratedPassword Bool False Whether the employee has a system-generated password.
Title String False The title of the employee.
UserName String False The username of the employee.
UserType String True The user type of the employee.
WorkPhone String False The work phone number of the employee.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the event.
AcceptRedirectURL String False The URL to redirect to if the event is accepted
ActivityStatus String False Status type of the event activity
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
Budget Double False Budget set for the event
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Currency String False Id of the currency used in the budget
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
DeclineRedirect_URL String False The URL to redirect to if the event is declined
Deleted Bool False The record deletion indicator.
Description String False Description given for the event
Duration String True Complete duration of the event
DurationHours Int False Number of hours the event lasts
DurationMinutes Int False Number of minutes the event lasts
EmailInviteTemplate String False Template for an email invite
EndDate Datetime False Date the event ends
LBL_RESPONSE_LINK String True Url that accepts the event invitation
LBL_RESPONSE_LINK_DECLINED String True URL that declines the event invitation
Locations String True Locations the event will take place
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name given to the event
StartDate Datetime False Date the event starts
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the inbound email.
Auto-ReplyTemplate String False The inbound email's auto-reply template
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
DeleteReadEmailsAfterImport Bool False Whether the emails are deleted after an import
Deleted Bool False The record deletion indicator.
GroupFolderId String False Id of the group's folder linked to the email
LBL_GROUP_ID String False Group ID linked to the email
LBL_SERVICE String False Service type of the inbound email
LBL_STORED_OPTIONS String False Stored options of the inbound email
MailServerAddress String False URL of the mail server to retrieve inbound emails from
MailServerProtocol Int False Mail protocol of the mail server
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
MonitoredFolders String False Folders monitored from the mail server
Name String False name of the inbound email
Password String False Authentication password required to access the mail server
Personal Bool False Whether the inbound email is personal
PossibleActions String False The mail server's mailbox type
Status String False Status of the inbound email
UserName String False SMTP Username to connect to the mail server
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the index.
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the index
LastOptimised Datetime True Date when the index was last optimized
Location String False Module where the index is applicable
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name given to te index
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the index event.
RecordId String False Which record produced the event
RecordModule String False Which module does the record exist in
Error String False Message of the event's recorded error
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description of the indexs event
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Title of the index event
Success Bool False Whether the index event is successful
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the invoice.
Account String True Billing account for the invoice
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
BillingCity String False City where the billing account is recorded in
BillingCountry String False Country where the billing account is recorded in
BillingPostalCode String False Postal code of the billing account
BillingState String False State where the billing account is recorded in
BillingStreet String False Street where the billing account is recorded in
billing_account_id String False Id of the billing account
billing_contact_id String False Id of the billing contact
Contact String True Name of the billing contact
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Currency String False Id of the currency used for currency values
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description provided for the
Discount Double False Discount amount of the invoice
LBL_DISCOUNT\_\_AMOUNT_USDOLLAR Double False Discount amount of the invoice in the system's default currency
DueDate Date False Due date of the invoice
GrandTotal Double True The invoice's grand total
GrandTotal(DefaultCurrency) Double True The invoice's grand total in the system's default currency
InvoiceDate Date False Date the invoice was issued
InvoiceNumber Int False Number of the invoice
InvoiceTemplates String False Template applying to the invoice
LineItems String True The invoice's list of line items
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
QuoteDate Date False Date of the invoice quote
QuoteNumber Int False Number of the invoice quote
Shipping Double False Shipping costs
Shipping(DefaultCurrency) Double False Shipping costs in the system's default currency
ShippingCity String False City of the shipping destination
ShippingCountry String False Country of the shipping destination
ShippingPostalCode String False Postal Code of the shipping destination
ShippingState String False State of the shipping destination
ShippingStreet String False Address of the shipping destination
ShippingTax String False Shipping tax costs
ShippingTax(DefaultCurrency) Double False Shipping tax costs in the system's default currency
ShippingTaxAmount Double False Amount of te shipping tax
Status String False Status of the invoice
Subtotal Double True Subtotal amount in the invoice
Subtotal(DefaultCurrency) Double True Subtotal amount in the invoice in the system's default currency
Subtotal+Tax Double True Subtotal and tax amount in the invoice
SubtotalTaxAmount Double True Subtotal and tax amount in the invoice in the system's default currency
Tax Double False Tax costs in the invoice
Tax(DefaultCurrency) Double False Tax costs in the invoice in the system's default currency
Title String False Title assigned to the invoice
Total Double True Total amount in the invoice
Total(DefaultCurrency) Double True Total amount in the invoice in the system's default currency
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
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
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the record.
AcceptStatusC String True The call accept status fields.
AcceptStatusId String True The ID of the accept status.
AcceptStatusM String True The meeting accept status fields.
AcceptStatusName String True The name of the accept status.
AccountDescription String False Description for the lead's account
AccountID String False Account the lead is linked to
AccountName String False The name of the associated acocunt.
Address String True Address from Google Maps of the lead.
AltAddressCity String False The city for the alternate address.
AltAddressCountry String False The country for the alternate address.
AltAddressPostalcode String False The postal code for the alternate address.
AltAddressState String False The state for the alternate address.
AltAddressStreet String False The street address for the alternate address.
AltAddressStreet2 String True The second line of the alternate address.
AltAddressStreet3 String True The third line of the alternate address.
AnyEmail String True The email for the lead.
Assignedto String True The user name of the user assigned to the record.
AssignedUser String False User ID assigned to the record.
Assistant String False The name of the assistant of the lead.
AssistantPhone String False The phone number of the assistant of the lead.
Birthdate Date False The birthdate of the lead.
Campaign String True The lead's campaign name
CampaignID String False Campaign that generated the lead.
ContactID String False Main contact for the lead
Converted Bool False Whether the lead has been converted.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The user name of the user who created the record.
DateCreated Datetime True Date the record was created.
DateModified Datetime True Date the record was last modified.
Deleted Bool False Record deletion indicator.
Department String False The department of the lead.
Description String False Full text of the note.
DoNotCall Bool False An indicator of whether the lead can be called.
EmailAddress String False The alternate email for the lead.
EmailAddress2 String True Email of WebToLead
EmailOptOut Bool True Whether the lead has opted out of radio.
EmailOptOut2 Bool True Whether the prospect has opted out of WebToLead emails.
EventStatusId String True Id of the lead's event status.
Fax String False The lead fax number.
FirstName String False The first name of the lead.
Fullname String True The full name of the lead.
GeocodeStatus String True Google Maps geocode status
HomePhone String False Home phone number of the lead.
InvalidEmail Bool True Whether the lead email has been marked as invalid.
LastName String False The last name of the lead.
Latitude Double True Latitude from Google Maps of the lead.
LBL_CONT_ACCEPT_STATUS String True The event accept status fields.
LBL_CONT_INVITE_STATUS String True The event invite status fields.
LBL_LIST_ACCEPT_STATUS_EVENT String True Status of the lead's event accept.
LBL_LIST_INVITE_STATUS String True Id of the lead's event invite.
LBL_LIST_INVITE_STATUS_EVENT String True Name of the lead's event status.
LeadInvalidEmail Bool True Whether the WebToLead email is invalid.
LeadSource String False How the lead came to be known.
LeadSourceDescription String False Description of the lead source.
Longitude Double True Longitude from Google Maps of the lead.
Mobile String False Mobile phone number of the lead.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The user name of the user who last modified the record.
Name String True The name of the lead.
NonPrimaryE-mails String True The nonprimary email addresses for the lead.
OfficePhone String False Work phone number of the lead.
OpportunityAmount String False The amount the opportunity yields
OpportunityID String False The Opportunity the lead was generated from
OpportunityName String False The name of the opportunity the lead was generated from
OtherEmail String True The alternate email for the lead.
OtherEmail2 String True Alternate email of WebToLead
OtherPhone String False Other phone number for the lead.
Photo String False The picture for the lead.
PortalApplication String False The Joomla portal application of the lead
PortalName String False The Joomla portal name of the lead
PrimaryAddressCity String False The city for the primary address.
PrimaryAddressCountry String False The country for the primary address.
PrimaryAddressPostalcode String False The postal code for the primary address.
PrimaryAddressState String False The state for the primary address.
PrimaryAddressStreet String False The street address used for the primary address.
PrimaryAddressStreet2 String True The second line of the primary address.
PrimaryAddressStreet3 String True The third line of the primary addrss.
ReferredBy String False The Name of the lead's reference.
ReportsTo String True The name the updates for the lead will be reported to
ReportsToID String False The name the updates for the lead will be reported to
Salutation String False The lead salutation (e.g., Mr. or Ms.).
Status String False Status of the lead
StatusDescription String False A description for the status
Title String False The title of the lead.
Website String False The lead's website
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
LineItemGroups

Create, update, delete, and query the SuiteCRM line items groups

Table Specific Information

Select

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

SELECT * FROM [Line Item Groups] WHERE Discount > 0

Insert

Create a Line Item Groups entry by specifying any writable column.

INSERT INTO [Line Item Groups] ([Group Name], Currency, Number) VALUES ('New Group', 'USD', 1)

Update

You can update any Line Item Groups entry column that is writable, by specifying the Id.

UPDATE [Line Item Groups] SET Tax = 450 WHERE ID = 'Test123'

Delete

Delete a Line Item Group by specifying the Id.

DELETE FROM [Line Item Groups] WHERE ID = '10003'
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the line items group.
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Currency String False Id of the currency used for currency values
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the line items group
Discount Double False Group's discount value
Discount(DefaultCurrency) Double True Group's discount value in the system's default currency
GroupName String False Name assigned to the line items group
GroupTotal Double True Group's total amount
GroupTotal(DefaultCurrency) Double True Group's total amount in the system's default currency
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Number Int False Number assigned to the group
LBL_PARENT_ID String False Parent record of this group
LBL_FLEX_RELATE String True Group's parent name
ParentType String False Parent't type of this group
Subtotal Double False Group's subtotal amount
Subtotal(DefaultCurrency) Double False Group's subtotal amount in the system's default currency
SubtotalTax Double True Group's subtotal and amount
SubtotalTaxAmount Double True Group's subtotaland tax amount in the system's default currency
Tax Double False Group's tax amount
Tax(DefaultCurrency) Double True Group's tax amount in the system's default currency
Total Double True Group's total amount in the system's default currency
Total(DefaultCurrency) Double True
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
LineItems

Create, update, delete, and query line items in SuiteCRM

Table Specific Information

Select

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

SELECT * FROM [Line Items] WHERE [Cost Price] > 0 AND [Cost Price] < 500

Insert

Create a Line Item by specifying any writable column.

INSERT INTO [Line Items] (Name, [Part Number], [Cost Price], [group_id]) VALUES ('Leather shoes', 2, 10, 'lineItemGroup2')

Update

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

UPDATE [Line Items] SET Quantity = 100 WHERE ID = 'Test123'

Delete

Delete a Line Item by specifying the Id.

DELETE FROM [Line Items] WHERE ID = '10003'
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the line item.
Assignedto String False The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
CostPrice Double False The line item's cost price
CostPrice(DefaultCurrency) Double True The line item's cost price in the system's default currency
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Currency String False Id of the currency used for currency values
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the item
Discount Double False The line item's product discount
Discount(DefaultCurrency) Double True The line item's product discount in the system's default currency
DiscountAmount Double False The line item's discount amount
DiscountAmount(DefaultCurrency) Double True The line item's discount amount in the system's default currency
DiscountType String False The type of discount for the item
Group String False Group name the line item belongs to
group_id String False Group ID the line item belongs to
ListPrice Double False The line item's list price
ListPrice(DefaultCurrency) Double True The line item's list price in the system's default currency
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the line item
Note String False Description for the line item
Number Int False Number assigned to the line item
ParentID String False Id of the line item's parent
Relatedto String False Parent name the line item is related to
ParentType String False Id of the line item's parent
PartNumber String False Part number assigned to the line items group
ProductID String False Id assigned to the line item product
Quantity String False Quantity of the line item
Tax String False The line item's VAT
TaxAmount Double False The line item's VAT amount
TaxAmount(DefaultCurrency) Double True The line item's VAT amount in the system's default currency
TotalPrice Double False The line item's total price
TotalPrice(DefaultCurrency) Double True The line item's total price in the system's default currency
UnitPrice Double False The line item's unit price
UnitPrice(DefaultCurrency) Double True The line item's unit price in the system's default currency
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Locations

Create, update, delete, and query locations in SuiteCRM

Table Specific Information

Select

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

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

Insert

Create a Location by specifying any writable column.

INSERT INTO Locations (Name, Address, City, Country) VALUES ('Ballroom Event', 'Rose Street', 'Paris', 'France')

Update

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

UPDATE Locations SET Capacity = '4000' WHERE ID = 'Test123'

Delete

Remove a Location by specifying the Id.

DELETE FROM Locations WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the location.
Address String False Street address of the location
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
Capacity String False Capacity estimated for the location
City String False City of the location
Country String False Country of the location
County String False County of the location
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the location
ModifiedById String True
ModifiedByName String True
Name String False Name assigned to the location
Postcode String False Postal code of the location
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
MapAddressCache

Create, update, delete, and query information on the Map Address saved in the server cache

Table Specific Information

Select

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

SELECT * FROM [Map Address Cache] WHERE Address LIKE '%LA%'

Insert

Create a Map Address Cache entry by specifying any writable column.

INSERT INTO [Map Address Cache] (Address, Latitude, Longitude) VALUES ('221 Baker Street', 51.5207,-0.1550)

Update

You can update any Map Address Cache entry column that is writable, by specifying the Id.

UPDATE [Map Address Cache] SET [Address] = 'New location' WHERE ID = 'Test123'

Delete

Remove a Map Address Cache by specifying the Id.

DELETE FROM [Map Address Cache] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the address cache.
Address String False Complete address of the cached address
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the cached address
Latitude Double False Latitude coordinate of the address
Longitude Double False Longitude coordinate of the address
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
MapAreas

Create, update, delete, and query saved map areas in SuiteCRM

Table Specific Information

Select

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

SELECT * FROM [Map Areas] WHERE City = 'Houston'

Insert

Create a Map Area entry by specifying any writable column.

INSERT INTO [Map Areas] (Name, Coordinates, City, Country) VALUES ('Investigation Area', '55.356608, 37.165067', 'Moscow', 'Russia')

Update

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

UPDATE [Map Areas] SET User = 'David' WHERE ID = 'Test123'

Delete

Remove a Map Area by specifying the Id.

DELETE FROM [Map Areas] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the map area.
User String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
City String False City of the map area
Coordinates String False Geographical coordinates of the map area
Country String False Country of the map area
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the map area
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the line items group
State String False State of the location
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
MapMarkers

Create, update, delete, and query Google Maps Map Markers saved in SuiteCRM

Table Specific Information

Select

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

SELECT * FROM [Map Markers] WHERE City = 'London'

Insert

Create a Map Marker entry by specifying any writable column.

INSERT INTO [Map Markers] (Name, Latitude, Longitude) VALUES ('Default Location', 51.5207,-0.1550)

Update

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

UPDATE [Map Markers] SET [Marker Image Type] = 'PIN' WHERE ID = 'Test123'

Delete

Remove a Map Marker by specifying the Id.

DELETE FROM [Map Markers] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the map marker.
User String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
City String False City of the map marker
Country String False Country of the map marker
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the map marker
Latitude Double False Latitude coordinate of the map marker
Longitude Double False Longitude coordinate of the map marker
MarkerImageType String False
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the map marker
State String False State of the map marker
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Maps

Create, update, delete, and query maps via Google Maps.

Table Specific Information

Select

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

SELECT * FROM Maps WHERE [Distance (Radius)] > 5000

Insert

Create a Map entry by specifying any writable column.

INSERT INTO Maps (Name, [Distance (Radius)], [Related to (Center)], [Unit Type]) VALUES ('Enclosed Area', 10, 'Dublin', 'KM')

Update

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

UPDATE Maps SET [Unit Type] = 'metres',[Distance (Radius)] = 700 WHERE ID = 'Test123'

Delete

Remove a Map by specifying the Id.

DELETE FROM Maps WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the map.
Assignedto String False The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the map
Distance(Radius) Double False Radius being covered in the map
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
ModuleTypetoDisplay String False Module linked to the map
Name String False Name assigned to the map
ParentID String False Id of the map's parent record
ParentType String False Type of the map's parent
Relatedto(Center) String True Name of the map's parent. Also identifies the map's center location
UnitType String False Length unit expressing the radius
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Meetings

Create, update, delete, and query meeting information.

Table Specific Information

Select

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

SELECT * FROM Meetings WHERE Status = 'Held'

Insert

Create a Meeting by specifying any writable column.

INSERT INTO Meetings (Subject,[Start Date],[Duration Minutes]) VALUES ('New meeting', '2017-06-13', 45)

Update

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

UPDATE Meetings SET [Meeting Password] = 'Updated pass' WHERE ID = 'Test123'

Delete

Remove a Meeting by specifying the Id.

DELETE FROM Meetings WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier for the meeting.
AcceptLink String True The accept status for the meeting.
Address String True Address from Google Maps of the meeting.
Assignedto String False The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
Contact String False The name of the associated contact.
contact_id String True The ID of the associated contact.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The user name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Full text of the note.
Direction String True Whether the meeting is inbound or outbound.
DisplayURL String False The meeting URL.
Duration String True Duration handler dropdown.
DurationHours Int False The duration (hours).
DurationMinutes Int False The duration (minutes).
EmailReminder Bool True Whether or not the email reminder value is set.
EmailRemindersent Bool False Whether the email reminder is already sent.
EmailReminderTime String False Specifies when a email reminder alert should be issued: -1 means no alert; otherwise the number of seconds prior to the start.
EndDate Datetime False Date the meeting ends.
ExternalAppID String False The meeting ID for the external app API.
GeocodeStatus String True Geocode from Google Maps of the meeting.
HostURL String False The host URL.
Latitude Double True Latitude from Google Maps of the meeting.
Location String False The location of the meeting.
Longitude Double True Longitude from Google Maps of the meeting.
MeetingCreator String False The meeting creator.
MeetingPassword String False The password of the meeting.
Meetingupdatesequence Int False Meeting update sequence for meetings as per iCalendar standards.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The user name of the user who last modified the record.
OutlookID String False When the Sugar Plug-in for Microsoft Outlook syncs an Outlook appointment, this is the Outlook appointment item Id.
ParentID String False Id of the first element of recurring records.
ParentType String False Module the meeting is associated with.
RecurringSource String False Source of recurring meeting.
Relatedto String True The name of the associated parent Sugar module.
ReminderChecked Bool True Whether or not the reminder value is set.
ReminderTime String False Specifies when a reminder alert should be issued: -1 means no alert; otherwise the number of seconds prior to the start.
Reminders String True List of reminders set for the meetings
RepeatCount Int False Number of recurrences.
RepeatDow String False The day of week of a meeting.
RepeatInterval Int False The interval of a recurring meeting.
RepeatParentID String False Id of the first element of recurring records.
RepeatType String False Type of a recurring meeting.
RepeatUntil Date False Repeat until the specified date.
StartDate Datetime False Date of the start of the meeting.
Start/JoinMeeting String False The join URL.
Status String False Meeting status (e.g., Planned, Held, or Not held).
Subject String False The meeting name.
Type String False Meeting type (e.g., WebEx, or Other).
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Notes

Create, update, delete, and query notes in SuiteCRM

Table Specific Information

Select

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

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

Insert

Create a Note by specifying any writable column.

INSERT INTO Notes (Subject,[Parent ID]) VALUES ('Test Note', 'AccountId')

Update

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

UPDATE Notes SET Attachment = 'selected.docx' WHERE ID = 'Test123'

Delete

Remove a Note by specifying the Id.

DELETE FROM Notes WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False Unique identifier of the record.
AccountID String True The ID of the account associated with the note.
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
Attachment String False File name associated with the note (attachment).
CaseID String True The ID of the case associated with the note.
Contact String True The name of the contact associated with the note.
ContactID String False The ID of the contact the note is associated with.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The user name of the user who created the record.
DateCreated Datetime True Date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
DisplayinPortal? Bool False Portal flag indicator, which determines if the note is created via portal.
EmailAddress String True The email of the contact associated with the note.
EmbedinEmail? Bool False Embed flag indicator, which determines if the note is embedded in an email.
LeadID String True The ID of the lead associated with the note.
MimeType String False Attachment MIME type.
ModifiedById String True The user who last modified the record.
ModifiedByName String True The user name of the user who last modified the record.
Note String False Full text of the note.
OpportunityID String True The ID of the opportunity associated with the note.
ParentID String False The ID of the parent Sugar item.
ParentType String False Sugar module the Note is associated with.
Phone String True The phone number of the contact associated with the note.
RelatedTo String True The name of the parent object associated with the note.
Subject String False Title of the note.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
OAuthConsumerKeys

Create, update, delete, and query information on OAuth keys distributed by the application.

Table Specific Information

Select

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

SELECT * FROM [OAuth Consumer Keys] WHERE [Consumer Key Name] LIKE '%facebook%' AND [Date Created] > '2017-10-09'

Insert

Create an OAuth Consumer Key by specifying any writable column.

INSERT INTO [OAuth Consumer Keys] ([Consumer Key Name], [Consumer Key], [Consumer Secret]) VALUES ('New', 'dfvnspidn', '223bbcsubd')

Update

You can update any OAuth Consumer Key entry column that is writable, by specifying the Id.

UPDATE [OAuth Consumer Keys] SET [Consumer Key Name] = 'Updated' WHERE ID = 'Test123'

Delete

Remove an OAuth Consumer Key by specifying the Id.

DELETE FROM [OAuth Consumer Keys] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the OAuth key.
User String True The user name of the user the key has been assigned to.
AssignedUserId String False The ID of the user the key has been assigned to.
ConsumerKeyName String False Name assigned to the key
ConsumerKey String False Key value used in the OAuth 1.0 authentication with SuiteCRM
ConsumerSecret String False Secret used in the OAuth 1.0 authentication with SuiteCRM
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the OAuth key
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
OAuthTokens

Query currently active OAuth tokens

Table Specific Information

Select

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

SELECT * FROM [OAuth Tokens] WHERE [Callback Url] LIKE '%.net/consume'

Insert

Create an OAuth Token by specifying any writable column.

INSERT INTO [Oauth Tokens] ([Consumer Name], Consumer, Secret) VALUES ('Testing app', 'code101', 'hushSecret3')

Update

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

UPDATE [Oauth Tokens] SET TState = '1' WHERE ID = 'Test123'

Delete

Remove an OAuth Token by specifying the Id.

DELETE FROM [OAuth Tokens] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the token.
CallbackURL String False Callback URL given for the token
Consumer String False Consumer key associated with the token
ConsumerName String True The consumer name given for the token
Deleted Bool False The record deletion indicator.
LBL_ASSIGNED_TO_ID String True The user name of the user the token has been assigned to.
AssignedUserId String False The ID of the user the token has been assigned to.
Secret String False Consumer secret associeated with the token
Token_TS String False Timestamp recorded for the token
TState String False Current state of the token
Verify String False Verification status of the token
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Opportunities

Create, update, delete, and query opportunities saved in SuiteCRM

Table Specific Information

Select

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

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

Insert

Create an Opportunity by specifying any writable column.

INSERT INTO Opportunities ([Opportunity Name], Amount, [Account Id]) VALUES ('Good opportunity', 5000, 'AccountId58')

Update

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

UPDATE Opportunities SET [Expected Close Date] = '2017-08-25' WHERE ID = 'Test123'

Delete

Remove an Opportunity by specifying the Id.

DELETE FROM Opportunities WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the opportunity.
AccountID String True The ID of the associated account.
AccountName String True The name of the associated account.
Address String True Address from Google Maps of the opprtunity.
Amount Double False Formatted amount of the opportunity.
Assignedto String True The user name of the user assigned to the record.
AssignedUser String False The ID of the user assigned to the record.
Campaign String True The name of the campaign that generated the lead.
campaign_id String False The ID of the campaign that generated the lead.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The user name of the user who created the record.
Currency String False The ID of the currency used for display purposes.
CurrencyName String True The name of the currency used for display purposes.
CurrencySymbol String True The symbol of the currency used for display purposes.
DateCreated Datetime True Date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Full text of the note.
ExpectedCloseDate Date False The expected or actual date the oppportunity will close.
GeocodeStatus String True Geocode from Google Maps of the opprtunity.
Latitude Double True Latitude from Google Maps of the opprtunity.
LeadSource String False Source of the opportunity.
Longitude Double True Longitude from Google Maps of the opprtunity.
ModifiedById String True User who last modified the record.
ModifiedByName String True The user name of the user who last modified the record.
NextStep String False The next step in the sales process.
OpportunityAmount Double False Unconverted amount of the opportunity.
OpportunityName String False Name of the opportunity.
Probability(%) Int False The probability of closure.
SalesStage String False Indication of progression towards closure.
Type String False Type of opportunity (e.g., Existing or New).
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
OutboundEmailAccounts

Create, update, delete, and query the outbound email accounts table

Table Specific Information

Select

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

SELECT * FROM [Outbound Email Accounts] WHERE Type = 'system'

Insert

Create an Outbound Email Account by specifying any writable column.

INSERT INTO [Outbound Email Accounts] (Name, [SMTP Username], [Mail Send Smtp Type]) VALUES ('Test email', 'suitecrm@gmail.com', 'Gmail')

Update

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

UPDATE [Outbound Email Accounts] SET [Use SMTP Authentication?] = true WHERE ID = 'Test123'

Delete

Delete an Outbound Email Account by specifying the Id.

DELETE FROM [Outbound Email Accounts] WHERE ID = '10003'
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the outbound account.
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
ChooseyourEmailprovider String True identifier for the email provider
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
MailSendSmptType String False SMTP Type of the connected account The allowed values are IMAP, POP3.
LBL_MAIL_SENDTYPE String False Type of mail intended to be sent
LBL_MAIL_SMTPSSL String False Secure layer protocol of the connected email account
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the outbound account
Password String True Password set for the account
SendTestEmail String True 'Send Test Email' button content
SMTPPassword String False Password to use in the SMTP authentication
SMTPPort Int False Port to use in the SMTP authentication
SMTPServer String False Server address to use in the SMTP authentication
SMTPUsername String False Username to use in the SMTP authentication
Type String False Outbound account type
UseSMTPAuthentication? Bool False Whether the account to be connected will use SMTP authentication
UserId String False Id of the user linked with the account
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
PDFTemplates

Create, update, delete, and query PDFTemplates table.

Table Specific Information

Select

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

SELECT * FROM [PDF Templates] WHERE Active = true

Insert

Create a pdf template by specifying any writable column.

INSERT INTO [PDF Templates] (Active, Name) VALUES (true, 'Test Template')

Update

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

UPDATE [PDF Templates] SET Active = false WHERE ID = '6e1d3749-7e1c-f19c-251d-5b855e30b695'

Delete

Remove a pdf template by specifying the Id.

DELETE FROM [PDF Templates] WHERE ID = '5fddceac-8715-d1f1-efa3-5b854ab921a6'
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier for the pdf template.
Active Bool False Whether or not the template is active.
Assigned_to String False The assignee of the template.
Assigned_User_Id String False The unique identifier of the assignee.
Body String False Content of the template's body.
Created_By String False The ID of the user who created the template.
Created_By2 String False The name of the user who created the template.
Date_Created Datetime False Date when the template was created.
Date_Modified Datetime False Date when the template was modified.
Deleted Bool False Whether the template was deleted.
Footer String False The content of the template's footer.
Header String False The content of the template's header.
Insert_Fields String False The fields that can be inserted.
Load_Sample String False The content of the template's sample.
Margin_Bottom Int False Value of the bottom margin for the template.
Margin_Footer Int False Value of the margin for the template's footer.
Margin_Header Int False Value of the margin for the template's header.
Margin_Left Int False Value of the left margin for the template.
Margin_Right Int False Value of the right margin for the template.
Margin_Top Int False Value of the top margin for the template.
Modified_By String False The ID of the user who modified the template.
Modified_By2 String False The name of the user who modified the template.
Name String False The template's name.
Orientation String False Orientation of the template.
Page_Size String False The size of the template.
Type String False The type of the template.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
ProcessAudit

Create, update, delete, and query information on process audits

Table Specific Information

Select

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

SELECT * FROM [Process Audit] WHERE Module = 'Opportunities'

Insert

Create a Process Audit by specifying any writable column.

INSERT INTO [Process Audit] (Name, Record, Module) VALUES ('Final Audit', 'recordId', 'Leads')

Update

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

UPDATE [Process Audit] SET Status = 'Completed' WHERE ID = 'Test123'

Delete

Delete a Process Audit by specifying the Id.

DELETE FROM [Process Audit] WHERE ID = '10003'
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the inbound.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the process audit
LBL_AOW_WORKFLOW_ID String False Id of the workflow the audit is following
Workflow String True Name of the workflow the audit is following
LBL_BEAN_ID String False The ID of the audit's parent record
Record String True Name of the audit's parent record
ParentType String False Module the audit's parent belongs to
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the process audit
Status String False Status of the audit
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
ProductCategories

Create, update, delete, and query the product categories.

Table Specific Information

Select

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

SELECT * FROM [Product Categories] WHERE Name = 'Clothing'

Insert

Create a Product Category by specifying any writable column.

INSERT INTO [Product Categories] (Name, [Is Parent Category]) VALUES ('Color', true)

Update

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

UPDATE [Product Categories] SET Name = 'Changed' WHERE ID = 'Test123'

Delete

Delete a Product Category by specifying the Id.

DELETE FROM [Product Categories] WHERE ID = '10003'
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the inbound.
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the category
Isparentcategory Bool False Whether this category will be a parent for other categories
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the category
ParentCategoryID String False Id of the category's parent category
ParentCategory String True Name of the parent category
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Products

Create, update, delete, and query the products registered for the SuiteCRM project

Table Specific Information

Select

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

SELECT * FROM Products WHERE [Product Name] LIKE '%test%'

Insert

Create a Product by specifying any writable column.

INSERT INTO Products ([Product Name], [Product Code]) VALUES ('Prod', 'CODE1')

Update

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

UPDATE Products SET [Product Code] = 'Test', [Part Number] = '2part' WHERE ID = 'Test123'

Delete

Delete a Product by specifying the Id.

DELETE FROM Products WHERE ID = '10003'
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the ACL Action.
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
Category String False Name of the product's category.
Contact String True Product's point of contact name.
contact_id String False Product's point of contact id.
Cost Double False Cost of the product.
Cost(DefaultCurrency) Double False Cost of the product in USD.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Currency String False The ID of the currency used for the product.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False The action description.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
PartNumber String False Part number of the product.
Price Double False Price of the product.
Price(DefaultCurrency) Double False Price of the product in USD.
ProductCategory String True Name of the product's category.
ProductCategoryID String False Id of the product's category.
ProductCode String False The code assigned to the product
ProductImage String False Image uploaded for the product.
ProductName String False The action name.
Type String False Type of the product.
URL String False Url where the product can be seen.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Projects

Create, update, delete, and query projects registered in SuiteCRM

Table Specific Information

Select

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

SELECT * FROM Projects WHERE [End Date] > '2017-10-09'

Insert

Create a Project by specifying any writable column.

INSERT INTO Projects (Name, Status, Priority) VALUES ('New projects', 'Draft', 'High')

Update

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

UPDATE Projects SET Status = 'Underway' WHERE ID = 'Test123'

Delete

Remove a Project by specifying the Id.

DELETE FROM Projects WHERE ID = 10003
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the project.
Address String True Address from Google Maps of the project.
AssignedTo String False The user name of the user assigned to the record.
ConsiderWorkingDays Bool False Whether to include business hours in the project duration.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Project description.
EndDate Date False The estimated end date.
GeocodeStatus String True Geocode from Google Maps of the project.
Latitude Double True Latitude from Google Maps of the project.
Longitude Double True Longitude from Google Maps of the project.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Project name.
Priority String False The priority of the project.
ProjectManager String True The user name of the user assigned to the project.
ProjectTemplate String True Which project template was the project created from
StartDate Date False The estimated start date.
Status String False The status of the project.
TotalActualEffort(hrs) Int True The total actual effort of the project.
TotalEstimatedEffort(hrs) Int True The total estimated effort of the project.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
ProjectTemplates

Create, update, delete, and query any saved project template.

Table Specific Information

Select

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

SELECT * FROM [Project Templates] WHERE Priority = 'High'

Insert

Create a Project Template by specifying any writable column.

INSERT INTO [Project Templates] ([Template Name], Status, Priority) VALUES ('Automobile Template', 'Created', 'High')

Update

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

UPDATE Accounts SET Notes = 'In partnership with BMW' WHERE ID = 'Test123'

Delete

Remove a Project Template by specifying the Id.

DELETE FROM Project Templates WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the project template.
TemplateName String False Name assigned to the template
ProjectManager String True The user name of the user the template has been assigned to
AssignedUserId String False The ID of the user the template has been assigned to.
ConsiderWorkingDays Bool False Whether to include working days in the project's effort
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Notes String False Description for the project template
Priority String False Priority to be assigned to the project
Status String False Status to be assigned to the project
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Quotes

Create, update, delete, and query quotes saved in SuiteCRM

Table Specific Information

Select

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

SELECT * FROM Quotes WHERE [Valid Until] < '2017-04-30'

Insert

Create a Quote by specifying any writable column.

INSERT INTO Quotes (Title, Account, [Valid Until], [Quote Number]) VALUES ('Brand Quote', 'BillingAccountZ3', ''2018-02-24', 3)

Update

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

UPDATE Quotes SET [Approval Status] = 'Incomplete',[Approval Issues] = 'Delays from the DMV' WHERE ID = 'Test123'

Delete

Remove a Quote by specifying the Id.

DELETE FROM Quotes WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False
ApprovalIssues String False
ApprovalStatus String False
Assignedto String True
AssignedUserId String False
BillingCity String False City where the billing account is recorded in
BillingCountry String False Country where the billing account is recorded in
BillingPostal_Code String False Postal code of the billing account
BillingState String False State where the billing account is recorded in
BillingStreet String False Street where the billing account is recorded in
billing_account_id String False Id of the billing account
Account String True Billing account name for the quote
billing_contact_id String False Id of the billing contact
Contact String True Name of the billing contact
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Currency String False Id of the currency used for currency values
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the quote
Discount Double False The quote's discount amount
Discount(DefaultCurrency) Double False The quote's discount amount in the system's default currency
GrandTotal Double False The quote's grand total
GrandTotal(DefaultCurrency) Double False The quote's grand total in the system's default currency
InvoiceStatus String False Quote's invoice status
LineItems String True The list of the quote's line items
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Opportunity String True Opportunity name of the quote
opportunity_id String False Opportunity ID of the quote
PaymentTerms String False Selected terms for quote's payments
QuoteNumber Int False The assigned quote number
QuoteStage String False The quote's assigned stage
QuoteTemplate String False The quote's template
Shipping Double False The quote's shipping amount
Shipping(DefaultCurrency) Double False The quote's shipping amount in the system's default currency
ShippingCity String False City of the shipping destination
ShippingCountry String False Country of the shipping destination
ShippingPostalCode String False Postal code of the shipping destination
ShippingState String False State of the shipping destination
ShippingStreet String False Street address of the shipping destination
ShippingTax String False The quote's shipping tax costs
ShippingTax(DefaultCurrency) Double False The quotes's discountamount in the system's default currency
ShippingTaxAmount Double False The quotes's shipping tax amount
Subtotal Double False The quotes's subtotal amount
Subtotal(DefaultCurrency) Double False The quotes's subtotal amount in the system's default currency
SubtotalTax Double False The quotes's subtotal and tax amount
SubtotalTaxAmount Double False The quotes's subtotal and tax amount in the system's default currency
Tax Double False The quotes's tax amount
Tax(DefaultCurrency) Double False The quotes's tax amount in the system's default currency
Terms String False The quote's terms
Title String False Title assigned to the quote
Total Double False The quotes's total amount
Total(DefaultCurrency) Double False The quotes's total amount in the system's default currency
ValidUntil Date False The date the quote expires
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Releases

Create, update, delete, and query the registered releases.

Table Specific Information

Select

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

SELECT * FROM Releases WHERE [Release Version] LIKE 'V3.%'

Insert

Create a Release by specifying any writable column.

INSERT INTO Releases ([Release Version], [Order]) VALUES ('V3.4', 3)

Update

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

UPDATE Releases SET Status = 'Published' WHERE ID = 'Test123'

Delete

Remove a Release by specifying the Id.

DELETE FROM Releases WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the record.
CreatedBy String True The ID of the user who created the record.
CreatedByName String True The Name of the user who created the record.
DateCreated Datetime True The date the record was entered.
DateModified Datetime True The date the record was last modified.
Deleted Bool False Whether the record is deleted.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The Name of the user who last modified the record.
Order Int False The relative order in the drop-down list.
ReleaseVersion String False The name of the release.
Status String False The status of the record.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
ReportCharts

Create, update, delete, and query report charts.

Table Specific Information

Select

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

SELECT * FROM [Report Charts] WHERE Type LIKE 'Bar Chart'

Insert

Create a Report Chart by specifying any writable column.LBL_X_FIELD and LBL_Y_FIELD must be equal to the order of the report fields selected for the reports to the report

INSERT INTO [Report Charts] (Name, LBL_X_FIELD, LBL_Y_FIELD, LBL_AOR_REPORT_ID) VALUES ('Yearly Report', 0, 1, 'RecordId34')

Update

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

UPDATE [Report Charts] SET Type = 'Histogram' WHERE ID = 'Test123'

Delete

Remove a Report Chart by specifying the Id.

DELETE FROM [Report Charts] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the inbound.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the Report Chart
LBL_AOR_REPORT_ID String False The report ID of the report the chart is built on
LBL_AOR_REPORT_NAME String True The report name of the report the chart is built on
LBL_X\_FIELD Int False Label for the X coordinate in the report
LBL_Y\_FIELD Int False Label for the Y coordinate in the report
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the chart
Type String False Type of the report chart
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
ReportConditions

Create, update, delete, and query report conditions.

Table Specific Information

Select

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

SELECT * FROM [Report Conditions] WHERE [Logic] = 'Less than'

Insert

Create a Report Condition entry by specifying any writable column.

INSERT INTO [Report Conditions] (Name, Logic, Operator, Value,[Report Id]) VALUES ('Comparison', 'Greater Than', '>', 410, 'reportIdR2')

Update

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

UPDATE [Report Conditions] SET Order = 1 WHERE ID = 'Test123'

Delete

Remove a Report Condition entry by specifying the Id.

DELETE FROM [Report Conditions] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the inbound.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the report condition
Field String False Module field to select for the condition
LBL_PARENTHESIS String False The ID of the report condition to enclose in the parenthesis
Logic String False The logic operator to link to the next condition The allowed values are AND, OR.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Module String False Which module to query in the condition
Name String False Name assigned to the condition
Operator String False The operator to test against the value, such as 'Equal to', 'Less than'
Order Int False Defines the order in which the condition will be resolved
Parameter Bool False Whether to treat the condition as a parameter
ReportId String False The report ID that the condition will apply to
Type String False What type of value to compare against
Value String False Value to use for comparison
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
ReportFields

Create, update, delete, and query the saved report fields.

Table Specific Information

Select

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

SELECT * FROM [Report Fields] WHERE Module = 'Spots'

Insert

Create a Report Field by specifying any writable column.

INSERT INTO [Report Fields] (Name, Module, Function) VALUES ('New Field', 'Reports', 'COUNT')

Update

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

UPDATE [Report Fields] SET Display = false WHERE ID = 'Test123'

Delete

Remove a Report Field by specifying the Id.

DELETE FROM [Report Fields] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the report field entry.
CreatedById String True The ID of the user who created the record.
CreatedByName String True
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the report field entry
Display Bool False Whether to display the selected field in the report
Field String False The module field targeted for the report
FormatOptions String False Additional format options
Function String False Function to be applied for the field
Group Bool False Whether the results from this field are grouped
GroupOrder String False The order of the group by
Label String True The field's label in its own module
LBL_AOR_REPORT_ID String False The report ID the report field will be applied to
LBL_GROUP_DISPLAY Int False Whether to display the group
Link Bool False Whether to enable a link to the field
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Module String False The module to retrieve the report field from
Name String False Name assigned to the report field entry
Order Int False The ordering of the report field
Sort String False Sort this field by ASC or DESC
SortOrder String False The ordering of the sort for the report field
Total String True The function that adds the results of the report query
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Reports

Create, update, delete, and query information on reports made in SuiteCRM.

Table Specific Information

Select

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

SELECT * FROM Reports WHERE [Report Module] = 'Projects'

Insert

Create a Report by specifying any writable column.

INSERT INTO Reports (Name, [Report Module], [Display Fields]) VALUES ('Incident Report', [Products], 'ID, Product Name, Damages')

Update

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

UPDATE Reports SET Name = 'Updated Report' WHERE ID = 'Test123'

Delete

Remove a Report by specifying the Id.

DELETE FROM Reports WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the report.
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
Chartsperrow Int False Number of charts to display for each row
Conditions String True The complete conditions of the report
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the report
DisplayFields String True The report fields selected for the report
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the report
ReportModule String False The module the report has targeted
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Roles

Create, update, delete, and query the roles in SuiteCRM.

Table Specific Information

Select

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

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

Insert

Create a Role by specifying any writable column.

INSERT INTO Roles (Name,[Modules]) VALUES ('Tester', 'AOS_Products')

Update

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

UPDATE Roles SET [Modules] = 'Accounts' WHERE ID = 'Test123'

Delete

Remove a Role by specifying the Id.

DELETE FROM Roles WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the role.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The Name of the user who created the record.
DateCreated Datetime True The date the record was entered.
DateModified Datetime True The date the record was last modified.
Deleted Bool False Whether the record is deleted.
Description String False The description of the role.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The Name of the user who last modified the record.
Modules String False The modules the role has permission to access.
Name String False The name of the role.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
SavedSearches

Query any saved searches

Table Specific Information

Select

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

SELECT * FROM [Saved Searches] WHERE Contents LIKE '%account%' AND [Date Created] > '2017-08-09'

Insert

Create a Saved Search by specifying any writable column.

INSERT INTO [Saved Searches] (Name, Module, Contents) VALUES ('Search 1', 'Opportunities', 'growth chance')

Update

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

UPDATE [Saved Searches] SET Contents = 'ideas' WHERE ID = 'Test123'

Delete

Remove a Saved Search by specifying the Id.

DELETE FROM [Saved Searches] WHERE ID = 10003
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the saved search.
AssignedUserId String False User ID assigned to the record.
AssignedUserName String True The user name of the user assigned to the record.
Contents String False The contents of the saved search.
LBL_CREATED_BY Bool False The record deletion indicator.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Description String False The description of the role.
Module String False The search's target module.
Name String False The name of the saved search.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
ScheduledReports

Create, update, delete, and query information on SuiteCRM scheduled reports.

Table Specific Information

Select

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

SELECT * FROM [Scheduled Reports] WHERE [Last Run] < '2014-11-15'

Insert

Create a Scheduled Report by specifying any writable column.

INSERT INTO [Scheduled Reports] (Name, LBL_AOR_REPORT_ID, Schedule) VALUES ('Monthly Earnings Reporting', 'ReportId91', 'ScheduleId20')

Update

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

UPDATE [Scheduled Reports] SET [Email Recipients] = 'emailaddress1,emailaddress2' WHERE ID = 'Test123'

Delete

Remove a Scheduled Report by specifying the Id.

DELETE FROM [Scheduled Reports] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the scheduled report.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the scheduled report
EmailRecipients String False List of email addresses the schedule results will be sent to
Lastrun String True Date of last run of the schedule
LBL_AOR_REPORT_ID String False Report ID of the report that is generated
Reports String True The name of the report being scheduled to run
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the schedule
Schedule String False The schedule expressed in cron language
Status String False Status of the schedule The allowed values are Active, Inactive.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Schedulers

Create, update, delete, and query schedulers.

Table Specific Information

Select

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

SELECT * FROM Schedulers WHERE [Last Successful Run] > '2017-07-16'

Insert

Create a Scheduler by specifying any writable column.

INSERT INTO Schedulers ([Job Name], Interval, [Date Time Start], [Date Time End]) VALUES ('Test Account', '0:0:*:*:*', '2017-06-16', '2017-08-16')

Update

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

UPDATE Schedulers SET [Active From] = '09:00', [Active To] = '15:00' WHERE ID = 'Test123'

Delete

Remove a Scheduler by specifying the Id.

DELETE FROM Schedulers WHERE ID = 10003
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the scheduler.
ActiveFrom String False The time the scheduler begins to be active per day.
ActiveTo String False Time the scheduler stops being active per day.
AdvancedOptions Bool True Whether there are advanced options for the interval.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateTimeEnd Datetime False The end date and time.
DateTimeStart Datetime False The start date and time.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
ExecuteIfMissed Bool False Whether the scheduler will catch up.
Interval String False The job interval expressed in standard crontab notation
Job String False The job.
JobFunction String True The job function.
JobName String False The name of the scheduler.
JobURL String True The job URL.
LastSuccessfulRun Datetime False The last run time of the scheduler.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Status String False The status of the scheduler.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
SecurityGroupsManagement

Create, update, delete, and query information on security groups

Table Specific Information

Select

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

SELECT * FROM [Security Groups Management] WHERE [Not Inheritable] = true

Insert

Create a Security Group by specifying any writable column.

INSERT INTO [Security Groups Management] (Name, [Not Inheritable]) VALUES ('Test Group', false)

Update

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

UPDATE [Security Groups Management] SET [Not Inheritable] = true WHERE ID = 'Test123'

Delete

Remove a Security Group by specifying the Id.

DELETE FROM [Security Groups Management] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the security group.
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the security group
LBL_SECURITYGROUP_NONINHERITABLE Bool True Whether the group is inheritable
LBL_securitygroup_noninherit_id String True Non-Inheritance Id
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False
NotInheritable Bool False Whether the group is inheritable
PrimaryGroup Bool True Whether the group is primary
UserName String True User fields for the group
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Spots

Create, update, delete, and query the saved spots.

Table Specific Information

Select

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

SELECT * FROM Spots WHERE Type = 'getMarketingSpotsData'

Insert

Create a Spot by specifying any writable column.

INSERT INTO Spots (Name, Type) VALUES ('Test Account', 'getServicesSpotsData')

Update

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

UPDATE Spots SET Description = 'Updated Spot' WHERE ID = 'Test123'

Delete

Remove a Spot by specifying the Id.

DELETE FROM Spots WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the record.
Assignedto String True The user name of the user assigned to the record.
AssignedUser_Id String False The ID of the user assigned to the record.
Config String False The the configuration set of the spot.
Configuration String True The GUI settings of the spot.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Full text of the note.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False The name of the spot.
Type String False The field this spot is targeting.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
SuiteCRMFeed

Create, update, delete, and query information on the activity feed in the SuiteCRM project.

Table Specific Information

Select

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

SELECT * FROM [SuiteCRM Feed] WHERE [Related Module] = 'Locations'

Insert

Create a SuiteCRM Feed by specifying any writable column.

INSERT INTO [SuiteCRM Feed] (Name, [Related Id], [Related Module], Description) VALUES ('Feed No.24', 'recordId32', 'Products', 'Updated')

Update

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

UPDATE [SuiteCRM Feed] SET [Related Id] = 'otherrecordId4',[Related Module] = 'Targets' WHERE ID = 'Test123'

Delete

Remove a SuiteCRM Feed by specifying the Id.

DELETE FROM [SuiteCRM Feed] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the feed entry.
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the feed entry
LinkType String False Type of link to the feed
LinkUrl String False The URL to the feed
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False A summary of the feed
RelatedId String False The record ID related to the feed
RelatedModule String False The module returned by the feed
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
TargetLists

Create, update, delete, and query the saved target lists.

Table Specific Information

Select

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

SELECT * FROM [Target Lists] WHERE [Targets in List] > 2

Insert

Create a Target List by specifying any writable column.

INSERT INTO [Target Lists] (Name, Type, [Assigned User Id]) VALUES ('Test Account', 'Default', 'UserId42')

Update

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

UPDATE [Target Lists] SET [Domain Name] = 'New Domain', Type = 'seed' WHERE ID = 'Test123'

Delete

Remove a Target List by specifying the Id.

DELETE FROM [Target Lists] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The ID of the target list.
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
CreatedBy Bool False The record deletion indicator.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Description String False The description of the target list.
DomainName String False The domain name of the target list.
MarketingId String True The marketing ID of the target list.
MarketingName String True The marketing name of the target list.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False The name of the target list.
TargetsinList Int True The entry count of the prospect list.
Type String False The type of the target list.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Targets

Query targets saved in SuiteCRM

Table Specific Information

Select

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

SELECT * FROM Targets WHERE Title = 'Eng'

Insert

Create a Target by specifying any writable column.

INSERT INTO Targets ([First Name], [Last Name], [Any Email], [Campaign ID]) VALUES ('Test', 'Prospect', 'example@test.com', 'IdCamp1')

Update

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

UPDATE Targets SET Department = 'Finance' WHERE ID = 'Test123'

Delete

Remove a Target by specifying the Id.

DELETE FROM Targets WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the record.
AccountName String False The name of the associated acocunt.
Address String True Address from Google Maps of the target.
AlternateAddressCity String False The city for the alternate address.
AlternateAddressCountry String False The country for the alternate address.
AlternateAddressPostalCode String False The postal code for the alternate address.
AlternateAddressState String False The state for the alternate address.
AlternateAddressStreet String False The street address for the alternate address.
AlternateAddressStreet2 String True The second line of the alternate address.
AlternateAddressStreet3 String True The third line of the alternate address.
AnyEmail String True The email for the target.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True The user name of the user assigned to the record.
Assistant String False The name of the assistant of the target.
AssistantPhone String False The phone number of the assistant of the target.
Birthdate Date False The birthdate of the target.
CampaignID String False Campaign that generated the lead.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Department String False The department of the target.
Description String False Full text of the note.
DoNotCall Bool False An indicator of whether the target can be called.
EmailAddress String False The alternate email for the target.
EmailOptOut Bool True Whether the target has opted out of radio.
Fax String False The target fax number.
FirstName String False The first name of the target.
FullName String True The full name of the target.
GeocodeStatus String True Geocode from Google Maps of the target.
Home String False Home phone number of the target.
InvalidEmail Bool True Whether the target email has been marked as invalid.
LastName String False The last name of the target.
Latitude Double True Latitude from Google Maps of the target.
LBL_CONT_ACCEPT_STATUS String True The event accept status fields.
LBL_CONT_INVITE_STATUS String True The event invite status fields.
LBL_LIST_ACCEPT_STATUS String True Id of the target's event status.
LBL_LIST_ACCEPT_STATUS_EVENT String True Status of the target's event accept.
LBL_LIST_INVITE_STATUS String True Id of the target's event invite.
LBL_LIST_INVITE_STATUS_EVENT String True Name of the target's event status.
LeadId String False The ID of the associated lead.
Longitude Double True Longitude from Google Maps of the target.
Mobile String False Mobile phone number of the target.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String True The name of the target.
NonPrimaryE-mails String True The nonprimary email addresses for the target.
OfficePhone String False Work phone number of the target.
OtherEmail String True The alternate email for the target.
OtherPhone String False Other phone number for the target.
Photo String False The picture for the target.
PrimaryAddressCity String False The city for the primary address.
PrimaryAddressCountry String False The country for the primary address.
PrimaryAddressPostalCode String False The postal code for the primary address.
PrimaryAddressState String False The state for the primary address.
PrimaryAddressStreet String False The street address used for the primary address.
PrimaryAddressStreet2 String True The second line of the primary address.
PrimaryAddressStreet3 String True The third line of the primary addrss.
Salutation String False The target salutation (e.g., Mr. or Ms.).
Title String False The title of the target.
TrackerKey Int False The key for the associated tracker.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Tasks

Create, update, delete, and query tasks in SuiteCRM.

Table Specific Information

Select

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

SELECT * FROM Tasks WHERE Status = 'In Progress'

Insert

Create a Task by specifying any writable column.

INSERT INTO Tasks (Subject, Priority, [Due Date]) VALUES ('Urgent Task', 'High', '2017-10-30')

Update

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

UPDATE Tasks SET Status = 'Completed' WHERE ID = 'Test123'

Delete

Remove a Task by specifying the Id.

DELETE FROM Tasks WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the record.
Assignedto String True The name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
ContactID String False The ID of the associated contact.
ContactName String True The name of the associated contact.
ContactPhone String True The phone of the associated contact.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False The full text of the note.
DueDate Datetime False The due date of the task.
DueTime Datetime True The time the task is due.
EmailAddress String True The email of the associated contact.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
NoDueDate Bool False Whether the task has a due date.
NoStartDate Bool False Whether the flag has a start date.
ParentID String False The ID of the Sugar item to which the call is related.
ParentType String False The type of the Sugar item to which the call is related.
Priority String False The priority of the task.
Relatedto String True The name of the Sugar item to which the call is related.
StartDate Datetime False The start date of the task.
Status String False The status of the task.
Subject String False The name of the task.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
TemplateSectionLine

Create, update, delete, and query the template section line entries in SuiteCRM

Table Specific Information

Select

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

SELECT * FROM [Template Section Line] WHERE [Name] LIKE '%Final%'

Insert

Create a Template Section Line by specifying any writable column.

INSERT INTO [Template Section Line] (Name, [Order], [Group]) VALUES ('New Temp Line', 3, 'Test Group')

Update

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

UPDATE [Template Section Line] SET [Order] = 1 WHERE ID = 'Test123'

Delete

Delete a Template Section Line by specifying the Id.

DELETE FROM [Template Section Line] WHERE ID = '10003'
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the template section line.
CreatedById String True The user name of the user assigned to the record.
CreatedByName String True
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Complete HTML text for the line
Group String False The group assigned to the line
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the template section line
Order Int False The line order of the section compared to the other lines in the group
Thumbnail String False Image URL
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Trackers

Create, update, delete, and query the created trackers.

Table Specific Information

Select

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

SELECT * FROM Trackers WHERE [Date of Last Action] <= '2015-04-01'

Insert

Create a Tracker by specifying any writable column.

INSERT INTO Trackers (Action, [Item Id], [Item Summary], Trackers) VALUES ('Detection', 'recordId3', 'Goal is to find inconsistencies', 'Leads')

Update

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

UPDATE Trackers SET [Record Visible] = false WHERE ID = 'Test123'

Delete

Delete a Tracker by specifying the Id.

DELETE FROM Trackers WHERE ID = '10003'
Columns
Name Type ReadOnly Description
ID [KEY] Int False The unique identifier of the tracker.
Action String False The type of action being tracked
DateofLastAction Datetime True Date when the action last took place
Deleted Bool False The record deletion indicator.
ItemId String True Id of the record being tracked
ItemSummary String True Name of the record being tracked
MonitorId String True Id of the tracker monitor
RecordVisible Bool True Whether the record is visible
SessionId String True Which session ID made the change
Trackers String False The module being tracked
UserId String True User ID that made the change
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
Users

Create, update, delete, and query the SuiteCRM registered users.

Table Specific Information

Select

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

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

Insert

Create a User by specifying any writable column.

INSERT INTO Users ([First Name], [Last Name], Title) VALUES ('First', 'Last', 'Mr')

Update

The Users table does not allow updates to its columns. Update the entries from the Employees table instead.

UPDATE Employees SET [Title] = 'Dr.', Department = 'HR' WHERE ID = 'test123'

Delete

Remove a User by specifying the Id.

DELETE FROM Users WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the user.
AcceptStatusC String False The fields for the accept status of the call.
AcceptStatusId String False The ID of the accept status.
AcceptStatusM String False The fields for the accept status of the meeting.
AcceptStatusName String False The name of the accept status.
AddressCity String False The city in the address.
AddressCountry String False The country in the address.
AddressPostalCode String False The ZIP code in the address.
AddressState String False The state in the address.
AddressStreet String False The street address of the user.
AssignedTo String False The ID of the user who created the record.
AuthenticationId String False The authentication Id.
CreatedByName String True The name of the user who created the record.
DateEntered Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Department String False The department of the user.
Description String False The description for the user.
DisplayEmployeeRecord Bool False Whether the user is shown on the employees tab.
EmailAddress String False The alternate email address of the user.
EmailClient String False The link type of the email.
EmployeeStatus String False The status of the employee.
ExternalAuthentication Bool False Whether only external authentication is enabled for the user.
Fax String False The fax of the user.
FirstName String False The first name of the user.
FullName String False The full name of the user.
GroupUser Bool False Whether the user is a group.
HomePhone String False The home phone number of the user.
IMName String False The ID of the messenger.
IMType String False The type of the messenger.
IsAdministrator Bool False Whether the user is an admin.
IsSuiteCRMUser Bool False Whether the user is SuiteCRM user. If not, it is a guest user.
LastName String False The last name of the user.
LBL_SECURITYGROUP_NONINHERITABLE Bool False Whether the group is non inheritable.
LBL_securitygroup_noninherit_id String False The security group's non-inheritance id.
Mobile String False The mobile phone number of the user.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Full name for the user
NotifyonAssignment Bool False Whether the user can receive notifications.
OtherPhone String False The alternate phone number of the user.
Password String False The hashed password for the user.
PasswordLastChanged Datetime False The date and time the password was last changed.
Photo String False The photo for the user.
PortalAPIUser Bool False Whether the user is portal only.
PrimaryGroup Bool False The primary security group the user is assigned to.
Reportsto String False The name of the manager of the user.
ReportstoID String False The ID of the manager of the user.
SecurityGroup String False The security group's fields that are selected for non-inheritance.
Status String False The status of the user.
SystemGeneratedPassword Bool False Whether the user has a system-generated password.
Title String False The title of the user.
UserType String False The type of the user.
Username String False The user name of the user.
WorkPhone String False The work phone number of the user.
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
WorkFlow

Create, update, delete, and query the wokflow actions in SuiteCRM

Table Specific Information

Select

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

SELECT * FROM [Workflow] WHERE Actions = 'Create Record' AND [Date Created] < '2017-10-09'

Insert

Create a Workflow by specifying any writable column.

INSERT INTO [Workflow] (Name, Run, LBL_RUN_ON) VALUES ('Test WF', 'Always', 'All Records')

Update

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

UPDATE [Workflow] SET [Repeated Runs] = true WHERE ID = 'Test123'

Delete

Remove a Workflow by specifying the Id.

DELETE FROM [Workflow] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the inbound.
Actions String True The list of actions to be taken in the workflow
Assignedto String True The user name of the user assigned to the record.
AssignedUserId String False The ID of the user assigned to the record.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Conditions String True The list of conditions to be applied during the workflow
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the worflow
LBL_RUN_ON String False Which records the workflow will operate on The allowed values are All Records, New Records, Modified Records.
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the workflow
RepeatedRuns Bool False Whether the worflow will run once or several times
Run String False On which occasion the workflow will execute The allowed values are Always, Only On Save, Only In The Scheduler.
Status String False The status of the workflow The allowed values are Active, Inactive.
WorkflowModule String False The module the workflow will target
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
WorkFlowActions

Create, update, delete, and query the wokflow actions in SuiteCRM.

Table Specific Information

Select

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

SELECT * FROM [Workflow Actions] WHERE Action = 'Create Record' AND [Date Created] < '2017-10-09'

Insert

Create a Workflow Action by specifying any writable column.

INSERT INTO [Workflow Actions] (Name, Action, LBL_WORKFLOW_ID) VALUES ('First action', 'Modify Record', 'exampleWFId')

Update

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

UPDATE [Workflow Actions] SET [Parameters] = 'name:test;status:complete;' WHERE ID = 'Test123'

Delete

Remove a Workflow Action by specifying the Id.

DELETE FROM [Workflow Actions] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the workflow.
Action String False The Specific action to be taken during the worklow
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the workflow
LBL_ORDER Int False The order of the worflow's action
LBL_WORKFLOW_ID String False The wokflow the action is linked to
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Name String False Name assigned to the action
Parameters String False List of parameters for the action
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.
WorkFlowConditions

Create, update, delete, and query the workflow conditions in SuiteCRM.

Table Specific Information

Select

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

SELECT * FROM [Workflow Conditions] WHERE Type = 'Field'

Insert

Create a Workflow Condition by specifying any writable column.

INSERT INTO [Workflow Conditions] (Name, Type, Value, Module, LBL_WORKFLOW_ID) VALUES ('WF Condition1', 'Value', 'testValue', 'Trackers', 'exampleWorkflowId')

Update

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

UPDATE [Workflow Conditions] SET Name = 'New name' WHERE ID = 'Test123'

Delete

Remove a Workflow Condition by specifying the Id.

DELETE FROM [Workflow Conditions] WHERE ID = 10003
Columns
Name Type ReadOnly Description
ID [KEY] String False The unique identifier of the inbound.
CreatedById String True The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
DateCreated Datetime True The date the record was created.
DateModified Datetime True The date the record was last modified.
Deleted Bool False The record deletion indicator.
Description String False Description for the workflow condition
Field String False The module field selected for the condition
ModifiedById String True The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
Module String False The module selected to apply the condition
Name String False Name assigned to the workflow condition
Operator String False Operator to test against
LBL_ORDER Int False The condition's order in the workflow
LBL_WORKFLOW_ID String False The workflow the condition will be applied for
Type String False Type of the field The allowed values are Value, Field, Any Change.
Value String False The value to compare against the field
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.

Name Type Description
Rows@Next String Identifier for the next page of results. Do not set this value manually.

SuiteCRM V8 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.

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
Name Description
Accounts Create, update, delete, and query accounts created in SuiteCRM
Calls Create, update, delete, and query the SuiteCRM project's Calls
Campaigns Create, update, delete, and query the SuiteCRM project's campaigns
Cases Create, update, delete, and query the SuiteCRM cases
Contacts Create, update, delete, and query the contacts in SuiteCRM
Contracts Create, update, delete, and query contracts in SuiteCRM
Documents Create, update, delete, and query Documents created in SuiteCRM
Emails Create, update, delete, and query the SuiteCRM Emails
EmailTemplates Create, update, delete, and query email templates to be used for emails
Events Create, update, delete, and query events registered in the SuiteCRM project
Invoices Create, update, delete, and query the invoices saved in the SuiteCRM
KBCategories Create, update, delete, and query the KBCategories saved in the SuiteCRM
KnowledgeBase Create, update, delete, and query the Knowledge Base saved in the SuiteCRM
Leads Create, update, delete, and query the registered Leads
Locations Create, update, delete, and query locations in SuiteCRM
Maps Create, update, delete, and query the map information of SuiteCRM
MapsAddressCache Create, update, delete, and query information on the Map Address saved in the server cache
MapsAreas Create, update, delete, and query maps via Google Maps.
MapsMarkers Create, update, delete, and query Google Maps Map Markers saved in SuiteCRM
Meetings Create, update, delete, and query Meetings created in SuiteCRM
Notes Create, update, delete, and query Notes created in SuiteCRM
Opportunities Create, update, delete, and query opportunities saved in SuiteCRM
PDFTemplates Create, update, delete, and query PDFTemplates table.
Products Create, update, delete, and query the products registered for the SuiteCRM project
ProductsCategories Create, update, delete, and query the product categories.
Projects Create, update, delete, and query projects registered in SuiteCRM
ProjectsTemplates Create, update, delete, and query any saved project template.
Quotes Create, update, delete, and query quotes saved in SuiteCRM
Reports Create, update, delete, and query information on reports made in SuiteCRM.
Spots Create, update, delete, and query the saved spots.
Surveys Create, update, delete, and query the surveys saved in the SuiteCRM
Tasks Create, update, delete, and query Tasks created in SuiteCRM
WorkFlow Create, update, delete, and query the wokflow actions in SuiteCRM
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 = 'test'

Insert

Create a SuiteCRM Account by specifying any writable column.

INSERT INTO Accounts (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Accounts SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Remove an Account by specifying the Account's Id.

DELETE FROM Accounts WHERE ID = 'account21'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the account.
Name String False The name of the account.
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False The full text of the alert.
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True The user name of the user assigned to the record.
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
AccountType String False The type of the account.
Industry String False The industry that the account belongs in.
AnnualRevenue String False Annual revenue for this account.
PhoneFax String False The fax phone number of this account.
BillingAddressStreet String False The first line of the billing address.
BillingAddressStreet2 String False The second line of the billing address.
BillingAddressStreet3 String False The third line of the billing address.
BillingAddressStreet4 String False The fourth line of the billing address.
BillingAddressCity String False The city used for the billing address.
BillingAddressState String False The state used for the billing address.
BillingAddressPostalcode String False The postal code used for the billing address.
BillingAddressCountry String False The country used for the billing address.
Rating String False An arbitrary rating for this account for use in comparisons with others.
PhoneOffice String False The office phone number.
PhoneAlternate String False An alternate phone number.
Website String False The URL of the website for the account.
Ownership String False The ownership of the account.
Employees String False Number of employees. Can be a number (100) or range (50-100)
TickerSymbol String False The stock trading (ticker) symbol for the account.
ShippingAddressStreet String False The first line of the shipping address.
ShippingAddressStreet2 String False The second line of the shipping address.
ShippingAddressStreet3 String False The third line of the shipping address.
ShippingAddressStreet4 String False The fourth line of the shipping address.
ShippingAddressCity String False The city used for the shipping address.
ShippingAddressState String False The state used for the shipping address.
ShippingAddressPostalcode String False The ZIP code used for the shipping address.
ShippingAddressCountry String False The country used for the shipping address.
Email1 String False Email address.
EmailAddressesPrimary String True Primary email address.
EmailAddresses String True Alternate Email address.
EmailAddressesNonPrimary String False Non primary email address.
ParentId String False The ID of the parent of this account.
SicCode String False SIC code of the account.
ParentName String True The name of the parent of this account.
Members String True
MemberOf String True
EmailOptOut Bool False Whether the account has opted out of emails.
InvalidEmail Bool False Whether the email address of the account has been marked as invalid.
Cases String True The cases associated with the record.
Email String False Email address
Tasks String True The tasks associated with the record.
Notes String True The notes associated with the record.
Meetings String True The meetings associated with the record.
Calls String True The calls associated with the record
Emails String True The emails associated with the record
Documents String True The documents associated with the record.
Bugs String True The bugs associated with the record.
Contacts String True The contacts associated with the record
Opportunities String True The opportunities associated with the record.
Project String True The project associated with the record.
Leads String True The leads associated with the record.
Campaigns String True The campaigns associated with the account.
CampaignAccounts String True
CampaignId String False The ID of the campaign associated with the record.
CampaignName String True The name of the campaign associated with the record.
ProspectLists String True
AosQuotes String True
AosInvoices String True
AosContracts String True
Calls

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

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 Name = 'test'

Insert

Create a Call by specifying any writable column.

INSERT INTO Calls (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Calls SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Delete a Call by specifying the Id.

DELETE FROM Calls WHERE ID = '10003'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the call.
Name String False A brief description of the call.
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False The full text of the note.
Deleted Bool False Record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
DurationHours String False The hours portion of the call duration.
DurationMinutes String False The minutes portion of the call duration.
DateStart Datetime False
DateEnd Datetime False
ParentType String False The type of the parent Sugar object related to the call.
ParentName String True The name of the parent of this account.
Status String False The status of the record.
Direction String False Indicates whether call is inbound or outbound.
ParentId String False The ID of the parent of this account.
ReminderChecked Bool False The checkbox indicating whether or not the reminder value is set.
ReminderTime String False When a reminder alert should be issued. -1 means no alert; otherwise, the number of seconds prior to the start.
EmailReminderChecked Bool False
EmailReminderTime String False When a email reminder alert should be issued. -1 means no alert; otherwise, the number of seconds prior to the start.
EmailReminderSent Bool False Whether the email reminder is already sent.
Reminders String False List of reminders for the call
OutlookId String False When the Suite Plug-in for Microsoft Outlook syncs an Outlook appointment, this is the Outlook appointment item Id.
AcceptStatus String False
SetAcceptLinks String False
ContactName String False
Opportunities String True The opportunities associated with the record.
Leads String True The leads associated with the record.
Project String True The project associated with the record.
Case String True
Accounts String True The accounts associated with the record
Contacts String True The contacts associated with the record
AosContracts String True
Users String True
Notes String True The notes associated with the record.
ContactId String False The ID of the contact for the call.
RepeatType String False The type of recurrence.
RepeatInterval String False The interval of recurrence.
RepeatDow String False The days of week in recurrence.
RepeatUntil String False Repeat until the specified date.
RepeatCount String False Number of recurrences.
RepeatParentId String False The ID of the first element of recurring records.
RecurringSource String False The recurring source requesting the call
RescheduleHistory String False
RescheduleCount String False
CallsReschedule String True
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 Name = 'campaign name'

Insert

Create a Campaign by specifying any writable column.

INSERT INTO Campaigns (Id, Name) VALUES ('id', 'Energy')

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
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the campaign.
Name String False The name of the campaign.
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False The description for the campaign.
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True The user name of the user assigned to the record.
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
TrackerKey String False
TrackerCount String False The number of accesses made to the tracker URL; no longer used as of 4.2. (See CampaignTrackers.)
ReferUrl String False
TrackerText String False
StartDate String False Starting date of the campaign.
EndDate String False Ending date of the campaign.
Status String False The status of the record.
Impressions String False Expected click throughs manually entered by the campaign manager.
CurrencyId String False
Budget String False Budgeted amount for the campaign.
ExpectedCost String False Expected cost of the campaign.
ActualCost String False Actual cost of the campaign.
ExpectedRevenue String False Expected revenue stemming from the campaign.
CampaignType String False
Objective String False The objective of the campaign.
Content String False The campaign description.
Prospectlists String True
Emailmarketing String True
Queueitems String True
LogEntries String True
TrackedUrls String True
Frequency String False Frequency of the campaign.
Leads String True The leads associated with the record.
Opportunities String True The opportunities associated with the record.
Contacts String True The contacts associated with the record
Accounts String True The accounts associated with the record
Survey String True
SurveyName String True
SurveyId String False
SurveyresponsesCampaigns String True
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 Name = 'test'

Insert

Create a Case by specifying any writable column.

INSERT INTO Cases (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Cases SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Delete a Case by specifying the Id.

DELETE FROM Cases WHERE ID = 'account21'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier for the record.
Name String False
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False The full text of the note.
Deleted Bool False Record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The user ID assigned to the record.
AssignedUserName String True The name of the user assigned to the record.
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
CaseNumber String False
Type String False The type of case.
Status String False The status of the record.
Priority String False The priority of the record.
Resolution String False The resolution of the case.
WorkLog String False Free-form text used to denote activities of interest.
SuggestionBox String False
AccountName String False The name of the associated account.
AccountName1 String False A second account for the case
AccountId String False The ID of the associated account.
State String False State the case is left in
CaseAttachmentsDisplay String False
CaseUpdateForm String False
ContactCreatedBy String True
ContactCreatedByName String True
ContactCreatedById String False
Tasks String True The tasks associated with the record.
Notes String True The notes associated with the record.
Meetings String True The meetings associated with the record.
Emails String True The emails associated with the record
Documents String True The documents associated with the record.
Calls String True The calls associated with the record
Bugs String True The bugs associated with the record.
Contacts String True The contacts associated with the record
Accounts String True The accounts associated with the record
Project String True The project associated with the record.
UpdateText String False Text associated with an update on the case.
Internal Bool False
AopCaseUpdatesThreaded String False
AopCaseUpdates String True
AopCaseEvents String True
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 Name = 'test'

Insert

Create a Contact by specifying any writable column.

INSERT INTO Contacts (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Contacts SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Delete a Contact by specifying the Id.

DELETE FROM Contacts WHERE ID = '2345345'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier for the record.
Name String False The name of the contact.
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False The full text of the note.
Deleted Bool False Record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
Salutation String False The contact salutation (e.g., Mr. or Ms.).
FirstName String False The first name of the contact.
LastName String False The last name of the contact.
FullName String False The full name of hte contact.
Title String False The title of the contact.
Photo String False The avatar for the contact.
Department String False The department of the contact.
DoNotCall Bool False An indicator of whether the contact can be called.
PhoneHome String False
Email String False The email and name of the contact.
PhoneMobile String False
PhoneWork String False
PhoneOther String False
PhoneFax String False
Email1 String False Email address.
Email2 String False
InvalidEmail Bool False Whether the email address of the account has been marked as invalid.
EmailOptOut Bool False Whether the contact has opted out of emails.
LawfulBasis String False
DateReviewed String False
LawfulBasisSource String False
PrimaryAddressStreet String False The street address for the alternate address.
PrimaryAddressStreet2 String False The second line of the alternate address.
PrimaryAddressStreet3 String False The third line of the alternate address.
PrimaryAddressCity String False The city for the primary address.
PrimaryAddressState String False The state for the primary address.
PrimaryAddressPostalcode String False The postal code for the primary address.
PrimaryAddressCountry String False The country for primary address.
AltAddressStreet String False
AltAddressStreet2 String False
AltAddressStreet3 String False
AltAddressCity String False
AltAddressState String False
AltAddressPostalcode String False
AltAddressCountry String False
Assistant String False The name of the assistant of the contact.
AssistantPhone String False The phone number of the assistant of the contact.
EmailAddressesPrimary String True Primary email address.
EmailAddresses String True Alternate Email address.
EmailAddressesNonPrimary String False Non primary email address.
EmailAndName1 String False
LeadSource String False The lead source for the contact.
AccountName String False The name of the account associated with the contact.
AccountId String False The ID of the account associated with the contact.
OpportunityRoleFields String True The opportunity role fields.
OpportunityRoleId String False The ID of the opportunity role.
OpportunityRole String False The opportunity role.
ReportsToId String False The ID of the contact this contact reports to.
ReportToName String False
Birthdate String False The birthdate of the contact.
Accounts String True The accounts associated with the record
ReportsToLink String True
Opportunities String True The opportunities associated with the record.
Bugs String True The bugs associated with the record.
Calls String True The calls associated with the record
Cases String True The cases associated with the record.
DirectReports String True
Emails String True The emails associated with the record
Documents String True The documents associated with the record.
Leads String True The leads associated with the record.
Meetings String True The meetings associated with the record.
Notes String True The notes associated with the record.
Project String True The project associated with the record.
ProjectResource String True
AmProjecttemplatesResources String True
AmProjecttemplatesContacts1 String True
Tasks String True The tasks associated with the record.
TasksParent String True
NotesParent String True
UserSync String True
CampaignId String False The ID of the campaign associated with the record.
CampaignName String True The name of the campaign associated with the record.
Campaigns String True The campaigns associated with the account.
CampaignContacts String True
CAcceptStatusFields String True
MAcceptStatusFields String True
AcceptStatusId String False The ID of the accept status.
AcceptStatusName String False The name of the accept status.
ProspectLists String True
SyncContact Bool False
FpEventsContacts String True
AosQuotes String True
AosInvoices String True
AosContracts String True
EInviteStatusFields String True
EventStatusName String False
EventInviteId String False
EAcceptStatusFields String True
EventAcceptStatus String False
EventStatusId String False
ProjectContacts1 String True
AopCaseUpdates String True
JoomlaAccountId String False Id of the contact's Joomla account.
PortalAccountDisabled Bool False
JoomlaAccountAccess String False
PortalUserType String False Type of the contact's portal account.
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 Name = 'test'

Insert

Create a Contract by specifying any writable column.

INSERT INTO Contracts (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Contracts SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Delete a Contract by specifying the Id.

DELETE FROM Contracts WHERE ID = 10003
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the contract.
Name String False
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Description for the contract
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the contract manager.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
ReferenceCode String False Reference code of the contract
StartDate String False Date the contract is valid from
EndDate String False Date until the contract expires
TotalContractValue String False
TotalContractValueUsdollar String False
CurrencyId String False
Status String False The status of the record.
CustomerSignedDate String False Date the customer signed the contract
CompanySignedDate String False Date the company signed the contract
RenewalReminderDate Datetime False Date to remind for the contract renewal
ContractType String False Type of the contract
ContractAccountId String False Id of the Account linked to the contract
ContractAccount String True
OpportunityId String False Id of the opportunity linked to the contract
Opportunity String True Name of the opportunity linked to the contract
ContactId String False Id of the contact linked to the account
Contact String True Name of the contact linked to the account
CallId String False Id of the call linked to the contract
LineItems String False Line items of the contract
TotalAmt String False
TotalAmtUsdollar String False
SubtotalAmount String False
SubtotalAmountUsdollar String False
DiscountAmount String False
DiscountAmountUsdollar String False
TaxAmount String False
TaxAmountUsdollar String False
ShippingAmount String False
ShippingAmountUsdollar String False
ShippingTax String False Shipping tax costs
ShippingTaxAmt String False
ShippingTaxAmtUsdollar String False
TotalAmount String False
TotalAmountUsdollar String False
Accounts String True The accounts associated with the record
Contacts String True The contacts associated with the record
Tasks String True The tasks associated with the record.
Notes String True The notes associated with the record.
Meetings String True The meetings associated with the record.
Calls String True The calls associated with the record
Emails String True The emails associated with the record
AosQuotesAosContracts String True
Documents String True The documents associated with the record.
AosProductsQuotes String True
AosLineItemGroups String True
Documents

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

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 Name = 'test'

Insert

Create a Document by specifying any writable column.

INSERT INTO Documents (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Documents SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Delete a Document by specifying the Id.

DELETE FROM Documents WHERE ID = '10003'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier for the record.
Name String False The name of the document.
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False The full text of the note.
Deleted Bool False Record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
DocumentName String False The document name.
DocId String False
DocType String False
DocUrl String False
Filename String False The file name of the document attachment.
ActiveDate String False
ExpDate String False
CategoryId String False
SubcategoryId String False
StatusId String False The ID of the document status.
Status String False The status of the record.
DocumentRevisionId String False The ID of the document revision.
Revisions String True
Revision String False The revision number.
LastRevCreatedName String False
LastRevMimeType String False
LatestRevision String False The latest revision.
LastRevCreateDate String False
Contracts String True
Leads String True The leads associated with the record.
Accounts String True The accounts associated with the record
Contacts String True The contacts associated with the record
Opportunities String True The opportunities associated with the record.
Cases String True The cases associated with the record.
Bugs String True The bugs associated with the record.
RelatedDocId String False
RelatedDocName String True
RelatedDocRevId String False
RelatedDocRevNumber String False
IsTemplate Bool False
TemplateType String False
LatestRevisionName String False
SelectedRevisionName String False The name of the selected revision.
ContractStatus String False The document's contract status.
ContractName String False The name of the document's contract
LinkedId String False The ID of the linked record.
SelectedRevisionId String False The ID of the selected revision.
LatestRevisionId String False The ID of the latest revision.
SelectedRevisionFilename String False The filename of the selected revision.
AosContracts String True
Emails

Create, update, delete, and query the SuiteCRM 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 Name = 'test'

Insert

Create an Email by specifying any writable column.

INSERT INTO Emails (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Emails SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Delete an Email by specifying the Id.

DELETE FROM Emails WHERE ID = '10003'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the email.
Name String False The subject of the email.
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False The description for the email.
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
Orphaned Bool False
LastSynced Datetime False
FromAddrName String False The from address in the email.
ReplyToAddr String False The reply-to address in the email.
ToAddrsNames String False The to addresses in the email.
CcAddrsNames String False The cc addresses in the email.
BccAddrsNames String False The bcc addresses in the email.
ImapKeywords String False
RawSource String False The raw source for the email.
DescriptionHtml String False The HTML description for the email.
DateSentReceived Datetime False
MessageId String False The ID of the email item obtained from the email transport system.
Type String False The type of the email (e.g., draft).
Status String False The status of the record.
Flagged Bool False The flagged status of the email.
ReplyToStatus Bool False The reply-to status of the email. If you reply to an email then the reply-to status of original email is set.
Intent String False The target of the action used in the Inbound Email assignment.
MailboxId String False
ParentName String True The name of the parent of this account.
ParentType String False The type of the Sugar module associated with the email. (Deprecated as of 4.2.)
ParentId String False The ID of the parent of this account.
Indicator String False
Subject String False
Attachment String False
Uid String False
Msgno String False
Folder String False
FolderType String False
InboundEmailRecord String False
IsImported String False
HasAttachment String False
IsOnlyPlainText Bool False
Accounts String True The accounts associated with the record
Bugs String True The bugs associated with the record.
Cases String True The cases associated with the record.
Contacts String True The contacts associated with the record
Leads String True The leads associated with the record.
Opportunities String True The opportunities associated with the record.
Project String True The project associated with the record.
Projecttask String True
Prospects String True
AosContracts String True
Tasks String True The tasks associated with the record.
Users String True
Notes String True The notes associated with the record.
Meetings String True The meetings associated with the record.
CategoryId String False
EmailsEmailTemplates String True
EmailsEmailTemplatesName String True
EmailsEmailTemplatesIdb String True
OptIn String False
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 Name = 'test'

Insert

Create an Email Template by specifying any writable column.

INSERT INTO [Email Templates] (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE [Email Templates] SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Delete an Email Template by specifying the Id.

DELETE FROM [Email Templates] WHERE ID = '10003'
Columns
Name Type ReadOnly Description
SecurityGroups String True The security group associated with the record.
Id [KEY] String False The unique identifier of the email template.
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
Published String False The published status of the record.
Name String False The name of the email template.
Description String False The description for the email template.
Subject String False The subject of the resulting email.
Body String False Plaintext body of the resulting email.
BodyHtml String False
Deleted Bool False The record deletion indicator.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True The user name of the user assigned to the record.
AssignedUserLink String True Link to the user which the record has been assigned to
TextOnly Bool False Whether the email template is to be sent in text only.
Type String False The type of the email template.
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 WHERE Name = 'test'

Insert

Create an Event entry by specifying any writable column.

INSERT INTO Events (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Events SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Delete an Event by specifying the Id.

DELETE FROM Events WHERE ID = '10003'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the event.
Name String False Name given to the event
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Description given for the event
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
DurationHours String False Number of hours the event lasts
DurationMinutes String False Number of minutes the event lasts
DateStart Datetime False
DateEnd Datetime False
Link String False
LinkDeclined String False
Budget String False Budget set for the event
CurrencyId String False
Duration String False Complete duration of the event
InviteTemplates String False
AcceptRedirect String False
DeclineRedirect String False
FpEventsContacts String True
FpEventsProspects1 String True
FpEventsLeads1 String True
FpEventLocationsFpEvents1 String True
FpEventLocationsFpEvents1Name String True
FpEventLocationsFpEvents1fpEventLocationsIda String True
ActivityStatusType String False
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 Name = 'test'

Insert

Create an Invoice by specifying any writable column.

INSERT INTO Invoices (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Invoices SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Delete an Invoice by specifying the Id.

DELETE FROM Invoices WHERE ID = '10003'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the invoice.
Name String False
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Description provided for the
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
BillingAccountId String False Id of the billing account
BillingAccount String True
BillingContactId String False Id of the billing contact
BillingContact String True
BillingAddressStreet String False The first line of the billing address.
BillingAddressCity String False The city used for the billing address.
BillingAddressState String False The state used for the billing address.
BillingAddressPostalcode String False The postal code used for the billing address.
BillingAddressCountry String False The country used for the billing address.
ShippingAddressStreet String False The first line of the shipping address.
ShippingAddressCity String False The city used for the shipping address.
ShippingAddressState String False The state used for the shipping address.
ShippingAddressPostalcode String False The ZIP code used for the shipping address.
ShippingAddressCountry String False The country used for the shipping address.
Number String False
LineItems String False The invoice's list of line items
TotalAmt String False
TotalAmtUsdollar String False
SubtotalAmount String False
SubtotalAmountUsdollar String False
DiscountAmount String False
DiscountAmountUsdollar String False
TaxAmount String False
TaxAmountUsdollar String False
ShippingAmount String False
ShippingAmountUsdollar String False
ShippingTax String False Shipping tax costs
ShippingTaxAmt String False
ShippingTaxAmtUsdollar String False
TotalAmount String False
TotalAmountUsdollar String False
CurrencyId String False
QuoteNumber String False Number of the invoice quote
QuoteDate String False Date of the invoice quote
InvoiceDate String False Date the invoice was issued
DueDate String False Due date of the invoice
Status String False The status of the record.
TemplateDdownC String False
SubtotalTaxAmount String False Subtotal and tax amount in the invoice in the system's default currency
SubtotalTaxAmountUsdollar String False
Accounts String True The accounts associated with the record
Contacts String True The contacts associated with the record
AosQuotesAosInvoices String True
AosProductsQuotes String True
AosLineItemGroups String True
KBCategories

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

Columns
Name Type ReadOnly Description
Id [KEY] String False
Name String False
DateEntered Datetime False The date the record was created.
DateModified Datetime False
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False
Deleted Bool False
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
AokKnowledgebaseCategories String True
KnowledgeBase

Create, update, delete, and query the Knowledge Base saved in the SuiteCRM

Columns
Name Type ReadOnly Description
Id [KEY] String False
Name String False
DateEntered Datetime False The date the record was created.
DateModified Datetime False
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False
Deleted Bool False
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
Status String False The status of the record.
Revision String False
AdditionalInfo String False
UserIdC String False
Author String True
UserId1C String False
Approver String True
AokKnowledgebaseCategories String True
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 Name = 'test'

Insert

Create a Lead by specifying any writable column.

INSERT INTO Leads (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Leads SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Remove a Lead by specifying the Id.

DELETE FROM Leads WHERE ID = 10003
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the record.
Name String False The name of the lead.
DateEntered Datetime False The date the record was created.
DateModified Datetime False Date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Full text of the note.
Deleted Bool False Record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
Salutation String False The lead salutation (e.g., Mr. or Ms.).
FirstName String False The first name of the lead.
LastName String False The last name of the lead.
FullName String False The full name of the lead.
Title String False The title of the lead.
Photo String False The picture for the lead.
Department String False The department of the lead.
DoNotCall Bool False An indicator of whether the lead can be called.
PhoneHome String False
Email String False
PhoneMobile String False
PhoneWork String False
PhoneOther String False
PhoneFax String False
Email1 String False Email address.
Email2 String False
InvalidEmail Bool False Whether the email address of the account has been marked as invalid.
EmailOptOut Bool False Whether the lead has opted out of radio.
LawfulBasis String False
DateReviewed String False
LawfulBasisSource String False
PrimaryAddressStreet String False The street address used for the primary address.
PrimaryAddressStreet2 String False The second line of the primary address.
PrimaryAddressStreet3 String False The third line of the primary addrss.
PrimaryAddressCity String False The city for the primary address.
PrimaryAddressState String False The state for the primary address.
PrimaryAddressPostalcode String False The postal code for the primary address.
PrimaryAddressCountry String False The country for the primary address.
AltAddressStreet String False The street address for the alternate address.
AltAddressStreet2 String False The second line of the alternate address.
AltAddressStreet3 String False The third line of the alternate address.
AltAddressCity String False The city for the alternate address.
AltAddressState String False The state for the alternate address.
AltAddressPostalcode String False The postal code for the alternate address.
AltAddressCountry String False The country for the alternate address.
Assistant String False The name of the assistant of the lead.
AssistantPhone String False The phone number of the assistant of the lead.
EmailAddressesPrimary String True Primary email address.
EmailAddresses String True Alternate Email address.
EmailAddressesNonPrimary String False Non primary email address.
Converted Bool False Whether the lead has been converted.
ReferedBy String False
LeadSource String False How the lead came to be known.
LeadSourceDescription String False Description of the lead source.
Status String False The status of the record.
StatusDescription String False A description for the status
ReportsToId String False The name the updates for the lead will be reported to
ReportToName String False
ReportsToLink String True
Reportees String True
Contacts String True The contacts associated with the record
AccountName String False The name of the associated acocunt.
Accounts String True The accounts associated with the record
AccountDescription String False Description for the lead's account
ContactId String False Main contact for the lead
Contact String True
AccountId String False Account the lead is linked to
OpportunityId String False The Opportunity the lead was generated from
Opportunity String True
OpportunityName String False The name of the opportunity the lead was generated from
OpportunityAmount String False The amount the opportunity yields
CampaignId String False The ID of the campaign associated with the record.
CampaignName String True The name of the campaign associated with the record.
CampaignLeads String True
CAcceptStatusFields String True
MAcceptStatusFields String True
AcceptStatusId String False The ID of the accept status.
AcceptStatusName String False The name of the accept status.
WebtoleadEmail1 String False
WebtoleadEmail2 String False
WebtoleadEmailOptOut Bool False
WebtoleadInvalidEmail Bool False
Birthdate String False The birthdate of the lead.
PortalName String False The Joomla portal name of the lead
PortalApp String False
Website String False The lead's website
Tasks String True The tasks associated with the record.
Notes String True The notes associated with the record.
Meetings String True The meetings associated with the record.
Calls String True The calls associated with the record
Oldmeetings String True
Oldcalls String True
Emails String True The emails associated with the record
Campaigns String True The campaigns associated with the account.
ProspectLists String True
FpEventsLeads1 String True
EInviteStatusFields String True
EventStatusName String False
EventInviteId String False
EAcceptStatusFields String True
EventAcceptStatus String False
EventStatusId String False Id of the lead's event status.
Locations

Create, update, delete, and query locations in SuiteCRM

Table Specific Information

Select

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

SELECT * FROM Locations WHERE Name = 'test'

Insert

Create a Location by specifying any writable column.

INSERT INTO Locations (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Locations SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Remove a Location by specifying the Id.

DELETE FROM Locations WHERE ID = 10003
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the location.
Name String False Name assigned to the location
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Description for the location
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
FpEventLocationsFpEvents1 String True
Address String False Street address of the location
AddressCity String False
AddressCountry String False
AddressPostalcode String False
AddressState String False
Capacity String False Capacity estimated for the location
Maps

Create, update, delete, and query the map information of SuiteCRM

Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the map.
Name String False Name assigned to the map
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Description for the map
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
Distance Double False
UnitType String False Length unit expressing the radius
ModuleType String False
ParentName String True The name of the parent of this account.
ParentType String False Type of the map's parent
ParentId String False The ID of the parent of this account.
Accounts String True The accounts associated with the record
Contacts String True The contacts associated with the record
Leads String True The leads associated with the record.
Opportunities String True The opportunities associated with the record.
Cases String True The cases associated with the record.
Projects String True
Meetings String True The meetings associated with the record.
Prospects String True
MapsAddressCache

Create, update, delete, and query information on the Map Address saved in the server cache

Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the address cache.
Name String False
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Description for the cached address
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
Lat Double False
Lng Double False
MapsAreas

Create, update, delete, and query maps via Google Maps.

Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the map area.
Name String False Name assigned to the line items group
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Description for the map area
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
City String False City of the map area
State String False State of the location
Country String False Country of the map area
Coordinates String False Geographical coordinates of the map area
JjwgMapsJjwgAreas String True
MapsMarkers

Create, update, delete, and query Google Maps Map Markers saved in SuiteCRM

Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the map marker.
Name String False Name assigned to the map marker
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Description for the map marker
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
City String False City of the map marker
State String False State of the map marker
Country String False Country of the map marker
JjwgMapsLat Double False
JjwgMapsLng Double False
MarkerImage String False
JjwgMapsJjwgMarkers String True
Meetings

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

Table Specific Information

Select

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

SELECT * FROM Meetings WHERE Name = 'test'

Insert

Create a Meeting by specifying any writable column.

INSERT INTO Meetings (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Meetings SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Remove a Meeting by specifying the Id.

DELETE FROM Meetings WHERE ID = 10003
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier for the meeting.
Name String False
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Full text of the note.
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
AcceptStatus String False
SetAcceptLinks String False
Location String False The location of the meeting.
Password String False
JoinUrl String False
HostUrl String False The host URL.
DisplayedUrl String False
Creator String False
ExternalId String False
DurationHours String False The duration (hours).
DurationMinutes String False The duration (minutes).
DateStart Datetime False
DateEnd Datetime False
ParentType String False Module the meeting is associated with.
Status String False The status of the record.
Type String False Meeting type (e.g., WebEx, or Other).
Direction String False Whether the meeting is inbound or outbound.
ParentId String False The ID of the parent of this account.
ReminderChecked Bool False Whether or not the reminder value is set.
ReminderTime String False Specifies when a reminder alert should be issued: -1 means no alert; otherwise the number of seconds prior to the start.
EmailReminderChecked Bool False
EmailReminderTime String False Specifies when a email reminder alert should be issued: -1 means no alert; otherwise the number of seconds prior to the start.
EmailReminderSent Bool False Whether the email reminder is already sent.
Reminders String False List of reminders set for the meetings
OutlookId String False When the Sugar Plug-in for Microsoft Outlook syncs an Outlook appointment, this is the Outlook appointment item Id.
Sequence String False
ContactName String False
Contacts String True The contacts associated with the record
ParentName String True The name of the parent of this account.
Users String True
Accounts String True The accounts associated with the record
Leads String True The leads associated with the record.
Opportunity String True
Case String True
AosContracts String True
Notes String True The notes associated with the record.
ContactId String False The ID of the associated contact.
RepeatType String False Type of a recurring meeting.
RepeatInterval String False The interval of a recurring meeting.
RepeatDow String False The day of week of a meeting.
RepeatUntil String False Repeat until the specified date.
RepeatCount String False Number of recurrences.
RepeatParentId String False Id of the first element of recurring records.
RecurringSource String False Source of recurring meeting.
Duration String False Duration handler dropdown.
GsyncId String False
GsyncLastsync String False
JjwgMapsLatC Double False
JjwgMapsLngC Double False
JjwgMapsGeocodeStatusC String False
JjwgMapsAddressC String False
Notes

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

Table Specific Information

Select

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

SELECT * FROM Notes WHERE Subject WHERE Name = 'test'

Insert

Create a Note by specifying any writable column.

INSERT INTO Notes (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Notes SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Remove a Note by specifying the Id.

DELETE FROM Notes WHERE ID = 10003
Columns
Name Type ReadOnly Description
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
Id [KEY] String False Unique identifier of the record.
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Name String False
FileMimeType String False
FileUrl String False
Filename String False
ParentType String False Sugar module the Note is associated with.
ParentId String False The ID of the parent of this account.
ContactId String False The ID of the contact the note is associated with.
PortalFlag Bool False
EmbedFlag Bool False
Description String False
Deleted Bool False The record deletion indicator.
ParentName String True The name of the parent of this account.
ContactName String True
ContactPhone String False
ContactEmail String False
AccountId String False The ID of the account associated with the note.
OpportunityId String False The ID of the opportunity associated with the note.
AcaseId String False
LeadId String False The ID of the lead associated with the note.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
Contact String True The name of the contact associated with the note.
Cases String True The cases associated with the record.
Accounts String True The accounts associated with the record
Opportunities String True The opportunities associated with the record.
Leads String True The leads associated with the record.
Bugs String True The bugs associated with the record.
AosContracts String True
Emails String True The emails associated with the record
Projects String True
ProjectTasks String True
Meetings String True The meetings associated with the record.
Calls String True The calls associated with the record
Tasks String True The tasks associated with the record.
Opportunities

Create, update, delete, and query opportunities saved in SuiteCRM

Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the opportunity.
Name String False
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Full text of the note.
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
OpportunityType String False
AccountName String False The name of the associated account.
AccountId String False The ID of the associated account.
CampaignId String False The ID of the campaign associated with the record.
CampaignName String True The name of the campaign associated with the record.
CampaignOpportunities String True
LeadSource String False Source of the opportunity.
Amount String False Formatted amount of the opportunity.
AmountUsdollar String False
CurrencyId String False
CurrencyName String True The name of the currency used for display purposes.
CurrencySymbol String True The symbol of the currency used for display purposes.
DateClosed String False
NextStep String False The next step in the sales process.
SalesStage String False Indication of progression towards closure.
Probability String False
Accounts String True The accounts associated with the record
Contacts String True The contacts associated with the record
Tasks String True The tasks associated with the record.
Notes String True The notes associated with the record.
Meetings String True The meetings associated with the record.
Calls String True The calls associated with the record
Emails String True The emails associated with the record
Documents String True The documents associated with the record.
Project String True The project associated with the record.
Leads String True The leads associated with the record.
Campaigns String True The campaigns associated with the account.
CampaignLink String True
Currencies String True
AosQuotes String True
AosContracts String True
JjwgMapsLatC Double False
JjwgMapsLngC Double False
JjwgMapsGeocodeStatusC String False
JjwgMapsAddressC String False
PDFTemplates

Create, update, delete, and query PDFTemplates table.

Table Specific Information

Select

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

SELECT * FROM [PDF Templates] WHERE Name = 'test'

Insert

Create a pdf template by specifying any writable column.

INSERT INTO [PDF Templates] (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE [PDF Templates] SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Remove a pdf template by specifying the Id.

DELETE FROM [PDF Templates] WHERE ID = '5fddceac-8715-d1f1-efa3-5b854ab921a6'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier for the pdf template.
Name String False The template's name.
DateEntered Datetime False The date the record was created.
DateModified Datetime False Date when the template was modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False
Deleted Bool False Whether the template was deleted.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The unique identifier of the assignee.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
Active Bool False Whether or not the template is active.
Type String False The type of the template.
Sample String False
InsertFields String False The fields that can be inserted.
Pdfheader String False
Pdffooter String False
MarginLeft String False Value of the left margin for the template.
MarginRight String False Value of the right margin for the template.
MarginTop String False Value of the top margin for the template.
MarginBottom String False Value of the bottom margin for the template.
MarginHeader String False Value of the margin for the template's header.
MarginFooter String False Value of the margin for the template's footer.
PageSize String False The size of the template.
Orientation String False Orientation of the template.
Products

Create, update, delete, and query the products registered for the SuiteCRM project

Table Specific Information

Select

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

SELECT * FROM Products WHERE Name = 'test'

Insert

Create a Product by specifying any writable column.

INSERT INTO Products (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Products SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Delete a Product by specifying the Id.

DELETE FROM Products WHERE ID = '10003'
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the ACL Action.
Name String False
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False The action description.
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
AosProductsPurchases String True
Maincode String False
PartNumber String False Part number of the product.
Category String False Name of the product's category.
Type String False Type of the product.
Cost String False Cost of the product.
CostUsdollar String False
CurrencyId String False
Price String False Price of the product.
PriceUsdollar String False
Url String False Url where the product can be seen.
ContactId String False Product's point of contact id.
Contact String True Product's point of contact name.
ProductImage String False Image uploaded for the product.
FileUrl String False
AosProductCategory String True
AosProductCategoryName String True
AosProductCategoryId String False
ProductsCategories

Create, update, delete, and query the product categories.

Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the inbound.
Name String False Name assigned to the category
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Description for the category
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
IsParent Bool False
AosProducts String True
SubCategories String True
ParentCategory String True Name of the parent category
ParentCategoryName String True
ParentCategoryId String False Id of the category's parent category
Projects

Create, update, delete, and query projects registered in SuiteCRM

Table Specific Information

Select

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

SELECT * FROM Projects WHERE Name = 'test'

Insert

Create a Project by specifying any writable column.

INSERT INTO Projects (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Projects SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Remove a Project by specifying the Id.

DELETE FROM Projects WHERE ID = 10003
Columns
Name Type ReadOnly Description
SecurityGroups String True The security group associated with the record.
Id [KEY] String False The unique identifier of the project.
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
AssignedUserId String False
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Name String False Project name.
Description String False Project description.
Deleted Bool False The record deletion indicator.
EstimatedStartDate String False
EstimatedEndDate String False
Status String False The status of the record.
Priority String False The priority of the record.
TotalEstimatedEffort String False
TotalActualEffort String False
Accounts String True The accounts associated with the record
Quotes String True
Contacts String True The contacts associated with the record
Opportunities String True The opportunities associated with the record.
Notes String True The notes associated with the record.
Tasks String True The tasks associated with the record.
Meetings String True The meetings associated with the record.
Calls String True The calls associated with the record
Emails String True The emails associated with the record
Projecttask String True
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserLink String True Link to the user which the record has been assigned to
AssignedUserName String False
Cases String True The cases associated with the record.
Bugs String True The bugs associated with the record.
Products String True
ProjectUsers1 String True
AmProjecttemplatesProject1 String True
AmProjecttemplatesProject1Name String True
AmProjecttemplatesProject1amProjecttemplatesIda String True
ProjectContacts1 String True
AosQuotesProject String True
OverrideBusinessHours Bool False
JjwgMapsLatC Double False
JjwgMapsLngC Double False
JjwgMapsGeocodeStatusC String False
JjwgMapsAddressC String False
ProjectsTemplates

Create, update, delete, and query any saved project template.

Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the project template.
Name String False
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user the template has been assigned to.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
Status String False The status of the record.
Priority String False The priority of the record.
AmProjecttemplatesProject1 String True
AmTasktemplatesAmProjecttemplates String True
AmProjecttemplatesUsers1 String True
AmProjecttemplatesContacts1 String True
OverrideBusinessHours Bool False
Quotes

Create, update, delete, and query quotes saved in SuiteCRM

Table Specific Information

Select

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

SELECT * FROM Quotes WHERE Name = 'test'

Insert

Create a Quote by specifying any writable column.

INSERT INTO Quotes (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Quotes SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Remove a Quote by specifying the Id.

DELETE FROM Quotes WHERE ID = 10003
Columns
Name Type ReadOnly Description
Id [KEY] String False null
Name String False
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Description for the quote
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False null
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
ApprovalIssue String False
BillingAccountId String False Id of the billing account
BillingAccount String True
BillingContactId String False Id of the billing contact
BillingContact String True
BillingAddressStreet String False The first line of the billing address.
BillingAddressCity String False The city used for the billing address.
BillingAddressState String False The state used for the billing address.
BillingAddressPostalcode String False The postal code used for the billing address.
BillingAddressCountry String False The country used for the billing address.
ShippingAddressStreet String False The first line of the shipping address.
ShippingAddressCity String False The city used for the shipping address.
ShippingAddressState String False The state used for the shipping address.
ShippingAddressPostalcode String False The ZIP code used for the shipping address.
ShippingAddressCountry String False The country used for the shipping address.
Expiration String False
Number String False
OpportunityId String False Opportunity ID of the quote
Opportunity String True Opportunity name of the quote
TemplateDdownC String False
LineItems String False The list of the quote's line items
TotalAmt String False
TotalAmtUsdollar String False
SubtotalAmount String False
SubtotalAmountUsdollar String False
DiscountAmount String False
DiscountAmountUsdollar String False
TaxAmount String False
TaxAmountUsdollar String False
ShippingAmount String False
ShippingAmountUsdollar String False
ShippingTax String False The quote's shipping tax costs
ShippingTaxAmt String False
ShippingTaxAmtUsdollar String False
TotalAmount String False
TotalAmountUsdollar String False
CurrencyId String False
Stage String False
Term String False
TermsC String False
ApprovalStatus String False null
InvoiceStatus String False Quote's invoice status
SubtotalTaxAmount String False The quotes's subtotal and tax amount in the system's default currency
SubtotalTaxAmountUsdollar String False
Accounts String True The accounts associated with the record
Contacts String True The contacts associated with the record
Opportunities String True The opportunities associated with the record.
AosQuotesProject String True
AosQuotesAosInvoices String True
AosQuotesAosContracts String True
AosProductsQuotes String True
AosLineItemGroups String True
Reports

Create, update, delete, and query information on reports made in SuiteCRM.

Table Specific Information

Select

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

SELECT * FROM Reports WHERE Name = 'test'

Insert

Create a Report by specifying any writable column.

INSERT INTO Reports (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Reports SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Remove a Report by specifying the Id.

DELETE FROM Reports WHERE ID = 10003
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the report.
Name String False Name assigned to the report
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Description for the report
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
ReportModule String False The module the report has targeted
GraphsPerRow String False
FieldLines String False
ConditionLines String False
AorFields String True
AorConditions String True
AorCharts String True
AorScheduledReports String True
Spots

Create, update, delete, and query the saved spots.

Table Specific Information

Select

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

SELECT * FROM Spots WHERE Name = 'test'

Insert

Create a Spot by specifying any writable column.

INSERT INTO Spots (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Spots SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Remove a Spot by specifying the Id.

DELETE FROM Spots WHERE ID = 10003
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the record.
Name String False The name of the spot.
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Full text of the note.
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
Config String False The the configuration set of the spot.
Type String False The field this spot is targeting.
ConfigurationGUI String False
Surveys

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

Columns
Name Type ReadOnly Description
Id [KEY] String False
Name String False
DateEntered Datetime False The date the record was created.
DateModified Datetime False
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False
Deleted Bool False
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
Status String False The status of the record.
SurveyQuestionsDisplay String False
SurveyUrlDisplay String False
SubmitText String False
SatisfiedText String False
NeitherText String False
DissatisfiedText String False
SurveysSurveyquestions String True
SurveysSurveyresponses String True
Tasks

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

Table Specific Information

Select

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

SELECT * FROM Tasks WHERE Name = 'test'

Insert

Create a Task by specifying any writable column.

INSERT INTO Tasks (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Tasks SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Remove a Task by specifying the Id.

DELETE FROM Tasks WHERE ID = 10003
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the record.
Name String False
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False The full text of the note.
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
Status String False The status of the record.
DateDueFlag Bool False
DateDue Datetime False
TimeDue Datetime False
DateStartFlag Bool False
DateStart Datetime False
ParentType String False The type of the Sugar item to which the call is related.
ParentName String True The name of the parent of this account.
ParentId String False The ID of the parent of this account.
ContactId String False The ID of the associated contact.
ContactName String True The name of the associated contact.
ContactPhone String False The phone of the associated contact.
ContactEmail String False
Priority String False The priority of the record.
Contacts String True The contacts associated with the record
Accounts String True The accounts associated with the record
Opportunities String True The opportunities associated with the record.
Cases String True The cases associated with the record.
Bugs String True The bugs associated with the record.
Leads String True The leads associated with the record.
Projects String True
ProjectTasks String True
AosContracts String True
Notes String True The notes associated with the record.
ContactParent String True
WorkFlow

Create, update, delete, and query the wokflow actions in SuiteCRM

Table Specific Information

Select

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

SELECT * FROM Workflow WHERE Name = 'test'

Insert

Create a Workflow by specifying any writable column.

INSERT INTO Workflow (Id, Name) VALUES ('id', 'Energy')

Update

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

UPDATE Workflow SET Name = 'new name', Description = 'Desc' WHERE ID = 'Test123'

Delete

Remove a Workflow by specifying the Id.

DELETE FROM Workflow WHERE ID = 10003
Columns
Name Type ReadOnly Description
Id [KEY] String False The unique identifier of the inbound.
Name String False Name assigned to the workflow
DateEntered Datetime False The date the record was created.
DateModified Datetime False The date the record was last modified.
ModifiedUserId String False The ID of the user who last modified the record.
ModifiedByName String True The name of the user who last modified the record.
CreatedBy String False The ID of the user who created the record.
CreatedByName String True The name of the user who created the record.
Description String False Description for the worflow
Deleted Bool False The record deletion indicator.
CreatedByLink String True Link to the record who created it
ModifiedUserLink String True Link to the record who modified it.
AssignedUserId String False The ID of the user assigned to the record.
AssignedUserName String True
AssignedUserLink String True Link to the user which the record has been assigned to
SecurityGroups String True The security group associated with the record.
FlowModule String False
FlowRunOn String False
Status String False The status of the record.
RunWhen String False
MultipleRuns Bool False
ConditionLines String False
ActionLines String False
AowConditions String True
AowActions String True
AowProcessed String True

Stored Procedures

Stored procedures are function-like interfaces that extend the functionality of the connector beyond simple SELECT/INSERT/UPDATE/DELETE operations with SuiteCRM.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from SuiteCRM, along with an indication of whether the procedure succeeded or failed.

SuiteCRM Connector Stored Procedures
Name Description
CreateSchema Creates a schema file for the specified table or view.
GetOAuthAccessToken Gets an authentication token from WooCommerce.
CreateSchema

Creates a schema file for the specified table or view.

CreateSchema

Creates a local schema file (.rsd) from an existing table or view in the data model.

The schema file is created in the directory set in the Location connection property when this procedure is executed. You can edit the file to include or exclude columns, rename columns, or adjust column datatypes.

The connector checks the Location to determine if the names of any .rsd files match a table or view in the data model. If there is a duplicate, the schema file will take precedence over the default instance of this table in the data model. If a schema file is present in Location that does not match an existing table or view, a new table or view entry is added to the data model of the connector.

Input

Name Type Accepts Output Streams Description
TableName String False The name of the table or view.
FileName String False The full file path and name of the schema to generate. Begin by choosing a parent directory (this parent directory should be set in the Location property). Complete the filepath by adding a directory corresponding to the schema used (suitecrmv8), followed by a .rsd file with a name corresponding to the desired table name. For example : 'C:\Users\User\Desktop\SuiteCRM\suitecrmv8\table.rsd'
FileStream String True An instance of an output stream where file data is written to. Only used if LocalFolderPath is not set.

Result Set Columns

Name Type Description
Result String Returns Success or Failure.
FileData String If the FileName input is empty.
GetOAuthAccessToken

Gets an authentication token from WooCommerce.

Input
Name Type Description
AuthMode String The type of authentication mode to use. Select App for getting authentication tokens via a desktop app. Select Web for getting authentication tokens via a Web app. The allowed values are APP, WEB. The default value is APP.
Scope String A comma-separated list of permissions to request from the user. Please check the WooCommerce API for a list of available permissions.
CallbackUrl String The URL the user will be redirected to after authorizing your application. This value must match the Redirect URL you have specified in the WooCommerce app settings. Only needed when the Authmode parameter is Web.
Verifier String The verifier returned from WooCommerce after the user has authorized your app to have access to their data. This value will be returned as a parameter to the callback URL.
State String Indicates any state which may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to the WooCommerce authorization server and back. Uses include redirecting the user to the correct resource in your site, nonces, and cross-site-request-forgery mitigations.
Result Set Columns
Name Type Description
OAuthAccessToken String The access token used for communication with WooCommerce.
OAuthRefreshToken String The OAuth refresh token. This is the same as the access token in the case of WooCommerce.
ExpiresIn String The remaining lifetime on the access token. A -1 denotes that it will not expire.

System Tables

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for SuiteCRM:

Data Source Tables

The following tables return information about how to connect to and query the data source:

  • sys_connection_props: Returns information on the available connection properties.
  • sys_sqlinfo: Describes the SELECT queries that the connector can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

  • sys_identity: Returns information about batch operations or single updates.

sys_catalogs

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs
Columns
Name Type Description
CatalogName String The database name.

sys_schemas

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas
Columns
Name Type Description
CatalogName String The database name.
SchemaName String The schema name.

sys_tables

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables
Columns
Name Type Description
CatalogName String The database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view.
TableType String The table type (table or view).
Description String A description of the table or view.
IsUpdateable Boolean Whether the table can be updated.

sys_tablecolumns

Describes the columns of the available tables and views.

The following query returns the columns and data types for the Accounts table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Accounts'
Columns
Name Type Description
CatalogName String The name of the database containing the table or view.
SchemaName String The schema containing the table or view.
TableName String The name of the table or view containing the column.
ColumnName String The column name.
DataTypeName String The data type name.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The storage size of the column.
DisplaySize Int32 The designated column's normal maximum width in characters.
NumericPrecision Int32 The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The column scale or number of digits to the right of the decimal point.
IsNullable Boolean Whether the column can contain null.
Description String A brief description of the column.
Ordinal Int32 The sequence number of the column.
IsAutoIncrement String Whether the column value is assigned in fixed increments.
IsGeneratedColumn String Whether the column is generated.
IsHidden Boolean Whether the column is hidden.
IsArray Boolean Whether the column is an array.
IsReadOnly Boolean Whether the column is read-only.
IsKey Boolean Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

sys_procedures

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures
Columns
Name Type Description
CatalogName String The database containing the stored procedure.
SchemaName String The schema containing the stored procedure.
ProcedureName String The name of the stored procedure.
Description String A description of the stored procedure.
ProcedureType String The type of the procedure, such as PROCEDURE or FUNCTION.

sys_procedureparameters

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the StoredProc stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='StoredProc' AND Direction=1 OR Direction=2
Columns
Name Type Description
CatalogName String The name of the database containing the stored procedure.
SchemaName String The name of the schema containing the stored procedure.
ProcedureName String The name of the stored procedure containing the parameter.
ColumnName String The name of the stored procedure parameter.
Direction Int32 An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName String The name of the data type.
DataType Int32 An integer indicating the data type. This value is determined at run time based on the environment.
Length Int32 The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision Int32 The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale Int32 The number of digits to the right of the decimal point in numeric data.
IsNullable Boolean Whether the parameter can contain null.
IsRequired Boolean Whether the parameter is required for execution of the procedure.
IsArray Boolean Whether the parameter is an array.
Description String The description of the parameter.
Ordinal Int32 The index of the parameter.

sys_keycolumns

Describes the primary and foreign keys.

The following query retrieves the primary key for the Accounts table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Accounts'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
IsKey Boolean Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey Boolean Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.

sys_foreignkeys

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
PrimaryKeyName String The name of the primary key.
ForeignKeyName String The name of the foreign key.
ReferencedCatalogName String The database containing the primary key.
ReferencedSchemaName String The schema containing the primary key.
ReferencedTableName String The table containing the primary key.
ReferencedColumnName String The column name of the primary key.
ForeignKeyType String Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

sys_primarykeys

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys
Columns
Name Type Description
CatalogName String The name of the database containing the key.
SchemaName String The name of the schema containing the key.
TableName String The name of the table containing the key.
ColumnName String The name of the key column.
KeySeq String The sequence number of the primary key.
KeyName String The name of the primary key.

sys_indexes

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'
Columns
Name Type Description
CatalogName String The name of the database containing the index.
SchemaName String The name of the schema containing the index.
TableName String The name of the table containing the index.
IndexName String The index name.
ColumnName String The name of the column associated with the index.
IsUnique Boolean True if the index is unique. False otherwise.
IsPrimary Boolean True if the index is a primary key. False otherwise.
Type Int16 An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder String The sort order: A for ascending or D for descending.
OrdinalPosition Int16 The sequence number of the column in the index.

sys_connection_props

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:suitecrm:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''
Columns
Name Type Description
Name String The name of the connection property.
ShortDescription String A brief description.
Type String The data type of the connection property.
Default String The default value if one is not explicitly set.
Values String A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value String The value you set or a preconfigured default.
Required Boolean Whether the property is required to connect.
Category String The category of the connection property.
IsSessionProperty String Whether the property is a session property, used to save information about the current connection.
Sensitivity String The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName String A camel-cased truncated form of the connection property name.
Ordinal Int32 The index of the parameter.
CatOrdinal Int32 The index of the parameter category.
Hierarchy String Shows dependent properties associated that need to be set alongside this one.
Visible Boolean Informs whether the property is visible in the connection UI.
ETC String Various miscellaneous information about the property.

sys_sqlinfo

Describes the SELECT query processing that the connector can offload to the data source.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name Description Possible Values
AGGREGATE_FUNCTIONS Supported aggregation functions. AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT Whether COUNT function is supported. YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR The opening character used to escape an identifier. [
IDENTIFIER_QUOTE_CLOSE_CHAR The closing character used to escape an identifier. ]
SUPPORTED_OPERATORS A list of supported SQL operators. =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY Whether GROUP BY is supported, and, if so, the degree of support. NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
STRING_FUNCTIONS Supported string functions. LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS Supported numeric functions. ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS Supported date/time functions. NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION Indicates if the provider supports transactions such as commit and rollback. YES, NO
DIALECT Indicates the SQL dialect to use.
KEY_PROPERTIES Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS Indicates if multiple schemas may exist for the provider. YES, NO
SUPPORTS_MULTIPLE_CATALOGS Indicates if multiple catalogs may exist for the provider. YES, NO
DATASYNCVERSION The Data Sync version needed to access this driver. Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY The Data Sync category of this driver. Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL Whether enhanced SQL functionality beyond what is offered by the API is supported. TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS Whether batch operations are supported. YES, NO
SQL_CAP All supported SQL capabilities for this driver. SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side. YES, NO
PSEUDO_COLUMNS A string array indicating the available pseudo columns.
MERGE_ALWAYS If the value is true, The Merge Mode is forcibly executed in Data Sync. TRUE, FALSE
REPLICATION_MIN_DATE_QUERY A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID Indicates whether the CheckCache statement should be done against the parent key column. TRUE, FALSE
CREATE_SCHEMA_PROCEDURES Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns
Name Type Description
NAME String A component of SQL syntax, or a capability that can be processed on the server.
VALUE String Detail on the supported SQL or SQL syntax.

sys_identity

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity
Columns
Name Type Description
Id String The database-generated ID returned from a data modification operation.
Batch String An identifier for the batch. 1 for a single operation.
Operation String The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message String SUCCESS or an error message if the update in the batch failed.

Advanced Configurations Properties

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Authentication

Property Description
Schema The schema which will be used to connect to SuiteCRM.
AuthScheme The type of authentication to use when connecting to SuiteCRM.
URL The URL of the SuiteCRM account.
User The SuiteCRM user account used to authenticate.
Password The password used to authenticate the user.

OAuth

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret The client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessToken The access token for connecting using OAuth.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
OAuthGrantType The grant type for the OAuth flow.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthAccessTokenUrl Use this connection property to bypass the OAuth access token URL.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

SSL

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

Schema

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Miscellaneous

Property Description
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from SuiteCRM.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
PushDisplayValues Push display values for enum fields instead of the API names.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UseDisplayNames Query SuiteCRM data by using the module's and fields' labels.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

Authentication

This section provides a complete list of authentication properties you can configure.

Property Description
Schema The schema which will be used to connect to SuiteCRM.
AuthScheme The type of authentication to use when connecting to SuiteCRM.
URL The URL of the SuiteCRM account.
User The SuiteCRM user account used to authenticate.
Password The password used to authenticate the user.

Schema

The schema which will be used to connect to SuiteCRM.

Possible Values

suitecrmv4, suitecrmv8

Data Type

string

Default Value

suitecrmv4

Remarks

Set this to suitecrmv8 if you want to consume the new Suitecrm V8 API. Note that the V8 API needs first to be configured in your instance of SuiteCRM.

AuthScheme

The type of authentication to use when connecting to SuiteCRM.

Data Type

string

Default Value

OAuthClient

Remarks
  • OAuthClient: Set to this to perform OAuth authentication with the client credentials grant type. Only available for the suitecrmv8 Schema.
  • OAuthPassword: Set to this to perform OAuth authentication with the password grant type. Only available for the suitecrmv8 Schema.
  • Basic: Set to this for Basic authentication with user and password. Only available for the suitecrmv4 Schema.

URL

The URL of the SuiteCRM account.

Data Type

string

Default Value

""

Remarks

The URL of the SuiteCRM account in the form 'http://{suite crm instance}.com'.

User

The SuiteCRM user account used to authenticate.

Data Type

string

Default Value

""

Remarks

Together with Password, this field is used to authenticate against the SuiteCRM server.

Password

The password used to authenticate the user.

Data Type

string

Default Value

""

Remarks

The User and Password are together used to authenticate with the server.

OAuth

This section provides a complete list of OAuth properties you can configure.

Property Description
InitiateOAuth Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.
OAuthClientId The client ID assigned when you register your application with an OAuth authorization server.
OAuthClientSecret The client secret assigned when you register your application with an OAuth authorization server.
OAuthAccessToken The access token for connecting using OAuth.
OAuthSettingsLocation The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH . Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.
OAuthGrantType The grant type for the OAuth flow.
OAuthVerifier The verifier code returned from the OAuth authorization URL.
OAuthAccessTokenUrl Use this connection property to bypass the OAuth access token URL.
OAuthExpiresIn The lifetime in seconds of the OAuth AccessToken.
OAuthTokenTimestamp The Unix epoch timestamp in milliseconds when the current Access Token was created.

InitiateOAuth

Set this property to initiate the process to obtain or refresh the OAuth access token when you connect.

Possible Values

OFF, GETANDREFRESH, REFRESH

Data Type

string

Default Value

OFF

Remarks

The following options are available:

  1. OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
  2. GETANDREFRESH: Indicates that the entire OAuth Flow will be handled by the connector. If no token currently exists, it will be obtained by prompting the user via the browser. If a token exists, it will be refreshed when applicable.
  3. REFRESH: Indicates that the connector will only handle refreshing the OAuthAccessToken. The user will never be prompted by the connector to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially.

OAuthClientId

The client ID assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

OAuthClientSecret

The client secret assigned when you register your application with an OAuth authorization server.

Data Type

string

Default Value

""

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

OAuthAccessToken

The access token for connecting using OAuth.

Data Type

string

Default Value

""

Remarks

The OAuthAccessToken property is used to connect using OAuth. The OAuthAccessToken is retrieved from the OAuth server as part of the authentication process. It has a server-dependent timeout and can be reused between requests.

The access token is used in place of your user name and password. The access token protects your credentials by keeping them on the server.

OAuthSettingsLocation

The location of the settings file where OAuth values are saved when InitiateOAuth is set to GETANDREFRESH or REFRESH. Alternatively, you can hold this location in memory by specifying a value starting with 'memory://'.

Data Type

string

Default Value

%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt

Remarks

When InitiateOAuth is set to GETANDREFRESH or REFRESH, the driver saves OAuth values to avoid requiring the user to manually enter OAuth connection properties and to allow the credentials to be shared across connections or processes.

Instead of specifying a file path, you can use memory storage. Memory locations are specified by using a value starting with 'memory://' followed by a unique identifier for that set of credentials (for example, memory://user1). The identifier can be anything you choose but should be unique to the user. Unlike file-based storage, where credentials persist across connections, memory storage loads the credentials into static memory, and the credentials are shared between connections using the same identifier for the life of the process. To persist credentials outside the current process, you must manually store the credentials prior to closing the connection. This enables you to set them in the connection when the process is started again. You can retrieve OAuth property values with a query to the sys_connection_props system table. If there are multiple connections using the same credentials, the properties are read from the previously closed connection.

The default location is "%APPDATA%\CData\Acumatica Data Provider\OAuthSettings.txt" with %APPDATA% set to the user's configuration directory. The default values are

  • Windows: "register://%DSN"
  • Unix: "%AppData%..."

where DSN is the name of the current DSN used in the open connection.

The following table lists the value of %APPDATA% by OS:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Linux ~/.config

OAuthGrantType

The grant type for the OAuth flow.

Possible Values

CLIENT, PASSWORD

Data Type

string

Default Value

CLIENT

Remarks

The following options are available: CLIENT,PASSWORD

OAuthVerifier

The verifier code returned from the OAuth authorization URL.

Data Type

string

Default Value

""

Remarks

The verifier code returned from the OAuth authorization URL. This can be used on systems where a browser cannot be launched such as headless systems.

Authentication on Headless Machines

See to obtain the OAuthVerifier value.

Set OAuthSettingsLocation along with OAuthVerifier. When you connect, the connector exchanges the OAuthVerifier for the OAuth authentication tokens and saves them, encrypted, to the specified location. Set InitiateOAuth to GETANDREFRESH to automate the exchange.

Once the OAuth settings file has been generated, you can remove OAuthVerifier from the connection properties and connect with OAuthSettingsLocation set.

To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH.

OAuthAccessTokenUrl

Use this connection property to bypass the OAuth access token URL.

Data Type

string

Default Value

""

Remarks

Use this connection property to bypass the OAuth access token URL. By default the access token URL wil be the SuiteCRM server instance URL + "/Api/access_token". If for any case you are having trouble accessing this endpoint please feel free to change it.

OAuthExpiresIn

The lifetime in seconds of the OAuth AccessToken.

Data Type

string

Default Value

""

Remarks

Pair with OAuthTokenTimestamp to determine when the AccessToken will expire.

OAuthTokenTimestamp

The Unix epoch timestamp in milliseconds when the current Access Token was created.

Data Type

string

Default Value

""

Remarks

Pair with OAuthExpiresIn to determine when the AccessToken will expire.

SSL

This section provides a complete list of SSL properties you can configure.

Property Description
SSLServerCert The certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description Example
A full PEM Certificate (example shortened for brevity) -----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate C:\\cert.cer
The public key (example shortened for brevity) -----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated) ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated) 34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Certificates are validated as trusted by the machine based on the System's trust store. The trust store used is the 'javax.net.ssl.trustStore' value specified for the system. If no value is specified for this property, Java's default trust store is used (for example, JAVA_HOME\lib\security\cacerts).

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Schema

This section provides a complete list of schema properties you can configure.

Property Description
Location A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA, SchemaB, SchemaC.
Tables This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA, TableB, TableC.
Views Restricts the views reported to a subset of the available tables. For example, Views=ViewA, ViewB, ViewC.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Data Type

string

Default Value

%APPDATA%\SuiteCRM Data Provider\Schema

Remarks

The path to a directory which contains the schema files for the connector (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

Note

Given that this connector supports multiple schemas, the structure for SuiteCRM custom schema files is as follows:

  • Each schema is given a folder corresponding to that schema name.
  • These schema folders are contained in a parent folder.
  • The parent folder should be set as the Location, not an individual schema's folder.

If left unspecified, the default location is "%APPDATA%\SuiteCRM Data Provider\Schema" with %APPDATA% being set to the user's configuration directory:

Platform %APPDATA%
Windows The value of the APPDATA environment variable
Mac ~/Library/Application Support
Linux ~/.config

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Data Type

string

Default Value

""

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Data Type

string

Default Value

""

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the connector.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Miscellaneous

This section provides a complete list of miscellaneous properties you can configure.

Property Description
MaxRows Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other These hidden properties are used only in specific use cases.
Pagesize The maximum number of results to return per page from SuiteCRM.
PseudoColumns This property indicates whether or not to include pseudo columns as columns to the table.
PushDisplayValues Push display values for enum fields instead of the API names.
Timeout The value in seconds until the timeout error is thrown, canceling the operation.
UseDisplayNames Query SuiteCRM data by using the module's and fields' labels.
UserDefinedViews A filepath pointing to the JSON configuration file containing your custom views.

MaxRows

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Other

These hidden properties are used only in specific use cases.

Data Type

string

Default Value

""

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting
Property Description
DefaultColumnSize Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename Records the underlying socket data transfer to the specified file.

Pagesize

The maximum number of results to return per page from SuiteCRM.

Data Type

int

Default Value

100

Remarks

The Pagesize property affects the maximum number of results to return per page from SuiteCRM. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

PushDisplayValues

Push display values for enum fields instead of the API names.

Data Type

bool

Default Value

false

Remarks

Determines whether to push display values for enum (drop-down) fields or their API names for SELECT queries. This is available only for the suitecrmv4 schema.

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the connector throws an exception.

UseDisplayNames

Query SuiteCRM data by using the module's and fields' labels.

Data Type

bool

Default Value

true

Remarks

Determines whether to use labels of modules and fields instead of the API original names. Labels are more user friendly. Set to false to use API names instead for the metadata.

UserDefinedViews

A filepath pointing to the JSON configuration file containing your custom views.

Data Type

string

Default Value

""

Remarks

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

Note that the specified path is not embedded in quotation marks.