Freshbooks

Introduction

The FreshBooks Data Provider wraps the complexity of accessing FreshBooks services in an easy-to-integrate, fully managed ADO.NET Data Provider. Applications then access FreshBooks through the FreshBooks Data Provider with simple Transact-SQL. The CData ADO.NET Provider for FreshBooks hides the complexity of accessing data and provides additional powerful security features, smart caching, batching, socket management, and more.

Connection String

To connect to FreshBooks, you can set the CompanyName and Token connection properties. Alternatively, you can use the OAuth authentication standard. OAuth can be used to enable other users to access their own company data. To authenticate using OAuth, you will need to obtain the OAuthClientId and OAuthClientSecret by registering an app.

Note: The authentication token method is deprecated and will not be supported by FreshBooks in the future.

Connecting to FreshBooks

Using OAuth

OAuth requires the authenticating user to interact with FreshBooks using the browser.

Register Your Application

Follow the steps below to obtain the OAuth client credentials:

Request Developer Access through https://www.freshbooks.com/developers. Create an account and Login to the account using the credentials.

Click My Account -> FreshBooks API.

Select the Use OAuth option and enter the application details.

The details are displayed to users when they log in to grant permissions to the application.

The OAuth consumer secret or OAuth secret is displayed.

To Get the ClientID or the CompanyName Click Settings -> Company.

Note: Use the OAuthSecret and the CompanyName in the connection string to connect to FreshBooks.

Authenticate to FreshBooks Using Web Application

To obtain the access token, set the following connection properties:

  • OAuthClientId: Set this to the name of the company you are connecting to. Note that you can also use the CompanyName property.
  • OAuthClientSecret: Set this to the consumer secret in your app settings.

When you connect via a Web application, or if the provider is not authorized to open a browser window, you need to exchange temporary verification values for the access token:

  • Get the URL to the FreshBooks OAuth endpoint.
  • Log in at the OAuth endpoint and authorize the app. You are redirected back to the callback URL.
  • The callback URL contains the verifier code in a query string parameter named “code”. Extract the verifier code from the callback URL.
  • Get The AccessToken.

To connect to data, set the following connection properties:

  • CompanyName
  • OAuthClientSecret
  • OAuthAccessToken
  • OAuthAccessTokenSecret

Data Migrations

To Check for the data migrations:

Add a new connection in Loome Integrate as shown.

  • Go to tasks and click on Connections.

  • Add a new connection Using Add New Connection Option.

  • Choose FreshBooks Connector from the available Connector Options.

Using the connection string parameters created, Verify the connection using the VERIFY CONNECTION Option in Loome Integrate.

Create a Job Using ADD A JOB option in Loome Integrate, add a data migration task to the job.

Create a New Task by right clicking job list and then Add a New Task Option. If you want to edit an Existing Task Use Edit Task.

Choose a Data Migration Task.

Choose the source and the destination for the migration of data. Name the Task.

Choose the Tables or the data that need to be migrated from the source to the destination. You can Copy all Tables by checking Copy all tables box.

Execute the Job. Check for results and the details of the data migrated in Execution History.

Data Model

The CData ADO.NET Provider for FreshBooks 2016 models FreshBooks entities in relational Tables, Views, and Stored Procedure.

Tables

The provider exposes tables for data sources that support both retrieving and updating data. The tables are normalized and contain an Id column, which is the primary key. This behaviour is consistent across all tables in the product.

Name Description
Categories A list of categories associated with a FreshBooks account. Only the master account can update and delete categories.
Clients A list of clients stored in FreshBooks. Staff can view and edit clients they are assigned to.
Contractors A list of contractors associated with a FreshBooks account.
Estimates A list of estimates found in FreshBooks. Staff can only access estimates for their assigned clients.
Expenses A list of expenses for the FreshBooks account. Staff can only access expenses that belong to them or are associated with clients they are assigned to.
Invoices A list of invoices associated with a given client Id. Staff can access invoices for clients that are assigned to them.
Items A list of items stored in FreshBooks.
Payments A list of payments stored in FreshBooks. Staff members can access payments for clients assigned to them.
Projects A list of projects associated with a FreshBooks account. Staff members can access all projects they’re assigned to, but can only edit projects for which they are the Project Manager.
Recurring A list of recurring profile summaries. Staff members can acces recurring profiles for clients they are assigned to.
Staff A list of staff associated with a FreshBooks account. Staff members can only access their own information.
Tasks A list of tasks associated with a FreshBooks account. Staff members can only use the ProjectId field for projects they’re assigned to.
Taxes A list of taxes associated with a FreshBooks account.
TimeEntries A list of time entries associated with a FreshBooks account. Staff can only create new time entries for projects they are assigned to

Views

Views are tables that cannot be modified. Typically, data that are read-only and cannot be updated are shown as views.

Name Description
ClientDetails A list of client details associated with a FreshBooks account.
EstimateDetails A list of estimate details associated with a FreshBooks account.
ExpenseDetails A list of expense details associated with a FreshBooks account.
ExpenseDetailsGroupByDate A list of expense details associated with a FreshBooks account.
Gateway A list of staff associated with a FreshBooks account.
InvoiceDetails A list of invoice details associated with a FreshBooks account.
InvoiceDetailsGroupByDate A list of invoice details associated with a FreshBooks account.
Languages A list of languages associated with a FreshBooks account.
ProfitDetails A list of profit details associated with a FreshBooks account.
ProfitDetailsGroupByDate A list of profit details associated with a FreshBooks account

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

Name Description
GetOAuthAccessToken Obtains the OAuth access token to be used for authentication with FreshBooks.
GetOAuthAuthorizationURL Obtains the OAuth authorization URL for authentication with FreshBooks