Adding a Connection to Pipedrive

To add a connection to Pipedrive:

1. In the application console, navigate to the Connections page.
2. At the Add Connections panel, select the icon for the connection you want to add.
3. If the Pipedrive icon is not available, click the Add More icon to download and install the Pipedrive 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 Pipedrive

Pipedrive offers two ways to connect and authenticate: Basic and OAuth.

Basic Authentication

To authenticate via Basic authentication:

1. Obtain an API Token:
   1. Open the Pipedrive portal.
   2. At the top right corner of the page, click the account name. Pipedrive displays a drop-down list.
   3. Navigate to Company Settings > Personal Preferences > API > Generate Token.
   4. Record the value of the generated API token. Also, note the CompanyDomain,which is visible in the PipeDrive HomePage URL. (This is the company's developer sandbox URL.)
2. Set these connection properties:
   • APIToken: The value for the API token you just obtained.
   • CompanyDomain: The CompanyDomain from the developer sandbox URL.
   • AuthScheme: Basic.

3. Log in with the approved user name and password.

The API Token is stored in the Pipedrive portal. To retrieve it, click the company name, then use the drop-down list to navigate to Company Settings > Personal Preferences > API.

OAuth Authentication

If you do not have access to the user name and password or do not want to require them, use the OAuth user consent flow. To enable this authentication from all OAuth flows, you must set AuthScheme to OAuth and create a custom OAuth application.

The following subsections describe how to authenticate to Pipedrive from three common authentication flows. For information about how to create a custom OAuth application, see Creating a Custom OAuth Application. For a complete list of connection string properties available in Pipedrive, see Connection.

Automatic refresh of the OAuth access token:

To have the Sync App automatically refresh the OAuth access token:

1. Before connecting to data for the first time, set these connection parameters:
   • InitiateOAuth: REFRESH.
   • OAuthClientId: The client Id in your custom OAuth application settings.
   • OAuthClientSecret: The client secret in your custom OAuth application settings.
   • OAuthAccessToken: The access token returned by GetOAuthAccessToken.
   • OAuthSettingsLocation: The path where you want the Sync App to save the OAuth values, which persist across connections.
2. On subsequent data connections, set:
   • InitiateOAuth
   • OAuthSettingsLocation

Manual refresh of the OAuth access token:

The only value needed to manually refresh the OAuth access token is the OAuth refresh token.

1. To manually refresh the OAuthAccessToken after the ExpiresIn period (returned by GetOAuthAccessToken) has elapsed, call the RefreshOAuthAccessToken stored procedure.
2. Set these connection properties:
   • OAuthClientId: The Client Id in your custom OAuth application settings.
   • OAuthClientSecret: The Client Secret in your custom OAuth application settings.

3. Call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken.
4. After the new tokens have been retrieved, set the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken. This opens a new connection.

Store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Using V1 API

See V1 Data Model for the available entities in the V1 Data Model.

Using V2 API

See V2 Data Model for the available entities in the V2 Data Model.

Overview

This section shows the available API objects and provides more information on executing SQL to Pipedrive APIs.

Key Features

• The Sync App models Pipedrive entities like activities, leads, and products as relational tables, allowing you to write SQL to query and modify Pipedrive data.
• Stored procedures allow you to execute operations to Pipedrive
• Live connectivity to these objects means any changes to your Pipedrive account are immediately reflected when using the Sync App.

  Additionally, the Pipedrive API limits the number and combinations of columns that can be projected over the data or used to restrict the results returned.

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

Pipedrive Connector for CData Sync Tables

Name	Description
Activities	Get all Activities assigned to a particular User.
ActivityTypes	Get all ActivityTypes.
CallLogs	Returns all call logs assigned to a particular user.
DealFields	Returns data about all deal fields.
Deals	Get all deals.
DealsFollowers	Get details of deals followers.
DealsParticipants	Get details of deals participants.
DealsProducts	Get details of deals products.
Files	Returns data about all files.
Filters	Returns data about all filters.
Goals	Goals help your team meet your sales targets.
LeadLabels	Returns details of all Lead Labels.
Leads	Returns lead data.
MailThreads	Get, Update and Delete mail threads in a specified folder ordered by the most recent message within.
NoteComments	Create, Update, Delete and Get the comments associated with a note.
Notes	Returns all notes.
OrganizationFields	Returns data about all organization fields.
OrganizationRelationships	Gets all of the relationships.
Organizations	Get details of organizations
OrganizationsFollowers	Get details of organizations followers.
OrganizationsInternal	Get details of organizations.
PersonFields	Returns data about all person fields.
PersonFollowers	Get details of persons followers.
Persons	Get all details of persons.
Pipelines	Get all Pipelines.
ProductFields	Returns data about all product fields.
Products	Get details of Products
ProductsFollowers	Get details of products followers.
Roles	Returns all the role.
RolesAssignments	List assignments for a role.
RolesSetting	Returns all the roles settings.
Stages	Returns data about all stages.
Subscriptions	Returns details of an installment or a recurring Subscription
Users	Returns data about all users within the company

Get all Activities assigned to a particular User.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.
• Done supports the '=' operator.
• Type supports the '=, IN' operators.
• UserId supports the '=' operator.
• FilterId supports the '=' operator.
• StartDate supports the '=' operator.
• EndDate supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM Activities WHERE Id = 246

SELECT * FROM Activities WHERE Done = 0

SELECT * FROM  Activities WHERE Type IN ('deadline', 'call')

SELECT * FROM  Activities WHERE EndDate = '2021-12-24'
 
SELECT * FROM  Activities WHERE UserId = 8230170

INSERT

The columns that are not read-only can be inserted. Following is an example of how to insert into this table.

INSERT INTO ActivitiesAttendees#TEMP (EmailAddress) VALUES ('[email protected]')
INSERT INTO Activities (DueDate, DueTime, Duration, Dealid, Attendees) VALUES ('1994-10-12', '10:20', '02:00', 1, 'ActivitiesAttendees#TEMP')

UPDATE

UPDATE can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Activities SET DealId = 2 WHERE Id = 245

DELETE

DELETE can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM Activities WHERE Id = 246

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Activity Id.
ActiveFlag	Boolean	True		ActiveFlag.
AddTime	Datetime	True		AddTime.
AssignedToUserId	Integer	True		AssignedToUserId.
Attendees	String	False		Attendees of the Activity This can be either your existing Pipedrive contacts or an external email address.
BusyFlag	Boolean	True		Set the Activity as Busy or Free. The allowed values are true, false. The default value is true.
CalendarSync	String	True		CalendarSync.
CompanyId	Integer	True		CompanyId.
MeetingClient	String	True		MeetingClient.
MeetingId	String	True		MeetingId.
MeetingUrl	String	True		MeetingUrl.
CreatedByUserId	Integer	True		CreatedByUserId.
DealDropboxBcc	String	True		DealDropboxBcc.
DealId	Integer	False	Deals.Id	The ID of the Deal this Activity is associated with.
DealTitle	String	True		DealTitle.
Done	Boolean	True		Whether the Activity is done or not 0 = Not done 1 = Done If omitted returns both Done and Not done activities. The allowed values are 0, 1.
DueDate	Date	False		Due date of the Activity Format YYYY-MM-DD
DueTime	Time	False		Due time of the Activity in UTC Format HH:MM
Duration	Time	True		Duration of the Activity Format HH:MM
FileCleanName	String	True		FileCleanName.
FileId	String	True	Files.Id	FileId.
FileUrl	String	True		FileUrl.
GcalEventId	String	True		GcalEventId.
GoogleCalendarEtag	String	True		GoogleCalendarEtag.
GoogleCalendarId	String	True		GoogleCalendarId.
LastNotificationTime	Datetime	True		LastNotificationTime.
LastNotificationUserId	Integer	True		LastNotificationUserId.
LeadId	String	True	Leads.Id	LeadId.
Location	String	False		The address of the Activity.
AdminAreaLevel1	String	True		AdminAreaLevel1.
AdminAreaLevel2	String	True		AdminAreaLevel2.
LocationCountry	String	True		LocationCountry.
FormattedAddress	String	True		FormattedAddress.
LocationLat	Double	True		LocationLat.
LocationLocality	String	True		LocationLocality.
LocationLong	Double	True		LocationLong.
PostalCode	String	True		PostalCode.
LocationRoute	String	True		LocationRoute.
StreetNumber	String	True		StreetNumber.
Sublocality	String	True		Sublocality.
Subpremise	String	True		Subpremise.
MarkedAsDoneTime	Datetime	True		MarkedAsDoneTime.
Note	String	False		Note of the Activity HTML format.
NotificationLanguageId	Integer	True		NotificationLanguageId.
OrgId	Integer	False		The ID of the Organization this Activity is associated with.
OrgName	String	True		OrgName.
OwnerName	String	True		OwnerName.
Participants	String	False		List of multiple Persons participants this Activity is associated with If omitted single participant from person_id field is used.
PersonDropboxBcc	String	True		PersonDropboxBcc.
PersonId	Integer	False	Persons.Id	The ID of the Person this Activity is associated with.
PersonName	String	True		PersonName.
PublicDescription	String	False		Additional details about the Activity that is synced to your external calendar Unlike the note added to the Activity the description is publicly visible to any guests added to the Activity.
RecMasterActivityId	String	True		RecMasterActivityId.
RecRule	String	True		RecRule.
RecRuleExtension	String	True		RecRuleExtension.
ReferenceId	Integer	True		ReferenceId.
ReferenceType	String	True		ReferenceType.
Series	String	True		Series.
SourceTimezone	String	True		SourceTimezone.
Subject	String	False		Subject of the Activity. The default value is Call.
Type	String	True		Type of the Activity This is in correlation with the key_string parameter of ActivityTypes When value for type is not set, it will be given a default value Call. The default value is Call.
UpdateTime	Datetime	True		UpdateTime.
UpdateUserId	Integer	True		The ID of the User whose Activities will be fetched If omitted the User associated with the API token will be used If 0 Activities for all company Users will be fetched based on the permission sets.
UserId	Integer	False	Users.Id	UserId.

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
FilterId	Integer	The ID of the Filter to use.
StartDate	String	Use the Activity due date where you wish to begin fetching Activities from Insert due date in YYYY-MM-DD format.
EndDate	String	Use the Activity due date where you wish to stop fetching Activities from Insert due date in YYYY-MM-DD format.

Get all ActivityTypes.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM Activities WHERE Id = 9

INSERT

Insert can be executed by specifying the Name and IconKey columns. The columns that are not required can be inserted optionally. For example:

INSERT INTO ActivityTypes (Name, IconKey, color, IsCustomFlag, KeyString) VALUES ('test42', 'task', 'FFFFFF', 'true', 'call')

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE ActivityTypes SET IconKey = 'email' WHERE id = 7

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM ActivityTypes WHERE Id = 2

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The ID of the ActivityType.
ActiveFlag	Boolean	True		ActiveFlag.
AddTime	Datetime	True		AddTime.
Color	String	False		A designated color for the ActivityType in 6-character HEX format.
IconKey	String	False		Icon graphic to use for representing this activity type. The allowed values are task, email, meeting, deadline, call, lunch, calendar, downarrow, document, smartphone, camera, scissors, cogs, bubble, uparrow, checkbox, signpost, shuffle, addressbook, linegraph, picture, car, world, search, clip, sound, brush, key, padlock, pricetag, suitcase, finish, plane, loop, wifi, truck, cart, bulb, bell, presentation.
IsCustomFlag	Boolean	True		IsCustomFlag.
KeyString	String	True		KeyString.
Name	String	False		The name of the ActivityType.
OrderNr	Integer	False		An order number for this ActivityType. Order numbers should be used to order the types in the ActivityType selections.
UpdateTime	Datetime	True		UpdateTime.

Returns all call logs assigned to a particular user.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM CallLogs WHERE Id = 'cf75de9e4cbcb4a33658ad40561e3230'                

INSERT

Insert can be executed by specifying the Outcome, ToPhoneNumber, StartTime and EndTime columns. The columns that are not required can be inserted optionally.

INSERT INTO CallLogs (Outcome, StartTime, EndTime, Duration, FromPhoneNumber, ToPhoneNumber, UserId, OrgId) VALUES ('connected', '2021-12-15', '2021-12-16', '140', '984656646', '9846566456', '8230170', '6')

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM CallLogs WHERE Id = '8381cea5da671fa16a1eb63af15e5ec4'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		ID.
ActivityId	Integer	False		ActivityId.
CompanyId	Integer	True		CompanyId.
DealId	Integer	False		The ID of the Deal this call is associated with.
Duration	String	False		Call duration in seconds.
EndTime	Datetime	False		The date and time of the end of the call in UTC. Format: YYYY-MM-DD HH:MM:SS
FromPhoneNumber	String	False		The number that made the call.
HasRecording	Boolean	True		HasRecording.
Note	String	False		Note for the call log in HTML format.
OrgId	Integer	False		The ID of the Organization this call is associated with.
Outcome	String	False		Describes the outcome of the call. The allowed values are connected, no_answer, left_message, left_voicemail, wrong_number, busy.
PersonId	Integer	False		The ID of the Person this call is associated with.
StartTime	Datetime	False		The date and time of the start of the call in UTC. Format: YYYY-MM-DD HH:MM:SS
Subject	String	False		Name of the activity this call is attached to.
ToPhoneNumber	String	False		The number called.
UserId	Integer	False		The ID of the owner of the call log.

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
LeadId	String	Only used for insert. The ID of the lead the call log is associated with in UUID format (optional).

Returns data about all deal fields.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM DealFields WHERE Id = 12478                  

INSERT

Insert can be executed by specifying the Name and IconKey columns. The columns that are not required can be inserted optionally. For example:

INSERT INTO DealFields (Name, AddVisibleFlag, FieldType) VALUES ('test43', 'false', 'address')

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE DealFields SET Name = 'test44' WHERE Id = '12500'

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM DealFields WHERE Id = 12500

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		ID of the field.
ActiveFlag	Boolean	True		Whether the field is available in 'add new' modal or not (both in web and mobile app). The default value is true.
AddTime	Datetime	True		AddTime.
AddVisibleFlag	Boolean	False		AddVisibleFlag.
BulkEditAllowed	Boolean	True		BulkEditAllowed.
DetailsVisibleFlag	Boolean	True		DetailsVisibleFlag.
EditFlag	Boolean	True		EditFlag.
FieldType	String	False		Type of the field. The allowed values are address, date, daterange, double, enum, monetary, org, people, phone, set, text, time, timerange, user, varchar, varchar_auto, visible_to.
FilteringAllowed	Boolean	True		FilteringAllowed.
ImportantFlag	Boolean	True		ImportantFlag.
IndexVisibleFlag	Boolean	True		IndexVisibleFlag.
Key	String	True		Key.
LastUpdatedByUserId	String	True		LastUpdatedByUserId.
MandatoryFlag	Boolean	True		MandatoryFlag.
Name	String	False		Name of the field.
Options	String	False		Options.
OrderNr	Integer	True		OrderNr.
SearchableFlag	Boolean	True		SearchableFlag.
SortableFlag	Boolean	True		SortableFlag.
UpdateTime	Datetime	True		UpdateTime.

Get all deals.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.
• StageId supports the '=' operator.
• Status supports the '=' operator.
• FilterId supports the '=' operator.
• UserId supports the '=' operator.
• OwnedByYou supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM Deals WHERE Id = 14

SELECT * FROM Deals WHERE StageId = 1

SELECT * FROM Deals WHERE  Status = 'Open'

SELECT * FROM Deals WHERE FilterId = 1       

SELECT * FROM Deals WHERE OwnedByYou = 1                

INSERT

Insert can be executed by specifying the Title columns.The columns that are not read-only can be inserted. Following is an example of how to insert into this table.

INSERT INTO Deals (title, PersonId) VALUES ('tetsptest', 6203)

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Deals SET Title = 'test' WHERE id = 15

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM Deals WHERE id = 15

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Deals id.
Active	Boolean	True		Active.
ActivitiesCount	Integer	True		Activities Count.
AddTime	Datetime	False		AddTime.
CcEmail	String	True		Cc Email.
CloseTime	String	True		Close Time.
CreatorActiveFlag	Boolean	True		Creator ActiveFlag.
CreatorEmail	String	True		Creator Email.
CreatorHasPic	Boolean	True		Creator HasPic.
CreatorId	Integer	True		Creator Id.
CreatorName	String	True		Creator Name.
CreatorPicHash	String	True		CreatorPicHash.
Creatorvalue	Integer	True		Creatorvalue.
Currency	String	False		Currency.
CustomeField	String	True		CustomeField you will get the result of this column only when criteria filter title is used.
Deleted	Boolean	True		Deleted.
DoneActivitiesCount	Integer	True		Done Activities Count.
EmailMessagesCount	Integer	True		Email Messages Count.
ExpectedCloseDate	Date	False		Expected Close Date.
FilesCount	Integer	True		Files Count.
FirstWonTime	Datetime	True		First Won Time.
FollowersCount	Integer	True		Followers Count.
FormattedValue	String	True		Formatted Value.
FormattedWeightedValue	String	True		Formatted Weighted Value.
Label	String	True		Label.
LastActivityDate	String	True		Last Activity Date.
LastActivityId	String	True		Last Activity Id.
LastIncomingMailTime	Datetime	True		Last Incoming MailTime.
LastOutgoingMailTime	Datetime	True		Last OutgoingMail Time.
LostReason	String	False		Lost Reason.
LostTime	String	True		Lost Time.
NextActivityDate	Date	True		Next Activity Date.
NextActivityDuration	Time	True		Next Activity Duration.
NextActivityId	Integer	True		Next Activity Id.
NextActivityNote	String	True		Next Activity Note.
NextActivitySubject	String	True		Next Activity Subject.
NextActivityTime	Time	True		Next Activity Time.
NextActivityType	String	True		Next Activity Type.
NotesCount	Integer	True		NotesCount.
Notes	String	True		Notes.
OrgHidden	Boolean	True		Org Hidden.
OrgActiveFlag	Boolean	True		Org ActiveFlag.
OrgAddress	String	True		Org Address.
OrgCcEmail	String	True		Org CcEmail.
OrgName	String	True		Org Name.
OrgOwnerId	Integer	True		Org OwnerId.
OrgPeopleCount	Integer	True		Org PeopleCount.
OrgValue	Integer	True		Org Value.
OwnerName	String	True		Owner Name.
OwnerId	String	True		Owner Id you will get the result of this column only when criteria filter title is used.
ParticipantsCount	Integer	True		Participants Count.
PersonHidden	Boolean	True		Person Hidden.
PersonActiveFlag	Boolean	True		Person Active Flag.
PersonEmail	String	True		Person Email.
PersonName	String	True		Person Name.
PersonPhone	String	True		Person Phone.
Personvalue	Integer	True		Personvalue.
PipelineId	Integer	True		PipelineId.
Probability	String	True		Probability.
ProductsCount	Integer	True		Products Count.
RottenTime	String	True		RottenTime.
ResultScore	String	True		Result score you will get the result of this column only when criteria filter title is used.
StageChangeTime	Datetime	True		Stage Change Time.
StageId	Integer	True	Stages.Id	StageId.
StageName	String	True		StageName.
StageOrderNr	Integer	True		Stage OrderNr.
Status	String	False		Status. The allowed values are open, won, lost, deleted, all_not_deleted. The default value is all_not_deleted.
Title	String	False		Title.
Type	String	True		Type you will get the result of this column only when criteria filter title is used.
UndoneActivitiesCount	Integer	True		Undone Activities Count.
UpdateTime	Datetime	True		Update Time.
UserActiveFlag	Boolean	True		User ActiveFlag.
UserEmail	String	True		User Email.
UserHasPic	Boolean	True		User HasPic.
UserId	Integer	False		User Id.
UserName	String	True		User Name.
UserPicHash	String	True		User PicHash.
Uservalue	Integer	True		User value.
Value	Integer	False		Value of the deal. The default value is 0.
VisibleTo	String	False		Visibility of the deal. The allowed values are 1, 3, 5, 7.
WeightedValue	Integer	True		Visible To.
WeightedValueCurrency	String	True		Weighted Value Currency.
OrderOfStages	Integer	True		You will get the result of this column only when criteria filter id is used.
AverageTimeToWonY	Integer	True		You will get the result of this column only when criteria filter id is used.
AverageTimeToWonM	Integer	True		You will get the result of this column only when criteria filter id is used.
AverageTimeToWond	Integer	True		You will get the result of this column only when criteria filter id is used.
AverageTimeToWonh	Integer	True		You will get the result of this column only when criteria filter id is used.
AverageTimeToWons	Integer	True		You will get the result of this column only when criteria filter id is used.
AverageTimeToWoni	Integer	True		You will get the result of this column only when criteria filter id is used.
AverageTotalSeconds	Integer	True		You will get the result of this column only when criteria filter id is used.
AverageStageProgress	Integer	True		You will get the result of this column only when criteria filter id is used.
AgeY	Integer	True		You will get the result of this column only when criteria filter id is used.
AgeM	Integer	True		You will get the result of this column only when criteria filter id is used.
Aged	Integer	True		You will get the result of this column only when criteria filter id is used.
Ageh	Integer	True		You will get the result of this column only when criteria filter id is used.
Ages	Integer	True		You will get the result of this column only when criteria filter id is used.
Agei	Integer	True		You will get the result of this column only when criteria filter id is used.
AgeTotalSeconds	Integer	True		You will get the result of this column only when criteria filter id is used.
WonTime	Datetime	True		Won time.

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
FilterId	Integer	Filter Id
OwnedByYou	Integer	Owned By You The allowed values are 0, 1.
PersonId	Integer	Person Id. Only used when performing INSERT or UPDATE operation.
OrgId	Integer	Org Id. Only used when performing INSERT or UPDATE operation.

Get details of deals followers.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.

For example, the following query is processed server side:

    SELECT * FROM DealsFollowers WHERE Id = 2                 

INSERT

Insert can be executed by specifying the Name and IconKey columns. The columns that are not required can be inserted optionally. For example:

INSERT INTO DealsFollowers (userid, id) VALUES (8230170, 8)

DELETE

Delete can be executed by specifying the Id, DealId in the WHERE Clause. For example:

DELETE FROM DealsFollowers WHERE Id = 1 AND Dealid = 1

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id.
AddTime	Datetime	True		AddTime.
UserId	Integer	False	Users.Id	User Id.
DealId	Integer	True	Deals.Id	Deal Id.

Get details of deals participants.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM DealsParticipants WHERE Id = 9 

INSERT

Insert can be executed by specifying the Content and PersonId columns. The columns that are not required can be inserted optionally. For example:

INSERT INTO DealsParticipants (Id, Personid) VALUES (4, 6) 

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM DealsParticipants WHERE id = 14 AND ItemDealId = 17

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id.
ActiveFlag	Boolean	True		ActiveFlag.
AddTime	Datetime	True		AddTime.
AddedByactiveFlag	Boolean	True		AddedByactiveFlag.
AddedByemail	String	True		AddedByemail.
AddedByhasPic	Integer	True		AddedByhasPic.
AddedByid	Integer	True		AddedByid.
AddedByname	String	True		AddedByname.
AddedBypicHash	String	True		AddedBypicHash.
AddedByvalue	Integer	True		Added_byvalue.
PersonActiveFlag	Boolean	True		PersonActiveFlag.
ActivitiesCount	Integer	True		ActivitiesCount.
PersonAddTime	Datetime	True		AddTime.
CcEmail	String	True		CcEmail.
ClosedDealsCount	Integer	True		ClosedDealsCount.
CompanyId	Integer	True		CompanyId.
DoneActivitiesCount	Integer	True		DoneActivitiesCount.
Email	String	True		Email.
EmailMessagesCount	Integer	True		EmailMessagesCount.
FilesCount	Integer	True		FilesCount.
FirstChar	String	True		FirstChar.
FirstName	String	True		FirstName.
FollowersCount	Integer	True		FollowersCount.
Personlabel	String	True		Personlabel.
LastActivityDate	Date	True		LastActivityDate.
LastActivityId	Integer	True		LastActivityId.
LastincomingMailTime	String	True		LastincomingMailTime.
Lastname	String	True		Lastname.
LastoutgoingMailTime	String	True		LastoutgoingMailTime.
LostdealsCount	Integer	True		LostdealsCount.
Name	String	True		Name.
NextActivityDate	Date	True		nextActivityDate.
NextActivityId	Integer	True		nextActivityId.
NextActivityTime	String	True		nextActivityTime.
NotesCount	Integer	True		NotesCount.
OpenDealsCount	Integer	True		OpenDealsCount.
OrgActiveFlag	Boolean	True		OrgActiveFlag.
OrgAddress	String	True		OrgAddress.
OrgCcEmail	String	True		OrgCcEmail.
OrgName	String	True		OrgName.
OrgownerId	Integer	True		OrgownerId.
OrgpeopleCount	Integer	True		OrgpeopleCount.
OrgId	Integer	True		OrgId.
OwnerActiveFlag	Boolean	True		OwnerActiveFlag.
OwnerEmail	String	True		OwnerEmail.
OwnerHasPic	Integer	True		OwnerHasPic.
OwnerId	Integer	True		OwnerId.
OwnerName	String	True		OwnerName.
OwnerPicHash	String	True		OwnerPicHash.
OwnerValue	Integer	True		OwnerValue.
ParticipantClosedDealsCount	Integer	True		ClosedDealsCount.
ParticipantOpenDealsCount	Integer	True		OpenDealsCount.
Phone	String	True		Phone.
PictureId	String	True		PictureId.
RelatedclosedDealsCount	Integer	True		RelatedclosedDealsCount.
RelatedlostDealsCount	Integer	True		RelatedlostDealsCount.
RelatedopenDealsCount	Integer	True		RelatedopenDealsCount.
RelatedwonDealsCount	Integer	True		RelatedwonDealsCount.
SyncNeeded	Boolean	True		SyncNeeded.
UndoneActivitiesCount	Integer	True		UndoneActivitiesCount.
UpdateTime	Datetime	True		UpdateTime.
VisibleTo	String	True		visibleTo.
WonDealsCount	Integer	True		WonDealsCount.
PersonIdActiveFlag	Boolean	True		PersonactiveFlag.
Personemail	String	True		Personemail.
Personname	String	True		Personname.
Personphone	String	True		Personphone.
PersonValues	Integer	False		PersonValues.
ItemDealId	Integer	True	Deals.id	ItemDealId.
ItemTitle	String	True		ItemTitle.
ItemId	Integer	True		ItemId.
ItemType	String	True		ItemType.
PersonId	Integer	False	Persons.Id	Person Id.
PersonOrgName	String	True		Person OrgName.
PersonOwnerName	String	True		Person OwnerName.

Get details of deals products.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM DealsProducts WHERE Id = 9 

INSERT

Insert can be executed by specifying the Content and PersonId columns. The columns that are not required can be inserted optionally. For example:

INSERT INTO DealsProducts (DealId, ProductId, ItemPrice, quantity) VALUES (2, 2, 20000, 1)

UPDATE

Update can be executed by specifying the Id and Dealid in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE DealsProducts SET quantity = 20 WHERE id = 15 AND Dealid = 2

DELETE

Delete can be executed by specifying the Id, DealId in the WHERE Clause. For example:

DELETE FROM DealsProducts WHERE id = 15 AND Dealid = 2

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id.
ActiveFlag	Boolean	True		ActiveFlag.
AddTime	Datetime	True		AddTime.
Comments	String	False		Any textual comment associated with this product-deal attachment.
Currency	String	True		Currency.
DealId	Integer	True	Deals.Id	Deal id.
DiscountPercentage	Double	True		Discount %. The default value is 0.
Duration	Integer	True		Duration of the product. The default value is 1.
DurationUnit	String	True		DurationUnit.
EnabledFlag	Boolean	False		Whether the product is enabled on the deal or not. The allowed values are 0, 1.
ItemPrice	Integer	False		Price at which this product will be added to the deal.
LastEdit	String	True		LastEdit.
Name	String	True		Name.
OrderNr	Integer	True		OrderNr.
ProductActiveFlag	Boolean	True		Product ActiveFlag.
ProductAddTime	Datetime	True		Product AddTime.
Category	String	True		Category.
code	String	True		Code.
description	String	True		Description.
FilesCount	String	True		FilesCount.
FirstChar	String	True		FirstChar.
ProductsId	Integer	True		ProductsId.
ProductName	String	True		ProductName.
OwnerActiveFlag	Boolean	True		OwnerActiveFlag.
OwnerEmail	String	True		OwnerEmail.
OwnerHasPic	Boolean	True		OwnerHasPic.
OwnerId	Integer	True		OwnerId.
OwnerName	String	True		OwnerName.
OwnerPicHash	String	True		OwnerPicHash.
OwnerValue	Integer	True		OwnerValue.
EURCost	Integer	True		EURCost.
EURCurrency	String	True		EURCurrency.
EURId	Integer	True		EURId.
EUROverheadCost	Integer	True		EUROverheadCost.
EURPrice	Integer	True		EURPrice.
EURProductId	Integer	True		EURProductId.
Selectable	Boolean	True		selectable.
ProductTax	Integer	True		ProductTax.
unit	String	True		unit.
UpdateTime	Datetime	True		UpdateTime.
VisibleTo	String	True		VisibleTo.
ProductId	Integer	False	Products.id	ID of the product that will be attached.
VariationId	String	False		ID of the product variation.
Quantity	Integer	False		How many items of this product will be added to the deal.
QuantityFormatted	String	True		QuantityFormatted.
Sum	Double	True		Sum.
SumFormatted	String	True		SumFormatted.
SumNoDiscount	Integer	True		SumNoDiscount.
Tax	Integer	False		Tax percentage. The default value is 0.

Returns data about all files.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM Files WHERE Id = 400

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Files SET Name = 'Updating PipeDrive Pipelines1' WHERE Id = 405

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM Files WHERE Id = 400

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		ID of the file.
ActiveFlag	Boolean	True		ActiveFlag.
ActivityId	String	True		ID of the activity to associate file.
AddTime	Datetime	True		AddTime.
Cid	String	True		Cid.
DealId	String	True		ID of the deal to associate file.
DealName	String	True		DealName.
Description	String	False		Description of the file.
FileName	String	True		FileName.
FileSize	Integer	True		FileSize.
FileType	String	True		FileType.
InlineFlag	Boolean	True		InlineFlag.
LogId	String	True		LogId.
MailMessageId	String	True		MailMessageId.
MailTemplateId	String	True		MailTemplateId.
Name	String	False		Visible name of the file.
OrgId	String	True		ID of the organization to associate file.
OrgName	String	True		OrgName.
PersonId	Integer	True		ID of the person to associate file.
PersonName	String	True		PersonName.
ProductId	String	True		ID of the product to associate file.
ProductName	String	True		ProductName.
RemoteId	String	True		RemoteId.
RemoteLocation	String	True		RemoteLocation.
S3Bucket	String	True		S3Bucket.
UpdateTime	Datetime	True		UpdateTime.
Url	String	True		Url.
UserId	Integer	True		UserId.
LeadId	String	True		LeadId.
LeadName	String	True		LeadName.

Returns data about all filters.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.
• Type supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM Filters WHERE Id = 39

INSERT

Insert can be executed by specifying the Name, Conditions and Type columns. The columns that are not required can be inserted optionally. For example:

INSERT INTO Filters (Name, Conditions, Type) VALUES ('Indias Filter', '{"glue": "and","conditions": [{"glue": "and","conditions": [{"object": "organization","field_id": "4020"}]}]}', 'deals')

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Filters SET Name = 'Updating Pipedrive filters', Conditions = '{"glue": "and","conditions": [{"glue": "or","conditions": [{"object": "organization123","field_id": "4021"}]}]}' WHERE Id = 39

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM Filters WHERE Id = 10

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		The ID of the filter.
ActiveFlag	Boolean	True		ActiveFlag.
AddTime	Datetime	True		AddTime.
CustomViewId	String	True		CustomViewId.
Name	String	False		The name of the filter.
TemporaryFlag	String	True		TemporaryFlag.
Type	String	False		The types of filters to fetch. The allowed values are deals, org, people, products, activity.
UpdateTime	String	True		UpdateTime.
UserId	Integer	True		UserId.
VisibleTo	Integer	True		VisibleTo.

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
Conditions	String	The conditions of the filter as a JSON object.

Goals help your team meet your sales targets.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.
• Title supports the '=' operator.
• Name supports the '=' operator.
• Type supports the '=' operator.
• Title supports the '=' operator.
• PipelineId supports the '=' operator.
• ActivityTypeId supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM Goals WHERE id = 'c924154b747f214228a906d3de079801' AND DurationEnd = '2022-02-03' AND DurationStart = '2022-01-01'

SELECT * FROM Goals WHERE Title = 'test'

SELECT * FROM Goals WHERE Type = 'test'

SELECT * FROM Goals WHERE TypeName = 'test'

INSERT

Insert can be executed by specifying the Content and PersonId columns. The columns that are not required can be inserted optionally. For example:

INSERT INTO Goals (Title, AssigneeId, AssigneeType, DurationStart, DurationEnd, Target, CurrencyId, TrackingMetric, Iterval, TypeName, PipelineId) VALUES ('test', 13815887, 'person', '2022-01-21', '2022-02-03', 50, 1, 'sum', 'monthly', 'deals_started', 'null')

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Goals SET title = 'test' WHERE id = 'c924154b747f214228a906d3de079801'

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM Goals WHERE id = 'c924154b747f214228a906d3de079801'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		Id.
AssigneeId	Integer	False		ID of the user who's goal to fetch.
AssigneeType	String	False		Type of the goal's assignee. If provided, everyone's goals will be returned. The allowed values are person, team, company.
DurationEnd	Date	False		End date of the period for which to find goals.
DurationStart	Date	False		Start date of the period for which to find goals.
Target	Integer	False		Numeric value of the outcome. If provided, everyone's goals will be returned.
CurrencyId	String	False		Numeric value of the outcome. If provided, everyone's goals will be returned.
TrackingMetric	String	False		Tracking metric of the expected outcome of the goal. If provided, everyone's goals will be returned.
Interval	String	False		Interval of the goal. The allowed values are weekly, monthly, quarterly, yearly.
IsActive	Boolean	False		Whether goal is active or not. The default value is true.
OwnerId	Integer	True		OwnerId.
ReportIds	String	True		ReportIds.
Title	String	False		Title of the goal.
TypeName	String	False		Type of the goal. If provided, everyone's goals will be returned. The allowed values are deals_won, deals_progressed, activities_completed, activities_added, deals_started.
PipelineId	String	False		ID of the pipeline.
ActivityTypeId	String	False		ID of the activity_type.

Returns details of all Lead Labels.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM LeadLabels

INSERT

Insert can be executed by specifying the Name and Color columns. The columns that are not required can be inserted optionally. For example:

INSERT INTO LeadLabels (Name, Color) VALUES ('BangaloreCdataIndia123', 'blue')

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE LeadLabels SET Name = 'I am updating content' WHERE id = '28093520-743a-11ec-96e6-031cfba07e9a'

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM LeadLabels WHERE Id = '28093520-743a-11ec-96e6-031cfba07e9a'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The ID of the Lead Label.
AddTime	Datetime	True		AddTime.
Color	String	False		The color of the label. The allowed values are green, blue, red, yellow, purple, gray.
Name	String	False		The name of the Lead Label.
UpdateTime	Datetime	True		UpdateTime.

Returns lead data.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.
• SearchByEmail supports the '=' operator.
• OwnerId supports the '=' operator.
• PersonId supports the '=' operator.
• OrganizationId supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM Leads

SELECT * FROM Leads WHERE Id = 'a300ea00-5d6c-11ec-9270-93cbb0be1eed'

SELECT * FROM Leads WHERE SearchByEmail = 'all'

INSERT

Insert can be executed by specifying the Title column. The columns that are not required can be inserted optionally. For example:

INSERT INTO Leads (Title, Personid, Visibleto, ExpectedCloseDate) VALUES ('CData123', 1, 1, '2022-01-01')

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Leads SET Title = 'CdataIndia' WHERE Id = 'bf1bb1e0-6e13-11ec-b981-a127469657bd'

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM Leads WHERE Id = 'bf1bb1e0-6e13-11ec-b981-a127469657bd'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The ID of the Lead.
Addtime	Datetime	True		Addtime.
CcEmail	String	True		Ccemail.
CreatorId	Integer	True		Creatorid.
ExpectedCloseDate	String	False		The date of when the Deal which will be created from the Lead is expected to be closed.
Isarchived	Boolean	True		A flag indicating whether the Lead is archived or not.
Labelids	String	False		The IDs of the Lead Labels which will be associated with the Lead.
NextactivityId	Integer	True		Nextactivityid.
OrganizationId	String	False		The ID of an Organization which this Lead will be linked to.
OwnerId	Integer	False		The ID of the User which will be the owner of the created Lead.
PersonId	Integer	False		The ID of a Person which this Lead will be linked to.
Sourcename	String	False		Sourcename.
Title	String	False		The name of the Lead.
Updatetime	Datetime	True		UpdateTime.
Amount	Integer	False		The potential value of the Lead.
Currency	String	True		The currency value of the Lead.
Visibleto	String	False		Visibility of the Lead. The allowed values are 1, 3, 5, 7.
Wasseen	Boolean	False		A flag indicating whether the Lead was seen by someone in the Pipedrive UI.

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
SearchByEmail	Integer	Filtering based on archived status of a Lead. If not provided, All is used. The allowed values are archived, not_archived, all. The default value is all.

Get, Update and Delete mail threads in a specified folder ordered by the most recent message within.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.
• Folder supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM MailThreads WHERE Folder = 'inbox'

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE MailThreads SET Subject = 'test' WHERE Id = 145

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM MailThreads WHERE Id = 145

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		ID of the Mail Threads.
PartiesTo	String	True		To.
PartiesFrom	String	True		From.
DraftParties	String	True		Draft Parties.
Folders	String	True		Folders.
AccountId	String	True		Account Id.
UserId	Integer	True		User Id.
Version	Integer	True		Version.
Subject	String	True		Subject.
Snippet	String	True		Snippet.
SnippetDraft	String	True		SnippetDraft.
SnippetSent	String	True		SnippetSent.
HasAttachmentsFlag	Integer	True		HasAttachmentsFlag.
HasInlineAttachmentsFlag	Integer	True		HasInlineAttachmentsFlag.
HasRealAttachmentsFlag	Integer	True		HasRealAttachmentsFlag.
HasDraftFlag	Integer	True		HasDraftFlag.
HasSentFlag	Integer	True		HasSentFlag.
ArchivedFlag	Integer	True		ArchivedFlag.
DeletedFlag	Integer	True		DeletedFlag.
SyncedFlag	Integer	True		SyncedFlag.
ExternalDeletedFlag	Integer	True		ExternalDeletedFlag.
SmartBccFlag	Integer	True		SmartBccFlag.
FirstMessageToMeFlag	Integer	True		FirstMessageToMeFlag.
MailLinkTrackingEnabledFlag	Integer	True		MailLinkTrackingEnabledFlag.
LastMessageTimestamp	String	True		LastMessageTimestamp.
FirstMessageTimestamp	String	True		FirstMessageTimestamp.
LastMessageSentTimestamp	String	True		LastMessageSentTimestamp.
LastMessageReceivedTimestamp	String	True		LastMessageReceivedTimestamp.
AddTime	String	True		AddTime.
UpdateTime	String	True		UpdateTime.
DealId	Integer	True		DealId.
DealStatus	Integer	True		DealStatus.
AllMessagesSentFlag	Integer	True		AllMessagesSentFlag.

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
Folder	String	The type of folder to fetch.

Create, Update, Delete and Get the comments associated with a note.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• NoteId supports the '=' operator.
• UUID supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM NoteComments WHERE NoteId = 14

INSERT

Insert can be executed by specifying the NoteId and Content columns.The columns that are not read-only can be inserted. Following is an example of how to insert into this table.

INSERT INTO NoteComments (NoteId,Content) VALUES (2, 'Test comment')

UPDATE

Update can be executed by specifying the NoteId and UUID in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE NoteComments SET Content='Test' where NoteId=1 and UUID = '53e0c79fdacf083d9fe1f799fdc0a206'

DELETE

Delete can be executed by specifying the NoteId and UUID in the WHERE Clause. For example:

DELETE FROM NoteComments WHERE NoteId=1 and UUID = '53e0c79fdacf083d9fe1f799fdc0a206'

Columns

Name	Type	ReadOnly	References	Description
NoteId [KEY]	Integer	True		ID of the note.
UUID	String	False		Comment Id.
ActiveFlag	Boolean	False		Active flag.
AddTime	String	False		Add time of the note comment.
CompanyId	Integer	True		Company Id.
Content	String	False		Content of the comment.
ObjectId	String	True		Object Id.
ObjectType	String	False		Object type.
UpdateTime	String	False		Update time of the comment.
UpdaterId	Integer	True		Updater Id.
UserId	Integer	True		User Id.

Returns all notes.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.
• UserId supports the '=' operator.
• LeadId supports the '=' operator.
• DealId supports the '=' operator.
• PersonId supports the '=' operator.
• OrgId supports the '=' operator.
• PinnedToLeadFlag supports the '=' operator.
• PinnedToDealFlag supports the '=' operator.
• PinnedToOrganizationFlag supports the '=' operator.
• PinnedToPersonFlag supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM Notes WHERE Id = 9                  

INSERT

Insert can be executed by specifying the Content and PersonId columns. The columns that are not required can be inserted optionally. For example:

INSERT INTO Notes (ActiveFlag, Content, PersonId, AddTime) VALUES ('true', 'this is frist notes', '8', '2021-12-31')

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Notes SET Content = 'I am updating content' WHERE id = 7

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM Notes WHERE Id = 5

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		ID of the note.
ActiveFlag	Boolean	False		ActiveFlag.
AddTime	Datetime	False		AddTime.
Content	String	False		Content.
DealTitle	String	True		DealTitle.
DealId	Integer	False		The ID of the deal which notes to fetch.
LastUpdateUserId	Integer	True		LastUpdateUserId.
LeadId	String	False		The ID of the lead which notes to fetch.
OrgId	Integer	False		The ID of the organization which notes to fetch.
OrganizationName	String	True		OrganizationName.
PersonName	String	True		PersonName.
PersonId	Integer	False		The ID of the person whose notes to fetch.
PinnedToDealFlag	Boolean	False		If set, then results are filtered by note to deal pinning state. The allowed values are 0, 1.
PinnedToLeadFlag	Boolean	False		If set, then results are filtered by note to lead pinning state. The allowed values are 0, 1.
PinnedToOrganizationFlag	Boolean	False		If set, then results are filtered by note to organization pinning state. The allowed values are 0, 1.
PinnedToPersonFlag	Boolean	False		If set, then results are filtered by note to person pinning state. The allowed values are 0, 1.
UpdateTime	Datetime	True		UpdateTime.
UserEmail	String	True		UserEmail.
UserIconUrl	String	True		UserIconUUserNamerl.
UserIsYou	Boolean	True		UserIsYouUserName.
UserName	String	True		UserName.
UserId	Integer	True		The ID of the user whose notes to fetch.

Returns data about all organization fields.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.
• OrgId supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM OrganizationRelationships WHERE id = 2
                 

INSERT

Insert can be executed by specifying the Type, RelOwnerOrgId and RelLinkedOrgId columns. The columns that are not required can be inserted optionally. For example:

INSERT INTO OrganizationRelationships (Type, RelOwnerOrgId, RelLinkedOrgId) VALUES ('parent', '8230170', '8230170')

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE OrganizationRelationships SET Type = 'related' WHERE id = 2

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM OrganizationRelationships WHERE Id = 2

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		ID of the field.
ActiveFlag	Boolean	True		ActiveFlag.
AddTime	Datetime	True		AddTime.
AddVisibleFlag	Boolean	False		Whether the field is available in 'add new' modal or not. The default value is true.
BulkEditAllowed	Boolean	True		BulkEditAllowed.
DetailsVisibleFlag	Boolean	True		DetailsVisibleFlag.
EditFlag	Boolean	True		EditFlag.
FieldType	String	False		Type of the field. The allowed values are address, date, daterange, double, enum, monetary, org, people, phone, set, text, time, timerange, user, varchar, varchar_auto, visible_to.
FilteringAllowed	Boolean	True		FilteringAllowed.
ImportantFlag	Boolean	True		ImportantFlag.
IndexVisibleFlag	Boolean	True		IndexVisibleFlag.
Key	String	True		Key.
LastUpdatedByUserId	String	True		LastUpdatedByUserId.
MandatoryFlag	Boolean	True		MandatoryFlag.
Name	String	False		Name of the field.
Options	String	False		Options.
OrderNr	Integer	True		OrderNr.
SearchableFlag	Boolean	True		SearchableFlag.
SortableFlag	Boolean	True		SortableFlag.
UpdateTime	Datetime	True		UpdateTime.

Gets all of the relationships.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM OrganizationRelationships WHERE Id = 9 

INSERT

Insert can be executed by specifying the Content and PersonId columns. The columns that are not required can be inserted optionally. For example:

INSERT INTO OrganizationRelationships (type, RelOwnerOrgId, RelLinkedOrgId) VALUES ('parent', 2, 3)

UPDATE

Update can be executed by specifying the Id and Dealid in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE OrganizationRelationships SET type = 'parent' WHERE Id = 10

DELETE

Delete can be executed by specifying the Id, DealId in the WHERE Clause. For example:

DELETE FROM OrganizationRelationships  WHERE Id = 10

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		ID of the organization relationship.
ActiveFlag	Boolean	True		ActiveFlag.
AddTime	Datetime	True		AddTime.
CalculatedRelatedOrgId	Integer	False		CalculatedRelatedOrgId.
CalculatedType	String	False		CalculatedType.
RelLinkedOrgIdActiveFlag	Boolean	False		RelLinkedOrgIdActiveFlag.
RelLinkedOrgIdAddress	String	False		RelLinkedOrgIdAddress.
RelLinkedOrgIdCcEmail	String	False		RelLinkedOrgIdCcEmail.
RelLinkedOrgIdname	String	False		RelLinkedOrgIdname.
RelLinkedOrgIdownerId	Integer	False		RelLinkedOrgIdownerId.
RelLinkedOrgIdPeopleCount	Integer	False		RelLinkedOrgIdPeopleCount.
RelLinkedOrgIdvalue	Integer	False		RelLinkedOrgIdvalue.
RelOwnerOrgIdActiveFlag	Boolean	False		RelOwnerOrgIdActiveFlag.
RelOwnerOrgIdAddress	String	False		RelOwnerOrgIdAddress.
RelOwnerOrgIdCcEmail	String	False		RelOwnerOrgIdCcEmail.
RelOwnerOrgIdName	String	False		RelOwnerOrgIdName.
RelOwnerOrgIdOwnerId	Integer	False		RelOwnerOrgIdOwnerId.
RelOwnerOrgIdPeopleCount	Integer	False		RelOwnerOrgIdPeopleCount.
OrgId	Integer	False		RelOwnerOrgIdValue.
RelatedOrganizationName	String	False		RelatedOrganizationName.
Type	String	False		The type of organization relationship. The allowed values are parent, related.
UpdateTime	Datetime	False		UpdateTime.

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
RelOwnerOrgId	Integer	Real Organization Id.
RelLinkedOrgId	Integer	Real Organization Id.

Get details of organizations

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.
• FirstChar supports the '=' operator.
• FilterId supports the '=' operator.
• UserId supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM Organizations WHERE Id = 14

SELECT * FROM Organizations WHERE FirstChar = 'c'

SELECT * FROM Organizations WHERE FilterId = 1
 
SELECT * FROM Organizations WHERE UserId = 1       

              

INSERT

Insert can be executed by specifying the Name columns.The columns that are not read-only can be inserted. Following is an example of how to insert into this table.

INSERT INTO Organizations (name) VALUES ('testpankaj')

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Organizations SET Name = 'test123' WHERE Id = 2495

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM Organizations WHERE id = 15

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id
ActiveFlag	Boolean	True		ActiveFlag
ActivitiesCount	Integer	True		ActivitiesCount
AddTime	Datetime	False		Optional creation date time of the organization in UTC. Requires admin user API token. Format: YYYY-MM-DD HH:MM:SS
Address	String	True		Address
AdminArealevel1	String	True		AdminArealevel1
AdminArealevel2	String	True		AdminArealevel2
Country	String	True		Country
FormattedAddress	String	True		FormattedAddress
Locality	String	True		Locality
PostalCode	String	True		PostalCode
Route	String	True		Route
StreetNumber	String	True		StreetNumber
Sublocality	String	True		Sublocality
Subpremise	String	True		Subpremise
CcEmail	String	True		CcEmail
CustomeField	String	True		CustomeField you will get the result of this column only when criteria filter title is used
ClosedDealsCount	Integer	True		ClosedDealsCount
CompanyId	Integer	True		CompanyId
CountryCode	String	True		CountryCode
DoneActivitiesCount	Integer	True		DoneActivitiesCount
EmailMessagesCount	Integer	True		EmailMessagesCount
FilesCount	Integer	True		FilesCount
FirstChar	String	True		FirstChar
FollowersCount	Integer	True		FollowersCount
Label	Integer	True		Label
LastActivityDate	Date	True		LastActivityDate
LastActivityId	Integer	True		LastActivityId
LostDealsCount	Integer	True		LostDealsCount
Name	String	False		Name
NextActivityDate	Date	True		NextActivityDate
NextActivityId	Integer	True		NextActivityId
NextActivityTime	Time	True		NextActivityTime
NotesCount	Integer	True		NotesCount
OpenDealsCount	Integer	True		OpenDealsCount
Owneractive_flag	Boolean	True		Owneractive_flag
OwnerEmail	String	True		OwnerEmail
OwnerHasPic	Integer	True		OwnerHasPic
OwnerId	Integer	False		OwnerId
OwnerName	String	True		OwnerName
OwnerPicHash	String	True		OwnerPicHash
OwnerIdValue	Integer	True		OwnerIdValue
OwnerName	String	True		OwnerName
PeopleCount	Integer	True		PeopleCount
PictureActiveFlag	Boolean	True		PictureActiveFlag
PictureAddTime	Datetime	True		PictureAddTime
PictureAddedByUserId	Integer	True		PictureAddedByUserId
PictureItemId	Integer	True		PictureItemId
PictureItemType	String	True		PictureItemType
Picture128	String	True		Picture128
Picture512	String	True		Picture512
PictureUpdateTime	String	True		PictureUpdateTime
PictureIid	Integer	True		PictureIid
RelatedClosedDealsCount	Integer	True		RelatedClosedDealsCount
RelatedLostDealsCount	Integer	True		RelatedLostDealsCount
RelatedOpenDealsCount	Integer	True		RelatedOpenDealsCount
RelatedWonDealsCount	Integer	True		RelatedWonDealsCount
Type	String	True		Type you will get the result of this column only when criteria filter title is used
UndoneActivitiesCount	Integer	True		UndoneActivitiesCount
UpdateTime	Datetime	True		UpdateTime
VisibleTo	Integer	False		Visibility of the organization The allowed values are 1, 3, 5, 7.
WonDealsCount	Integer	True		WonDealsCount

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
UserId	Integer	User Id
FilterId	Integer	Filter Id

Get details of organizations followers.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• OrgId supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM OrganizationsFollowers WHERE OrgId = 6 

INSERT

Insert can be executed by specifying the UserId, OrgId columns.The columns that are not read-only can be inserted. Following is an example of how to insert into this table.

INSERT INTO OrganizationsFollowers (UserId, OrgId) VALUES (8230170, 1)

DELETE

Delete can be executed by specifying the Id, OrgId in the WHERE Clause. For example:

DELETE FROM OrganizationsFollowers WHERE OrgId = 1 AND id = 1

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id.
AddTime	Datetime	True		AddTime.
OrgId	Integer	True		OrgId.
UserId	Integer	False		UserId.

Get details of organizations.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id.
ActiveFlag	Boolean	True		ActiveFlag.
ActivitiesCount	Integer	True		ActivitiesCount.
AddTime	Datetime	False		Optional creation date time of the organization in UTC. Requires admin user API token. Format: YYYY-MM-DD HH:MM:SS
Address	String	True		Address.
AdminArealevel1	String	True		AdminArealevel1.
AdminArealevel2	String	True		AdminArealevel2.
Country	String	True		Country.
FormattedAddress	String	True		FormattedAddress.
Locality	String	True		Locality.
PostalCode	String	True		PostalCode.
Route	String	True		Route.
StreetNumber	String	True		StreetNumber.
Sublocality	String	True		Sublocality.
Subpremise	String	True		Subpremise.
CcEmail	String	True		CcEmail.
CustomeField	String	True		CustomeField you will get the result of this column only when criteria filter title is used.
ClosedDealsCount	Integer	True		ClosedDealsCount.
CompanyId	Integer	True		CompanyId.
CountryCode	String	True		CountryCode.
DoneActivitiesCount	Integer	True		DoneActivitiesCount.
EmailMessagesCount	Integer	True		EmailMessagesCount.
FilesCount	Integer	True		FilesCount.
FirstChar	String	True		FirstChar.
FollowersCount	Integer	True		FollowersCount.
Label	Integer	True		Label.
LastActivityDate	Date	True		LastActivityDate.
LastActivityId	Integer	True		LastActivityId.
LostDealsCount	Integer	True		LostDealsCount.
Name	String	False		Name.
NextActivityDate	Date	True		NextActivityDate.
NextActivityId	Integer	True		NextActivityId.
NextActivityTime	Time	True		NextActivityTime.
NotesCount	Integer	True		NotesCount.
OpenDealsCount	Integer	True		OpenDealsCount.
OwneractiveFlag	Boolean	True		Owneractive_flag.
OwnerEmail	String	True		OwnerEmail.
OwnerHasPic	Boolean	True		OwnerHasPic.
OwnerId	Integer	False		OwnerId.
OwnerIdName	String	True		OwnerName.
OwnerPicHash	String	True		OwnerPicHash.
OwnerIdValue	Integer	True		OwnerIdValue.
PeopleCount	Integer	True		PeopleCount.
PictureActiveFlag	Boolean	True		PictureActiveFlag.
PictureAddTime	Datetime	True		PictureAddTime.
PictureAddedByUserId	Integer	True		PictureAddedByUserId.
PictureItemId	Integer	True		PictureItemId.
PictureItemType	String	True		PictureItemType.
Picture128	String	True		Picture128.
Picture512	String	True		Picture512.
PictureUpdateTime	String	True		PictureUpdateTime.
PictureId	Integer	True		PictureIid.
RelatedClosedDealsCount	Integer	True		RelatedClosedDealsCount.
RelatedLostDealsCount	Integer	True		RelatedLostDealsCount.
RelatedOpenDealsCount	Integer	True		RelatedOpenDealsCount.
RelatedWonDealsCount	Integer	True		RelatedWonDealsCount.
Type	String	True		Type you will get the result of this column only when criteria filter title is used.
UndoneActivitiesCount	Integer	True		UndoneActivitiesCount.
UpdateTime	Datetime	True		UpdateTime.
VisibleTo	Integer	False		Visibility of the organization. The allowed values are 1, 3, 5, 7.
WonDealsCount	Integer	True		WonDealsCount.
OwnerName	String	True		Owner 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
UserId	Integer	User Id.
FilterId	Integer	Filter Id.

Returns data about all person fields.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM PersonFields WHERE Id = 9039                 

INSERT

Insert can be executed by specifying the Name and FieldType columns. The columns that are not required can be inserted optionally. For example:

INSERT INTO PersonFields (Name, AddVisibleFlag, FieldType) VALUES ('NameCdataIndia', 'true', 'address')

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE PersonFields SET Name = 'My name just started here' WHERE Id = '9062'

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM PersonFields WHERE Id = 9040

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		ID of the field.
ActiveFlag	Boolean	True		ActiveFlag.
AddTime	Datetime	True		AddTime.
AddVisibleFlag	Boolean	False		Whether the field is available in 'add new' modal or not (both in web and mobile app). The default value is true.
BulkEditAllowed	Boolean	True		BulkEditAllowed.
DetailsVisibleFlag	Boolean	True		DetailsVisibleFlag.
EditFlag	Boolean	True		EditFlag.
FieldType	String	False		Type of the field. The allowed values are address, date, daterange, double, enum, monetary, org, people, phone, set, text, time, timerange, user, varchar, varchar_auto, visible_to.
FilteringAllowed	Boolean	True		FilteringAllowed.
ImportantFlag	Boolean	True		ImportantFlag.
IndexVisibleFlag	Boolean	True		IndexVisibleFlag.
Key	String	True		Key.
LastUpdatedByUserId	String	True		LastUpdatedByUserId.
MandatoryFlag	Boolean	True		MandatoryFlag.
Name	String	False		Name of the field.
Options	String	False		Options.
OrderNr	Integer	True		OrderNr.
SearchableFlag	Boolean	True		SearchableFlag.
SortableFlag	Boolean	True		SortableFlag.
UpdateTime	Datetime	True		UpdateTime.

Get details of persons followers.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Followers id.
AddTime	Datetime	True		Add time.
PersonId	Integer	True		Person id.
UserId	Integer	False		User id.
DealId	Integer	True		Deal id.

Get all details of persons.

Select

The Sync App will use the Pipedrive API to process WHERE clause conditions built with the following columns and operators. The rest of the filter is executed client side within the Sync App.

• Id supports the '=' operator.
• FirstChar supports the 'LIKE' operator.
• FilterId supports the '=' operator.
• UserId supports the '=' operator.

For example, the following query is processed server side:

SELECT * FROM Persons WHERE Id = 14

SELECT * FROM Persons WHERE FirstChar = 'c'

SELECT * FROM Persons WHERE FilterId = 1
 
SELECT * FROM Persons WHERE UserId = 1                 

INSERT

Insert can be executed by specifying the Name columns.The columns that are not read-only can be inserted. Following is an example of how to insert into this table.

INSERT INTO Persons (name) VALUES ('testpankaj')

UPDATE

Update can be executed by specifying the Id in the WHERE Clause. The columns that are not read-only can be Updated. For example:

UPDATE Persons SET Name = 'test123' WHERE Id = 2495

DELETE

Delete can be executed by specifying the Id in the WHERE Clause. For example:

DELETE FROM Persons WHERE id = 15

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Integer	True		Id.
ActiveFlag	Boolean	False		ActiveFlag.
ActivitiesCount	Integer	True		ActivitiesCount.
AddTime	Datetime	False		Optional creation date time of the person Requires admin user API token. Format: YYYY-MM-DD HH:MM:SS
CcEmail	String	True		CcEmail.
ClosedDealsCount	Integer	True		ClosedDealsCount.
CompanyId	Integer	True		CompanyId.
CustomeField	String	True		CustomeField you will get the result of this column only when criteria filter title is used.
DoneActivitiesCount	Integer	True		DoneActivitiesCount.
Email	String	False		Email.
EmailMessagesCount	Integer	True		EmailMessagesCount.
FilesCount	Integer	True		FilesCount.
FirstChar	String	True		If supplied, only persons whose name starts with the specified letter will be returned.
FirstName	String	True		FirstName.
FollowersCount	Integer	True		FollowersCount.
Label	Integer	True		Label.
LastActivityDate	Date	True		LastActivityDate.
LastActivityId	Integer	True		LastActivityId.
LastIncomingMailTime	Datetime	True		LastIncomingMailTime.
LastName	String	True		LastName.
LastOutgoingMailTime	Datetime	True		LastOutgoingMailTime.
LostDealsCount	Integer	True		LostDealsCount.
Name	String	False		Name.
NextActivityDate	Date	True		NextActivityDate.
NextActivityId	Integer	True		NextActivityId.
NextActivityTime	Time	True		NextActivityTime.
Notes	String	True		Notes.
NotesCount	Integer	True		NotesCount.
OpenDealsCount	Integer	True		OpenDealsCount.
OrgActiveFlag	Boolean	True		OrgActiveFlag.
OrgAddress	String	True		OrgAddress.
OrgccEmail	String	True		OrgccEmail.
OrgName	String	True		OrgName.
OrgownerId	Integer	True		OrgownerId.
OrgpeopleCount	Integer	True		OrgpeopleCount.
Orgvalue	Integer	False		Orgvalue.
OwnerActiveFlag	Boolean	True		OwnerActiveFlag.
OwnerEmail	String	True		OwnerEmail.
OwnerHasPic	Integer	True		OwnerHasPic.
OwnerId	Integer	False		OwnerId.
OwnerIdName	String	True		OwnerName.
OwnerPicHash	String	True		OwnerPicHash.
OwnerValue	Integer	True		OwnerValue.
ParticipantClosedDealscount	Integer	True		ParticipantClosedDealscount.
ParticipantOpenDealsCount	Integer	True		ParticipantOpenDealsCount.
Phone	String	False		Phone.
PictureActiveFlag	Boolean	True		PictureActiveFlag.
PictureAddTime	Datetime	True		PictureAddTime.
PictureAddedByUserId	Integer	True		PictureAddedByUserId.
PictureItemId	Integer	True		PictureItemId.
PictureItemType	String	True		PictureItemType.
Picture128	String	True		Picture128.
Picture512	String	True		Picture512.
PictureUpdateTime	String	True		PictureUpdateTime.
Picturevalue	Integer	True		Picturevalue.
RelatedClosedDealsCount	Integer	True		RelatedClosedDealsCount.
RelatedLostDealsCount	Integer	True		RelatedLostDealsCount.
RelatedOpenDealsCount	Integer	True		RelatedOpenDealsCount.
RelatedWonDealsCount	Integer	True		RelatedWonDealsCount.
UndoneActivitiesCount	Integer	True		UndoneActivitiesCount.
UpdateTime	Datetime	True		UpdateTime.
VisibleTo	String	False		Visibility of the person. The allowed values are 1, 3.
WonDealsCount	Integer	True		WonDealsCount.
PrimaryEmail	String	True		Primary Email.
MarketingStatus	String	True		Marketing Status.
OwnerName	String	True		Owner 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
FilterId	Integer	Filter Id.
UserId	Integer	User Id.