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.