Invoices
Invoices in FreshBooks are what gets sent to Clients, detailing specific goods or services performed or provided by the Administrator of their System, and the amount that Client owes to the Admin.
Table Specific Information
Insert
To insert, CustomerId and CreateDate fields are required. To insert Invoices with multiple line items, you can either insert an aggregate value into the LinesAggregate field, or use a temporary table. This section provides examples of both procedures.You can insert Invoices with multiple line items using aggregates. For example:
Insert INTO Invoices (CustomerId, CreateDate, LinesAggregate) VALUES ('12717', '2018-02-03', '[\n" + " {\n" + " \"description\": \"\",\n" + " \"taxName1\": \"\",\n" + " \"taxAmount1\": 0,\n" + " \"name\": \"Paperwork\",\n" + " \"qty\": 1,\n" + " \"taxName2\": \"\",\n" + " \"taxAmount2\": 0,\n" + " \"unit_cost\": {\n" + " \"amount\": \"5000.00\",\n" + " \"code\": \"USD\"\n" + " }\n" + " },\n" + " {\n" + " \"description\": \"\",\n" + " \"taxName1\": \"\",\n" + " \"taxAmount1\": 0,\n" + " \"name\": \"TV Ads\",\n" + " \"qty\": 1,\n" + " \"taxName2\": \"\",\n" + " \"taxAmount2\": 0,\n" + " \"unit_cost\": {\n" + " \"amount\": \"3000.00\",\n" + " \"code\": \"USD\"\n" + " }\n" + " }\n" + " ]')"
You can also insert Invoices with multiple line items using a temporary table.
The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:
INSERT INTO InvoicesLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20) INSERT INTO InvoicesLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10) INSERT INTO InvoicesLineItems#TEMP (Name, Amount) VALUES ('C', 300) INSERT INTO InvoicesLineItems#TEMP (Name, Amount) VALUES ('D', 150) INSERT INTO InvoicesLineItems#TEMP (Name, Amount) VALUES ('E', 200)
Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for LinesAggregate. For example:
Insert INTO Invoices (CustomerId, CreateDate, LinesAggregate) VALUES ('12717', '2018-02-03', 'InvoicesLineItems#TEMP')
Update
To update, the Id value is required in the WHERE clause. To update LinesAggregate, you need to override the aggregate LinesAggregate field, either by setting an aggregate value, or by using a temporary table. For example:INSERT INTO InvoiceLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20) INSERT INTO InvoiceLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10) UPDATE Invoices SET CreateDate = '2018-01-01', LinesAggregate = 'InvoiceLineItems#TEMP' WHERE Id = 29153
Columns
Name | Type | ReadOnly | Description |
Id [KEY] | Integer | True |
Invoice Id that is unique to this system. |
AccountingSystemId | String | True |
Unique Id for the system. |
AccountId | String | True |
Unique Id for the system. |
TotalAmount | Decimal | True |
Total amount of invoice, to two decimal places. |
PaidAmount | Decimal | True |
Amount paid on invoice, to two decimal places. |
PaidCode | String | True |
Three-letter currency code. |
OutstandingAmount | Decimal | True |
Amount outstanding on the invoice, to two decimal places. |
OutstandingCode | String | True |
Three-letter currency code. |
DiscountTotalAmount | Decimal | True |
Amount of discount, to two decimal places. |
DiscountTotalCode | String | True |
Three-letter currency code. |
Description | String | True |
Description of first line of invoice. |
CurrentOrganization | String | True |
Name of organization being invoiced. |
DueDate | Date | True |
Date invoice is marked as due by. (YYYY-MM-DD, calculated from due_offset_days) |
DatePaid | Date | True |
Date invoice was fully paid. (YYYY-MM-DD) |
OwnerId | Integer | False |
Id of the invoice's creator. (writable on create only) |
EstimateId | Integer | False |
Id of associated estimate, 0 if none.(writable on create only) |
BasecampId | Integer | False |
Id of connected basecamp account, 0 if none.(writable on create only) |
SentId | Integer | False |
User Id of user who sent the invoice.(writable on create only) |
Status | String | False |
Invoice status.(writable on create only) |
Parent | Integer | False |
Id of object this invoice was generated from, 0 if none.(writable on create only) |
CreatedAt | Datetime | False |
Time the invoice was created. (YYYY-MM-DD, writable on create only) |
Updated | Datetime | False |
Time the invoice was last updated. (YYYY-MM-DD, writable on create only) |
DisplayStatus | String | False |
Description of status shown in Freshbooks UI.(writable on create only) |
AutobillStatus | String | False |
Description of autobill status.(writable on create only) |
PaymentStatus | String | False |
Description of payment status.(writable on create only) |
LastOrderStatus | String | False |
Describes the status of the last attempted payment.(writable on create only) |
DisputeStatus | String | False |
Description of whether invoice has been disputed.(writable on create only) |
DepositStatus | String | False |
Description of deposits applied to invoice.(writable on create only) |
AutoBill | String | False |
Whether this invoice has a credit card saved.(writable on create only) |
V3Status | String | False |
Description of Invoice status.(writable on create only) |
InvoiceNumber | String | False |
User-specified and visible Invoice Id. |
CustomerId | Integer | False |
Client Id unique to this system. |
CreateDate | Date | False |
Date invoice was created. (YYYY-MM-DD) |
GenerationDate | Date | False |
Date invoice was generated from object.(YYYY-MM-DD, or null if not) |
DiscountValue | Decimal | False |
Decimal-string amount of discount. |
DiscountDescription | String | False |
Public note about discount. |
PONumber | String | False |
Reference number for address on invoice. |
Template | String | False |
Choice of rendering style. (internal, deprecated) |
CurrencyCode | String | False |
Three-letter currency code for invoice. |
Language | String | False |
Two-letter language code. |
Terms | String | False |
Terms listed on invoice. |
Notes | String | False |
Notes listed on invoice. |
Address | String | False |
First line of address on invoice. |
ReturnUri | String | False |
(deprecated) |
DepositAmount | Decimal | False |
Amount required as deposit, null if none. |
DepositPercentage | Decimal | False |
Percentage of the invoice's value required as a deposit. |
Gmail | String | False |
Whether to send via ground mail. |
ShowAttachments | String | False |
Whether attachments on invoice are rendered. |
VisState | Integer | False |
Whether active (0) or deleted (1). |
Street | String | False |
Street for address on invoice. |
Street2 | String | False |
Second line of street for address on invoice. |
City | String | False |
City for address on invoice. |
Province | String | False |
Province for address on invoice. |
Code | String | False |
Zip code for address on invoice. |
Country | String | False |
Country for address on invoice. |
Organization | String | False |
Name of organization being invoiced. |
FirstName | String | False |
First name of client on invoice. |
LastName | String | False |
Last name of client on invoice. |
VatName | String | False |
Value added tax name, if provided. |
VatNumber | String | False |
Value added tax number, if provided. |
DueOffsetDays | Integer | False |
Number of days from creation that invoice is due. |
LinesAggregate | String | False |
An aggregate of lines associated with an invoice. See the help for the Invoices table for more information on inserting and updating to this field. |