Create a connection to FreshBooks by navigating to the Connections page in the Sync App application and selecting the corresponding icon in the Add Connections panel. If the FreshBooks icon is not available, click the Add More icon to download and install the FreshBooks connector from the CData site.

Required properties are listed under the Settings tab. The Advanced tab lists connection properties that are not typically required.

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.

Web Applications

When connecting via a Web application, you need to register a custom OAuth app with FreshBooks. You can then use the Sync App to get and manage the OAuth token values. See Creating a Custom OAuth App for more information.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the client Id in your app settings.
• OAuthClientSecret: Set this to the client secret in your app settings.

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the Redirect URI you specified in your app settings. The stored procedure returns the URL to the OAuth endpoint.
2. Navigate to the URL that the stored procedure returned in Step 1. Log in, and authorize the web application. After authenticating, the browser redirects you to the redirect URI.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the "code" parameter in the query string of the callback URL. If necessary, set the Permissions parameter to request custom permissions.

To connect to data, set the OAuthAccessToken connection property to the access token returned by the stored procedure. When the access token expires after ExpiresIn seconds, call GetOAuthAccessToken again to obtain a new access token.

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.

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.

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.

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.

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

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

FreshBooks Connector for CData Sync Tables

Name	Description
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.
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.
Payments	Payments are a record of the payments made on your invoices.
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.

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 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	False	Identifier of the creator of the estimate. (writable on create only)
SentId	Integer	False	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	False	The time the estimate was last updated. (writable on create only)
DisplayStatus	String	False	Description of status shown in FreshBooks UI. Either draft, sent, or viewed. (writable on create only)
ReplyStatus	String	False	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.
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 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.

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 are stored from invoice lines to make invoicing easier in the future.

Columns

Name	Type	ReadOnly	Description
Id [KEY]	Integer	False	Unique identifier of the item.
AccountingSystemId	String	False	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.

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	False	The unique identifier of the business the client exists on.
Amount	Decimal	False	Amount paid on invoice to two decimal places.
ClientId	Integer	False	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	False	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	False	Identifier of related overpayment if relevant.
TransactionId	Integer	False	Identifier of the related transaction.
Type	String	False	The type of the payment: 'Check', 'Credit', 'Cash', etc.
Updated	Datetime	False	Date object was last updated. (YYYY-MM-DD)
VisState	Integer	False	0 for active, 1 for deleted.

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

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

Queries can be executed against a view as if it were a normal table, and the data that comes back is similar in that regard.

Dynamic views, such as queries exposed as views, and views for looking up specific combinations of project_team work items are supported.

FreshBooks Connector for CData Sync Views

Name	Description
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.
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.
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.
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.
Taxes	FreshBooks allows you to store your previously used taxes to re-apply conveniently.
TimeEntries	Time Entries represent time spent working for a client or project.

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

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.

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

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.

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.

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.

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.

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.

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

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

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

Columns

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

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.

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.
CompanyName	The company name used to access FreshBooks.

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. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
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 rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
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.

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.
CompanyName	The company name used to access FreshBooks.

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.

The company name used to access FreshBooks.

Remarks

This value is required to connect to FreshBooks and is the same as the first part of the URL used to connect. For example, the account with the URL https://sample.freshbooks.com/home has a company name "sample".

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.
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 .

This indicates whether to use the system proxy settings or not. This takes precedence over other proxy settings, so you'll need to set ProxyAutoDetect to FALSE in order use custom proxy settings.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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:

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.

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.

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.

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 rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.
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.

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

Remarks

Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.

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.

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, "*=*".

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.

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"