Basecamp Version Support

The connector models Basecamp 2 and Basecamp 3 accounts as bidirectional tables.

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

Authenticate via Basic Authentication

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.

Authenticate via OAuth Authentication

To use OAuth, set the AuthScheme to OAuth.

You can connect without setting any connection properties for your user credentials. Set InitiateOAuth to GETANDREFRESH to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.

When you connect, the connector opens the Basecamp OAuth endpoint in your default browser. Log in and grant permissions to the connector. The connector then completes the following OAuth process:

1. Extracts the access token from the callback URL and authenticates requests.
2. Obtains a new access token when the old one expires.
3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

See Using OAuth Authentication for other OAuth authentication flows.

OAuth requires the authenticating user to interact with Basecamp using the browser. The connector facilitates this in various ways as described below.

Embedded Credentials

See Embedded Credentials to connect with the connector's embedded credentials and skip creating a custom OAuth app.

Custom Credentials

Instead of connecting with the connector's embedded credentials, you can register an app to obtain the OAuthClientId and OAuthClientSecret.

When to Create a Custom OAuth App

Creating a custom OAuth app is optional as the connector is already registered with Basecamp and you can connect with its embedded credentials. You might want to create a custom OAuth app to change the information displayed when users log into the Basecamp OAuth endpoint to grant permissions to the connector.

Creating a Custom OAuth App

See Creating a Custom OAuth App for a procedure.

Authenticate using the Embedded OAuth Credentials

Desktop Authentication with the Embedded OAuth App

You can connect without setting any connection properties for your user credentials. After setting the following, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AccountId: Copy this from the URL after you log into Basecamp:

  https://basecamp.com/<AccountId>

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

When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process.

1. Extracts the access token from the callback URL and authenticates requests.
2. Obtains a new access token when the old one expires.
3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

When to Use a Custom OAuth App

Creating a custom OAuth app is optional as the connector is already registered with Basecamp and you can connect with its embedded credentials.

You might want to create a custom OAuth app to change the information displayed when users log into Basecamp to grant permissions to the connector.

Desktop Authentication with a Custom OAuth App

Follow the steps below to authenticate with the credentials for a custom OAuth app. See Creating a Custom OAuth App for more information.

Get and Refresh the OAuth Access Token

After setting the following, you are ready to connect:

• OAuthClientId: Set this to the client ID assigned when you registered your app.
• OAuthClientSecret: Set this to the client secret assigned when you registered your app.
• CallbackURL: Set this to the redirect URI defined when you registered your app.
• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• AccountId: Copy this from the URL after you log into Basecamp:

  https://basecamp.com/<AccountId>

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

When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

1. Extracts the access token from the callback URL and authenticates requests.
2. Refreshes the access token when it expires.
3. Saves OAuth values in OAuthSettingsLocation to be persisted across connections.

Using OAuth on a Headless Machine

To create Basecamp data sources on headless servers or other machines on which the connector cannot open a browser, you need to authenticate from another machine. Authentication is a two-step process.

1. Instead of installing the connector on another machine, you can follow the steps below to obtain the OAuthVerifier value. Or, you can install the connector on another machine and transfer the OAuth authentication values, after you authenticate through the usual browser-based flow.
2. You can then configure the connector to automatically refresh the access token from the headless machine.

You can follow the headless OAuth authentication flow using the connector's embedded OAuth credentials or using the OAuth credentials for your custom OAuth app.

Using the Embedded OAuth Credentials

Obtain a Verifier Code

Follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property:

1. Click the following link to open the Basecamp OAuth endpoint in your browser.
2. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. You will set this in the OAuthVerifier connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values.

• OAuthVerifier: Set this to the verifier code.
• InitiateOAuth: Set this to REFRESH.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified file.
• AccountId: Copy this from the URL after you log into Basecamp:

  https://basecamp.com/<AccountId>

After the OAuth settings file is generated, set the following properties to connect to data:

• OAuthSettingsLocation: Set this to the file containing the encrypted OAuth authentication values. Make sure this file gives read and write permissions to the connector to enable the automatic refreshing of the access token.
• InitiateOAuth: Set this to REFRESH.
• AccountId: Copy this from the URL after you log into Basecamp:

  https://basecamp.com/<AccountId>

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

Transfer OAuth Settings

Follow the steps below to install the connector on another machine, authenticate, and then transfer the resulting OAuth values.

On a second machine, install the connector and connect with the following properties set:

• OAuthSettingsLocation: Set this to a writable text file.
• InitiateOAuth: Set this to GETANDREFRESH.
• AccountId: Copy this from the URL after you log into Basecamp:

  https://basecamp.com/<AccountId>

Test the connection to authenticate in the browser. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthSettingsLocation: Set this to the path to your OAuth settings file. Make sure this file gives read and write permissions to the connector to enable the automatic refreshing of the access token.
• AccountId: Copy this from the URL after you log into Basecamp:

  https://basecamp.com/<AccountId>

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

Using the Credentials for a Custom OAuth App

Create a Custom OAuth App

Creating a custom OAuth app is optional in the headless OAuth flow; you can skip creating an app by connecting with the connector's embedded OAuth credentials. You might want to create a custom OAuth app to change the information displayed when users log into Basecamp to grant permissions to the connector.

See Creating a Custom OAuth App for a procedure. You can then follow the procedures below to authenticate and connect to data.

Obtain a Verifier Code

Set the following properties on the headless machine:

• InitiateOAuth: Set this to OFF.
• OAuthClientId: Set this to the Client ID in your app settings.
• OAuthClientSecret: Set this to the Client Secret in your app settings.
• AccountId: Copy this from the URL after you log into Basecamp:

  https://basecamp.com/<AccountId>

You can then follow the steps below to authenticate from another machine and obtain the OAuthVerifier connection property.

1. Call the GetOAuthAuthorizationURL stored procedure* with the CallbackURL input parameter set to the exact Redirect URI you specified in your app settings.
2. Open the returned URL in a browser. Log in and grant permissions to the connector. You are then redirected to the callback URL, which contains the verifier code.
3. Save the value of the verifier code. You will set this in the OAuthVerifier connection property.

On the headless machine, set the following connection properties to obtain the OAuth authentication values:

• OAuthClientId: Set this to the consumer key in your app settings.
• OAuthClientSecret: Set this to the consumer secret in your app settings.
• OAuthVerifier: Set this to the verifier code.
• OAuthSettingsLocation: Set this to persist the encrypted OAuth authentication values to the specified file.
• InitiateOAuth: Set this to REFRESH.
• AccountId: Copy this from the URL after you log into Basecamp:

  https://basecamp.com/<AccountId>

After the OAuth settings file is generated, set the following properties to connect to data:

• OAuthClientId: Set this to the consumer key in your app settings.
• OAuthClientSecret: Set this to the consumer secret in your app settings.
• OAuthSettingsLocation: Set this to the file containing the encrypted OAuth authentication values. Make sure this file gives read and write permissions to the provider to enable the automatic refreshing of the access token.
• InitiateOAuth: Set this to REFRESH.
• AccountId: Copy this from the URL after you log into Basecamp:

  https://basecamp.com/<AccountId>

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

Transfer OAuth Settings

Follow the steps below to install the connector on another machine, authenticate, and then transfer the resulting OAuth values.

On a second machine, install the connector and connect with the following properties set:

• OAuthSettingsLocation: Set this to a writable text file.
• InitiateOAuth: Set this to GETANDREFRESH.
• OAuthClientId: Set this to the client ID assigned when you registered your app.
• OAuthClientSecret: Set this to the client secret assigned when you registered your app.
• CallbackURL: Set this to the Redirect URI you specified in your app settings.
• AccountId: Copy this from the URL after you log into Basecamp:

  https://basecamp.com/<AccountId>

Test the connection to authenticate. The resulting authentication values are written, encrypted, to the path specified by OAuthSettingsLocation. Once you have successfully tested the connection, copy the OAuth settings file to your headless machine. On the headless machine, set the following connection properties to connect to data:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the consumer key in your app settings.
• OAuthClientSecret: Set this to the consumer secret in your app settings.
• OAuthSettingsLocation: Set this to the path to your OAuth settings file. Make sure this file gives read and write permissions to the connector to enable the automatic refreshing of the access token.
• AccountId: Copy this from the URL after you log into Basecamp:

  https://basecamp.com/<AccountId>

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

When to Create a Custom OAuth App

Creating a custom OAuth app is optional as the connector is already registered with Basecamp and you can connect with its embedded credentials.

You might want to create a custom OAuth app to change the information displayed when users log into the Basecamp OAuth endpoint to grant permissions to the connector.

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: Desktop

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

• OAuthClientId
• OAuthClientSecret
• CallbackURL

Set Redirect URI to http://localhost:portnumber and set CallbackURL to match. You can specify any port available.

This section details a selection of advanced features of the Basecamp connector.

User Defined Views

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

SSL Configuration

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

Proxy

To configure the connector using Private Agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

The Jitterbit Connector for Basecamp allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Defining Views Using a Configuration File

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

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

This User Defined View configuration file is formatted as follows:

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

For example:

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

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

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

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Working with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

Customizing the SSL Configuration

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

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

The Jitterbit Connector for Basecamp 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. You can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

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

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 Jitterbit Connector for Basecamp 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 Jitterbit Connector for Basecamp to automate working with Basecamp data.

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

The connector models the data in Basecamp into a list of tables that can be queried using standard SQL statements.

Generally, querying Basecamp tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Jitterbit Connector for 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', 'someone@example.com, someoneelse@example.com')

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'

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'

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 composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.

Jitterbit Connector for 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 desc=
CreatorFullsizeAvatarUrl	String desc=
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

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 available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.

Jitterbit Connector for Basecamp Stored Procedures

Name	Description
GetOAuthAccessToken	Gets the OAuth access token from Basecamp.
GetOAuthAuthorizationURL	Gets the Basecamp authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to Basecamp.
PublishProject	Will publish (activate) a project created from a template (draft project).
RefreshOAuthAccessToken	Refreshes the OAuth access token used for authentication with various Basecamp services.
StarProject	Will star a specified project.
UnstarProject	Will unstar a specified project.

Gets the OAuth access token from Basecamp.

Input

Name	Type	Required	Description
AuthMode	String	True	WEB or APP. The Basecamp authentication mode. Default is APP. The default value is APP.
CallbackUrl	String	False	The URL the user will be redirected to after authorizing your application.
Verifier	String	False	The verifier token returned by Basecamp. Required for only the Web AuthMode.
State	String	False	This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to Basecamp authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery.

Result Set Columns

Name	Type	Description
OAuthAccessToken	String	The OAuth token.
OAuthRefreshToken	String	The OAuth refresh token.
ExpiresIn	String	The remaining lifetime for the access token in seconds.

Gets the Basecamp authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to Basecamp.

Input

Name	Type	Required	Description
CallbackUrl	String	False	The URL that Basecamp will return to after the user has authorized your app.
State	String	False	This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to Basecamp authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery.

Result Set Columns

Name	Type	Description
URL	String	The URL to be entered into a Web browser to obtain the verifier token and authorize the data provider with.

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.

Refreshes the OAuth access token used for authentication with various Basecamp services.

Input

Name	Type	Required	Description
OAuthRefreshToken	String	True	The refresh token returned with the previous access token.

Result Set Columns

Name	Type	Description
OAuthAccessToken	String	The authentication token returned from Basecamp. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.

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 connector models the data in Basecamp into a list of tables that can be queried using standard SQL statements.

Generally, querying Basecamp tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Jitterbit Connector for Basecamp Tables

Name	Description
CalendarEvents	Retrieve calendar events on Basecamp 3.
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.

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

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	True	Projects.Id	The ID of the Project associated with the to-do list.
ToDoSetId	Long	True	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 composed of columns and pseudo columns. Views are similar to tables in the way that data is represented; however, views do not support updates. Entities that are represented as views are typically read-only entities. Often, a stored procedure* is available to update the data if such functionality is applicable to the data source.

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard. To find out more about tables and stored procedures, please navigate to their corresponding entries in this help document.

Jitterbit Connector for Basecamp Views

Name	Description
Assignments	Retrieve Assignments on Basecamp 3.
Calendars	Retrieve calendars on Basecamp 3.
Events	Retrieve activity Events on Basecamp 3.
People	Retrieve People 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.

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

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 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.
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 available to complement the data available from the Data Model. It may be necessary to update data available from a view using a stored procedure* because the data does not provide for direct, table-like, two-way updates. In these situations, the retrieval of the data is done using the appropriate view or table, while the update is done by calling a stored procedure. Stored procedures* take a list of parameters and return back a dataset that contains the collection of tuples that constitute the response.

Jitterbit Connector for Basecamp Stored Procedures

Name	Description
GetOAuthAccessToken	Gets the OAuth access token from Basecamp.
GetOAuthAuthorizationURL	Gets the Basecamp authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to Basecamp.
RefreshOAuthAccessToken	Refreshes the OAuth access token used for authentication with various Basecamp services.

Gets the OAuth access token from Basecamp.

Input

Name	Type	Required	Description
AuthMode	String	True	WEB or APP. The Basecamp authentication mode. Default is APP. The default value is APP.
CallbackUrl	String	False	The URL the user will be redirected to after authorizing your application.
Verifier	String	False	The verifier token returned by Basecamp. Required for only the Web AuthMode.
State	String	False	This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to Basecamp authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery.

Result Set Columns

Name	Type	Description
OAuthAccessToken	String	The OAuth token.
OAuthRefreshToken	String	The OAuth refresh token.
ExpiresIn	String	The remaining lifetime for the access token in seconds.

Gets the Basecamp authorization URL. Access the URL returned in the output in a Web browser. This requests the access token that can be used as part of the connection string to Basecamp.

Input

Name	Type	Required	Description
CallbackUrl	String	False	The URL that Basecamp will return to after the user has authorized your app.
State	String	False	This field indicates any state that may be useful to your application upon receipt of the response. Your application receives the same value it sent, as this parameter makes a round-trip to Basecamp authorization server and back. Uses include redirecting the user to the correct resource in your site, using nonces, and mitigating cross-site request forgery.

Result Set Columns

Name	Type	Description
URL	String	The URL to be entered into a Web browser to obtain the verifier token and authorize the data provider with.

Refreshes the OAuth access token used for authentication with various Basecamp services.

Input

Name	Type	Required	Description
OAuthRefreshToken	String	True	The refresh token returned with the previous access token.

Result Set Columns

Name	Type	Description
OAuthAccessToken	String	The authentication token returned from Basecamp. This can be used in subsequent calls to other operations for this particular service.
OAuthRefreshToken	String	A token that may be used to obtain a new access token.
ExpiresIn	String	The remaining lifetime on the access token.

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

Schema Tables

The following tables return database metadata for Basecamp:

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

Data Source Tables

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

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

Query Information Tables

The following table returns query statistics for data modification queries:

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

Lists the available databases.

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

SELECT * FROM sys_catalogs

Columns

Name	Type	Description
CatalogName	String	The database name.

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns

Name	Type	Description
CatalogName	String	The database name.
SchemaName	String	The schema name.

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns

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

Describes the columns of the available tables and views.

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

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='Projects' 

Columns

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

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns

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

Describes stored procedure* parameters.

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

SELECT * FROM sys_procedureparameters WHERE ProcedureName='RefreshOAuthAccessToken' AND Direction=1 OR Direction=2

Columns

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