Getting Started

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

Connecting to SuiteCRM

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

Accessing Data from CData Cloud Services

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

Establishing a Connection

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

Connecting to 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.

Authenticating 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.)
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.
URL: The base URL of your SuiteCRM system. For example, https://suitecrmhost/.

SSL Configuration

Customizing the SSL Configuration

To enable TLS, set the following:

URL: Prefix the connection string with https://

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

To specify another certificate, see the SSLServerCert connection property.

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

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

Other Proxies

Set the following properties:

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

Data Model

CData Cloud models SuiteCRM modules as relational .

Tables

Table definitions are dynamically retrieved. The Cloud 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 Cloud.

The Cloud 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 Cloud 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 Cloud.

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

CData Cloud models SuiteCRM modules as relational database.

Tables

Table definitions are dynamically retrieved. The Cloud 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 Cloud.

The Cloud 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 Cloud uses the SuiteCRM API to process supported filters. The Cloud processes other filters client-side within the Cloud.

Tables

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

CData Cloud - SuiteCRM 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 Cloud 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', '[email protected]', '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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 ('[email protected]')

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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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', '[email protected]', '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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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', '[email protected]', '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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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

CData Cloud models SuiteCRM modules as relational .

Tables

Table definitions are dynamically retrieved. The Cloud 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 Cloud.

The Cloud 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 Cloud offloads as much of the SELECT statement processing as possible to SuiteCRM and then processes the rest of the query in memory. The Cloud uses the SuiteCRM API to process supported filters. The Cloud processes other filters client-side within the Cloud.

Tables

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

CData Cloud - SuiteCRM 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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 Cloud 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.

CData Cloud - SuiteCRM Stored Procedures


Name	Description

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:

sys_catalogs: Lists the available databases.
sys_schemas: Lists the available schemas.
sys_tables: Lists the available tables and views.
sys_tablecolumns: Describes the columns of the available tables and views.
sys_procedures: Describes the available stored procedures.
sys_procedureparameters: Describes stored procedure parameters.
sys_keycolumns: Describes the primary and foreign keys.
sys_indexes: Describes the available indexes.

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 Cloud 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.
ColumnType	String	The role or classification of the column in the schema. Possible values include SYSTEM, LINKEDCOLUMN, NAVIGATIONKEY, REFERENCECOLUMN, and NAVIGATIONPARENTCOLUMN.

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

To include result set columns in addition to the parameters, set the IncludeResultColumns pseudo column to True:

SELECT * FROM sys_procedureparameters WHERE ProcedureName = 'StoredProc' AND IncludeResultColumns='True'

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.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
DataTypeName	String	The name of the data type.
NumericPrecision	Int32	The maximum precision for numeric data. The column length in characters for character and date-time data.
Length	Int32	The number of characters allowed for character data. The number of digits allowed for numeric 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.
Values	String	The values you can set in this parameter are limited to those shown in this column. Possible values are comma-separated.
SupportsStreams	Boolean	Whether the parameter represents a file that you can pass as either a file path or a stream.
IsPath	Boolean	Whether the parameter is a target path for a schema creation operation.
Default	String	The value used for this parameter when no value is specified.
SpecificName	String	A label that, when multiple stored procedures have the same name, uniquely identifies each identically-named stored procedure. If there's only one procedure with a given name, its name is simply reflected here.
IsCDataProvided	Boolean	Whether the procedure is added/implemented by CData, as opposed to being a native SuiteCRM procedure.

Pseudo-Columns


Name	Type	Description
IncludeResultColumns	Boolean	Whether the output should include columns from the result set in addition to parameters. Defaults to False.

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.

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 Cloud can offload to the data source.

See SQL Compliance for SQL syntax details.

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
OJ_CAPABILITIES	The supported varieties of outer joins supported.	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	Whether outer joins are supported.	YES, NO
SUBQUERIES	Whether subqueries are supported, and, if so, the degree of support.	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
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 CData Data Sync version needed to access this driver.	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	The CData 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.

sys_information

Describes the available system information.

The following query retrieves all columns:

SELECT * FROM sys_information

Columns


Name	Type	Description
Product	String	The name of the product.
Version	String	The version number of the product.
Datasource	String	The name of the datasource the product connects to.
NodeId	String	The unique identifier of the machine where the product is installed.
HelpURL	String	The URL to the product's help documentation.
License	String	The license information for the product. (If this information is not available, the field may be left blank or marked as 'N/A'.)
Location	String	The file path location where the product's library is stored.
Environment	String	The version of the environment or rumtine the product is currently running under.
DataSyncVersion	String	The tier of CData Sync required to use this connector.
DataSyncCategory	String	The category of CData Sync functionality (e.g., Source, Destination).

Connection String Options

The connection string 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 in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

Property	Description
Schema	The API version to use when connecting to SuiteCRM.
AuthScheme	The type of authentication to use when connecting to SuiteCRM.
URL	The URL of the SuiteCRM account.
User	Specifies the authenticating user's user ID.
Password	Specifies the authenticating user's password.

OAuth

Property	Description
OAuthClientId	Specifies the client ID (also known as the consumer key) assigned to your custom OAuth application. This ID is required to identify the application to the OAuth authorization server during authentication.
OAuthClientSecret	Specifies the client secret assigned to your custom OAuth application. This confidential value is used to authenticate the application to the OAuth authorization server. (Custom OAuth applications only.).
OAuthAccessTokenURL	Supply an OAuth access token URL in this connection property to override the default token URL.

SSL

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

Logging

Property	Description
Verbosity	Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Schema

Property	Description
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

Miscellaneous

Property	Description
MaxRows	Specifies the maximum number of rows returned for queries that do not include either aggregation or GROUP BY.
Pagesize	Specifies the maximum number of records per page the provider returns when requesting data from SuiteCRM.
PseudoColumns	Specifies the pseudocolumns to expose as table columns, expressed as a string in the format 'TableName=ColumnName;TableName=ColumnName'.
PushDisplayValues	Use the user-facing display names of drop-down (enum) field values, rather than their API names, in query results and metadata listings.
ThrowsKeyNotFound	Specifies whether or not throws an exception if there is no rows updated.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error.
UseDisplayNames	Use the user-facing display names of modules and fields, rather than their API names, in query results and metadata listings.

Authentication

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

Property	Description
Schema	The API version to use when connecting to SuiteCRM.
AuthScheme	The type of authentication to use when connecting to SuiteCRM.
URL	The URL of the SuiteCRM account.
User	Specifies the authenticating user's user ID.
Password	Specifies the authenticating user's password.

Schema

The API version to use when connecting to SuiteCRM.

Possible Values

suitecrmv4, suitecrmv8

Data Type

string

Default Value

"suitecrmv4"

Remarks

Set this connection property to suitecrmv4 to access data from the SuiteCRM V4.1 API or suitecrmv8 to access data from the 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.

Possible Values

OAuthClient, OAuthPassword, Basic

Data Type

string

Default Value

"OAuthClient"

Remarks

OAuthClient: Set to this to perform OAuth authentication with the client credentials grant type. Only available when Schema is set to suitecrmv8 Schema.
OAuthPassword: Set to this to perform OAuth authentication with the password grant type. Only available when Schema is set to suitecrmv8 Schema.
Basic: Set to this for Basic authentication with user and password. Only available when Schema is set to 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

Specifies the authenticating user's user ID.

Data Type

string

Default Value

""

Remarks

The authenticating server requires both User and Password to validate the user's identity.

Password

Specifies the authenticating user's password.

Data Type

string

Default Value

""

Remarks

The authenticating server requires both User and Password to validate the user's identity.

OAuth

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

Property	Description
OAuthClientId	Specifies the client ID (also known as the consumer key) assigned to your custom OAuth application. This ID is required to identify the application to the OAuth authorization server during authentication.
OAuthClientSecret	Specifies the client secret assigned to your custom OAuth application. This confidential value is used to authenticate the application to the OAuth authorization server. (Custom OAuth applications only.).
OAuthAccessTokenURL	Supply an OAuth access token URL in this connection property to override the default token URL.

OAuthClientId

Specifies the client ID (also known as the consumer key) assigned to your custom OAuth application. This ID is required to identify the application to the OAuth authorization server during authentication.

Data Type

string

Default Value

""

Remarks

This property is required in two cases:

When using a custom OAuth application, such as in web-based authentication flows, service-based authentication, or certificate-based flows that require application registration.
If the driver does not provide embedded OAuth credentials.

(When the driver provides embedded OAuth credentials, this value may already be provided by the Cloud and thus not require manual entry.)

OAuthClientId is generally used alongside other OAuth-related properties such as OAuthClientSecret and OAuthSettingsLocation when configuring an authenticated connection.

OAuthClientId is one of the key connection parameters that need to be set before users can authenticate via OAuth. You can usually find this value in your identity provider’s application registration settings. Look for a field labeled Client ID, Application ID, or Consumer Key.

While the client ID is not considered a confidential value like a client secret, it is still part of your application's identity and should be handled carefully. Avoid exposing it in public repositories or shared configuration files.

For more information on how this property is used when configuring a connection, see Establishing a Connection.

OAuthClientSecret

Specifies the client secret assigned to your custom OAuth application. This confidential value is used to authenticate the application to the OAuth authorization server. (Custom OAuth applications only.).

Data Type

string

Default Value

""

Remarks

This property (sometimes called the application secret or consumer secret) is required when using a custom OAuth application in any flow that requires secure client authentication, such as web-based OAuth, service-based connections, or certificate-based authorization flows. It is not required when using an embedded OAuth application.

The client secret is used during the token exchange step of the OAuth flow, when the driver requests an access token from the authorization server. If this value is missing or incorrect, authentication fails with either an invalid_client or an unauthorized_client error.

OAuthClientSecret is one of the key connection parameters that need to be set before users can authenticate via OAuth. You can obtain this value from your identity provider when registering the OAuth application.

Notes:

This value should be stored securely and never exposed in public repositories, scripts, or unsecured environments.
Client secrets may also expire after a set period. Be sure to monitor expiration dates and rotate secrets as needed to maintain uninterrupted access.

For more information on how this property is used when configuring a connection, see Establishing a Connection

OAuthAccessTokenURL

Supply an OAuth access token URL in this connection property to override the default token URL.

Data Type

string

Default Value

""

Remarks

By default, the access token URL is constructed from the SuiteCRM server instance URL, followed by /api/access_token.

SSL

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

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

SSLServerCert

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

Data Type

string

Default Value

""

Remarks

If you are using a TLS/SSL connection, use this property to specify the TLS/SSL certificate to be accepted from the server. If you specify a value for this property, all other certificates that are not trusted by the machine are 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`

Note: It is possible to use '*' to signify that all certificates should be accepted, but due to security concerns this is not recommended.

Logging

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

Property	Description
Verbosity	Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Verbosity

Specifies the verbosity level of the log file, which controls the amount of detail logged. Supported values range from 1 to 5.

Data Type

string

Default Value

"1"

Remarks

This property defines the level of detail the Cloud includes in the log file. Higher verbosity levels increase the detail of the logged information, but may also result in larger log files and slower performance due to the additional data being captured.

The default verbosity level is 1, which is recommended for regular operation. Higher verbosity levels are primarily intended for debugging purposes. For more information on each level, refer to Logging.

When combined with the LogModules property, Verbosity can refine logging to specific categories of information.

Schema

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

Property	Description
BrowsableSchemas	Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

BrowsableSchemas

Optional setting that restricts the schemas reported to a subset of all available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC .

Data Type

string

Default Value

""

Remarks

Listing all available database schemas can take extra time, thus degrading performance. Providing a list of schemas in the connection string saves time and improves performance.

Miscellaneous

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

Property	Description
MaxRows	Specifies the maximum number of rows returned for queries that do not include either aggregation or GROUP BY.
Pagesize	Specifies the maximum number of records per page the provider returns when requesting data from SuiteCRM.
PseudoColumns	Specifies the pseudocolumns to expose as table columns, expressed as a string in the format 'TableName=ColumnName;TableName=ColumnName'.
PushDisplayValues	Use the user-facing display names of drop-down (enum) field values, rather than their API names, in query results and metadata listings.
ThrowsKeyNotFound	Specifies whether or not throws an exception if there is no rows updated.
Timeout	Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error.
UseDisplayNames	Use the user-facing display names of modules and fields, rather than their API names, in query results and metadata listings.

MaxRows

Specifies the maximum number of rows returned for queries that do not include either aggregation or GROUP BY.

Data Type

int

Default Value

-1

Remarks

The default value for this property, -1, means that no row limit is enforced unless the query explicitly includes a LIMIT clause. (When a query includes a LIMIT clause, the value specified in the query takes precedence over the MaxRows setting.)

Setting MaxRows to a whole number greater than 0 ensures that queries do not return excessively large result sets by default.

This property is useful for optimizing performance and preventing excessive resource consumption when executing queries that could otherwise return very large datasets.

Pagesize

Specifies the maximum number of records per page the provider returns when requesting data from SuiteCRM.

Data Type

int

Default Value

100

Remarks

When processing a query, instead of requesting all of the queried data at once from SuiteCRM, the Cloud can request the queried data in pieces called pages.

This connection property determines the maximum number of results that the Cloud requests per page.

Note: Setting large page sizes may improve overall query execution time, but doing so causes the Cloud to use more memory when executing queries and risks triggering a timeout.

PseudoColumns

Specifies the pseudocolumns to expose as table columns, expressed as a string in the format 'TableName=ColumnName;TableName=ColumnName'.

Data Type

string

Default Value

""

Remarks

This property allows you to define which pseudocolumns the Cloud exposes as table columns.

To specify individual pseudocolumns, use the following format:

Table1=Column1;Table1=Column2;Table2=Column3

To include all pseudocolumns for all tables use:

*=*

PushDisplayValues

Use the user-facing display names of drop-down (enum) field values, rather than their API names, in query results and metadata listings.

Data Type

bool

Default Value

false

Remarks

The SuiteCRM API refers to the possible values associated with drop-down fields using developer-focused names, which are harder to read and interpret than the straightforward names used in the user interface for those same drop-down field values.

When this connection property is set to True, the schema of the Cloud uses the user-friendly names from the user interface to refer to drop-down field values.

When this connection property is set to False, the schema of the Cloud uses the developer-focused API names to refer to drop-down field values.

Note: The effects of this connection property only apply when the Schema connection property is set to suitecrmv4. When Schema is set to suitecrmv8, the Cloud always displays the API names of drop-down fields.

ThrowsKeyNotFound

Specifies whether or not throws an exception if there is no rows updated.

Data Type

bool

Default Value

false

Remarks

Specifies whether or not throws an exception if there is no rows updated.

Timeout

Specifies the maximum time, in seconds, that the provider waits for a server response before throwing a timeout error.

Data Type

int

Default Value

60

Remarks

The timeout applies to each individual communication with the server rather than the entire query or operation. For example, a query could continue running beyond 60 seconds if each paging call completes within the timeout limit.

Timeout is set to 60 seconds by default. To disable timeouts, set this property to 0.

Disabling the timeout allows operations to run indefinitely until they succeed or fail due to other conditions such as server-side timeouts, network interruptions, or resource limits on the server.

Note: Use this property cautiously to avoid long-running operations that could degrade performance or result in unresponsive behavior.

UseDisplayNames

Use the user-facing display names of modules and fields, rather than their API names, in query results and metadata listings.

Data Type

bool

Default Value

true

Remarks

The SuiteCRM API refers to modules and fields with developer-focused names, which are harder to read and interpret than the straightforward names used in the user interface for those same modules and fields.

When this connection property is set to True and the Schema connection property is set to suitecrmv4, the schema of the Cloud uses the user-friendly names from the user interface to refer to modules and fields.

When this connection property is set to True and Schema is set to suitecrmv8, modules use the user-friendly names from the user interface, but fields use the API names.

When this connection property is set to False, the schema of the Cloud uses the developer-focused API names to refer to modules and fields.

Third Party Copyrights

LZMA from 7Zip LZMA SDK

LZMA SDK is placed in the public domain.

Anyone is free to copy, modify, publish, use, compile, sell, or distribute the original LZMA SDK code, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means.

LZMA2 from XZ SDK

Version 1.9 and older are in the public domain.

Xamarin.Forms

Xamarin SDK

The MIT License (MIT)

Copyright (c) .NET Foundation Contributors

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

NSIS 3.10

Copyright (C) 1999-2025 Contributors THE ACCOMPANYING PROGRAM IS PROVIDED UNDER THE TERMS OF THIS COMMON PUBLIC LICENSE ("AGREEMENT"). ANY USE, REPRODUCTION OR DISTRIBUTION OF THE PROGRAM CONSTITUTES RECIPIENT'S ACCEPTANCE OF THIS AGREEMENT.

1. DEFINITIONS

"Contribution" means:

a) in the case of the initial Contributor, the initial code and documentation distributed under this Agreement, and b) in the case of each subsequent Contributor:

i) changes to the Program, and

ii) additions to the Program;

where such changes and/or additions to the Program originate from and are distributed by that particular Contributor. A Contribution 'originates' from a Contributor if it was added to the Program by such Contributor itself or anyone acting on such Contributor's behalf. Contributions do not include additions to the Program which: (i) are separate modules of software distributed in conjunction with the Program under their own license agreement, and (ii) are not derivative works of the Program.

"Contributor" means any person or entity that distributes the Program.

"Licensed Patents " mean patent claims licensable by a Contributor which are necessarily infringed by the use or sale of its Contribution alone or when combined with the Program.

"Program" means the Contributions distributed in accordance with this Agreement.

"Recipient" means anyone who receives the Program under this Agreement, including all Contributors.

2. GRANT OF RIGHTS

a) Subject to the terms of this Agreement, each Contributor hereby grants Recipient a non-exclusive, worldwide, royalty-free copyright license to reproduce, prepare derivative works of, publicly display, publicly perform, distribute and sublicense the Contribution of such Contributor, if any, and such derivative works, in source code and object code form.

b) Subject to the terms of this Agreement, each Contributor hereby grants Recipient a non-exclusive, worldwide, royalty-free patent license under Licensed Patents to make, use, sell, offer to sell, import and otherwise transfer the Contribution of such Contributor, if any, in source code and object code form. This patent license shall apply to the combination of the Contribution and the Program if, at the time the Contribution is added by the Contributor, such addition of the Contribution causes such combination to be covered by the Licensed Patents. The patent license shall not apply to any other combinations which include the Contribution. No hardware per se is licensed hereunder.

c) Recipient understands that although each Contributor grants the licenses to its Contributions set forth herein, no assurances are provided by any Contributor that the Program does not infringe the patent or other intellectual property rights of any other entity. Each Contributor disclaims any liability to Recipient for claims brought by any other entity based on infringement of intellectual property rights or otherwise. As a condition to exercising the rights and licenses granted hereunder, each Recipient hereby assumes sole responsibility to secure any other intellectual property rights needed, if any. For example, if a third party patent license is required to allow Recipient to distribute the Program, it is Recipient's responsibility to acquire that license before distributing the Program.

d) Each Contributor represents that to its knowledge it has sufficient copyright rights in its Contribution, if any, to grant the copyright license set forth in this Agreement.

3. REQUIREMENTS

A Contributor may choose to distribute the Program in object code form under its own license agreement, provided that:

a) it complies with the terms and conditions of this Agreement; and

b) its license agreement:

i) effectively disclaims on behalf of all Contributors all warranties and conditions, express and implied, including warranties or conditions of title and non-infringement, and implied warranties or conditions of merchantability and fitness for a particular purpose;

ii) effectively excludes on behalf of all Contributors all liability for damages, including direct, indirect, special, incidental and consequential damages, such as lost profits;

iii) states that any provisions which differ from this Agreement are offered by that Contributor alone and not by any other party; and

iv) states that source code for the Program is available from such Contributor, and informs licensees how to obtain it in a reasonable manner on or through a medium customarily used for software exchange.

When the Program is made available in source code form:

a) it must be made available under this Agreement; and

b) a copy of this Agreement must be included with each copy of the Program.

Contributors may not remove or alter any copyright notices contained within the Program.

Each Contributor must identify itself as the originator of its Contribution, if any, in a manner that reasonably allows subsequent Recipients to identify the originator of the Contribution.

4. COMMERCIAL DISTRIBUTION

Commercial distributors of software may accept certain responsibilities with respect to end users, business partners and the like. While this license is intended to facilitate the commercial use of the Program, the Contributor who includes the Program in a commercial product offering should do so in a manner which does not create potential liability for other Contributors. Therefore, if a Contributor includes the Program in a commercial product offering, such Contributor ("Commercial Contributor") hereby agrees to defend and indemnify every other Contributor ("Indemnified Contributor") against any losses, damages and costs (collectively "Losses") arising from claims, lawsuits and other legal actions brought by a third party against the Indemnified Contributor to the extent caused by the acts or omissions of such Commercial Contributor in connection with its distribution of the Program in a commercial product offering. The obligations in this section do not apply to any claims or Losses relating to any actual or alleged intellectual property infringement. In order to qualify, an Indemnified Contributor must: a) promptly notify the Commercial Contributor in writing of such claim, and b) allow the Commercial Contributor to control, and cooperate with the Commercial Contributor in, the defense and any related settlement negotiations. The Indemnified Contributor may participate in any such claim at its own expense.

For example, a Contributor might include the Program in a commercial product offering, Product X. That Contributor is then a Commercial Contributor. If that Commercial Contributor then makes performance claims, or offers warranties related to Product X, those performance claims and warranties are such Commercial Contributor's responsibility alone. Under this section, the Commercial Contributor would have to defend claims against the other Contributors related to those performance claims and warranties, and if a court requires any other Contributor to pay any damages as a result, the Commercial Contributor must pay those damages.

5. NO WARRANTY

EXCEPT AS EXPRESSLY SET FORTH IN THIS AGREEMENT, THE PROGRAM IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, EITHER EXPRESS OR IMPLIED INCLUDING, WITHOUT LIMITATION, ANY WARRANTIES OR CONDITIONS OF TITLE, NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Each Recipient is solely responsible for determining the appropriateness of using and distributing the Program and assumes all risks associated with its exercise of rights under this Agreement, including but not limited to the risks and costs of program errors, compliance with applicable laws, damage to or loss of data, programs or equipment, and unavailability or interruption of operations.

6. DISCLAIMER OF LIABILITY

EXCEPT AS EXPRESSLY SET FORTH IN THIS AGREEMENT, NEITHER RECIPIENT NOR ANY CONTRIBUTORS SHALL HAVE ANY LIABILITY FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING WITHOUT LIMITATION LOST PROFITS), HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OR DISTRIBUTION OF THE PROGRAM OR THE EXERCISE OF ANY RIGHTS GRANTED HEREUNDER, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

7. GENERAL

If any provision of this Agreement is invalid or unenforceable under applicable law, it shall not affect the validity or enforceability of the remainder of the terms of this Agreement, and without further action by the parties hereto, such provision shall be reformed to the minimum extent necessary to make such provision valid and enforceable.

If Recipient institutes patent litigation against a Contributor with respect to a patent applicable to software (including a cross-claim or counterclaim in a lawsuit), then any patent licenses granted by that Contributor to such Recipient under this Agreement shall terminate as of the date such litigation is filed. In addition, if Recipient institutes patent litigation against any entity (including a cross-claim or counterclaim in a lawsuit) alleging that the Program itself (excluding combinations of the Program with other software or hardware) infringes such Recipient's patent(s), then such Recipient's rights granted under Section 2(b) shall terminate as of the date such litigation is filed.

All Recipient's rights under this Agreement shall terminate if it fails to comply with any of the material terms or conditions of this Agreement and does not cure such failure in a reasonable period of time after becoming aware of such noncompliance. If all Recipient's rights under this Agreement terminate, Recipient agrees to cease use and distribution of the Program as soon as reasonably practicable. However, Recipient's obligations under this Agreement and any licenses granted by Recipient relating to the Program shall continue and survive.

Everyone is permitted to copy and distribute copies of this Agreement, but in order to avoid inconsistency the Agreement is copyrighted and may only be modified in the following manner. The Agreement Steward reserves the right to publish new versions (including revisions) of this Agreement from time to time. No one other than the Agreement Steward has the right to modify this Agreement. IBM is the initial Agreement Steward. IBM may assign the responsibility to serve as the Agreement Steward to a suitable separate entity. Each new version of the Agreement will be given a distinguishing version number. The Program (including Contributions) may always be distributed subject to the version of the Agreement under which it was received. In addition, after a new version of the Agreement is published, Contributor may elect to distribute the Program (including its Contributions) under the new version. Except as expressly stated in Sections 2(a) and 2(b) above, Recipient receives no rights or licenses to the intellectual property of any Contributor under this Agreement, whether expressly, by implication, estoppel or otherwise. All rights in the Program not expressly granted under this Agreement are reserved.

This Agreement is governed by the laws of the State of New York and the intellectual property laws of the United States of America. No party to this Agreement will bring a legal action under this Agreement more than one year after the cause of action arose. Each party waives its rights to a jury trial in any resulting litigation.

Cloud

SuiteCRM - CData Cloud

Overview

Key Features

Getting Started

Connecting to SuiteCRM

Accessing Data from CData Cloud Services

Establishing a Connection

Connecting to SuiteCRM

SuiteCRM V4.1 API

SuiteCRM V8 API

Authenticating to SuiteCRM V8 API

Client Credentials Grant

Password Grant

SSL Configuration

Customizing the SSL Configuration

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

Other Proxies

Data Model

Tables

Key Features

SuiteCRM V4.1 Data Model

SuiteCRM V8 Data Model

SuiteCRM V4.1 Data Model

Tables

Query Processing

Tables

CData Cloud - SuiteCRM Tables

Accounts

Table Specific Information

Select

Insert

Update

Delete

Columns

Pseudo-Columns

ACLRoles

Table Specific Information

Select

Insert

Update

Delete

Columns

Pseudo-Columns

Alerts

Table Specific Information

Select

Insert

Update

Delete

Columns

Pseudo-Columns

Bugs

Table Specific Information

Select

Insert

Update

Delete

Columns

Pseudo-Columns

BusinessHours

Table Specific Information

Select

Insert

Update

Delete

Columns

Pseudo-Columns

Calls

Table Specific Information

Select

Insert

Update

Delete

Columns

Pseudo-Columns

CallsReschedule

Table Specific Information