This page provides a guide to Establishing a Connection to FreshBooks in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to FreshBooks

Establishing a Connection shows how to authenticate to FreshBooks and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from FreshBooks through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to FreshBooks by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. 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.

Connect to the Alpha API

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

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

Register Your Application

To obtain the OAuth client credentials:

1. Log into the FreshBooks developers site at https://my.freshbooks.com/#/developer and click Create an App.
2. Enter information to be displayed to your users when they are prompted to grant permissions to your app.
3. Specify a redirect URI.

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

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

Authenticate to FreshBooks from a Desktop Application

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

• OAuthClientId: Set this to the name of the company you are connecting to.
• OAuthClientSecret: Set this to the consumer secret in your app settings.
• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the access token in the connection string.
• AccountId: Set this to connect to data. Query the IdentityInfo view to obtain this value.

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

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

Authenticate to FreshBooks from a Web Application

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

• OAuthClientId: Set this to the name of the company you are connecting to.
• OAuthClientSecret: Set this to the consumer secret in your app settings.

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

1. Call GetOAuthAuthorizationURL. The stored procedure returns the URL to the FreshBooks OAuth endpoint.
2. Log in at the OAuth endpoint and authorize the app. You are redirected back to the callback URL.

   The callback URL contains the verifier code in a query string parameter. The parameter is named "oauth_verifier". Extract the verifier code from the callback URL.

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

To connect to data, set the following connection properties:

• AccountId
• OAuthClientSecret
• OAuthAccessToken
• OAuthAccessTokenSecret

Note: To obtain an AccountId, query the IdentityInfo view to obtain this value.

Refresh the Token

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

Customizing the SSL Configuration

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

Alpha API

The Data Model uses the OAuth 2.0 specification.

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

CData Cloud - FreshBooks 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.

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.

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

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

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

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

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

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

CData Cloud - FreshBooks 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.

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.

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

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

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.

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

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.

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.

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

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.

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

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

CData Cloud - FreshBooks Stored Procedures

Name	Description
CreateExpenseWithAttachment	To let end users create an expense with attachment.
CreateInvoiceWithAttachment	To let end users upload attachment to an invoice.

To let end users create an expense with attachment.

Input

Name	Type	Required	Description
Attachment	String	False	The full path of the attachment to upload.
FileName	String	False	Name of the file. If content is not empty.
Amount	Decimal	False	The amount of the expense.
CategoryId	Integer	True	The identifier of the related expense category.
Date	Date	False	Date of the expense. (YYYY-MM-DD)
StaffId	Integer	True	Identifier of related staff member, if applicable.

Result Set Columns

Name	Type	Description
Success	String	True if the image is uploaded successfully.

To let end users upload attachment to an invoice.

Execute

Create an invoice with attachment:

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

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

Input

Name	Type	Required	Description
Attachment	String	False	The full path of the attachment to upload.
CustomerId	Integer	True	Client Id unique to this system.
CreateDate	Date	True	Date invoice was created. (YYYY-MM-DD)
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.
FileName	String	False	Name of the file. If content is not empty.
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.
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.

Result Set Columns

Name	Type	Description
Success	String	True if the image is uploaded successfully.

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

Schema Tables

The following tables return database metadata for FreshBooks:

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

Data Source Tables

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

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

Query Information Tables

The following table returns query statistics for data modification queries:

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

Lists the available databases.

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

SELECT * FROM sys_catalogs

Columns

Name	Type	Description
CatalogName	String	The database name.

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas
          

Columns

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

Lists the available tables.

The following query retrieves the available tables and views:

          SELECT * FROM sys_tables
          

Columns

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

Describes the columns of the available tables and views.

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

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

Columns

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

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures
          

Columns

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

Describes stored procedure parameters.

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

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

Columns

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

Describes the primary and foreign keys.

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

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

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
IsKey	Boolean	Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey	Boolean	Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.

Describes the foreign keys.

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

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.
ForeignKeyType	String	Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

Describes the primary keys.

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

         SELECT * FROM sys_primarykeys
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
KeySeq	String	The sequence number of the primary key.
KeyName	String	The name of the primary key.

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

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

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the index.
SchemaName	String	The name of the schema containing the index.
TableName	String	The name of the table containing the index.
IndexName	String	The index name.
ColumnName	String	The name of the column associated with the index.
IsUnique	Boolean	True if the index is unique. False otherwise.
IsPrimary	Boolean	True if the index is a primary key. False otherwise.
Type	Int16	An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder	String	The sort order: A for ascending or D for descending.
OrdinalPosition	Int16	The sequence number of the column in the index.

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

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

jdbc:cdata:freshbooks:config:

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

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

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Name	Type	Description
Name	String	The name of the connection property.
ShortDescription	String	A brief description.
Type	String	The data type of the connection property.
Default	String	The default value if one is not explicitly set.
Values	String	A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value	String	The value you set or a preconfigured default.
Required	Boolean	Whether the property is required to connect.
Category	String	The category of the connection property.
IsSessionProperty	String	Whether the property is a session property, used to save information about the current connection.
Sensitivity	String	The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName	String	A camel-cased truncated form of the connection property name.
Ordinal	Int32	The index of the parameter.
CatOrdinal	Int32	The index of the parameter category.
Hierarchy	String	Shows dependent properties associated that need to be set alongside this one.
Visible	Boolean	Informs whether the property is visible in the connection UI.
ETC	String	Various miscellaneous information about the property.

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

See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

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

Name	Description	Possible Values
AGGREGATE_FUNCTIONS	Supported aggregation functions.	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	Whether COUNT function is supported.	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	The opening character used to escape an identifier.	[
IDENTIFIER_QUOTE_CLOSE_CHAR	The closing character used to escape an identifier.	]
SUPPORTED_OPERATORS	A list of supported SQL operators.	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	Whether GROUP BY is supported, and, if so, the degree of support.	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIES	The supported varieties of outer joins supported.	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	Whether outer joins are supported.	YES, NO
SUBQUERIES	Whether subqueries are supported, and, if so, the degree of support.	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONS	Supported string functions.	LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS	Supported numeric functions.	ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS	Supported date/time functions.	NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES	Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS	A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN	String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION	Indicates if the provider supports transactions such as commit and rollback.	YES, NO
DIALECT	Indicates the SQL dialect to use.
KEY_PROPERTIES	Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS	Indicates if multiple schemas may exist for the provider.	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	Indicates if multiple catalogs may exist for the provider.	YES, NO
DATASYNCVERSION	The CData Data Sync version needed to access this driver.	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	The CData Data Sync category of this driver.	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	Whether enhanced SQL functionality beyond what is offered by the API is supported.	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	Whether batch operations are supported.	YES, NO
SQL_CAP	All supported SQL capabilities for this driver.	SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS	A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY	Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.	YES, NO
PSEUDO_COLUMNS	A string array indicating the available pseudo columns.
MERGE_ALWAYS	If the value is true, The Merge Mode is forcibly executed in Data Sync.	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION	Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE	Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY	A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION	Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID	Indicates whether the CheckCache statement should be done against the parent key column.	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	Indicates stored procedures that can be used for generating schema files.

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

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

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

Columns

Name	Type	Description
NAME	String	A component of SQL syntax, or a capability that can be processed on the server.
VALUE	String	Detail on the supported SQL or SQL syntax.

Returns information about attempted modifications.

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

         SELECT * FROM sys_identity
          

Columns

Name	Type	Description
Id	String	The database-generated Id returned from a data modification operation.
Batch	String	An identifier for the batch. 1 for a single operation.
Operation	String	The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message	String	SUCCESS or an error message if the update in the batch failed.

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.

Logging

---

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

Schema

---

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

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

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.

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

Data Type

string

Default Value

""

Remarks

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

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.

Data Type

string

Default Value

""

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.

Data Type

string

Default Value

""

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.

Data Type

string

Default Value

""

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.

Data Type

string

Default Value

""

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.

Data Type

string

Default Value

""

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 Logging properties you can configure in the connection string for this provider.

---

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

The verbosity level that determines the amount of detail included in the log file.

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.

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

---

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

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

Data Type

string

Default Value

""

Remarks

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

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

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

Data Type

int

Default Value

-1

Remarks

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

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

Data Type

string

Default Value

""

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.

Data Type

int

Default Value

60

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 Cloud throws an exception.