Establishing a Connection

Adding a Connection to FreshBooks

To add a connection to FreshBooks:

In the application console, navigate to the Connections page.
At the Add Connections panel, select the icon for the connection you want to add.
If the FreshBooks icon is not available, click the Add More icon to download and install the FreshBooks connector from the CData site.

For required properties, see the Settings tab.

For connection properties that are not typically required, see the Advanced tab.

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

Advanced Features

This section details a selection of advanced features of the FreshBooks Sync App.

User Defined Views

The Sync App 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 Sync App handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Firewall and Proxy

Configure the Sync App for compliance with Firewall and Proxy, including Windows proxies and HTTP proxies. You can also set up tunnel connections.

Query Processing

The Sync App 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.

Logging

See Logging for an overview of configuration settings that can be used to refine CData logging. For basic logging, you only need to set two connection properties, but there are numerous features that support more refined logging, where you can select subsets of information to be logged using the LogModules connection property.

SSL Configuration

Customizing the SSL Configuration

By default, the Sync App 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.

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

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

Data Model

The CData Sync App 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 Sync App 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 Sync App.

Alpha API

The Data Model uses the OAuth 2.0 specification.

Tables

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

FreshBooks Connector for CData Sync Tables


Name	Description
BillPayments	Payments are a record of the payments made on your invoices.
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.
BillVendors	A Vendor will work with your business to provide goods or services with a Bill to be paid at a later date.
Clients	A client in the new FreshBooks is a resource representing an entity you send invoices to.
Estimates	Estimates in FreshBooks provides Owners and Clients a way to agree and negotiate on the price and scope of work before it commences.
Expenses	Expenses are used to track expenditures your business incurs.
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.
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.
Items	Items are stored from invoice lines to make invoicing easier in the future.
OtherIncomes	Other Income is for recording income that doesn’t require an invoice and is received through other means.
Payments	Payments are a record of the payments made on your invoices.
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.
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.
Taxes	FreshBooks allows you to store your previously used taxes to re-apply conveniently.

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


Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique identifier of the payment.
BillId	Integer	False	The identifier of the related bill.
Amount	Decimal	False	Amount paid on invoice to two decimal places.
Code	String	False	Three-letter currency code of payment.
PaidDate	Date	False	Date the payment was made. (YYYY-MM-DD)
MatchedWithExpense	Boolean	True	Indicates whether or not the payment was converted from a credit on a client's account.
Note	String	False	Notes on payment, often used for credit card reference number.
PaymentType	String	False	The type of the payment: 'Check', 'Credit', 'Cash', etc.
VisState	Integer	True	0 for active, 1 for deleted.

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


Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	Uniquely identifies the bill associated with the business.
VendorId	Integer	False	Unique identifier of client.
Amount	String	True	Bill subtotatal amount excluding the taxes.
AmountCode	String	True	Three-letter currency code
AttachmentId	String	True	Attachment Id
AttachmentType	String	True	Media Type of Attachment
AttachmentJWT	String	True	Attachment JWT
BillNumber	String	False	Reference to vendor bill number
BillPaymentsAggregate	String	True	Bill Payments made against the bill
CreatedAt	Datetime	True	Time the invoice was created, YYYY-MM-DD HH:MM:SS format
CurrencyCode	String	False	Three-letter currency code
DueDate	Date	True	Date for which the bill is due for payment
DueOffsetDays	Integer	False	Number of days from the issue date that the invoice needs to be set to due
IssueDate	Date	False	Date when the bill was issued by the vendor
Language	String	False	Two-letter language code, e.g. “en”
LinesAggregate	String	False	Array of bill line items.
OutstandingAmount	String	True	Outstanding/unpaid amount on the bill
OutstandingCode	String	True	Three-letter currency code
OverallCategory	String	True	If multiple categories are selected in the bill lines, then overall_category is Split. Otherwise, it will be the selected category.
OverallDescription	String	True	First non-null value of bill line descriptions
PaidAmount	String	True	Paid amount on the bill
PaidCode	String	True	Three-letter currency code
Status	String	True	Status of the bill: “unpaid”, “overdue”, “partial”, “paid”
TaxAmountAmount	String	True	Total tax amount on the bill
TaxAmountCode	String	True	Three-letter currency code
TotalAmountAmount	String	True	Grand total amount on bill amount tax amount
TotalAmountCode	String	True	Three-letter currency code
UpdatedAt	Datetime	True	Last time the resource was updated. YYYY-MM-DD HH:MM:SS format
VisState	Integer	True	0 for active, 1 for deleted, 2 for archived

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


Name	Type	ReadOnly	Description
VendorId [KEY]	Integer	True	Unique identifier for vendor
AccountNumber	String	False	Account number of the vendor
City	String	False	City of vendor
Country	String	False	Country of vendor
CreatedAt	Datetime	True	The vendor was created, YYYY-MM-DD HH:MM:SS format
CurrencyCode	String	False	Default three-letter currency code for vendor
Is1099	Boolean	False	Set true if vendor is a 1099 contractor
Language	String	False	Two-letter language code, e.g. “en”
Note	String	True	Note
OutstandingBalanceAmount	String	True	The outstanding amount to be paid to the Vendor
OutstandingBalanceCode	String	True	Three-letter currency code
OverdueBalanceAmount	String	True	Overdue amount to be paid to the Vendor
OverdueBalanceCode	String	True	Three-letter currency code
Phone	String	False	Phone number
PostalCode	String	False	Postal code
PrimaryContactEmail	String	False	Vendor primary email
PrimaryContactFirstName	String	False	Vendor primary first name
PrimaryContactLastName	String	False	Vendor primary last name
Province	String	False	Province
Street	String	False	Street address
Street2	String	False	Street address 2nd part
TaxDefaultsAggregate	String	False	Tax Defaults Aggregate
UpdatedAt	Datetime	True	Time of last modification to resource
VendorName	String	False	Vendor Name
VisState	Integer	True	Visibility state, possible values are 0, 1, 2
Website	String	False	Vendor website address

Clients

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

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique identifier of a client.
AccountingSystemId	String	True	The unique identifier of the accounting system.
AllowLateNotifications	Boolean	False	Indicates whether client is allowed late notifications. (deprecated)
AllowLateFees	Boolean	False	Indicates whether client is allowed late fees. (deprecated)
BusinessPhone	String	False	The business phone number of the client.
CompanyIndustry	String	False	Description of the industry the client is in.
CompanySize	String	False	Size of client's company.
CurrencyCode	String	False	3-letter shortcode for client's preferred currency.
DirectLinkToken	String	True	(deprecated)
Email	String	False	The email of the client.
Fax	String	False	The fax number of the client.
FirstName	String	False	The first name of the client.
HomePhone	String	False	The home phone number of the client.
Language	String	False	Shortcode indicating user language.
LastActivity	Datetime	False	Time of last client activity.
LastLogin	String	False	Client's last login time.
Level	Integer	True	Indication of access level on system. (deprecated)
LastName	String	False	The last name of the client.
MobilePhone	String	False	The mobile phone number of the client.
Note	String	False	Notes kept by administrator about client.
Notified	Boolean	False	(deprecated)
NumLogins	Integer	False	Number of client logins.
Organization	String	False	Name for client's business.
BillingCity	String	False	Client's billing city.
BillingPostalCode	String	False	Client's billing postal code.
BillingCountry	String	False	Client's billing country.
BillingProvince	String	False	Client's billing province.
BillingStreetAddress	String	False	Client's billing street address.
BillingStreetAddress2	String	False	Second line of client's billing street address.
PrefEmail	Boolean	False	Boolean value indicating whether they prefer email over ground mail.
PrefGmail	Boolean	False	Boolean value indicating whether they prefer ground mail over email.
ShippingCity	String	False	Client's shipping city.
ShippingCode	String	False	Client's shipping postal code.
ShippingCountry	String	False	Client's shipping country.
ShippingProvince	String	False	Client's shipping province.
ShippingStreetAddress	String	False	Client's shipping street address.
ShippingStreetAddress2	String	False	Second line of client's shipping street address.
SignupDate	Datetime	False	Time of user signup.
StatementToken	String	True	(deprecated)
Subdomain	String	True	Client subdomain name. (deprecated)
Updated	Datetime	False	Time of last modification to resource.
Username	String	False	Username used by client to log in.
VatName	String	False	Value Added Tax name.
VatNumber	Integer	False	Value Added Tax number.
VisState	Integer	False	Visibility state of the client - values can be active, deleted, or archived.

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


Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique identifier of an estimate.
AccountingSystemId	String	True	The unique identifier for the system.
UIStatus	String	True	Estimate UI status
Status	String	True	Estimate status
Accepted	Boolean	True	Indicates whether estimate has been accepted.
Amount	Deicmal	True	Total amount of estimate, to two decimal places.
AmountCode	String	True	Three letter currency code associated with the amount.
DiscountTotalAmount	Decimal	True	Amount of discount, to two decimal places.
DiscountTotalCode	String	True	Three letter currency code associated with the discount.
Description	String	True	Description of first line of estimate.
CurrentOrganization	String	True	Name of organization being estimated.
Invoiced	String	True	Indicator of whether this estimate has been converted to an invoice that was sent.
OwnerId	Integer	True	Identifier of the creator of the estimate. (writable on create only)
SentId	Integer	True	Identifier of user who sent the estimate, typically 1 for admin. (writable on create only)
CreatedAt	Datetime	True	The time the estimate was created. (writable on create only)
Updated	Datetime	True	The time the estimate was last updated. (writable on create only)
DisplayStatus	String	True	Description of status shown in FreshBooks UI. Either draft, sent, or viewed. (writable on create only)
ReplyStatus	String	True	Description of status shown in Classic FreshBooks UI. Either replied or resolved. (deprecated)
EstimateNumber	String	False	User-specified and visible estimate Id.
CustomerId	Integer	False	Unique identifier of client.
CreateDate	Date	False	The date the estimate was created.
DiscountValue	Decimal	False	Decimal-string amount of discount.
PONumber	String	False	Post Office box number for address on estimate.
Template	String	False	Choice of rendering style. (internal, deprecated)
CurrencyCode	String	False	Three-letter currency code for estimate.
Language	String	False	Two-letter language code.
Terms	String	False	Terms listed on estimate.
Notes	String	False	Notes listed on estimate.
Address	String	False	First line of address listed on estimate.
ExtArchive	Integer	False	0 or 1 indicating whether estimate is archived or not. (deprecated)
VisState	Integer	False	0 indicates active, 1 indicates deleted.
Street	String	False	Street for address on estimate.
Street2	String	False	Second line of address on estimate.
City	String	False	City of address on estimate.
Province	String	False	Province of address on estimate.
Country	String	False	Country of address on estimate.
Organization	String	False	Name of organization being estimated.
FirstName	String	False	First name of client on estimate.
LastName	String	False	Last name of client being estimated.
VatName	String	False	Value Added Tax name if provided.
VatNumber	String	False	Value Added Tax number if provided.
LinesAggregate	String	False	An aggregate of lines associated with an estimate. See the help for the Estimates table for more information on inserting and updating to this field.

Expenses

Expenses are used to track expenditures your business incurs.

Columns


Name	Type	ReadOnly	Description
ExpenseId [KEY]	Integer	True	A unique identifier for the expense, unique to business id.
AccountName	String	False	The name of the related account, if applicable.
AccountId	Integer	False	The identifier of the related account, if applicable.
AccountingSystemId	String	False	The identifier of the system.
Amount	Decimal	False	The amount of the expense.
BackgroundJobId	Integer	False	The identifier of the related background job if applicable.
BankName	String	False	The name of the bank the expense was imported from, if applicable.
CategoryId	Integer	False	The identifier of the related expense category.
ClientId	Integer	False	The Id of the related client, if applicable.
Code	String	False	Three-letter currency code for expense.
CompoundedTax	Boolean	False	Boolean indicated if the tax was a compound tax.
Date	Date	False	Date of the expense. (YYYY-MM-DD)
ExtInvoiceId	Integer	False	The identifier of the related contractor invoice if applicable.
ExtSystemId	Integer	False	The identifier of the related contractor system if applicable.
HasReceipt	Boolean	False	Boolean indicating if there is a receipt attached.
InvoiceId	Integer	False	Identifier of the related invoice, if applicable.
IsDuplicate	Boolean	False	Boolean indicating if this is a duplicated expense.
MarkupPercent	String	False	Note of percent to mark the expense up. (String-decimal)
Notes	String	False	Notes about the expense.
ProfileId	Integer	False	Identifier of related profile, if applicable.
ProjectId	Integer	False	Identifier of related project, if applicable.
StaffId	Integer	False	Identifier of related staff member, if applicable.
Status	Integer	False	Values from expense status table.
TaxAmount1	Decimal	False	The amount of the first tax.
TaxAmount2	Decimal	False	The amount of the second tax.
TaxName1	String	False	Name of first tax.
TaxName2	String	False	Name of second tax.
TaxPercent1	Decimal	False	The percent of the first tax.
TaxPercent2	Decimal	False	The percent of the second tax.
TransactionId	Integer	False	Identifier of related transaction, if applicable.
Updated	Datetime	False	Time the invoice was last updated. (YYYY-MM-DD HH:MM:SS)
Vendor	String	False	Name of vendor.
VisState	Integer	False	0 for active, 1 for deleted.

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


Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique id for the invoice profile
AccountingSystemId	String	False	Unique id for the system
Address	String	False	The address on the invoice
Amount	String	True	The amount to be paid for invoice
AmountCode	String	True	Three-letter currency code
AutoBill	Boolean	True	Whether this invoice has a credit card saved
BillGateway	String	False	The gateway that is used for payment
City	String	False	City for address on invoice
Code	String	True	Three-letter currency code
Country	String	False	Country for address on invoice
CreateDate	Date	False	The create day of the invoice profile
CurrencyCode	String	True	Three-letter currency code for invoice
CustomerId	Integer	False	The unique id for the client of the invoice
Description	String	False	The description of the invocie
Disable	Boolean	False	True will disable the auto-generation of invoices
DiscountTotalAmount	String	True	The amount of the discount
DiscountTotalCode	String	True	Three-letter currency code for the discount
DiscountValue	String	False	Decimal-string amount
DueOffsetDays	Integer	False	Number of days from creation that invoice is due
ExtArchive	Integer	False	0 or 1 indicating archived or not
Fname	String	False	The first name of client on invoice
Frequency	String	False	The frequency the invoice will be created. In the form of xy where x is an integer and y is either d,w,m,y. (example: Every two weeks would be 2w)
IncludeUnbilledTime	Boolean	False	True if unbilled time is included, false otherwise
Language	String	False	2 letter code representing the language
Lname	String	False	The last name of client on invoice
Notes	Unknown	False	Notes listed on invoice
NumberRecurring	Integer	False	The number of invoices that will be generated, 0 for infinite
OccurrencesToDate	Integer	True	Number of invoices that have been generated
Organization	String	False	Name of the organization belonging to the client
OwnerId	Integer	True	Id of creator of invoice. 1 if business admin, other if created by e.g. a contractor
PaymentDetails	String	False	Details for payment for the invoice
PoNumber	String	False	Post office box number for address on invoice
ProfileId	Integer	True	Profile Id
Province	String	False	Province for address on invoice.
SendEmail	Boolean	False	True email invoice on creation, false will leave as draft
SendGmail	Boolean	False	True to send invoice via ground mail
Street	String	False	Street for address on invoice
Street2	String	False	Second street for address on invoice
Terms	String	False	Terms listed on invoice
Updated	Datetime	True	The date the invoice profile was updated
Vatname	String	False	Value added tax name if provided
Vatnumber	String	False	Value added tax number if provided
Visstate	Integer	False	0 for active, 1 for deleted
LinesAggregate	String	False	An aggregate of lines associated with an invoice. See the help for the Invoices table for more information on inserting and updating to this field.

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


Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	Invoice Id that is unique to this system.
AccountingSystemId	String	True	Unique Id for the system.
AccountId	String	True	Unique Id for the system.
TotalAmount	Decimal	True	Total amount of invoice, to two decimal places.
PaidAmount	Decimal	True	Amount paid on invoice, to two decimal places.
PaidCode	String	True	Three-letter currency code.
OutstandingAmount	Decimal	True	Amount outstanding on the invoice, to two decimal places.
OutstandingCode	String	True	Three-letter currency code.
DiscountTotalAmount	Decimal	True	Amount of discount, to two decimal places.
DiscountTotalCode	String	True	Three-letter currency code.
Description	String	True	Description of first line of invoice.
CurrentOrganization	String	True	Name of organization being invoiced.
DueDate	Date	True	Date invoice is marked as due by. (YYYY-MM-DD, calculated from due_offset_days)
DatePaid	Date	True	Date invoice was fully paid. (YYYY-MM-DD)
OwnerId	Integer	False	Id of the invoice's creator. (writable on create only)
EstimateId	Integer	False	Id of associated estimate, 0 if none.(writable on create only)
BasecampId	Integer	False	Id of connected basecamp account, 0 if none.(writable on create only)
SentId	Integer	False	User Id of user who sent the invoice.(writable on create only)
Status	String	False	Invoice status.(writable on create only)
Parent	Integer	False	Id of object this invoice was generated from, 0 if none.(writable on create only)
CreatedAt	Datetime	False	Time the invoice was created. (YYYY-MM-DD, writable on create only)
Updated	Datetime	False	Time the invoice was last updated. (YYYY-MM-DD, writable on create only)
DisplayStatus	String	False	Description of status shown in Freshbooks UI.(writable on create only)
AutobillStatus	String	False	Description of autobill status.(writable on create only)
PaymentStatus	String	False	Description of payment status.(writable on create only)
LastOrderStatus	String	False	Describes the status of the last attempted payment.(writable on create only)
DisputeStatus	String	False	Description of whether invoice has been disputed.(writable on create only)
DepositStatus	String	False	Description of deposits applied to invoice.(writable on create only)
AutoBill	String	False	Whether this invoice has a credit card saved.(writable on create only)
V3Status	String	False	Description of Invoice status.(writable on create only)
InvoiceNumber	String	False	User-specified and visible Invoice Id.
CustomerId	Integer	False	Client Id unique to this system.
CreateDate	Date	False	Date invoice was created. (YYYY-MM-DD)
GenerationDate	Date	False	Date invoice was generated from object.(YYYY-MM-DD, or null if not)
DiscountValue	Decimal	False	Decimal-string amount of discount.
DiscountDescription	String	False	Public note about discount.
PONumber	String	False	Reference number for address on invoice.
Template	String	False	Choice of rendering style. (internal, deprecated)
CurrencyCode	String	False	Three-letter currency code for invoice.
Language	String	False	Two-letter language code.
Terms	String	False	Terms listed on invoice.
Notes	String	False	Notes listed on invoice.
Address	String	False	First line of address on invoice.
ReturnUri	String	False	(deprecated)
DepositAmount	Decimal	False	Amount required as deposit, null if none.
DepositPercentage	Decimal	False	Percentage of the invoice's value required as a deposit.
Gmail	String	False	Whether to send via ground mail.
ShowAttachments	String	False	Whether attachments on invoice are rendered.
VisState	Integer	False	Whether active (0) or deleted (1).
Street	String	False	Street for address on invoice.
Street2	String	False	Second line of street for address on invoice.
City	String	False	City for address on invoice.
Province	String	False	Province for address on invoice.
Code	String	False	Zip code for address on invoice.
Country	String	False	Country for address on invoice.
Organization	String	False	Name of organization being invoiced.
FirstName	String	False	First name of client on invoice.
LastName	String	False	Last name of client on invoice.
VatName	String	False	Value added tax name, if provided.
VatNumber	String	False	Value added tax number, if provided.
DueOffsetDays	Integer	False	Number of days from creation that invoice is due.
LinesAggregate	String	False	An aggregate of lines associated with an invoice. See the help for the Invoices table for more information on inserting and updating to this field.

Items

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

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	Unique identifier of the item.
AccountingSystemId	String	True	Unique identifier of business client exists on.
Amount	Decimal	False	Amount paid on invoice, to two decimal places.
Code	String	False	Three-letter currency code.
Description	String	False	Descriptive text for item.
Inventory	String	False	Count of inventory.
Name	String	False	Descriptive name of item.
Quantity	String	False	Number to multiply unit cost by.
Tax1	Integer	False	Identifier of tax on invoice.
Tax2	Integer	False	Identifier of second tax on invoice if applicable.
Updated	Datetime	False	Date object was last updated. (YYYY-MM-DD)
VisState	Integer	False	0 for active, 1 for deleted.

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


Name	Type	ReadOnly	Description
IncomeId [KEY]	Integer	True	Unique identifier of this other income entry within the business
Amount	String	False	Amount of the income, to two decimal places
AmountCode	String	False	Three-letter currency code
CategoryName	String	False	Options include: advertising, in_person_sales, online_sales, rentals, other
CreatedAt	Datetime	True	Time the other income entry was created, YYYY-MM-DD HH:MM:SS format
Date	Date	False	The date the income was received, YYYY-MM-DD format
Note	String	False	Notes on the income
PaymentType	String	False	Optional type of payment made. “Check”, “Credit”, “Cash”, etc.
Source	String	False	Source of external income. E.g. Shopify, Etsy, Farmers’ Market
Sourceid	Unknown	False	Source ID
TaxesAggregate	String	False	Taxes Aggregate
UpdatedAt	Datetime	True	Time the other income entry was last updated, YYYY-MM-DD HH:MM:SS format
Userid	Unknown	True	User Id
VisState	Integer	True	0 for active,1 for deleted,2 for archived(more info on vis_state)

Payments

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

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique identifier of the payment.
AccountingSystemId	String	True	The unique identifier of the business the client exists on.
Amount	Decimal	False	Amount paid on invoice to two decimal places.
ClientId	Integer	True	Identifier of client who made the payment.
Code	String	False	Three-letter currency code of payment.
CreditId	Integer	False	Identifier of related credit.
Date	String	False	Date the payment was made. (YYYY-MM-DD)
FromCredit	Boolean	False	Indicates whether or not the payment was converted from a credit on a client's account.
Gateway	String	True	The payment processor used, if any.
InvoiceId	Integer	False	The identifier of the related invoice.
Note	String	False	Notes on payment, often used for credit card reference number.
OrderId	Integer	False	Identifier of related order.
OverpaymentId	Integer	True	Identifier of related overpayment if relevant.
TransactionId	Integer	False	Identifier of the related transaction. (deprecated)
Type	String	False	The type of the payment: 'Check', 'Credit', 'Cash', etc.
Updated	Datetime	True	Date object was last updated. (YYYY-MM-DD)
VisState	Integer	False	0 for active, 1 for deleted.

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


Name	Type	ReadOnly	Description
Id [KEY]	String	True	Unique identifier of the project.
Title	String	False	Title of the project.
ClientId	String	False	Unique identifier of the client.
GroupId	String	True	Unique id of group membership.
FixedPrice	Decimal	False	Used for flat-rate projects. Represents the amount being charged to the client for the project.
UpdatedAt	Datetime	True	The time the project was last updated.
Sample	Boolean	True	True if project is sample.
Active	Boolean	True	Whether the project is active or not.
Rate	String	True	The hourly rate of the project. Only applies to hourly_rate projects.
ProjectType	String	False	Type of project: fixed_price, hourly_rate.
Description	String	True	Description of project.
LoggedDuration	Integer	True	The time logged for the project in seconds.
Internal	Boolean	True	Clarifies that the project is internally within the company (client is the company).
DueDate	Date	True	Date of projected completion.
Complete	Boolean	True	Whether the project is completed or not.
BilledAmount	Decimal	True	The amount billed for the project.
BilledStatus	String	True	Shows the billed status.
BillingMethod	String	True	The method of payment for the project.
CreatedAt	Datetime	True	The date/time the project was created.
Budget	Integer	True	Budget for project.

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


Name	Type	ReadOnly	Description
Id [KEY]	Integer	False	The unique identifier of the staff member to this business id.
AccountingSystemId	String	False	The unique identifier of the business staff member exists on.
APIToken	String	False	Token used for Classic API. (deprecated)
BusinessPhone	String	False	The business phone number.
CurrencyCode	String	False	Three-digit shortcode for preferred currency.
DisplayName	String	False	Name chosen by staff member to display.
Email	String	False	Email address for staff member.
Fax	String	False	Fax number for staff member.
FirstName	String	False	First name of staff member.
HomePhone	String	False	Staff member's home phone number.
Language	String	False	Staff member's selected language.
LastLogin	Datetime	False	Date and time the staff account was last logged into.
Level	Integer	False	Description of staff member access level. (deprecated)
LastName	String	False	Last name of staff member.
MobilePhone	String	False	Staff member's mobile phone number.
Note	String	False	Notes about staff member.
Logins	Integer	False	Number of times the staff member has logged in.
Organization	String	False	Organization the staff member is affiliated with.
BillingCity	String	False	Staff member's billing address city.
BillingCode	String	False	Staff member's billing address zip code.
BillingCountry	String	False	Staff member's billing address country.
BillingProvince	String	False	Staff member's billing address province.
BillingStreet	String	False	Staff member's billing address primary street information.
BillingStreet2	String	False	Staff member's billing address secondary street information.
Rate	String	False	Rate this staff member is billed at.
SignupDate	Datetime	False	Date the staff member account was created. (YYYY-MM-DD HH:MM:SS)
Updated	Datetime	False	Date the staff member account was last updated. (YYYY-MM-DD HH:MM:SS)
Username	String	False	Username specified for the staff member; randomly assigned if none specified at creation time.
VisState	Integer	False	Visibility state: 'active,' 'deleted,' or 'archived.'

Taxes

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

Columns


Name	Type	ReadOnly	Description
Id [KEY]	Integer	True	The unique identifier of the tax.
AccountingSystemId	String	True	The unique identifier of the business the client exists on.
Amount	Decimal	False	The string-decimal representing percentage value of tax.
Compound	Boolean	False	Indicates whether this is a compound tax to calculate on top of primary taxes.
Name	String	False	Identifiable name for the tax.
Number	String	False	An external number that identifies the tax submission.
TaxId	Integer	True	The unique identifier of the tax within this business.
Updated	Datetime	True	The date the staff object was last updated. (YYYY-MM-DD HH:MM:SS)

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 for CData Sync Views


Name	Description
AccountsAgingReport	The Account Aging Report shows details regarding overdue invoices from clients.
BillsLineItems	Bill lines are used to determine the amount of a bill.
BillVendorsTaxDefaults	FreshBooks allows you to store your previously used taxes to re-apply conveniently.
EstimatesLineItems	Estimate lines are used to determine the amount of an estimate.
ExpenseCategories	FreshBooks supplies Expense Categories to group expenses together to aid in expense tracking.
ExpensesReport	The Expenses Report shows all the information involving your Expenses.
Gateways	Gateways are also referred to as payment processors. The information returned by these endpoints specifies what payment processors are enabled for your businesses.
IdentityInfo	You can find ids for the Businesses and Accounts a user can interact with by querying the IdentityInfo view.
InvoicesLineItems	Invoice lines are used to determine the amount of an invoice, in addition to being able to tie the invoice to rebilled expenses.
InvoicesReport	The Invoice Details Report shows all the information involving your invoices.
OtherIncomesTaxes	Other Income is for recording income that doesn’t require an invoice and is received through other means.
PaymentsCollectedReport	The Payments Collected Report shows details regarding collected payments made to your business.
ProfitLossReport	The Profit/Loss Report shows all the information involving both your Profits and Losses.
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.
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.
TaxSummaryReport	The Tax Summary Report that outlines the taxes involved with your sales.
TimeEntries	Time Entries represent time spent working for a client or project.

AccountsAgingReport

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

Columns


Name	Type	Description
UserId [KEY]	String	Unique identifier of the client
Lname	String	The last name of the client
Fname	String	The first name of the client
Organization	String	Organization of the client
Email	String	Email of the client
CurrencyCode	String	Three letter currency code
CompanyName	String	Company Name of the client
DownloadToken	String	The download token allows you to download the report into a csv file
EndDate	Date	The ending date for the profit/loss report query
Account_0To30Interval_Amount	String	The total amount due for a particular account from all its overdue invoices in 0-30 interval.
Account_0To30Interval_Code	String	Three letter currency code
Account_31To60Interval_Amount	String	The total amount due for a particular account from all its overdue invoices in 31-60 interval.
Account_31To60Interval_Code	String	Three letter currency code
Account_61To90Interval_Amount	String	The total amount due for a particular account from all its overdue invoices in 61-90 interval.
Account_61To90Interval_Code	String	Three letter currency code
Account_From91_Amount	String	The total amount due for a particular account from all its overdue invoices for more than 90 days.
Account_From91_Code	String	Three letter currency code
AccountTotalAmount	String	The total amount due for a particular account from all its overdue invoices.
AccountTotalCode	String	Three letter currency code
Totals_0To30Interval_Amount	String	The total amount due from all overdue invoices in 0-30 interval.
Totals_0To30Interval_Code	String	Three letter currency code
Totals_31To60Interval_Amount	String	The total amount due from all overdue invoices in 31-60 interval
Totals_31To60Interval_Code	String	Three letter currency code
Totals_61To90Interval_Amount	String	The total amount due from all overdue invoices in 61-90 interval
Totals_61To90Interval_Code	String	Three letter currency code
Totals_From91_Amount	String	The total amount due from all overdue invoices for more than 90 interval
Totals_From91_Code	String	Three letter currency code
TotalsTotalAmount	String	The total amount due from all overdue invoices from all the intervals
TotalsTotalCode	String	Three letter currency code

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
StartDate	String	The starting date for the expense report query.

BillsLineItems

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

Columns


Name	Type	Description
LineId [KEY]	Integer	Line Id unique to this bill.
CategoryId	String	Id of related expense category.
BillId	Integer	Id of related bill.
CategoryName	String	Name of related expense category.
ListIndex	Integer	Line number on the Bill
Description	String	Description of line item.
Amount	Decimal	Amount calculated from quantity and unit_cost.
Code	String	Three-letter currency code.
TotalAmount	Decimal	Total amount calculated from quantity and unit_cost.
TotalAmountCode	String	Three letter currency code.
Quantity	Integer	Quantity of the line unit.
UnitCostAmount	Decimal	unit cost amount of the line item.
UnitCostCode	String	Three-letter currency code.
TaxName1	String	Name for the first tax on the bill line.
TaxName2	String	Name for the second tax on the bill line.
TaxPercent1	Integer	Percentage of first tax to 2 decimal places.
TaxPercent2	Integer	Percentage of second tax to 2 decimal places.
TaxAuthorityId1	String	Tax Authority 1.
TaxAuthorityId2	String	Tax Authority 2.
TaxAmount1Amount	Decimal	First tax amount, in percentage. (up to three decimal places)
TaxAmount1Code	String	Three letter currency code
TaxAmount2Amount	Decimal	Second tax amount, in percentage. (up to three decimal places)
TaxAmount2Code	String	Three letter currency code

BillVendorsTaxDefaults

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

Columns


Name	Type	Description
TaxId [KEY]	Integer	The unique identifier of the tax.
VendorId [KEY]	Integer	The unique identifier of the vendor.
SystemTaxId	Integer	Tax id in your FreshBooks business.
Amount	String	Populated from related system tax
Enabled	Boolean	If the tax is enabled for the vendor or not.
Name	String	Populated from related system tax
TaxAuthorityId	String	Custom identifier for tax tax authority.
UpdatedAt	Datetime	Time the resource was updated, YYYY-MM-DD HH:MM:SS format
CreatedAt	Datetime	Time the resource was created, YYYY-MM-DD HH:MM:SS format

EstimatesLineItems

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

Columns


Name	Type	Description
LineId	String	Line Id unique to this estimate.
EstimateId	Integer	Id of the line's estimate.
CompoundedTax	String
Description	String	Description for the estimate line item.
Amount	Decimal	Amount of estimate line item account, to two decimal places.
Code	String	Three-letter currency code.
Name	String	Name for the estimate line item.
Quantity	String	Quantity of the estimate line item, to be multiplied against unit cost.
UnitCostAmount	Decimal	Unit cost of the line item.
UnitCostCode	String	Three-letter currency code.
TaxName1	String	Name for the first tax on the estimate line.
TaxAmount1	Decimal	First tax amount, in percentage. (up to three decimal places)
TaxNumber1	String
TaxName2	String	Name for the second tax on the estimate line.
TaxAmount2	Decimal	Second tax amount, in percentage. (up to three decimal places)
TaxNumber2	String

ExpenseCategories

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

Columns


Name	Type	Description
Categoryid [KEY]	Integer	A unique identifier for the category, unique to this business id.
Category	String	Name for this category.
IsCogs	Boolean	Represents cost of goods sold.
IsEditable	Boolean	Indicates whether this can be edited.
ParentId	Integer	Categoryid of parent category.
VisState	Integer	0 for active, 1 for deleted.

ExpensesReport

The Expenses Report shows all the information involving your Expenses.

Columns


Name	Type	Description
ExpenseId [KEY]	String	Unique id for the expense.
GroupId	String	Unique id for the group.
Amount	Decimal	The amount of money that is owed or been paid.
Code	String	The currency that the amount is in.
VendorId	String	The unique id for the vendor.
Vendor	String	The name of the vendor.
Notes	String	Custom notes about the expense.
ClientId	String	Unique id for the client.
AuthorId	String	Id for the author.
CreatedDate	Date	The date the expense took place.
TaxName1	String	The name of the first tax.
TaxPercent1	Decimal	The percentage you are being taxed on.
TaxAmount1	Decimal	The amount of the first tax.
TaxCode1	String	The currency of the first tax.
TaxName2	String	The name of the second tax.
TaxPercent2	Decimal	The percentage you are being taxed on.
TaxAmount2	Decimal	The amount of the second tax.
TaxCode2	String	The currency of the second tax.
CategoryId	String	The identifier of the category.
CompanyName	String	Name of the company that the expenses are charged too.

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
StartDate	String	The starting date for the expense report query.
EndDate	String	Will only return invoices that were created before the given date.

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


Name	Type	Description
Id [KEY]	String	Unique identifier to the business Id for the gateway, unique to the whole environment.
GatewayName	String	The name of the gateway.
SingleGatewayId	Integer	Unique identifier to the business Id for the gateway.

IdentityInfo

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

Columns


Name	Type	Description
Id [KEY]	Integer	Unique identifier of identity.
Name	String	Name of business.
AccountId	String	Unique identifier of accounting system, if applicable.

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


Name	Type	Description
LineId	String	Line Id unique to this invoice.
InvoiceId	Integer	Id of related invoice.
Amount	String	Total amount of an invoice line, to two decimal places.
Code	String	Three-letter currency code.
Updated	Datetime	Time that the invoice was last updated. (YYYY-MM-DD)
Type	Integer	Invoice line type: normal invoice (0) or rebilling expense (1)
Quantity	Integer	Quantity of the invoice line item, to be multiplied against unit cost.
UnitCostAmount	Decimal	Unit cost amount, to two decimal places.
UnitCostCode	String	Three-letter currency code.
Description	String	Description for the invoice line item.
Name	String	Name for the invoice line item.
TaxName1	String	Name for the first tax on invoice line.
TaxAmount1	Decimal	First tax percentage amount, up to three decimal places.
TaxName2	String	Name for the second tax on invoice line.
TaxAmount2	Decimal	Second tax percentage amount, up to three decimal places.

InvoicesReport

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

Columns


Name	Type	Description
CreatedDate	Date	The date the invoice was created.
InvoiceId [KEY]	String	The unique id of the invoice.
DueOffsetDays	Int	Number of days from creation that the invoice is due.
Amount	Decimal	The amount of money that is owed or been paid.
Code	String	The currency that the amount is in.
PostOfficeNumber	Int	Post office box number for address on invoice.
TaxAmount	Decimal	Amount of tax.
TaxCode	String	Three-letter currency code.
PaidAmount	Decimal	Amount paid.
PaidCode	String	Three-letter currency code.
DatePaid	Date	The date the invoice was paid.
DiscountAmount	Decimal	The amount discount.
DiscountCode	String	Three-letter currency code.
InvoiceNumber	String	User-specified and visible invoice id.
currency_code	String	Three-letter currency code for invoice.
UserId	String	The unique id of the client.
FirstName	String	First name of the user.
LastName	String	The last name of the user.
Email	String	The email of the user.
Organization	String	Name of the organization the user is a part of.

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
StartDate	String	The starting date for the expense report query.
EndDate	String	Will only return invoices that were created before the given date.

OtherIncomesTaxes

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

Columns


Name	Type	Description
IncomeId	Integer	Unique identifier of this other income entry within the business
Amount	String	Amount of the income, to two decimal places
Name	String	Name of the tax

PaymentsCollectedReport

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

Columns


Name	Type	Description
InvoiceId	String	The unique id of the invoice.
ClientId	String	The unique id of the client.
Amount	Decimal	Amount paid on invoice to two decimal places.
Code	String	Three-letter currency code.
Client	String	The name of the business client belongs to.
Description	String	The description of the payment.
Date	Date	The date of the payment.
InvoiceNumber	String	A custom id for the invoice.
Method	String	Method of payment.

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
StartDate	String	The starting date for the expense report query.
EndDate	String	Will only return invoices that were created before the given date.

ProfitLossReport

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

Columns


Name	Type	Description
ProfitsAmount	Decimal	Amount of total profits.
ProfitsCode	String	Three-letter currency code.
IncomesAmount	Decimal	Amount of total incomes.
IncomesCode	String	Three-letter currency code.
ExpensesAmount	Decimal	Amount of total expenses.
ExpensesCode	String	Three-letter currency code.
FromDate	Date	The beginning date of the report.
ToDate	Date	The ending date of the report.

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
StartDate	String	the starting date for the expense report query.
EndDate	String	Will only return invoices that were created before the given date.

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


Name	Type	Description
Id [KEY]	Integer	The unique identifier of the system.
AccountId	String	The second unique identifier for the system.
Active	Boolean	Indicates whether the system is live or cancelled.
Amount	Decimal	Two-place decimal formatted amount being paid for the system.
AutoBill	Integer	Count of the number of autobills the system is allowed to have.
BillingStatus	String	Description of whether the owner of the system has paid when they were supposed to.
BusinessPhone	String	Main phone number for the business.
BusinessType	String	A description of the business.
City	String	The city this business is based in.
Code	String	The postal code for the business.
Country	String	The country this business is based in.
CurrencyCode	String	The default three-letter currency code for the business.
Date	Date	The signup date. (YYYY-MM-DD)
DiscountId	String	Identifier for tracking discounts (internal use).
DaylightSavingsTime	String	Indicates whether to use daylight savings time version of system's timezone.
Duration	Integer	(deprecated)
Email	String	The administrator email.
Fax	String	The fax number for a business.
GSTAmount	Decimal	The two-place decimal formatted amount of gst being paid for the system.
HeardAboutUsVia	String	Channel system came to Freshbooks through.
InfoEmail	String	The public email address to display.
IP	String	The signup IP address.
LandingUrl	String	The landing url used for analytics.
MasterlockBilling	String	Indicates whether a system is billed via a particular service.
MobilePhone	String	The mobile phone number for the business.
ModernSystem	String	Indicates whether the system belongs to the new version of FreshBooks or FreshBooks Classic.
Name	String	Descriptive name of the business.
Clients	Integer	Number of clients allowed on business.
Staff	String	Number of staff allowed on business.
PaymentAmount	Decimal	Two-place decimal formatted amount being paid for system.
PaymentFrequency	Integer	How many months per pay cycle. (internal)
Province	String	The province or state of the business.
ReferralId	String	A referral ID for analytics.
ReferringUrl	String	Tracks origin of user signup for analytics.
Salutation	String	The preferred greeting of the user.
SizeLimit	Integer	(deprecated)
SplitToken	String	Used for tracking split tests.
Street	String	The first line of the street of the business.
Street2	String	The second line of the street of the business.
TestSystem	String	Indicates whether this system is marked by FreshBooks as a test system.
Timezone	String	The timezone the business is in. (deprecated)
TimezoneId	Integer	The identifier of the timezone the business is in. (deprecated)

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


Name	Type	Description
Id [KEY]	String	Unique identifier of the task.
Name	String	The name of the task.
UpdatedAt	Datetime	The Date/Time the task was last updated
Description	String	The description of the task.
RateAmount	Decimal	The hourly amount charged for the task.
RateCode	String	The currency of the rate.
Billable	Boolean	True if the task is billable.
Tax1	Decimal	Amount of first tax.
Tax2	Decimal	Amount of second tax.
VisState	Integer	0 marks the task as active, 1 if inactive.

TaxSummaryReport

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

Columns


Name	Type	Description
CashBased	Boolean	True toggles tax to be calculated on collected sales rather than billed
CurrencyCode	String	Three-letter currency code for overdue payments
DownloadToken	String	The download token allows you to download the report into a csv file
EndDate	Date	The ending date for the tax summary report query
StartDate	Date	The starting date for the tax summary report query
TaxableAmountPaid	String	Taxable Amount Paid
TaxableAmountPaidCode	String	Taxable Amount Paid Code
TaxableAmountCollected	String	Taxable Amount Collcted Amount
TaxableAmountCollectedCode	String	Taxable Amount Collected Code
TaxCollectedAmount	String	Tax Collected Amount
TaxCollectedCode	String	Tax Collected Code
TaxName	String	Tax Name
NetTaxAmount	String	Net Tax Amount
NetTaxCode	String	Net Tax Code
NetTaxableAmount	String	Net Taxable Amount
NetTaxableCode	String	Net Taxable Amount Code
TaxPaidAmount	String	Tax Paid Amount
TaxPaidCode	String	Tax Paid Code
TotalInvoicedAmount	String	Total Invoiced Amount
TotalInvoicedCode	String	Total Invoiced Code

TimeEntries

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

Columns


Name	Type	Description
Id [KEY]	String	Unique identifier of the time entry.
ClientId	String	Unique identifier of the client.
ProjectId	String	Unique identifier of the project.
TaskId	String	Unique identifier of the task.
IsLogged	Boolean	False if the time entry is being created from a running timer.
CreatedAt	Datetime	The date when the time entry was created.
Billable	Boolean	True for entries that can be automatically added to an invoice.
TimerId	String	Unique identifier of the timer.
TimerIsRunning	Boolean	True if the timer is running.
PendingTask	String	The pending task.
Description	String	Notes of the user on the time entry.
Active	Boolean	True if the time entry is active.
Internal	Boolean	True if the time entry is not assigned to a client.
PendingProject	String	The pending project.
PendingClient	String	The pending client.
Duration	Integer	Duration of the time entry.
ServiceId	String	Unique identifier of the service.
Billed	Boolean	True if the entry time is billed.
StartedAt	Datetime	The date/time when the time entry started.

Connection String Options

The connection string 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 in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

Property	Description
AccountId	The Account Id to connect to. If a value is not specified, the first one returned will be used.

OAuth

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.
AuthToken	The authentication token used to request and obtain the OAuth Access Token.
AuthKey	The authentication secret used to request and obtain the OAuth Access Token.

SSL

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

Firewall

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

Proxy

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Logging

Property	Description
LogModules	Core modules to be included in the log file.

Schema

Property	Description
Location	A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Tables	This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Views	Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Miscellaneous

Property	Description
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other	These hidden properties are used only in specific use cases.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews	A filepath pointing to the JSON configuration file containing your custom views.

Authentication

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

Property	Description
AccountId	The Account Id to connect to. If a value is not specified, the first one returned will be used.

AccountId

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

Remarks

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

OAuth

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

Property	Description
OAuthClientId	The client Id assigned when you register your application with an OAuth authorization server.
OAuthClientSecret	The client secret assigned when you register your application with an OAuth authorization server.
AuthToken	The authentication token used to request and obtain the OAuth Access Token.
AuthKey	The authentication secret used to request and obtain the OAuth Access Token.

OAuthClientId

The client Id assigned when you register your application with an OAuth authorization server.

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId value, sometimes also called a consumer key, and a client secret, the OAuthClientSecret.

OAuthClientSecret

The client secret assigned when you register your application with an OAuth authorization server.

Remarks

As part of registering an OAuth application, you will receive the OAuthClientId, also called a consumer key. You will also receive a client secret, also called a consumer secret. Set the client secret in the OAuthClientSecret property.

AuthToken

The authentication token used to request and obtain the OAuth Access Token.

Remarks

This property is required only when performing headless authentication in OAuth 1.0. It can be obtained from the GetOAuthAuthorizationUrl stored procedure.

It can be supplied alongside the AuthKey in the GetOAuthAccessToken stored procedure to obtain the OAuthAccessToken.

AuthKey

The authentication secret used to request and obtain the OAuth Access Token.

Remarks

This property is required only when performing headless authentication in OAuth 1.0. It can be obtained from the GetOAuthAuthorizationUrl stored procedure.

It can be supplied alongside the AuthToken in the GetOAuthAccessToken stored procedure to obtain the OAuthAccessToken.

SSL

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

SSLServerCert

The certificate to be accepted from the server when connecting using TLS/SSL.

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:


Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

Firewall

This section provides a complete list of the Firewall properties you can configure in the connection string for this provider.

Property	Description
FirewallType	The protocol used by a proxy-based firewall.
FirewallServer	The name or IP address of a proxy-based firewall.
FirewallPort	The TCP port for a proxy-based firewall.
FirewallUser	The user name to use to authenticate with a proxy-based firewall.
FirewallPassword	A password used to authenticate to a proxy-based firewall.

FirewallType

The protocol used by a proxy-based firewall.

Remarks

This property specifies the protocol that the Sync App will use to tunnel traffic through the FirewallServer proxy. Note that by default, the Sync App connects to the system proxy; to disable this behavior and connect to one of the following proxy types, set ProxyAutoDetect to false.


Type	Default Port	Description
TUNNEL	80	When this is set, the Sync App opens a connection to FreshBooks and traffic flows back and forth through the proxy.
SOCKS4	1080	When this is set, the Sync App sends data through the SOCKS 4 proxy specified by FirewallServer and FirewallPort and passes the FirewallUser value to the proxy, which determines if the connection request should be granted.
SOCKS5	1080	When this is set, the Sync App sends data through the SOCKS 5 proxy specified by FirewallServer and FirewallPort. If your proxy requires authentication, set FirewallUser and FirewallPassword to credentials the proxy recognizes.

To connect to HTTP proxies, use ProxyServer and ProxyPort. To authenticate to HTTP proxies, use ProxyAuthScheme, ProxyUser, and ProxyPassword.

FirewallServer

The name or IP address of a proxy-based firewall.

Remarks

This property specifies the IP address, DNS name, or host name of a proxy allowing traversal of a firewall. The protocol is specified by FirewallType: Use FirewallServer with this property to connect through SOCKS or do tunneling. Use ProxyServer to connect to an HTTP proxy.

Note that the Sync App uses the system proxy by default. To use a different proxy, set ProxyAutoDetect to false.

FirewallPort

The TCP port for a proxy-based firewall.

Remarks

This specifies the TCP port for a proxy allowing traversal of a firewall. Use FirewallServer to specify the name or IP address. Specify the protocol with FirewallType.

FirewallUser

The user name to use to authenticate with a proxy-based firewall.

Remarks

The FirewallUser and FirewallPassword properties are used to authenticate against the proxy specified in FirewallServer and FirewallPort, following the authentication method specified in FirewallType.

FirewallPassword

A password used to authenticate to a proxy-based firewall.

Remarks

This property is passed to the proxy specified by FirewallServer and FirewallPort, following the authentication method specified by FirewallType.

Proxy

This section provides a complete list of the Proxy properties you can configure in the connection string for this provider.

Property	Description
ProxyAutoDetect	This indicates whether to use the system proxy settings or not.
ProxyServer	The hostname or IP address of a proxy to route HTTP traffic through.
ProxyPort	The TCP port the ProxyServer proxy is running on.
ProxyAuthScheme	The authentication type to use to authenticate to the ProxyServer proxy.
ProxyUser	A user name to be used to authenticate to the ProxyServer proxy.
ProxyPassword	A password to be used to authenticate to the ProxyServer proxy.
ProxySSLType	The SSL type to use when connecting to the ProxyServer proxy.
ProxyExceptions	A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

ProxyAutoDetect

This indicates whether to use the system proxy settings or not.

Remarks

This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

To connect to an HTTP proxy, see ProxyServer. For other proxies, such as SOCKS or tunneling, see FirewallType.

ProxyServer

The hostname or IP address of a proxy to route HTTP traffic through.

Remarks

The hostname or IP address of a proxy to route HTTP traffic through. The Sync App can use the HTTP, Windows (NTLM), or Kerberos authentication types to authenticate to an HTTP proxy.

If you need to connect through a SOCKS proxy or tunnel the connection, see FirewallType.

By default, the Sync App uses the system proxy. If you need to use another proxy, set ProxyAutoDetect to false.

ProxyPort

The TCP port the ProxyServer proxy is running on.

Remarks

The port the HTTP proxy is running on that you want to redirect HTTP traffic through. Specify the HTTP proxy in ProxyServer. For other proxy types, see FirewallType.

ProxyAuthScheme

The authentication type to use to authenticate to the ProxyServer proxy.

Remarks

This value specifies the authentication type to use to authenticate to the HTTP proxy specified by ProxyServer and ProxyPort.

Note that the Sync App will use the system proxy settings by default, without further configuration needed; if you want to connect to another proxy, you will need to set ProxyAutoDetect to false, in addition to ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

The authentication type can be one of the following:

BASIC: The Sync App performs HTTP BASIC authentication.
DIGEST: The Sync App performs HTTP DIGEST authentication.
NEGOTIATE: The Sync App retrieves an NTLM or Kerberos token based on the applicable protocol for authentication.
PROPRIETARY: The Sync App does not generate an NTLM or Kerberos token. You must supply this token in the Authorization header of the HTTP request.

If you need to use another authentication type, such as SOCKS 5 authentication, see FirewallType.

ProxyUser

A user name to be used to authenticate to the ProxyServer proxy.

Remarks

The ProxyUser and ProxyPassword options are used to connect and authenticate against the HTTP proxy specified in ProxyServer.

You can select one of the available authentication types in ProxyAuthScheme. If you are using HTTP authentication, set this to the user name of a user recognized by the HTTP proxy. If you are using Windows or Kerberos authentication, set this property to a user name in one of the following formats:

user@domain
domain\user

ProxyPassword

A password to be used to authenticate to the ProxyServer proxy.

Remarks

This property is used to authenticate to an HTTP proxy server that supports NTLM (Windows), Kerberos, or HTTP authentication. To specify the HTTP proxy, you can set ProxyServer and ProxyPort. To specify the authentication type, set ProxyAuthScheme.

If you are using HTTP authentication, additionally set ProxyUser and ProxyPassword to HTTP proxy.

If you are using NTLM authentication, set ProxyUser and ProxyPassword to your Windows password. You may also need these to complete Kerberos authentication.

For SOCKS 5 authentication or tunneling, see FirewallType.

By default, the Sync App uses the system proxy. If you want to connect to another proxy, set ProxyAutoDetect to false.

ProxySSLType

The SSL type to use when connecting to the ProxyServer proxy.

Remarks

This property determines when to use SSL for the connection to an HTTP proxy specified by ProxyServer. This value can be AUTO, ALWAYS, NEVER, or TUNNEL. The applicable values are the following:


AUTO	Default setting. If the URL is an HTTPS URL, the Sync App will use the TUNNEL option. If the URL is an HTTP URL, the component will use the NEVER option.
ALWAYS	The connection is always SSL enabled.
NEVER	The connection is not SSL enabled.
TUNNEL	The connection is through a tunneling proxy. The proxy server opens a connection to the remote host and traffic flows back and forth through the proxy.

ProxyExceptions

A semicolon separated list of destination hostnames or IPs that are exempt from connecting through the ProxyServer .

Remarks

The ProxyServer is used for all addresses, except for addresses defined in this property. Use semicolons to separate entries.

Note that the Sync App uses the system proxy settings by default, without further configuration needed; if you want to explicitly configure proxy exceptions for this connection, you need to set ProxyAutoDetect = false, and configure ProxyServer and ProxyPort. To authenticate, set ProxyAuthScheme and set ProxyUser and ProxyPassword, if needed.

Logging

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

Property	Description
LogModules	Core modules to be included in the log file.

LogModules

Core modules to be included in the log file.

Remarks

Only the modules specified (separated by ';') will be included in the log file. By default all modules are included.

See the Logging page for an overview.

Schema

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

Property	Description
Location	A path to the directory that contains the schema files defining tables, views, and stored procedures.
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
Tables	This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.
Views	Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Location

A path to the directory that contains the schema files defining tables, views, and stored procedures.

Remarks

The path to a directory which contains the schema files for the Sync App (.rsd files for tables and views, .rsb files for stored procedures). The folder location can be a relative path from the location of the executable. The Location property is only needed if you want to customize definitions (for example, change a column name, ignore a column, and so on) or extend the data model with new tables, views, or stored procedures.

Note: Given that this Sync App supports multiple schemas, the structure for FreshBooks custom schema files is as follows:

Each schema is given a folder corresponding to that schema name.
These schema folders are contained in a parent folder.
The parent folder should be set as the Location, not an individual schema's folder.

If left unspecified, the default location is "%APPDATA%\\CData\\FreshBooks Data Provider\\Schema" with %APPDATA% being set to the user's configuration directory:


Platform	%APPDATA%
Windows	The value of the APPDATA environment variable
Linux	~/.config

BrowsableSchemas

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Tables

This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.

Remarks

Listing the tables from some databases can be expensive. Providing a list of tables in the connection string improves the performance of the Sync App.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the tables you want in a comma-separated list. Each table should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Tables=TableA,[TableB/WithSlash],WithCatalog.WithSchema.`TableC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Views

Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.

Remarks

Listing the views from some databases can be expensive. Providing a list of views in the connection string improves the performance of the Sync App.

This property can also be used as an alternative to automatically listing views if you already know which ones you want to work with and there would otherwise be too many to work with.

Specify the views you want in a comma-separated list. Each view should be a valid SQL identifier with any special characters escaped using square brackets, double-quotes or backticks. For example, Views=ViewA,[ViewB/WithSlash],WithCatalog.WithSchema.`ViewC With Space`.

Note that when connecting to a data source with multiple schemas or catalogs, you will need to provide the fully qualified name of the table in this property, as in the last example here, to avoid ambiguity between tables that exist in multiple catalogs or schemas.

Miscellaneous

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

Property	Description
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Other	These hidden properties are used only in specific use cases.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UserDefinedViews	A filepath pointing to the JSON configuration file containing your custom views.

MaxRows

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Other

These hidden properties are used only in specific use cases.

Remarks

The properties listed below are available for specific use cases. Normal driver use cases and functionality should not require these properties.

Specify multiple properties in a semicolon-separated list.

Integration and Formatting


DefaultColumnSize	Sets the default length of string fields when the data source does not provide column length in the metadata. The default value is 2000.
ConvertDateTimeToGMT	Determines whether to convert date-time values to GMT, instead of the local time of the machine.
RecordToFile=filename	Records the underlying socket data transfer to the specified file.

PseudoColumns

This property indicates whether or not to include pseudo columns as columns to the table.

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

Timeout

The value in seconds until the timeout error is thrown, canceling the operation.

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the Sync App throws an exception.

UserDefinedViews

A filepath pointing to the JSON configuration file containing your custom views.

Remarks

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The Sync App 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 Sync App.

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

Note that the specified path is not embedded in quotation marks.

FreshBooks Connector for CData Sync

FreshBooks Connector for CData Sync

Overview

FreshBooks Version Support

Establishing a Connection

Adding a Connection to FreshBooks

Connecting to FreshBooks

Advanced Features

User Defined Views

SSL Configuration

Firewall and Proxy

Query Processing

Logging

SSL Configuration

Customizing the SSL Configuration

Firewall and Proxy

Connecting Through a Firewall or Proxy

HTTP Proxies

Other Proxies

Data Model

Key Features

Alpha API

Tables

FreshBooks Connector for CData Sync Tables

BillPayments

Table Specific Information

Insert

Update

Delete

Columns

Bills

Table Specific Information

Insert

Update

Delete

Columns

BillVendors

Table Specific Information

Insert

Update

Delete

Columns

Clients

Columns

Estimates

Table Specific Information

Insert

Update

Columns

Expenses

Columns

InvoiceProfiles

Table Specific Information

Insert

Update

Delete

Columns

Invoices

Table Specific Information

Insert

Update

Columns

Items

Columns

OtherIncomes

Table Specific Information

Insert

Update

Delete

Columns

Payments

Columns

Projects

Columns

Staff

Columns

Taxes

Columns

Views

FreshBooks Connector for CData Sync Views