FreshBooks Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8895 of the connector.

Get Started¶

FreshBooks Version Support

The connector models the FreshBooks Alpha API as database objects that can be queried with SQL.

Establish a Connection¶

Connect to FreshBooks¶

FreshBooks uses the OAuth authentication standard. To authenticate using OAuth, you must create an app to obtain the OAuthClientId, OAuthClientSecret, and CallbackURL connection properties.

Desktop Applications¶

You must create a custom OAuth application to authenticate your connection. See Creating a Custom OAuth App for information about creating custom applications.

Get and Refresh the OAuth Access Token

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.
• OAuthClientId: Set this to the client ID in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings.
• CallbackURL: Set this to the redirect URI defined when you registered your app. For example: https://localhost:3333

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

1. The connector obtains an access token from FreshBooks and uses it to request data.
2. The OAuth values are saved in the location specified in OAuthSettingsLocation, to be persisted across connections.

The connector refreshes the access token automatically when it expires.

Create a Custom OAuth App¶

Connect to the Alpha API¶

Use the OAuth 2.0 authentication standard to authenticate to the FreshBooks Alpha APIs.

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

Register Your Application¶

To obtain the OAuth client credentials:

1. Log into the FreshBooks developers site at https://my.freshbooks.com/#/developer and click Create an App.

2. Enter information to be displayed to your users when they are prompted to grant permissions to your app.

3. Specify a redirect URI.

   Set the redirect URI to https://localhost:33333/, or some other similar https URL.

   If you are making a Web application, set the Callback URL to a page on your Web app you would like the user to be returned to after they have authorized your application.

Authenticate to FreshBooks from a Desktop Application¶

After setting the following connection properties, you are ready to connect:

• OAuthClientId: Set this to the name of the company you are connecting to.
• OAuthClientSecret: Set this to the consumer secret in your app settings.
• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the access token in the connection string.
• AccountId: Set this to connect to data. Query the IdentityInfo view 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 following OAuth process:

1. Retrieves the OAuthAccessToken and OAuthAccessTokenSecret and authenticates requests.
2. Refreshes the access token when it expires.
3. Saves OAuth values to be persisted across connections.

Authenticate to FreshBooks from a 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.
• OAuthClientSecret: Set this to the consumer secret in your app settings.

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

1. Call GetOAuthAuthorizationURL. The stored procedure returns the URL to the FreshBooks OAuth endpoint.

2. 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. The parameter is named "oauth_verifier". Extract the verifier code from the callback URL.

3. Call GetOAuthAccessToken. The stored procedure returns the access token.

To connect to data, set the following connection properties:

• AccountId
• OAuthClientSecret
• OAuthAccessToken
• OAuthAccessTokenSecret

Refresh the Token¶

To automatically refresh the access token when it expires, set InitiateOAuth to REFRESH and set OAuthRefreshToken. Alternatively, when the access token expires, call the RefreshOAuthAccessToken stored procedure to refresh the access token.

Important Notes¶

Configuration Files and Their Paths¶

• All references to adding configuration files and their paths refer to files and locations on the Harmony Agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features¶

This section details a selection of advanced features of the FreshBooks 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.

Query Processing

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

See Query Processing for more information.

User Defined Views¶

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

Define 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 Clients 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"

Define Views Using DDL Statements¶

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View¶

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View¶

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View¶

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

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.

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

SSL Configuration¶

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

Data Model¶

The FreshBooks connector can model your account as a database through one API the Data Model. You can programmatically access the available entities by querying the available System Tables.

Key Features

• The connector models FreshBooks Categories, Projects, and more, allowing you to write SQL to query FreshBooks data.
• Stored procedures allow you to execute operations to FreshBooks, including downloading and uploading objects.
• Live connectivity to these objects means any changes to your FreshBooks account are immediately reflected when using the connector.

Alpha API

The Data Model uses the OAuth 2.0 specification.

Tables¶

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

FreshBooks Connector Tables¶

BillPayments¶

Payments are a record of the payments made on your invoices.

Table Specific Information¶

Insert¶

To insert, BillID, PaidDate,Amount, PaymentType and Code fields are required. The below example illustrates how to insert in BillPayments table:

INSERT Into BillPayments (BillID, PaidDate, Amount, PaymentType, Code) Values ('12435', '2023-11-02', 2, 'Cash', 'INR')

Update¶

To update, the ID value is required in the WHERE clause.For example:

UPDATE BillPayments set PaymentType = 'Check' where ID = 8082

Delete¶

To delete, ID value is required in the WHERE clause.

DELETE from BillPayments where ID = '26157'

Columns¶

Bills¶

A Bill is used to record a business transaction where the items and services from a Vendor have been provided to the business owner, but payment isn’t due until a later date.

Table Specific Information¶

Insert¶

To insert, VendorID, IssueDate, DueOffsetDays, CurrencyCode, Language, LinesAggregate fields are required. To insert Bills with multiple line items, you can either insert an aggregate value into the LinesAggregate field, or use a temporary table. This section provides examples of both procedures. You can insert Bills with multiple line items using aggregates. CategoryId, UnitCostAmount and Quantity is required. For example:

Insert Into Bills (VendorID, IssueDate, DueOffsetDays, CurrencyCode, Language, LinesAggregate) Values ('16307', '2023-11-02', 2, INR, en, '[{\"categoryid\":\"46781\",\"quantity\":\"1\",\"unit_cost\":{\"amount\":\"200\"}}]')

You can also insert Bills with multiple line items using a temporary table.

The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:

INSERT Into BillsLineItems#Temp (CategoryId, UnitCostAmount, Quantity) Values ('4331719', 1, 2)

Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for LinesAggregate. For example:

INSERT Into Bills (VendorID, IssueDate, DueOffsetDays, CurrencyCode, Language, LinesAggregate) Values ('16307', '2023-11-02', 2, INR, en, BillsLineItems#Temp)

Update¶

To update, the ID value is required in the WHERE clause. To update LinesAggregate, you need to override the aggregate LinesAggregate field, either by setting an aggregate value, or by using a temporary table. For example:

Insert Into BillsLineItems#Temp (CategoryId, UnitCostAmount, Quantity) Values ('4331719', 1, 2)

UPDATE Bills SET IssueDate = '2023-11-01', LinesAggregate = 'BillsLineItems#TEMP' WHERE ID = 29153

Delete¶

To delete, ID value is required in the WHERE clause.

DELETE from Bills where ID = '26157'

Columns¶

BillVendors¶

A Vendor will work with your business to provide goods or services with a Bill to be paid at a later date.

Table Specific Information¶

Insert¶

To insert, VendorName, CurrencyCode and Language fields are required. To insert BillVendors with multiple tax defaults, you can either insert an aggregate value into the TaxDefaults field, or use a temporary table. This section provides examples of both procedures. You can insert BillVendors with multiple tax defaults using aggregates. For example:

Insert Into BillVendors (VendorName, CurrencyCode, Language, TaxDefaultsAggregate) Values ('Test23', 'INR', 'en', '[{\"system_taxid\":\"7620\"}]')

You can also insert Invoices with multiple line items using a temporary table.

The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:

INSERT INTO BillVendorsTaxDefaults#TEMP (SystemTaxId) VALUES ('23145')

Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for LinesAggregate. For example:

Insert INTO BillVendors (VendorName, CurrencyCode, Language, TaxDefaultsAggregate) Values ('Test23', 'INR', 'en',  BillVendorsTaxDefaults#TEMP)

Update¶

To update, the VendorId value is required in the WHERE clause. For example:

Update BillVendors set VendorName = 'testing' where VendorId = 16651

Delete¶

To delete, VendorId value is required in the WHERE clause.

DELETE from BillVendors where VendorId = '26157'

Columns¶

Clients¶

A client in the new FreshBooks is a resource representing an entity you send invoices to.

Columns¶

Estimates¶

Estimates in FreshBooks provides Owners and Clients a way to agree and negotiate on the price and scope of work before it commences.

Table Specific Information¶

Insert¶

To insert, CustomerId and CreateDate fields are required. To insert Estimates with multiple line items, you can either insert into the LinesAggregate field an aggregate value, or use a temporary table. This section provides examples of both procedures. You can insert Estimates with multiple line items using aggregates. For example:

Insert INTO Estimates (CustomerId, CreateDate, LinesAggregate) VALUES ('12717', '2018-02-03',
               '[\n" +
        "      {\n" +
        "        \"description\": \"\",\n" +
        "        \"taxName1\": \"\",\n" +
        "        \"taxAmount1\": 0,\n" +
        "        \"name\": \"Paperwork\",\n" +
        "        \"qty\": 1,\n" +
        "        \"taxName2\": \"\",\n" +
        "        \"taxAmount2\": 0,\n" +
        "        \"unit_cost\": {\n" +
        "            \"amount\": \"5000.00\",\n" +
        "            \"code\": \"USD\"\n" +
        "        }\n" +
        "      },\n" +
        "      {\n" +
        "        \"description\": \"\",\n" +
        "        \"taxName1\": \"\",\n" +
        "        \"taxAmount1\": 0,\n" +
        "        \"name\": \"TV Ads\",\n" +
        "        \"qty\": 1,\n" +
        "        \"taxName2\": \"\",\n" +
        "        \"taxAmount2\": 0,\n" +
        "        \"unit_cost\": {\n" +
        "            \"amount\": \"3000.00\",\n" +
        "            \"code\": \"USD\"\n" +
        "        }\n" +
        "      }\n" +
"    ]')"

You can also insert Estimates with multiple line items using a temporary table.

The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:

INSERT INTO EstimatesLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20)
INSERT INTO EstimatesLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10)
INSERT INTO EstimatesLineItems#TEMP (Name, Amount) VALUES ('C', 300 )
INSERT INTO EstimatesLineItems#TEMP (Name, Amount) VALUES ('D', 150 )
INSERT INTO EstimatesLineItems#TEMP (Name, Amount) VALUES ('E', 200 )

Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for LinesAggregate. For example:

Insert INTO Estimates (CustomerId, CreateDate, LinesAggregate) VALUES ('12717', '2018-02-03', 'EstimatesLineItems#TEMP')

Update¶

To update, the ID value is required in the WHERE clause. To update LinesAggregate, you need to override the aggregate LinesAggregate field, either by setting an aggregate value, or by using a temporary table. For example:

INSERT INTO Estimate2195LineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20)
INSERT INTO Estimate2195LineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10)

UPDATE Estimates SET EstimateNumber = 5, DiscountValue = 15, LinesAggregate = 'Estimate2195LineItems#TEMP' WHERE ID = 2195

Columns¶

Expenses¶

Expenses are used to track expenditures your business incurs.

Columns¶

InvoiceProfiles¶

Invoice Profiles are used to create recurring invoices. They have the ability to be saved as a draft invoice or be automatically sent out to the client via email.

Table Specific Information¶

Insert¶

To insert, Frequency, CreateDate, NumberRecurring and CustomerID fields are required. To insert InvoiceProfiles with multiple line items, you can either insert an aggregate value into the LinesAggregate field, or use a temporary table. This section provides examples of both procedures. You can insert Invoices with multiple line items using aggregates. For example:

Insert INTO InvoiceProfiles (Frequency, CreateDate, NumberRecurring, CustomerId, LinesAggregate) VALUES ('m', '2018-02-03', '2', '12761','[{ \"description\": \"This is description\", \"taxName1\": \"Tax1\", \"taxAmount1\": 0,\"name\": \"Paperwork\", \"unit_cost\": {\"amount\": \"5000.00\", \"code\": \"USD\"}}]')

You can also insert InvoiceProfiles with multiple line items using a temporary table.

The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:

INSERT INTO InvoicesLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20)
INSERT INTO InvoicesLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10)

Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for LinesAggregate. For example:

Insert INTO InvoiceProfiles (Frequency, CreateDate, NumberRecurring, CustomerId, LinesAggregate) VALUES ('m', '2018-02-03', '2', '12761', 'InvoicesLineItems#TEMP')

Update¶

To update, the ID value is required in the WHERE clause. To update LinesAggregate, you need to override the aggregate LinesAggregate field, either by setting an aggregate value, or by using a temporary table. For example:

INSERT INTO InvoiceLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20)
INSERT INTO InvoiceLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10)

UPDATE InvoiceProfiles SET CreateDate = '2023-11-08', LinesAggregate = 'InvoiceLineItems#TEMP' WHERE ID = 29153

Delete¶

To delete, ID value is required in the WHERE clause.

DELETE from InvoiceProfiles where ID = '26157'

Columns¶

Invoices¶

Invoices in FreshBooks are what gets sent to Clients, detailing specific goods or services performed or provided by the Administrator of their System, and the amount that Client owes to the Admin.

Table Specific Information¶

Insert¶

To insert, CustomerId and CreateDate fields are required. To insert Invoices with multiple line items, you can either insert an aggregate value into the LinesAggregate field, or use a temporary table. This section provides examples of both procedures. You can insert Invoices with multiple line items using aggregates. For example:

Insert INTO Invoices (CustomerId, CreateDate, LinesAggregate) VALUES ('12717', '2018-02-03',
               '[\n" +
        "      {\n" +
        "        \"description\": \"\",\n" +
        "        \"taxName1\": \"\",\n" +
        "        \"taxAmount1\": 0,\n" +
        "        \"name\": \"Paperwork\",\n" +
        "        \"qty\": 1,\n" +
        "        \"taxName2\": \"\",\n" +
        "        \"taxAmount2\": 0,\n" +
        "        \"unit_cost\": {\n" +
        "            \"amount\": \"5000.00\",\n" +
        "            \"code\": \"USD\"\n" +
        "        }\n" +
        "      },\n" +
        "      {\n" +
        "        \"description\": \"\",\n" +
        "        \"taxName1\": \"\",\n" +
        "        \"taxAmount1\": 0,\n" +
        "        \"name\": \"TV Ads\",\n" +
        "        \"qty\": 1,\n" +
        "        \"taxName2\": \"\",\n" +
        "        \"taxAmount2\": 0,\n" +
        "        \"unit_cost\": {\n" +
        "            \"amount\": \"3000.00\",\n" +
        "            \"code\": \"USD\"\n" +
        "        }\n" +
        "      }\n" +
"    ]')"

You can also insert Invoices with multiple line items using a temporary table.

The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:

INSERT INTO InvoicesLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20)
INSERT INTO InvoicesLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10)
INSERT INTO InvoicesLineItems#TEMP (Name, Amount) VALUES ('C', 300)
INSERT INTO InvoicesLineItems#TEMP (Name, Amount) VALUES ('D', 150)
INSERT INTO InvoicesLineItems#TEMP (Name, Amount) VALUES ('E', 200)

Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for LinesAggregate. For example:

Insert INTO Invoices (CustomerId, CreateDate, LinesAggregate) VALUES ('12717', '2018-02-03', 'InvoicesLineItems#TEMP')

Update¶

To update, the ID value is required in the WHERE clause. To update LinesAggregate, you need to override the aggregate LinesAggregate field, either by setting an aggregate value, or by using a temporary table. For example:

INSERT INTO InvoiceLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20)
INSERT INTO InvoiceLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10)

UPDATE Invoices SET CreateDate = '2018-01-01', LinesAggregate = 'InvoiceLineItems#TEMP' WHERE ID = 29153

Columns¶

Items¶

Items are stored from invoice lines to make invoicing easier in the future.

Columns¶

OtherIncomes¶

Other Income is for recording income that doesn’t require an invoice and is received through other means.

Table Specific Information¶

Insert¶

To insert OtherIncomes Date and Source fields are required. To insert OtherIncomes with multiple taxes, you can either insert an aggregate value into the TaxesAggregate field, or use a temporary table. This section provides examples of both procedures. You can insert OtherIncomes with multiple taxes using aggregates. For example:

Insert INTO OtherIncomes (Date, Source, TaxesAggregate) VALUES ('2018-02-03','Shopify','[{\"amount\" : \"5\", \"name\" : \"GST\"}]')"

You can also insert OtherIncomes with multiple taxes using a temporary table.

The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:

INSERT INTO OtherIncomesTaxes#TEMP (Name, Amount) VALUES ('Tax1', 500)
INSERT INTO OtherIncomesTaxes#TEMP (Name, Amount) VALUES ('Tax2', 200)

Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for TaxesAggregate. For example:

Insert INTO OtherIncomes (Date, Source, TaxesAggregate) VALUES ('2018-02-03','Shopify', OtherIncomesTaxes#TEMP)

Update¶

To update, the IncomeId value is required in the WHERE clause. To update TaxesAggregate, you need to override the aggregate TaxesAggregate field, either by setting an aggregate value, or by using a temporary table. For example:

INSERT INTO OtherIncomesTaxes#TEMP (Name, Amount) VALUES ('Tax1', 500)
INSERT INTO OtherIncomesTaxes#TEMP (Name, Amount) VALUES ('Tax2', 300)

UPDATE OtherIncomes SET Date = '2018-01-01', TaxesAggregate = 'InvoiceLineItems#TEMP' WHERE IncomeId = 29153

Delete¶

To delete, IncomeId value is required in the WHERE clause.

DELETE from OtherIncomes where IncomeId = '26157'

Columns¶

Payments¶

Payments are a record of the payments made on your invoices.

Columns¶

Projects¶

Projects in FreshBooks are used to track business projects and related information such as hourly rate, service(s) being offered, projected end date...etc.

Columns¶

Staff¶

Staff are your employees. Staff created via this endpoint are only Accounting representations of the staff members of your business, for the purpose of ownership of invoices, expenses, etc. To create staff members for actual use please use the Staff Invitation flow in the new FreshBooks' web interface.

Columns¶

Taxes¶

FreshBooks allows you to store your previously used taxes to re-apply conveniently.

Columns¶

Views¶

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

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

FreshBooks Connector Views¶

AccountsAgingReport¶

The Account Aging Report shows details regarding overdue invoices from clients.

Columns¶

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.

BillsLineItems¶

Bill lines are used to determine the amount of a bill.

Columns¶

BillVendorsTaxDefaults¶

FreshBooks allows you to store your previously used taxes to re-apply conveniently.

Columns¶

EstimatesLineItems¶

Estimate lines are used to determine the amount of an estimate.

Columns¶

ExpenseCategories¶

FreshBooks supplies Expense Categories to group expenses together to aid in expense tracking.

Columns¶

ExpensesReport¶

The Expenses Report shows all the information involving your Expenses.

Columns¶

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.

Gateways¶

Gateways are also referred to as payment processors. The information returned by these endpoints specifies what payment processors are enabled for your businesses.

Columns¶

IdentityInfo¶

You can find ids for the Businesses and Accounts a user can interact with by querying the IdentityInfo view.

Columns¶

InvoicesLineItems¶

Invoice lines are used to determine the amount of an invoice, in addition to being able to tie the invoice to rebilled expenses.

Columns¶

InvoicesReport¶

The Invoice Details Report shows all the information involving your invoices.

Columns¶

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.

OtherIncomesTaxes¶

Other Income is for recording income that doesn’t require an invoice and is received through other means.

Columns¶

PaymentsCollectedReport¶

The Payments Collected Report shows details regarding collected payments made to your business.

Columns¶

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.

ProfitLossReport¶

The Profit/Loss Report shows all the information involving both your Profits and Losses.

Columns¶

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.

Systems¶

An Accounting System represents an entity that can send invoices. It is the central point of association between all of a single Administrator of a single Business, Invoices, Clients, Staff, Expenses, and Reports.

Columns¶

Tasks¶

Tasks in Freshbooks represent services that your business offers to clients. Tasks are used to keep track of details of the service such as name and hourly rate.

Columns¶

TaxSummaryReport¶

The Tax Summary Report that outlines the taxes involved with your sales.

Columns¶

TimeEntries¶

Time Entries represent time spent working for a client or project.

Columns¶

Stored Procedures¶

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

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

FreshBooks Connector Stored Procedures¶

CreateExpenseWithAttachment¶

To let end users create an expense with attachment.

Input¶

Result Set Columns¶

CreateInvoiceWithAttachment¶

To let end users upload attachment to an invoice.

Execute¶

Create an invoice with attachment:

INSERT INTO InvoiceLineItems#TEMP (Name, Amount) Values ('E', 200 )

EXECUTE CreateInvoiceWithAttachment Atatchment = 'C:\files\log.txt', CustomerId = '33578', CreateDate = '2023-11-06', LinesAggregate= InvoiceLineItems#TEMP

Input¶

Result Set Columns¶

GetOAuthAccessToken¶

Obtains the OAuth access token to be used for authentication with FreshBooks.

Input¶

Result Set Columns¶

GetOAuthAuthorizationURL¶

Obtains the OAuth authorization URL for authentication with FreshBooks.

Input¶

Result Set Columns¶

RefreshOAuthAccessToken¶

Usage information for the operation RefreshOAuthAccessToken.rsb.

Input¶

Result Set Columns¶

System Tables¶

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

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

sys_catalogs¶

Lists the available databases.

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

SELECT * FROM sys_catalogs

Columns¶

sys_schemas¶

Lists the available schemas.

The following query retrieves all available schemas:

SELECT * FROM sys_schemas

Columns¶

sys_tables¶

Lists the available tables.

The following query retrieves the available tables and views:

SELECT * FROM sys_tables

Columns¶

sys_tablecolumns¶

Describes the columns of the available tables and views.

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

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

Columns¶

sys_procedures¶

Lists the available stored procedures.

The following query retrieves the available stored procedures:

SELECT * FROM sys_procedures

Columns¶

sys_procedureparameters¶

Describes stored procedure parameters.

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

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

Columns¶

sys_keycolumns¶

Describes the primary and foreign keys.

The following query retrieves the primary key for the Clients table:

SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='Clients'

Columns¶

sys_foreignkeys¶

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'

Columns¶

sys_primarykeys¶

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

SELECT * FROM sys_primarykeys

Columns¶

sys_indexes¶

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

SELECT * FROM sys_indexes WHERE IsPrimary='false'

Columns¶

sys_connection_props¶

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:freshbooks:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns¶

sys_sqlinfo¶

Describes the SELECT query processing that the connector can offload to the data source.

Discovering the Data Source's SELECT Capabilities¶

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns¶

sys_identity¶

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

SELECT * FROM sys_identity

Columns¶

sys_information¶

Describes the available system information.

The following query retrieves all columns:

SELECT * FROM sys_information

Columns¶

Advanced Configurations Properties¶

The advanced configurations properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure. Click the links for further details.

Authentication

OAuth

SSL

Schema

Miscellaneous

Authentication¶

This section provides a complete list of authentication properties you can configure.

AccountId¶

The Account ID to connect to. If a value is not specified, the first one returned will be used.

Data Type¶

string

Default Value¶

""

Remarks¶

The available Account Ids can be found in the IdentityInfo view.

OAuth¶

This section provides a complete list of OAuth properties you can configure.