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

Connecting to Basecamp

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

Accessing Data from CData Cloud Services

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

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

Connecting to Basecamp

In addition to authenticating, set the following connection properties to access Basecamp tables.

• Schema: Set this to "V2" or "V3" to connect to a Basecamp 2 or Basecamp 3 account.
• ProjectId: This is a required parameter for most table operations. You can query the Projects table to obtain this value.

  If you do not specify the ProjectId connection property, the Cloud will use the first project Id returned from the Projects table in your first authentication to Basecamp.

• AccountId: You can find the AccountId in the URL after you log in to Basecamp in a web browser:
  

  http://basecamp.com/<AccountId>

Authenticating to Basecamp

Basecamp 2 uses basic or OAuth 2.0 authentication. You can use basic authentication to connect to your own account, or you can use OAuth to enable other users to log into their own accounts.

Basecamp 3 requires OAuth 2.0.

Basic

In basic authentication, you use your login credentials to connect. Set the following properties:

• User: This is the username you use to log in to Basecamp.
• Password: This is the password you use to log in to Basecamp.
• AuthScheme: Set this to Basic.

OAuth

AuthScheme must be set to OAuth in all user account flows.

When to Create a Custom OAuth App

CData embeds OAuth Application Credentials with CData branding that can be used when connecting via either a Desktop Application or from a Headless Machine. Creating a custom OAuth application is, however, required when using a web application.

You may choose to create your own OAuth Application Credentials when you want to

• control branding of the Authentication Dialog
• control the redirect URI that the application redirects the user to after the user authenticates
• customize the permissions that you are requesting from the user

Follow the steps below to create a custom OAuth app and obtain the connection properties in a specific OAuth authentication flow.

Create a Custom OAuth App

Register your Basecamp app on integrate.37signals.com to obtain the following connection properties:

• OAuthClientId
• OAuthClientSecret
• CallbackURL (desktop only)

The only difference between desktop applications, web applications, and headless machines is what you set the Redirect URI to:

• Desktop Applications: Set the Redirect URI to http://localhost:portnumber and set CallbackURL to match. You can specify any port available.
• Web Applications: Set the Redirect URI to a page on your website. This page is what users return to after granting permissions to the application.
• Headless Machines: Set the Redirect URI to http://localhost:portnumber. You can specify any port available.

Customizing the SSL Configuration

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

To specify another certificate, see the SSLServerCert connection property.

Connecting Through a Firewall or Proxy

HTTP Proxies

To authenticate to an HTTP proxy, set the following:

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

Other Proxies

Set the following properties:

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

The CData Cloud models the Basecamp 2 and Basecamp APIs as relational tables, views, and stored procedures. These are defined in schema files, which are simple, text-based configuration files.

The available entities, as well as any API limitations and requirements for querying these entities, are documented in Basecamp 2 Data Model and Basecamp 3 Data Model.

Overview

The Data Models illustrate an example of what your Basecamp environment might look like. The actual data model will be obtained dynamically based on your Basecamp account.

Key Features

• Tables and Views are dynamically defined to model calendars, documents, and projects on Basecamp.
• Stored procedures allow you to execute operations to Basecamp, including downloading and uploading objects.
• Live connectivity to these objects means any changes to your Basecamp account are immediately reflected when using the Cloud.

Basecamp 2 Data Model

Basecamp 2 Data Model describes the schemas available to connect to Basecamp 2 accounts. You can use tables to work with live Basecamp data. You can use stored procedures provided by CData Cloud to automate working with Basecamp data.

Basecamp 3 Data Model

Basecamp 3 Data Model describes the schemas available to connect to Basecamp 3 accounts. You can use tables to work with live Basecamp data. You can use stored procedures provided by CData Cloud to automate working with Basecamp data.

This section documents the Tables, Views, and Stored Procedures available to connect to Basecamp 2 accounts.

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

CData Cloud - Basecamp Tables

Name	Description
Accesses	Retrieve, grant, and deny access permissions to Projects and Calendars on Basecamp.
CalendarEvents	Retrieve, create, update, and delete Calendar Events on Basecamp.
Calendars	Retrieve, create, update, and delete Calendars on Basecamp.
Documents	Retrieve, create, update, and delete Documents on Basecamp.
People	Retrieve and delete People on Basecamp.
Projects	Retrieve, create, update, and delete Projects on Basecamp.

Retrieve, grant, and deny access permissions to Projects and Calendars on Basecamp.

Table Specific Information

Select

You need to specify a ProjectId or CalendarId to retrieve Accesses. These columns are also the only columns supported by Basecamp as filter criteria; they can be used with the '=' operator.

SELECT * FROM Accesses WHERE ProjectId = '11111111'
SELECT * FROM Accesses WHERE CalendarId = '11111111'

Insert

You can give someone access to a Project or Calendar using their Email Addresses or Ids (if they are part of the organization already).

INSERT INTO Accesses (ProjectId, EmailAddresses) VALUES ('11111111', '[email protected], [email protected]')

INSERT INTO Accesses (CalendarId, Ids) VALUES ('11111111', '123456789, 987654321')

Update

UPDATE is not a supported operation for the table Accesses.

Delete

To revoke access to someone from a Project or Calendar, specify the ProjectId or CalendarId and the Person's Id.

DELETE FROM Accesses WHERE ProjectId = '11111111' AND Id = '123456789'
DELETE FROM Accesses WHERE CalendarId = '11111111' AND Id = '123456789'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The unique identifier of the person with access to the project or calendar.
Admin	Boolean	True		Boolean value to show if the current person is an admin or not.
AppUrl	String	True		The app URL.
AvatarUrl	String	True		The avatar URL of the person.
CanCreateProjects	Boolean	True		Whether the person can create projects or not.
CreatedAt	Datetime	True		The date and time when the record was created.
EmailAddress	String	True		The email address of the person with access to the project or calendar.
FullsizeAvatarUrl	String	True		The full-size avatar URL of the person.
IdentityId	String	True		The identity Id of the person with access to the project or calendar.
IsClient	Boolean	False		Is the returned user a client or not.
Name	String	True		The name of the person with access to the project or calendar.
Trashed	Boolean	False		Boolean value for deleted people.
UpdatedAt	Datetime	True		The date and time when the record was last updated.
URL	String	True		The URL of the person.
Ids	String	False		A comma separated list of user Ids to be granted access to a project or calendar.
EmailAddresses	String	False		A comma separated list of user email addresses to be granted access to a project or calendar.

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
ProjectId	String	The Project Id associated with the access.
CalendarId	String	The Calendar Id associated with the access.

Retrieve, create, update, and delete Calendar Events on Basecamp.

Table Specific Information

Select

Basecamp supports only a limited subset of columns to be used as filter criteria in the WHERE clause. All Boolean columns for this table can be used with the '=' operator. ProjectId, CalendarId, StartsAt, and EndsAt can also be used with the "=" operator. Additionally, the StartsAt column can be used with the '>=' operator.

The following query filters on a particular time range.

SELECT * FROM CalendarEvents WHERE StartsAt = '2016-01-01' AND EndsAt = '2016-02-01'

The following queries get CalendarEvents for a certain Project or Calendar.

SELECT * FROM CalendarEvents WHERE ProjectId = '123456768'
SELECT * FROM CalendarEvents WHERE CalendarId = '123456768'

If Id is provided in the WHERE clause, either ProjectId or CalendarId is required. The following queries get CalendarEvents for a certain Id either a part of a Project or a standalone Calendar:

SELECT * FROM CalendarEvents WHERE ProjectId = '123456768' AND Id = '12345678'
SELECT * FROM CalendarEvents WHERE CalendarId = '123456768' AND Id = '12345678'

Insert

Specify the ProjectId or CalendarId and StartsAt when creating a new CalendarEvent.

INSERT INTO CalendarEvents (ProjectId, Summary, Description, AllDay, StartsAt) VALUES ('12345678', 'My single, all-day Project event', 'Details to follow', 'true', 2016-02-01)

Update and Delete

Specify the ProjectId or CalendarId and the CalendarEvent Id when updating or deleting CalendarEvents.

UPDATE CalendarEvents SET Summary = 'Updated, two-day Project event', Description = 'Details to follow', AllDay = 'true', StartsAt = '2016-01-01', EndsAt = '2016-01-03' WHERE ProjectId = '12345678' AND Id = '12345678'

DELETE FROM CalendarEvents WHERE ProjectId = '12345678' AND Id = '12345678'
DELETE FROM CalendarEvents WHERE CalendarId = '12345678' AND Id = '12345678'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The unique identifier for the calendar event.
AllDay	Boolean	False		Whether the calendar event is an all-day event.
AppUrl	String	True		The app URL.
BucketAppUrl	String	True		The calendar event bucket app url.
BucketColor	String	True		The calendar event bucket color.
BucketId	String	True		The calendar event bucket Id.
BucketName	String	True		The calendar event bucket name.
BucketType	String	True		The calendar event bucket type.
BucketUrl	String	True		The calendar event bucket URL.
CommentsCount	Integer	True		The number of comments on this calendar event.
CreatedAt	Datetime	True		The date and time when the calendar event was created.
CreatorAvatarUrl	String	False		Creator Avatar URL.
CreatorFullsizeAvatarUrl	String	False		Creator full-size avatar URL.
CreatorId	String	False		The Id of the creator of the calendar event.
CreatorName	String	False		The name of the creator of the calendar event.
Description	String	False		The description for the calendar event.
EndsAt	Datetime	False		The end date and time for the calendar event.
Past	Boolean	False		Boolean value for past calendar events.
Private	Boolean	False		Whether this calendar event is private.
RemindAt	Datetime	False		The date and time for the calendar event reminder.
StartsAt	Datetime	False		The start date and time for the calendar event.
SubscribersId	String	False		The Id of the subscriber to the calendar event.
SubscribersName	String	False		The name of the subscriber to the calendar event.
Summary	String	False		The summary for the calendar event.
Trashed	Boolean	False		Boolean value for deleted calendar events.
UpdatedAt	Datetime	True		The date and time when the calendar event was updated.
Url	String	True		The URL of the calendar event.

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
ProjectId	String	The Id of the Project associated with the calendar event.
CalendarId	String	The calendar Id associated with the calendar event.

Retrieve, create, update, and delete Calendars on Basecamp.

Table Specific Information

Select

Select all from Calendars or specify an Id to retrieve more detail on a certain Calendar (Id is the only column supported as search criteria by Basecamp).

SELECT * FROM Calendars WHERE Id = '123456789'

Insert

Specify (at least) the Name property to create a new Calendar under the organization.

INSERT INTO Calendars (Name) VALUES ('New Test Calendar')

Update and Delete

Specify a Calendar Id to update or delete Calendars. Set the Name property again if you are updating.

UPDATE Calendars SET Name = 'This is a new name for the Test Calendar.' WHERE Id = '123456789'

DELETE FROM Calendars WHERE Id = '123456789'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The unique identifier for the calendar.
AccessesAppUrl	String	True		The accesses app URL.
AccessesCount	Integer	True		The accesses count.
AccessesUpdatedAt	Datetime	True		The time when accesses were updated.
AccessesUrl	String	True		The accesses URL.
AppUrl	String	True		The app URL.
CalendarEventsCount	Integer	True		Number of calendar events.
CalendarEventsUpdatedAt	Datetime	True		The date and time when the calendar event was last updated.
CalendarEventsUrlsPast	String	True		URL of the past calendar event.
CalendarEventsUrlsUpcoming	String	True		The date and time when the calendar was last updated.
Color	String	True		The color of the calendar.
CreatedAt	Datetime	True		The date and time when the document was created.
CreatorAvatarUrl	String	False		Creator Avatar URL.
CreatorFullsizeAvatarUrl	String	False		Creator full-size avatar Url.
CreatorId	String	False		The Id of the creator of the calendar.
CreatorName	String	False		The name of the creator of the calendar.
Name	String	False		The name of the calendar.
UpdatedAt	Datetime	True		The date and time when the calendar was last updated.
Url	String	True		The URL of the calendar.

Retrieve, create, update, and delete Documents on Basecamp.

Table Specific Information

Select

You can retrieve all Documents (for all Projects), select Documents belonging to a certain Project only, or specify a Project and a certain Document at the same time (ProjectId and Id are the only columns supported as search criteria by Basecamp).

You can also sort Documents by UpdatedAt, CreatedAt, or Title.

SELECT * FROM Documents WHERE ProjectId = '11111111' AND Id = '1234567689'

SELECT * FROM Documents ORDER BY UpdatedAt

SELECT * FROM Documents ORDER BY Title DESC

Insert

To add a new Document to a major Project, specify the ProjectId and provide the Document's title or some Content.

INSERT INTO Documents (ProjectId, Title, Content) VALUES ('11111111', 'Memo', 'This is our document content')

Update and Delete

Specify the ProjectId and the Document Id to update or delete a Document.

UPDATE Documents SET Title = 'Updated Memo3', Content = 'Updated Content of Memo.' WHERE ProjectId = '11111111' AND Id = '1234567689'

DELETE FROM Documents WHERE ProjectId = '11111111' AND Id = '12345678'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The unique identifier for the document.
AppUrl	String	True		The app URL.
BucketAppUrl	String	True		The topic bucket app url.
BucketColor	String	True		The to-do bucket color.
BucketId	String	True		The to-do bucket Id.
BucketName	String	True		The to-do bucket name.
BucketType	String	True		The to-do bucket type.
BucketUrl	String	True		The to-do bucket URL.
Content	String	False		The content of the document.
CreatedAt	Datetime	True		The date and time when the document was created.
CreatorAvatarUrl	String	False		Creator Avatar URL.
CreatorFullsizeAvatarUrl	String	False		Creator full-size avatar URL.
CreatorId	String	False		The Id of the creator of the document.
CreatorName	String	False		The name of the creator of the document.
LastUpdaterId	String	False		The Id of the person who last updated the document.
LastUpdaterName	String	False		The name of the person who last updated the document.
Private	Boolean	False		Whether this to-do is private.
SubscribersId	String	False		The Id of the subscriber to the document.
SubscribersName	String	False		The name of the subscriber to the document.
Title	String	False		The title of the document.
Trashed	Boolean	False		Boolean value for deleted to-dos.
UpdatedAt	Datetime	True		The date and time when the document was last updated.
URL	String	True		The URL 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
ProjectId	String	The Id of the Project associated with the document.

Retrieve and delete People on Basecamp.

Table Specific Information

Select

Retrieve all People from the organization, specify an Id to get more info, or see who has been deleted (admin access is required). Id and Trashed are the columns supported by Basecamp as search criteria.

SELECT * FROM People WHERE Id = '123456789'

SELECT * FROM People WHERE Trashed = true

INSERT

New People can be invited directly to Projects via the Accesses table.

Update

UPDATE is not a supported operation for the table People.

Delete

Specify the Person's Id to delete the record from the organization.

DELETE FROM People WHERE Id = '123456789'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The unique identifier for the person.
AccountOwner	Boolean	True		Boolean value to show if the current person is the account owner.
Admin	Boolean	True		Boolean value to show if the current person is an admin or not.
AssignedTodosCount	Integer	True		Number of assigned to-dos.
AssignedTodosUpdatedAt	Datetime	True		The date and time when the record was updated.
AssignedTodosUrl	String	True		The URL of the assigned to-do.
AppUrl	String	True		The app URL.
AvatarUrl	String	True		The avatar URL of the person.
CalendarEventsTodayCount	Integer	True		Number of events for today.
CanCreateProjects	Boolean	True		Whether the person can create projects or not.
CreatedAt	Datetime	True		The date and time when the record was created.
EmailAddress	String	True		The email address of the person.
EventsAppUrl	String	True		The events app URL.
EventsCount	Integer	True		Number of events.
EventsUpdatedAt	Datetime	True		The date and time when the event was updated.
EventsUrl	String	True		The URL of the assigned to-do.
FullsizeAvatarUrl	String	True		The full-size avatar URL of the person.
HasBasecampSiblings	Boolean	False		Boolean value for Basecamp siblings.
IdentityId	String	True		The identity Id of the person.
Name	String	True		The name of the person.
OutstandingTodosCount	Integer	True		Number of outstanding to-dos.
Trashed	Boolean	False		Boolean value for deleted people.
UpdatedAt	Datetime	True		The date and time when the record was last updated.
Url	String	True		The URL of the person.

Retrieve, create, update, and delete Projects on Basecamp.

Table Specific Information

Select

You can retrieve all active Projects from the organization, filter records by Drafts and Archived Projects, or simply set a Project Id for more information. Drafts, Archived, and Id are the columns supported as search criteria by Basecamp.

SELECT * FROM Projects WHERE Drafts = True
SELECT * FROM Projects WHERE Advanced = True
SELECT * FROM Projects WHERE Id = '11111111'

Insert

Specify the Name and Description properties at a minimum to create a new Project.

INSERT INTO Projects (Name, Description) VALUES ('Test Proj', 'This is a new test project.')

Update and Delete

Simply set the Project Id to update or delete a project you have access to.

UPDATE Projects SET Name = 'Updated Proj', Description = 'This is an updated test project.' WHERE Id = '11111111'

DELETE FROM Projects WHERE Id = '11111111'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The unique identifier of the project.
AccessesAppUrl	String	True		The accesses app URL.
AccessesCount	Integer	True		The accesses count.
AccessesUpdatedAt	Datetime	True		Time when accesses were updated.
AccessesUrl	String	True		The accesses URL.
AppUrl	String	True		The app URL.
Archived	Boolean	False		When user archives a project, this value will be set to true.
AttachmentsAppUrl	String	True		The attachments app URL.
AttachmentsCount	Integer	True		The attachments count.
AttachmentsUpdatedAt	Datetime	True		Time when attachments were updated.
AttachmentsUrl	String	True		The attachments URL.
CalendarEventsAppUrl	String	True		The calendar events app URL.
CalendarEventsCount	Integer	True		The calendar events count.
CalendarEventsUpdatedAt	Datetime	True		Time when calendar events were updated.
CalendarEventsUrl	String	True		The calendar events URL.
Color	String	True		Color.
CreatedAt	Datetime	True		The creation time of the project.
CreatorAvatarUrl	String	False		Creator Avatar URL.
CreatorFullsizeAvatarUrl	String	False		Creator full-size Avatar URL.
CreatorId	String	False		The Id of the creator.
CreatorName	String	False		The name of the creator.
Description	String	False		The description of the project.
DocumentsAppUrl	String	True		The documents app URL.
DocumentsCount	Integer	True		The documents count.
DocumentsUpdatedAt	Datetime	True		Time when documents were updated.
DocumentsUrl	String	True		The documents URL.
Drafts	Boolean	False		Boolean value for a draft project.
ForwardsAppUrl	String	True		The forwards app URL.
ForwardsCount	Integer	True		The forwards count.
ForwardsUpdatedAt	Datetime	True		Time when forwards were updated.
ForwardsUrl	String	True		The forwards URL.
IsClientProject	Boolean	False		Whether the project is related to clients or not.
LastEventAt	Datetime	False		Time when the project was last modified.
Name	String	False		The name of the project.
Starred	Boolean	False		Boolean value for a starred project.
Template	Boolean	False		Boolean value for a template project.
TodolistsAppUrl	String	True		The todolists app URL.
TodolistsCompletedCount	Integer	True		The todolists completed count.
TodolistsRemainingCount	Integer	True		The todolists remaining count.
TodolistsUpdatedAt	Datetime	True		Time when todolists were updated.
TodolistsUrl	String	True		The todolists URL.
TopicsAppUrl	String	True		The topics app URL.
TopicsCount	Integer	True		The topics count.
TopicsUpdatedAt	Datetime	True		Time when topics were updated.
TopicsUrl	String	True		The topics URL.
Trashed	Boolean	False		Boolean value for a trashed project.
UpdatedAt	Datetime	True		Time when the project was updated.
URL	String	True		The URL of the project.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Basecamp Views

Name	Description
Attachments	Retrieve Attachments on Basecamp.
Comments	Retrieve, create, update, and delete Calendars on Basecamp.
Events	Retrieve activity Events on Basecamp.
Forwards	Retrieve Forwards on Basecamp.
ToDoLists	Retrieve ToDoLists on Basecamp.
ToDos	Retrieve ToDos on Basecamp.
Topics	Retrieve Topics on Basecamp.

Retrieve Attachments on Basecamp.

Columns

Name	Type	References	Description
Id [KEY]	String		The Id of the attachment.
Key	String		The key of the attachment.
Name	String		The name of the attachment.
ByteSize	Int		The size of the attachment in bytes.
CreatedAt	Datetime		The date when the attachment was created.
UpdatedAt	Datetime		The date when the attachment was last updated.
Url	String		The Url of the attachment.
AppUrl	String		The AppUrl of the attachment.
ThumbnailUrl	String		The Thumbnail Url of the attachment.
Private	Boolean		Indicates if the attachemtn is private.
Trashed	Boolean		Indicates if the attachment is trashed.
Tags	String		The tags of the attachment.
CreatorId	String		The Id of the attachment creator.
CreatorName	String		The name of the attachment creator.
CreatorAvatarUrl	String		The avatar url of the attachment creator.
CreatorFullsizeAvatarUrl	String		The url of the full sized avatar of the attachment creator.
AttachableId	String		The Id of the attachable.
AttachableType	String		The type of the attachable.
AttachableUrl	String		The Url of the attachable.
AttachableAppUrl	String		The AppUrl of the attachable.

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
ProjectId	String		The id of the project

Retrieve, create, update, and delete Calendars on Basecamp.

Table Specific Information

Select

You can retrieve all active Comments on the platform, and any field of the table can filter the result.

SELECT * FROM Comments WHERE ProjectId = '1111111' AND ItemType = 'Message'

Columns

Name	Type	References	Description
Id [KEY]	String		The unique identifier for the comment.
ProjectId [KEY]	String		The unique identifier for the project where the comment is contained.
ItemId	String		The unique identifier for the topic containing the comment.
ItemType	String		Type of the object containing the comment. Valid values are: Message, Document, Forward, CalendarEvent, Upload, Todolist, Todo.
AttachmentsAggregate	String		The attachments related to the comment.
Content	String		The content of the comment.
Private	Boolean		Privacy of the comment
Trashed	Boolean		If the comment was trashed or not.
CreatedAt	Datetime		The date and time when the document was created.
CreatorAvatarUrl	String		Creator Avatar URL.
CreatorFullsizeAvatarUrl	String		Creator full-size avatar Url.
CreatorId	String		The Id of the creator of the comment.
CreatorName	String		The name of the creator of the comment.
UpdatedAt	Datetime		The date and time when the comment was last updated.

Retrieve activity Events on Basecamp.

Table Specific Information

Select

You can retrieve all activity Events or filter data by the created date. The columns and operators supported by Basecamp as search criteria are shown below.

Column	Operators
ProjectId	=
PersonId	=
CreatedAt	>, >=

For example, the following query retrieves activity occurring after the specified date for the specified person:

SELECT * FROM Events WHERE CreatedAt >= '2016-01-01' AND PersonId = '1111111'

Columns

Name	Type	References	Description
Id [KEY]	String		The unique identifier of the event.
Action	String		The action description for the event.
BucketAppUrl	String		The calendar events app URL.
BucketColor	String		The bucket event color.
BucketId	String		The bucket event Id.
BucketName	String		The bucket event name.
BucketType	String		The bucket event type.
BucketUrl	String		The bucket event URL.
CreatedAt	Datetime		The date and time when the event was created.
CreatorAvatarUrl	String		Creator Avatar URL.
CreatorFullsizeAvatarUrl	String		Creator full-size avatar URL.
CreatorId	String		The Id of the event creator.
CreatorName	String		The name of the event creator.
EventableAppUrl	String		Eventable App URL.
EventableId	String		Eventable Id.
EventableType	String		Eventable type.
EventableUrl	String		Eventable URL.
Excerpt	String		The type of excerpt for this event.
HtmlUrl	String		The HTML URL of the event.
Private	Boolean		Whether the event is private.
RawExcerpt	String		The raw excerpt type for this event.
Summary	String		The summary for the event.
Target	String		The target project of this event.
UpdatedAt	Datetime		The date and time when the event was last updated.
URL	String		The URL of the event.

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
ProjectId	String		The Id of the Project associated with this event.
PersonId	String		The Id of the Person associated with this event.

Retrieve Forwards on Basecamp.

Columns

Name	Type	References	Description
Id [KEY]	String		The id of the forward.
Subject	String		The subject of the forward.
From	String
CreatedAt	Datetime		The date when the forward was created.
UpdatedAt	Datetime		The date when the forward was last updated.
Url	String		The Url of the forward.
AppUrl	String		The AppUrl of the forward.
Private	Boolean		Indicates if the forward is private.
Trashed	Boolean		Indicates if the forward is trashed.
BucketId	String		The bucket Id of the forward.
BucketName	String		The bucket name of the forward.
BucketType	String		The bucket type of the forward.
BucketUrl	String		The bucket Url of the forward.
BucketAppUrl	String		The bucket app url of the forward.

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
ProjectId	String		The Id of the project

Retrieve ToDoLists on Basecamp.

Table Specific Information

Select

The following columns are supported by Basecamp as search criteria:

• ProjectId
• Id
• PersonId
• Completed
• Trashed
• CreatedAt

CreatedAt can be used with the '>' or '>=' operators.

The following query retrieves only completed ToDoLists belonging to a certain Project since a certain date.

SELECT * FROM ToDoLists WHERE ProjectId = '11111111' AND Completed = True AND CreatedAt > '2016-01-01'M

The following query retrieves a single ToDoList from a certain Project.

SELECT * FROM ToDoLists WHERE ProjectId = '11111111' AND Id = '123456789'

The following query retrieves ToDoLists that are assigned to a certain Person.

SELECT * FROM ToDoLists WHERE PersonId = '11111111'

Columns

Name	Type	References	Description
Id [KEY]	String		The unique identifier of the topic.
AppUrl	String		The app URL.
AssignedTodos	String		Assigned to-dos.
BucketAppUrl	String		The topic bucket app URL.
BucketColor	String		The to-do bucket color.
BucketId	String		The to-do bucket Id.
BucketName	String		The to-do bucket name.
BucketType	String		The to-do bucket type.
BucketUrl	String		The to-do bucket URL.
Completed	Boolean		Whether the to-do list is completed.
CompletedCount	Integer		Number of completed to-dos.
CreatedAt	Datetime		The date and time when the record was created.
CreatorAvatarUrl	String		Creator Avatar URL.
CreatorFullsizeAvatarUrl	String		Creator full-size avatar URL.
CreatorId	String		The Id of the creator.
CreatorName	String		The name of the creator.
Description	String		The to-do list description.
Name	String		The name of the to-do list.
Position	Integer		Position number of the current to-do list.
Private	Boolean		Whether the to-do list is private.
RemainingCount	Integer		Number of unfinished to-dos.
SubscribersId	String		The Id of the subscriber.
SubscribersName	String		The name of the subscriber.
ToDos	String		To-dos list.
Trashed	Boolean		Boolean value for deleted to-do lists.
UpdatedAt	Datetime		The date and time when the record was last updated.
URL	String		The URL of the to-do 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
ProjectId	String		The Id of the Project associated with the to-do list.
PersonId	String		The Id of the Person associated with the to-do list.

Retrieve ToDos on Basecamp.

Table Specific Information

Select

The following are columns supported as search criteria by Basecamp:

• ProjectId
• ToDoListId
• Id
• Completed
• Remaining
• Trashed
• DueAt
• DueOn

DueAt and DueOn can be used with the '>' or'>=' operators.

The following query retrieves only completed ToDos belonging to a certain Project.

SELECT * FROM ToDos WHERE ProjectId = '11111111' AND Completed = True

The following query filters ToDos that are due since a certain date.

SELECT * FROM ToDos WHERE ProjectId = '11111111' AND DueAt > '2016-01-01'

The following query lists the remaining ToDos that belong to a ToDo list.

SELECT * FROM ToDos WHERE ProjectId = '11111111' AND ToDoListId = '123456789' AND Remaining = true

Columns

Name	Type	References	Description
Id [KEY]	String		The unique identifier of the topic.
AppUrl	String		The app URL.
AssigneeId	String		Id of the person assigned to.
AssigneeName	String		Name of the person assigned to.
AssigneeType	String		Type of assignee.
CommentsCount	Integer		Number of comments on this to-do.
Completed	Boolean		Whether the to-do is completed.
Content	String		Description of the to-do.
CreatedAt	Datetime		The date and time when the record was created.
CreatorAvatarUrl	String		Creator Avatar URL.
CreatorFullsizeAvatarUrl	String		Creator full-size avatar URL.
CreatorId	String		The Id of the creator.
CreatorName	String		The name of the creator.
DueAt	Datetime		The date and time when the to-do is set due.
DueOn	Datetime		The date and time when the to-do is set due.
Position	Integer		Position number of the current to-do on the list.
Private	Boolean		Whether this to-do is private.
Remaining	Boolean		Boolean value for remaining to-dos.
SubscribersId	String		The Id of the subscriber.
SubscribersName	String		The name of the subscriber.
ToDoList	String		To-do list.
ToDoListId	String		The to-do list Id.
Trashed	Boolean		Boolean value for deleted to-dos.
UpdatedAt	Datetime		The date and time when the record was last updated.
URL	String		The URL of the to-do.

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
ProjectId	String		The Id of the Project associated with the to-do.

Retrieve Topics on Basecamp.

Table Specific Information

Select

You can retrieve all active Topics, sort Topics that belong to a Project, or only list archived Topics (ProjectId and Archived are the only columns supported as search criteria by Basecamp).

SELECT * FROM Topics WHERE ProjectId = '1111111' ORDER BY UpdatedAt ASC

SELECT * FROM Topics WHERE ProjectId = '1111111' AND Archived = True

Columns

Name	Type	References	Description
Id [KEY]	String		The unique identifier of the topic.
Archived	Boolean		Boolean value for archived topics.
Attachments	Integer		The number of attachments to the topic.
BucketAppUrl	String		The topic bucket app URL.
BucketColor	String		The bucket topic color.
BucketId	String		The bucket topic Id.
BucketName	String		The bucket topic name.
BucketType	String		The bucket topic type.
BucketUrl	String		The bucket topic URL.
CreatedAt	Datetime		The date and time when the record was created.
Excerpt	String		The excerpt from the latest comment on the topic.
LastUpdaterId	String		The Id of the person who made the last comment.
LastUpdaterName	String		The name of the person who last updated the topic.
Private	Boolean		Whether the topic is private.
Title	String		The title of the topic.
TopicableAppUrl	String		The URL of the topicable Basecamp entity.
TopicableId	String		The Id of the topicable Basecamp entity.
TopicableType	String		The type of the topicable Basecamp entity.
TopicableUrl	String		The URL of the topicable Basecamp entity.
Trashed	Boolean		Boolean value for deleted topics.
UpdatedAt	Datetime		The date and time when the record was last updated.

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
ProjectId	String		The Project Id.

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

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

CData Cloud - Basecamp Stored Procedures

Name	Description
PublishProject	Will publish (activate) a project created from a template (draft project).
StarProject	Will star a specified project.
UnstarProject	Will unstar a specified project.

Will publish (activate) a project created from a template (draft project).

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the project.

Result Set Columns

Name	Type	Description
Result	String	Project creation result.

Will star a specified project.

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the project.

Result Set Columns

Name	Type	Description
Result	String	Project star result.

Will unstar a specified project.

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the project.

Result Set Columns

Name	Type	Description
Result	String	Project unstar result.

This section documents the Tables, Views, and Stored Procedures available to connect to Basecamp 3 accounts.

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

CData Cloud - Basecamp Tables

Name	Description
CalendarEvents	Retrieve calendar events on Basecamp 3.
CardTableCards	Create, Update, and Query the Card table cards.
CardTableColumns	Create, Update, and Query the Card table columns.
Comments	Retrieve comments on a Basecamp 3 recording.
Documents	Retrieve, create, update, and delete Documents on Basecamp 3.
Messages	Retrieve messages on Basecamp 3.
Projects	Retrieve, create, update, and delete Projects on Basecamp 3.
ToDoLists	Retrieve ToDoLists on Basecamp 3.
ToDos	Retrieve ToDos on Basecamp 3.
Vaults	Retrieve Vaults(folders) on Basecamp 3 folders.

Retrieve calendar events on Basecamp 3.

Table Specific Information

You can query Calendar Events in the specified project.

Select

The following columns are supported as search criteria by Basecamp:

• ProjectId
• CalendarId
• Status (by default only active events are retrieved)

If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM CalendarEvents WHERE ProjectId = '11111111' AND CalendarId = '22222222' AND Status = 'active'

You can also retrieve a single calendar event by specifying the Id.

SELECT * FROM CalendarEvents WHERE  ProjectId = '11111111' AND Id = '33333333'

Insert

You can insert a new Calendar Event by providing the ProjectId, CalendarId, StartsAt, EndsAt, and Summary as required parameters.

INSERT INTO CalendarEvents (ProjectId, CalendarId, Summary, StartsAt, EndsAt, Description, Notify, ParticipantsIds ) VALUES ( '1111111', '2222222', 'summary test', '2017-11-11 09:00Z', '2017-11-11 11:00Z', 'description', 'false', '3333333, 4444444')

Update

You can also change the Calendar Event by providing the ProjectId and event Id.

UPDATE CalendarEvents SET Summary = 'Updated summary',  StartsAt =  '2017-11-12', EndsAt = '2017-11-12', AllDay = 'true' WHERE ProjectId = '11111111' AND Id = '1234567689'

Delete

Calendar Events cannot be deleted.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The unique identifier of the calendar event.
ProjectId	Long	True	Projects.Id	The Project Id associated with the schedule.
CalendarId	Long	True	Projects.CalendarId	The Calendar Id that the event belongs to.
Status	String	True		The status of the calendar event.
Type	String	True		The type of the returned record.
CreatedAt	Datetime	True		The date and time when the calendar event was created.
UpdatedAt	Datetime	True		The date and time when the calendar event was last updated.
Description	String	False		Provided description for the calendar event.
AllDay	Boolean	False		Indicates that the event is an all day event.
StartsAt	Datetime	False		The date and time when the calendar event starts.
EndsAt	Datetime	False		The date and time when the calendar event ends.
Summary	String	False		Provided summary for the calendar event.
ParticipantsIds	String	False		A list of comma separated participant ids.
ParticipantsNames	String	True		A list of comma separated participant names.
ParentTitle	String	True		Title of the parent.
ParentType	String	True		Type of the parent.
BucketName	String	True		Name of the containing bucket.
BucketType	String	True		Type of the containing bucket.
CreatorId	Long	True	People.Id	The Id of the creator.
CreatorName	String	True		The name of the creator.
CommentsCount	Integer	True		Number of comments the document has.

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
Notify	String	When set to true, will notify the participants about the event.

Create, Update, and Query the Card table cards.

Table Specific Information

Select

The following queries are processed server side while other filters are processed client side within the Cloud.

SELECT * FROM CardTableCards WHERE ProjectId = '37595039' AND CardTableColumnId = '7416523073'
SELECT * FROM CardTableCards WHERE ProjectId = '37595039' AND Id = '7416545710'

Insert

The columns CardTableColumnId, ProjectId and Title are required to insert.

INSERT INTO CardTableCards(ProjectId, CardTableColumnId, Title, Content, DueOn) VALUES('37595039', '7416523073', 'Test title2', 'Test Content2', '2025-01-02')

Update

You must specify the ProjectId and Id of the CardTableCard to update it.

UPDATE CardTableCards SET Title = 'Updated title' WHERE ProjectId = '37595039' AND Id = '7420207738'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True	Projects.KanbanBoardId	Id of the Card Table column.
ProjectId	Long	True	Projects.Id	The Project Id.
CardTableColumnId	Long	True	CardTableColumns.Id	The CardTableColum Id.
VisibleToClients	Boolean	True		Boolean value representing whether the columns are visible to clients.
CreatedAt	Datetime	True		The creation date.
UpdatedAt	Datetime	True		The updation date.
Title	String	False		Title.
InheritsStatus	Boolean	True		Inherits status.
Type	String	True		Type.
Url	String	True		Url.
AppUrl	String	True		App url.
BookmarkUrl	String	True		Bookmark url.
SubscriptionUrl	String	True		Subscription url.
Position	Integer	True		Position.
ParentId	Long	True		Parent Id.
ParentTitle	String	True		Parent title.
ParentType	String	True		Parent Type.
ParentUrl	String	True		Parent url.
ParentAppUrl	String	True		Parent app url.
BucketId	Long	True		Project Id.
BucketName	String	True		Project name.
BucketType	String	True		Project type.
CreatorId	Long	True	People.Id	Creator Id.
Description	String	False		Description.
Completed	Boolean	True		Completed.
Content	String	False		Content.
DueOn	Datetime	False		Due date.
CommentCount	Integer	True		Comment count.
CommentsCount	Integer	True		Comments count.
CommentsUrl	String	True		Comments Url.
Assignees	String	True		Assigness.
CompletionSubscribers	String	True		Completion subscribers.
CompletionUrl	String	True		Completion url.

Create, Update, and Query the Card table columns.

Table Specific Information

Select

The following query is processed server side while other filters are processed client side within the Cloud.

SELECT * FROM CardTableColumns WHERE ProjectId = '37595039' AND Id = '7416500344'

Insert

The columns CardId, ProjectId and Title are required to insert.

INSERT INTO CardTableColumns(CardId, ProjectId, Title, Description) VALUES('7416037619', '37595039', 'testTitle', 'testDesc')

Update

You must specify the ProjectId and Id of the CardTableColumn to update it.

UPDATE CardTableColumns SET Title = 'Updated title' WHERE ProjectId = '37595039' AND Id = '7416500344'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		Id of the Card Table column.
ProjectId	Long	True	Projects.Id	The Project Id.
Status	String	True		Status.
VisibleToClients	Boolean	True		Boolean value representing whether the columns are visible to clients.
CreatedAt	Datetime	True		The creation date.
UpdatedAt	Datetime	True		The updation date.
Title	String	False		Title.
InheritsStatus	Boolean	True		Inherits status.
Type	String	True		Type.
Url	String	True		Url.
AppUrl	String	True		App url.
BookmarkUrl	String	True		Bookmark url.
SubscriptionUrl	String	True		Subscription url.
Position	Integer	True		Position.
ParentId	Long	True		Parent Id.
ParentTitle	String	True		Parent title.
ParentType	String	True		Parent Type.
ParentUrl	String	True		Parent url.
ParentAppUrl	String	True		Parent app url.
BucketId	Long	True		Project Id.
BucketName	String	True		Project name.
BucketType	String	True		Project type.
CreatorId	Long	True	People.Id	Creator Id.
Description	String	False		Description.
Subscribers	String	True		Subscribers.
Color	String	True		Color.
CardsCount	Integer	True		Cards count.
CommentCount	Integer	True		Comments count.
CardsUrl	String	True		Cards 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
CardId	Long	The Card Id.

Retrieve comments on a Basecamp 3 recording.

Table Specific Information

Select

The columns in Comments are filtered client side. You can either get all comments for a recording by specifying the RecordingId or you can get a certain comment by specifying its Id.

If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM Comments WHERE ProjectId = '11111111' AND RecordingId = '222222222' AND Content = 'Exempli gratia' AND CreatedAt < '2017-04-18T15:53:38.040-06:00'

You can also retrieve a single comment by specifying the Id (given that you've specified the ProjectId in the connection)

SELECT * FROM Comments WHERE Id = '1234567'

Insert

You can insert a new comment, by providing the required ProjectId, RecordingId, and Content.

INSERT INTO Comments (Content, ProjectId, RecordingId) VALUES ('Very informative document. Thanks!', 114857506, 392946856)

Update

You can change the columns that are not read-only by providing the Id, ProjectId, and new values.

UPDATE Comments SET Content = 'I like this better now' WHERE ProjectId = '18061798' AND Id = '1308746423'

Delete

Comments cannot be deleted (unsupported by the API).

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	String	True		The ID of the comment.
Content	String	False		The conent of the comment.
Title	String	True		The title of the comment.
Status	String	True		The status of the comment.
CreatedAt	Datetime	True		The time the comment was created.
UpdatedAt	Datetime	True		The time the comment was last updated.
InheritsStatus	Boolean	True		Whether this comment inherits its status or not.
URL	String	True		The URL of the comment.
VisibleToClients	Boolean	True		Whether this comment is visible to clients or not.
ProjectId	Long	False	Projects.Id	The ID of the project this comment is a child of. A project contains recordings.
ProjectName	String	True		The name of the project this comment is a child of.
RecordingId	Long	False	Recordings.Id	The ID of the recording the comment is written on. A recording can be a comment, document, message, question::answer, schedule::entry, todo, todolist or upload.
CommenterId	String	True		The ID of the commenter.
CommenterName	String	True		The name of the commenter.
CommenterIsAdmin	Boolean	True		Whether the commenter is an admin or not.
CommenterIsOwner	Boolean	True		Whether the commenter is the owner of the recording or not.
CommenterTitle	String	True		The commenter's title.
CommenterEmail	String	True		The email address of the commenter.
CommenterAvatarURL	String	True		The URL of the avatar of the commenter.
CommenterCompanyId	String	True		The company ID of the commenter.
CommenterCompanyName	String	True		The name of the company of the commenter.
AppURL	String	True		The URL of the app this comment belongs to.
BookmarkURL	String	True		The URL of the bookmark.
CommenterAttachableSGID	String	True		Te attachable SGID of the commenter.

Retrieve, create, update, and delete Documents on Basecamp 3.

Table Specific Information

Select

You can retrieve all documents belonging to a certain Project and Vault only, or specify a Project and a certain Document at the same time (ProjectId, VaultId and Id are the only columns supported as search criteria by Basecamp).

If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM Documents WHERE ProjectId = '11111111' AND VaultId = '222222222'

SELECT * FROM Documents WHERE ProjectId = '11111111' AND Id = '1234567689'

Insert

To add a new document to a major project, you need to specify the ProjectId, Vaultid, Title, Content, and Status (optional).

INSERT INTO Documents (ProjectId, Vaultid, Title, Content, Status) VALUES ('11111111', '222222222', 'Memo', 'This is our document content', 'active')

Update

Specify the ProjectId and the document Id to update a document.

UPDATE Documents SET Title = 'Updated Memo3', Content = 'Updated Content of Memo.' WHERE ProjectId = '11111111' AND Id = '1234567689'

Delete

DELETE is not supported for this table.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The unique identifier for the document.
ProjectId	Long	True	Projects.Id	The Id of the Project associated with the document.
VaultId	Long	True	Vaults.Id	The Vault Id associated with the document.
Status	String	False		The status of the vault.
Title	String	False		The title of the document.
Content	String	False		The content of the Document
Type	String	True		The type of the returned record.
CreatedAt	Datetime	True		The date and time when the document was created.
UpdatedAt	Datetime	True		The date and time when the document was last updated.
ParentTitle	String	True		Title of the parent.
ParentType	String	True		Type of the parent.
BucketName	String	True		Name of the containing bucket.
BucketType	String	True		Type of the containing bucket.
CreatorId	Long	True	People.Id	The Id of the creator.
CreatorName	String	True		The name of the creator.
CommentsCount	Integer	True		Number of comments the document has.

Retrieve messages on Basecamp 3.

Table Specific Information

You can query messages posted on the message board of the specified project.

Select

The columns supported as search criteria by Basecamp are ProjectId, MessageBoardId.

If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM Messages WHERE ProjectId = '11111111' AND MessageBoardId = '22222222'

You can also retrieve a single message by specifying the Id.

SELECT * FROM Messages WHERE  ProjectId = '11111111' AND Id = '33333333'

Insert

You can insert a new message, by providing the required ProjectId, MessageBoardId, and Subject in addition to the message related parameters.

INSERT INTO Messages (ProjectId, MessageBoardId, Content, Subject, Status ) VALUES ('1111111', '2222222', 'Content test', 'Subject test',  'active')

Update

You can also update messages by providing the ProjectId and message Id.

UPDATE Messages SET subject = 'Updated subject', content = 'Updated content'  WHERE ProjectId = '11111111' AND Id = '1234567689'

Delete

Messages cannot be deleted.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The unique identifier of the message.
ProjectId	Long	True	Projects.Id	The Project Id associated with the schedule.
MessageBoardId	Long	True	Projects.MessageBoardId	The Message Board Id that the message belongs to.
Status	String	False		The status of the message
Type	String	True		The type of the returned record.
CreatedAt	Datetime	True		The date and time when the message was created.
UpdatedAt	Datetime	True		The date and time when the message was last updated.
Content	String	False		Provided content for the message
Subject	String	False		Subject of the message
ParentTitle	String	True		Title of the parent.
ParentType	String	True		Type of the parent.
BucketName	String	True		Name of the containing bucket.
BucketType	String	True		Type of the containing bucket.
CreatorId	Long	True	People.Id	The Id of the creator.
CreatorName	String	True		The name of the creator.
CategoryId	Long	False		The Id of the category.
CategoryName	String	True		The name of the category.
CommentsCount	Integer	True		Number of comments the document has.

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
IncludeReplies	Boolean	Set this to true to retrieve replies for each message.

Retrieve, create, update, and delete Projects on Basecamp 3.

Table Specific Information

Select

You can retrieve all active Projects from the organization, or set a project Id to retrieve a single project. Id is the only column supported as search criteria by Basecamp.

SELECT * FROM Projects WHERE Id = '11111111'

Insert

Specify at least the Name and optionally Description properties to create a new Project.

INSERT INTO Projects (Name, Description) VALUES ('Test Proj', 'This is a new test project.')

Update and Delete

Set the Project Id to update or delete a project you have access to.

UPDATE Projects SET Name = 'Updated Project', Description = 'This is an updated test project.' WHERE Id = '11111111'

DELETE FROM Projects WHERE Id = '11111111'

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The unique identifier of the project.
Status	String	True		The status of the project.
Purpose	String	True		The purpose of the project.
CreatedAt	Datetime	True		The creation time of the project.
UpdatedAt	Datetime	True		Time when the project was updated.
Description	String	False		The description of the project.
Name	String	False		The name of the project.
Bookmarked	Boolean	False		Boolean value for a bookmarked project.
CalendarId	Long	True		Id of the project's Calendar.
ChatId	Long	True		Id of the project's Chat.
ToDoSetId	Long	True		Id of the project's ToDo Set.
MessageBoardId	Long	True		Id of the project's Message Board.
QuestionnaireId	Long	True		Id of the project's Questionnaire.
VaultId	Long	True		Id of the project's Vault.
InboxId	Long	True		Id of the project's Inbox.
KanbanBoardId	Long	True		Id of the project's Kanban Board.

Retrieve ToDoLists on Basecamp 3.

Table Specific Information

Select

The following columns are supported by Basecamp as search criteria:

• ProjectId
• ToDoSetId and Status
• ProjectId and Id

ToDoSetId can be retrieved in the Projects table.

The following query retrieves only completed to-do lists belonging to a certain Project and a certain Status. If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM ToDoLists WHERE ProjectId = '11111111' AND ToDoSetId = '2222222' AND Status = 'active'

The following query retrieves a single ToDoList from a certain Project.

SELECT * FROM ToDoLists WHERE ProjectId = '11111111' AND Id = '123456789'

Insert

Specify the Name, ProjectId, Description (optional), and the ToDoSetId to insert a to-do list.

INSERT INTO ToDoLists (Name, Description, ProjectId, ToDoSetId ) VALUES ('Name', 'desc', '11111111', '2222222')

Update

Basecamp allows changing the name and description of the to-do list, provided that the ProjectId and to-do list Id are included in the query:

UPDATE ToDoLists SET Name = 'Updated Name', Description = 'Updated desc' WHERE ProjectId = '11111111' AND Id = '1234567689'

Delete

To-do lists cannot be deleted.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The unique identifier of the topic.
ProjectId	Long	False	Projects.Id	The Id of the Project associated with the to-do list.
ToDoSetId	Long	False	Projects.ToDoSetId	The Id of the To Do Set the to-do list belongs to.
Status	String	True		The to-do status.
Type	String	True		The record type.
Name	String	False		The to-do name.
Completed	Boolean	True		The to-do state.
CompletedRatio	String	True		The to-do state in ratio.
Description	String	False		The to-do list description.
CreatedAt	Datetime	True		The date and time when the event was created.
UpdatedAt	Datetime	True		The date and time when the record was last updated.
BucketName	String	True		The to-do list bucket name.
BucketType	String	True		The to-do list bucket type.
ParentTitle	String	True		Title of the parent.
ParentType	String	True		Type of the parent.
CreatorId	Long	True	People.Id	Event creator Id.
CreatorAttachableSgid	String	True		Event creator Attachable SGID.
CreatorName	String	True		Event creator Name.
CreatorEmailAddress	String	True		Event creator Email Address.
CreatorTitle	String	True		Event creator Title.
CreatorBio	String	True		Event creator Bio.
CreatorCreatedAt	Datetime	True		Event creator creation date and time.
CreatorUpdatedAt	Datetime	True		Event creator update date and time.
CreatorAdmin	Boolean	True		Boolean indicationg if the event creator is an admin.
CreatorOwner	Boolean	True		Boolean indicationg if the event creator is an owner.
CreatorTimeZone	String	True		Event creator's timezone.
CreatorCompanyId	Long	True		Event creator's company Id.
CreatorCompanyName	String	True		Event creator's company name.
CommentsCount	Integer	True		Number of coments the document has.

Retrieve ToDos on Basecamp 3.

Table Specific Information

Select

The following columns are supported as search criteria by Basecamp:

• ProjectId
• ToDoListId
• Id
• Completed
• Status

By default, only active to-dos will be retrieved.

If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM ToDos WHERE ProjectId = '11111111' AND ToDoListId = '222222222' AND Status = 'active' AND Completed = True

You can also retrieve a single to-do by specifying the Id.

SELECT * FROM ToDos WHERE ProjectId = '11111111' AND Id = '33333333'

Insert

You can insert a new to-do by providing the required ProjectId, ToDoListId, and Content in addition to the to-do related parameters.

INSERT INTO ToDos (Content, Description, DueOn, StartsOn, AssigneeIds, ProjectId, ToDoListId ) VALUES ('What to do', 'description', '2017-11-11', '2017-11-12', '333333333', '1111111111', '22222222')

Update

You can change the columns that are not read-only by providing the ProjectId, to-do Id, and new values.

UPDATE ToDos SET Content = 'Updated content' WHERE ProjectId = '11111111' AND Id = '1234567689'

You can change the Completed state of the to-do by providing the ProjectId and to-do Id. The Completed column can only be updated standalone, and other columns cannot be updated in the same query.

UPDATE ToDos SET Completed = True WHERE ProjectId = '11111111' AND Id = '1234567689'

Delete

To-dos cannot be deleted.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The unique identifier of the todo.
ProjectId	Long	True	Projects.Id	The Id of the Project associated with the to-do list.
ToDoListId	Long	True	ToDoLists.Id	The Id of the To Do list the to-do belongs to.
Status	String	True		The state the todo.
CreatedAt	Datetime	True		The date and time when the event was created.
UpdatedAt	Datetime	True		The date and time when the record was last updated.
StartsOn	Date	False		The date and time when the todo should start.
DueOn	Date	False		The date and time when the todo is due.
CommentsCount	Integer	True		Number of coments the document has.
Description	String	False		The to-do list description.
Content	String	False		What to do.
AssigneesIds	String	False		List of assigned user Ids.
Completed	Boolean	False		The to-do state.
CompletedAt	Datetime	True		The tdate and time when the todo was completed.
CompletionCreatorId	String	True		The id of the user who completed the todo.
CompletionCreatorName	String	True		The name of the user who completed the todo.
BucketName	String	True		The to-do list bucket name.
BucketType	String	True		The to-do list bucket type.
ParentTitle	String	True		Title of the parent.
ParentType	String	True		Type of the parent.
CreatorId	Long	True	People.Id	Event creator Id.
CreatorAttachableSgid	String	True		Event creator Attachable SGID.
CreatorName	String	True		Event creator Name.
CreatorEmailAddress	String	True		Event creator Email Address.
CreatorTitle	String	True		Event creator Title.
CreatorBio	String	True		Event creator Bio.
CreatorCreatedAt	Datetime	True		Event creator creation date and time.
CreatorUpdatedAt	Datetime	True		Event creator update date and time.
CreatorAdmin	Boolean	True		Boolean indicationg if the event creator is an admin.
CreatorOwner	Boolean	True		Boolean indicationg if the event creator is an owner.
CreatorTimeZone	String	True		Event creator's timezone.
CreatorCompanyId	String	True		Event creator's company Id.
CreatorCompanyName	String	True		Event creator's company name.

Retrieve Vaults(folders) on Basecamp 3 folders.

Table Specific Information

All projects have a primary Vault (folder). Additional Vaults may be nested under the primary Vault or any child Vault.

Select

The columns supported as search criteria by Basecamp are ProjectId, VaultId, and Id. If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM Vaults WHERE  ProjectId = '11111111' AND VaultId = '222222222'

You can also retrieve a single To-do by specifying the Id.

SELECT * FROM Vaults WHERE  ProjectId = '11111111' Id = '333333333'

INSERT

You can insert a new Vault by providing the ProjectId, VaultId, and Title, which are required parameters.

INSERT INTO Vaults (Title, ProjectId, Vaultid) VALUES ('InsertedVault', '11111111', '222222222')

Update

You can update the Vault title by providing the ProjectId, VaultId, and the new title.

UPDATE Vaults SET Title = 'Updated title' WHERE ProjectId = '11111111' AND Id = '333333333'

Delete

Vaults cannot be deleted.

Columns

Name	Type	ReadOnly	References	Description
Id [KEY]	Long	True		The unique identifier of the topic.
ProjectId	Long	True	Projects.Id	The Project Id associated with the vault.
Status	String	True		The status of the vault.
Title	String	False		The title of the returned record.
Type	String	True		The type of the returned record.
CreatedAt	Datetime	True		The date and time when the record was created.
UpdatedAt	Datetime	True		The date and time when the record was last updated.
ParentId	Long	True		Id of the parent.
ParentTitle	String	True		Title of the parent.
ParentType	String	True		Type of the parent.
BucketId	Long	True		Id of the containing bucket.
BucketName	String	True		Name of the containing bucket.
BucketType	String	True		Type of the containing bucket.
CreatorId	Long	True	People.Id	The Id of the creator.
CreatorName	String	True		The name of the creator.
DocumentsCount	Integer	True		Number of documents the vault contains.
UploadsCount	Integer	True		Number of uploads the vault contains.
VaultsCount	Integer	True		Number of vaults the vault contains.

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
VaultId	Long	The Vault Id that the record belongs to.

Views are similar to tables in the way that data is represented; however, views are read-only.

Queries can be executed against a view as if it were a normal table.

CData Cloud - Basecamp Views

Name	Description
Assignments	Retrieve Assignments on Basecamp 3.
Calendars	Retrieve calendars on Basecamp 3.
Cards	A card table is made of multiple columns which contain cards.
Events	Retrieve activity Events on Basecamp 3.
People	Retrieve People on Basecamp 3.
ProjectTools	Retrieve project tools on Basecamp 3.
Recordings	Retrieve recordings on Basecamp 3 folders.
Uploads	Retrieve Uploads on Basecamp 3.

Retrieve Assignments on Basecamp 3.

Columns

Name	Type	References	Description
Id [KEY]	Long		The unique identifier for the person.
TodoListId	Long	ToDoLists.Id	The unique identifier for the todolist.
ProjectId	Long	Projects.Id	Use filter to retrieve active people on the project with the given ID.
Name	String		The name of the person.
Owner	Boolean		Boolean value to show if the current person is the account owner.
Admin	Boolean		Boolean value to show if the current person is an admin or not.
Title	String		The title of the person.
Bio	String		Bio description of the person.
CreatedAt	Datetime		The date and time when the record was created.
UpdatedAt	Datetime		The date and time when the record was last updated.
EmailAddress	String		The email address of the person.
AttachableSgid	String		The attachable Set Group ID.
PersonableType	String		Type of the person.
TimeZone	String		Human readable timezone for the user.
CompanyId	Long		Id of users company.
CompanyName	String		Name of users company.

Retrieve calendars on Basecamp 3.

Table Specific Information

There is only one Calendar (Schedule) in each Basecamp project.

Select

Retrieve more detail on a specific Calendar (ProjectId and Id are the only columns supported as search criteria by Basecamp). If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM Calendars WHERE ProjectId = '111111111' AND Id = '123456789'

Columns

Name	Type	References	Description
Id [KEY]	Long	Projects.CalendarId	The unique identifier of the calendar.
ProjectId	Long	Projects.Id	The Project Id associated with the calendar.
Status	String		The status of the calendar.
Type	String		The type of the returned record.
CreatedAt	Datetime		The date and time when the calendar was created.
UpdatedAt	Datetime		The date and time when the calendar was last updated.
Title	String		Title of the calendar.
EntriesCount	Integer		Number of calendar events.
BucketId	Long		Id of the containing bucket.
BucketName	String		Name of the containing bucket.
BucketType	String		Type of the containing bucket.
CreatorId	Long	People.Id	The Id of the creator.
CreatorName	String		The name of the creator.

A card table is made of multiple columns which contain cards.

Columns

Name	Type	References	Description
Id [KEY]	Long	Projects.KanbanBoardId
ProjectId	Long	Projects.Id	The Project Id associated with the calendar.
AppUrl	String		App URL
BookmarkUrl	String		Bookmark URL
BucketId	Long		The Id of the bucket
BucketName	String		Name of the bucket
BucketType	String		Type of the bucket
CreatedAt	Datetime		The date and time when card was created
CreatorAdmin	Boolean		The admin who created the card
CreatorAttachableSGID	String		Attachable SGID of Creator
CreatorAvatarUrl	String		Avatar URL of Creator
CreatorBio	String		Bio of Creator
CreatorCanManagePeople	Boolean		Boolean to tell whether creator can manage people.
CreatorCanManageProjects	Boolean		Boolean to tell whether creator can manage project.
CreatorClient	Boolean		Creator Client
CreatorCompanyId	Long		Creator Company Id
CreatorCompanyName	String		Creator Company Name
CreatorCreatedAt	Datetime		Creator Created At
CreatorEmailAddress	String		Email Address of Creator
CreatorEmployee	Boolean		Creator Employee
CreatorId	Long		Creator Id
CreatorLocation	String		Creator Location
CreatorName	String		Creator Name
CreatorOwner	Boolean		Creator Owner
CreatorPersonableType	String		Creator Personable Type
CreatorTimeZone	String		Creator TimeZone
CreatorRitle	String		Creaor Title
CreatorUpdatedAt	Datetime		Creator Updated At
InheritsStatus	Boolean		Inherits Status
Lists	String		Lists
Status	String		Status
Subscribers	String		Subscribers
SubscriptionUrl	String		Subscription URL
Title	String		Title
Type	String		Type
UpdatedAt	Datetime		Updated At
Url	String		URL
VisibleToClients	Boolean		Visible to clients

Retrieve activity Events on Basecamp 3.

View Specific Information

An event is created any time a recording changes.

Select

You can retrieve all activity Events in the specified project and recording. The columns supported as search criteria by Basecamp are ProjectId and RecordingId.

If ProjectId is not specified in the query, the default ProjectId will be used.

SELECT * FROM Events WHERE ProjectId = '1111111' AND RecordingId = '22222222'

Columns

Name	Type	References	Description
Id [KEY]	Long		The unique identifier of the event.
ProjectId	Long	Projects.Id	The Id of the Project associated with the document.
RecordingId	Long	Recordings.Id	The Id of the Recording associated with this event.
Action	String		The action description for the event.
Details	String		Details on the event.
CreatedAt	Datetime		The date and time when the event was created.
CreatorId	Long	People.Id	Event creator Id.
CreatorAttachableSgid	String		Event creator Attachable SGID.
CreatorName	String		Event creator Name.
CreatorEmailAddress	String		Event creator Email Address.
CreatorTitle	String		Event creator Title.
CreatorBio	String		Event creator Bio.
CreatorCreatedAt	Datetime		Event creator creation date and time.
CreatorUpdatedAt	Datetime		Event creator update date and time.
CreatorAdmin	Boolean		Boolean indicationg if the event creator is an admin.
CreatorOwner	Boolean		Boolean indicationg if the event creator is an owner.
CreatorTimeZone	String		Event creator's timezone.
CreatorCompanyId	Long		Event creator's company Id.
CreatorCompanyName	String		Event creator's company name.

Retrieve People on Basecamp 3.

View Specific Information

Select

Retrieve all People visible to the current user, list people by Project, or specify an Id to get more information. Id and ProjectId are the columns supported by Basecamp as search criteria.

SELECT * FROM People WHERE Id = '123456789'

SELECT * FROM People WHERE ProjectId = '5168618'

Columns

Name	Type	References	Description
Id [KEY]	Long		The unique identifier for the person.
Name	String		The name of the person.
Owner	Boolean		Boolean value to show if the current person is the account owner.
Admin	Boolean		Boolean value to show if the current person is an admin or not.
Title	String		The title of the person.
Bio	String		Bio description of the person.
CreatedAt	Datetime		The date and time when the record was created.
UpdatedAt	Datetime		The date and time when the record was last updated.
EmailAddress	String		The email address of the person.
AttachableSgid	String		The attachable Set Group ID.
PersonableType	String		Type of the person.
TimeZone	String		Human readable timezone for the user.
CompanyId	Long		Id of users company.
CompanyName	String		Name of users company.

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
ProjectId	Long		Use filter to retrieve active people on the project with the given ID.

Retrieve project tools on Basecamp 3.

Table Specific Information

Select

No filters are supported server side for this table. All criteria will be handled client side within the Cloud.

For example, the following queries are processed server side:

	SELECT * FROM ProjectTools

Columns

Name	Type	References	Description
Id	Long		The id of the tool associated with the project.
Name	String		The name of the tool associated with the project.
AppUrl	String		The app url of the tool associated with this project.
Enabled	Boolean		Boolean value for a tool of the project.
Position	Int		The postion of the tool associated with this project.
Title	String		The title of the tool associated with this project.
Url	String		The url of the tool associated with this project.
ProjectId	Long		The unique identifier of the project.

Retrieve recordings on Basecamp 3 folders.

View Specific Information

Retrieve most of the data structures in the Basecamp 3, which are represented as "Recordings."

Select

The columns supported as search criteria by Basecamp are Type (required parameter), Bucket, and Status.

SELECT * FROM Recordings WHERE type = 'Upload' AND Bucket = '1111111, 22222222' AND Status = 'active'

Columns

Name	Type	References	Description
Id [KEY]	Long		The unique identifier of the topic.
Status	String		The status of the vault. The allowed values are active, archived, trashed.
Type	String		The type of the vault. The allowed values are Comment, Document, Message, Question::Answer, Schedule::Entry, Todo, Todolist, Upload, Kanban::Card, Kanban::Step, Vault.
CreatedAt	Datetime		The date and time when the event was created.
UpdatedAt	Datetime		The date and time when the event was last modified.
CreatorId	Long	People.Id	Event Creator.
CreatorName	String		Event Creator.

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
Bucket	String		Single or comma separated list of project IDs. Default: All active projects visible to the current user

Retrieve Uploads on Basecamp 3.

Columns

Name	Type	References	Description
Id [KEY]	Long		The unique identifier for the file uploaded.
ProjectId	Long	Projects.Id	The Id of the Project associated with the file uploaded.
VaultId	Long	Vaults.Id	The Vault Id associated with the file uploaded.
Status	String		The status of the vault.
Title	String		The title of the file uploaded.
ContentType	String		The content type of the file uploaded
Type	String		The type of the returned record.
CreatedAt	Datetime		The date and time when the file uploaded was created.
UpdatedAt	Datetime		The date and time when the file uploaded was last updated.
ParentTitle	String		Title of the parent.
ParentType	String		Type of the parent.
BucketName	String		Name of the containing bucket.
BucketType	String		Type of the containing bucket.
CreatorId	Long	People.Id	The Id of the creator.
CreatorName	String		The name of the creator.
CommentsCount	Integer		Number of comments the file uploaded has.
DownloadURL	String		Number of comments the file uploaded has.
ByteSize	Integer		Number of comments the file uploaded has.

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

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

CData Cloud - Basecamp Stored Procedures

Name	Description
ChangeColumnColor	Allows changing the color of the column.
CreateOnHoldSectionColumn	Creates an 'on_hold' section in the column.
MoveCard	Allows moving of a card to 'In Progress' State.
MoveColumn	Allows moving of a column.
RemoveOnHoldSectionColumn	Removes an 'on_hold' section in the column.
UnWatchColumn	Stops watching the column.
WatchColumn	Starts watching the column.

Allows changing the color of the column.

Stored Procedure Specific Information

Basecamp allows only a small subset of columns to be used in the Exec query. These columns can typically be used with only = comparison. For example:

EXECUTE ChangeColumnColor ProjectId = '37595039', CardTableColumnId = '7416523073', Color = 'orange'

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the project.
CardTableColumnId	String	True	The CardTableColumn Id.
Color	String	True	The color. Available values are white, red, orange, yellow, green, blue, aqua, purple, gray, pink and brown.

Result Set Columns

Name	Type	Description
Success	String	This value shows a boolean indication of whether the operation was successful or not.

Creates an 'on_hold' section in the column.

Stored Procedure Specific Information

Basecamp allows only a small subset of columns to be used in the Exec query. These columns can typically be used with only = comparison. For example:

EXECUTE CreateOnHoldSectionColumn ProjectId = '37595039', CardTableColumnId = '7416523073'

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the project.
CardTableColumnId	String	True	The CardTableColumn Id.

Result Set Columns

Name	Type	Description
Success	String	This value shows a boolean indication of whether the operation was successful or not.

Allows moving of a card to 'In Progress' State.

Stored Procedure Specific Information

Basecamp allows only a small subset of columns to be used in the Exec query. These columns can typically be used with only = comparison. For example:

EXECUTE MoveCard ProjectId = '37595039', CardTableCardId = '7416545710', CardTableColumnId = '7416523073'

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the project.
CardTableCardId	String	True	The CardTableCard Id.
CardTableColumnId	String	True	The CardTableColumn Id.

Result Set Columns

Name	Type	Description
Success	String	This value shows a boolean indication of whether the operation was successful or not.

Allows moving of a column.

Stored Procedure Specific Information

Basecamp allows only a small subset of columns to be used in the Exec query. These columns can typically be used with only = comparison. For example:

EXECUTE MoveColumn ProjectId = '37595039', CardTableCardId = '7416545710', SourceId = '7416523073', TargetId = '7416545710', Position = 2

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the project.
CardTableCardId	String	True	The CardTableCard Id.
SourceId	String	True	The id of the column to move.
TargetId	String	True	The id of the card table in which we want to move the column.
Position	String	False	Index among the columns (ignoring Triage, Not Now or Done). Default value is 1.

Result Set Columns

Name	Type	Description
Success	String	Project star result.

Removes an 'on_hold' section in the column.

Stored Procedure Specific Information

Basecamp allows only a small subset of columns to be used in the Exec query. These columns can typically be used with only = comparison. For example:

EXECUTE RemoveOnHoldSectionColumn ProjectId = '37595039', CardTableColumnId = '7416523073'

Input

Name	Type	Required	Description
ProjectId	String	True	Id of the project.
CardTableColumnId	String	True	The CardTableColumn Id.

Result Set Columns

Name	Type	Description
Success	String	This value shows a boolean indication of whether the operation was successful or not.