Establishing a Connection

Adding a Connection to SuiteCRM

To add a connection to SuiteCRM:

In the application console, navigate to the Connections page.
At the Add Connections panel, select the icon for the connection you want to add.
If the SuiteCRM icon is not available, click the Add More icon to download and install the SuiteCRM connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

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/.

Advanced Features

This section details a selection of advanced features of the SuiteCRM Sync App.

User Defined Views

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

SSL Configuration

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

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

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

See Query Processing for more information.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

SSL Configuration

Customizing the SSL Configuration

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

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

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

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 Sync App models SuiteCRM modules as relational .

Tables

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

The Sync App 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 Sync App 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 Sync App.

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 Sync App models SuiteCRM modules as relational database.

Tables

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

The Sync App 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 Sync App offloads as much of the SELECT statement processing as possible to SuiteCRM and then processes the rest of the query in memory; API limitations and requirements are documented in this section.

Tables

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

SuiteCRM Connector for CData Sync 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App models SuiteCRM modules as relational .

Tables

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

The Sync App 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 Sync App offloads as much of the SELECT statement processing as possible to SuiteCRM and then processes the rest of the query in memory; API limitations and requirements are documented in this section.

Tables

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

SuiteCRM Connector for CData Sync 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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 Sync App 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

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 schema which will be used to connect to SuiteCRM.
AuthScheme	The type of authentication to use when connecting to SuiteCRM.
URL	The URL of the SuiteCRM account.
User	The SuiteCRM user account used to authenticate.
Password	The password used to authenticate the user.

OAuth

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.
OAuthGrantType	The grant type for the OAuth flow.
OAuthAccessTokenUrl	Use this connection property to bypass the OAuth access token url.

SSL

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

Firewall

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

Proxy

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Logging

Property	Description
LogModules	Core modules to be included in the log file.

Schema

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

Miscellaneous

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

Authentication

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

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

Schema

The schema which will be used to connect to SuiteCRM.

Remarks

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

AuthScheme

The type of authentication to use when connecting to SuiteCRM.

Remarks

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

URL

The URL of the SuiteCRM account.

Remarks

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

User

The SuiteCRM user account used to authenticate.

Remarks

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

Password

The password used to authenticate the user.

Remarks

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

OAuth

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

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.
OAuthGrantType	The grant type for the OAuth flow.
OAuthAccessTokenUrl	Use this connection property to bypass the OAuth access token url.

OAuthClientId

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

Remarks

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

OAuthClientSecret

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

Remarks

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

OAuthGrantType

The grant type for the OAuth flow.

Remarks

The following options are available: CLIENT,PASSWORD

OAuthAccessTokenUrl

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

Remarks

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

SSL

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

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

SSLServerCert

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

Remarks

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

This property can take the following forms:


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

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

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

Firewall

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

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

FirewallType

The protocol used by a proxy-based firewall.

Remarks

This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.


Type	Default Port	Description
TUNNEL	80	When this is set, the Sync App opens a connection to SuiteCRM and traffic flows back and forth through the proxy.
SOCKS4	1080	When this is set, the Sync App sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5	1080	When this is set, the Sync App sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

FirewallServer

The name or IP address of a proxy-based firewall.

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

FirewallPort

The TCP port for a proxy-based firewall.

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

FirewallUser

The user name to use to authenticate with a proxy-based firewall.

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

FirewallPassword

A password used to authenticate to a proxy-based firewall.

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

Proxy

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

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

ProxyAutoDetect

This indicates whether to use the system proxy settings or not.

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

ProxyServer

The hostname or IP address of a proxy to route HTTP traffic through.

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

ProxyPort

The TCP port the ProxyServer proxy is running on.

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

ProxyAuthScheme

The authentication type to use to authenticate to the ProxyServer proxy.

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

BASIC: The Sync App performs HTTP BASIC authentication.
DIGEST: The Sync App performs HTTP DIGEST authentication.
NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
PROPRIETARY: The Sync App does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

ProxyUser

A user name to be used to authenticate to the ProxyServer proxy.

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

ProxyPassword

A password to be used to authenticate to the ProxyServer proxy.

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

ProxySSLType

The SSL type to use when connecting to the ProxyServer proxy.

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:


AUTO	Default setting. If the URL is an HTTPS URL, the Sync App will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYS	The connection is always SSL enabled.
NEVER	The connection is not SSL enabled.
TUNNEL	The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

ProxyExceptions

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

Logging

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

Property	Description
LogModules	Core modules to be included in the log file.

LogModules

Core modules to be included in the log file.

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

Schema

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

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

Location

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

Remarks

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

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

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

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


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

BrowsableSchemas

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

Remarks

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

Tables

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

Remarks

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

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

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

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

Views

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

Remarks

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

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

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

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

Miscellaneous

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

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

MaxRows

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

Remarks

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

Other

These hidden properties are used only in specific use cases.

Remarks

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

Specify multiple properties in a semicolon-separated list.

Integration and Formatting


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

Pagesize

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

Remarks

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

PseudoColumns

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

Remarks

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

PushDisplayValues

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

Remarks

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

Timeout

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

Remarks

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

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

UseDisplayNames

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

Remarks

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

UserDefinedViews

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

Remarks

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

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

This User Defined View configuration file is formatted as follows:

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

For example:

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

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

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

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

SuiteCRM Connector for CData Sync

SuiteCRM Connector for CData Sync

Overview

SuiteCRM Version Support

Establishing a Connection

Adding a Connection to SuiteCRM

Connecting to SuiteCRM

SuiteCRM V4.1 API

SuiteCRM V8 API

Authenticating to SuiteCRM V8 API

Client Credentials Grant

Password Grant

Advanced Features

User Defined Views

SSL Configuration

Firewall and Proxy

Query Processing

Logging

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

SuiteCRM Connector for CData Sync 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