InvoiceProfiles
Invoice Profiles are used to create recurring invoices. They have the ability to be saved as a draft invoice or be automatically sent out to the client via email.
Table Specific Information
Insert
To insert, Frequency, CreateDate, NumberRecurring and CustomerID fields are required. To insert InvoiceProfiles with multiple line items, you can either insert an aggregate value into the LinesAggregate field, or use a temporary table. This section provides examples of both procedures.You can insert Invoices with multiple line items using aggregates. For example:
Insert INTO InvoiceProfiles (Frequency, CreateDate, NumberRecurring, CustomerId, LinesAggregate) VALUES ('m', '2018-02-03', '2', '12761','[{ \"description\": \"This is description\", \"taxName1\": \"Tax1\", \"taxAmount1\": 0,\"name\": \"Paperwork\", \"unit_cost\": {\"amount\": \"5000.00\", \"code\": \"USD\"}}]')
You can also insert InvoiceProfiles with multiple line items using a temporary table.
The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:
INSERT INTO InvoicesLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20) INSERT INTO InvoicesLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10)
Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for LinesAggregate. For example:
Insert INTO InvoiceProfiles (Frequency, CreateDate, NumberRecurring, CustomerId, LinesAggregate) VALUES ('m', '2018-02-03', '2', '12761', 'InvoicesLineItems#TEMP')
Update
To update, the Id value is required in the WHERE clause. To update LinesAggregate, you need to override the aggregate LinesAggregate field, either by setting an aggregate value, or by using a temporary table. For example:INSERT INTO InvoiceLineItems#TEMP (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20) INSERT INTO InvoiceLineItems#TEMP (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10) UPDATE InvoiceProfiles SET CreateDate = '2023-11-08', LinesAggregate = 'InvoiceLineItems#TEMP' WHERE Id = 29153
Delete
To delete, Id value is required in the WHERE clause.DELETE from InvoiceProfiles where Id = '26157'
Columns
Name | Type | ReadOnly | Description |
Id [KEY] | Integer | True |
The unique id for the invoice profile |
AccountingSystemId | String | False |
Unique id for the system |
Address | String | False |
The address on the invoice |
Amount | String | True |
The amount to be paid for invoice |
AmountCode | String | True |
Three-letter currency code |
AutoBill | Boolean | True |
Whether this invoice has a credit card saved |
BillGateway | String | False |
The gateway that is used for payment |
City | String | False |
City for address on invoice |
Code | String | True |
Three-letter currency code |
Country | String | False |
Country for address on invoice |
CreateDate | Date | False |
The create day of the invoice profile |
CurrencyCode | String | True |
Three-letter currency code for invoice |
CustomerId | Integer | False |
The unique id for the client of the invoice |
Description | String | False |
The description of the invocie |
Disable | Boolean | False |
True will disable the auto-generation of invoices |
DiscountTotalAmount | String | True |
The amount of the discount |
DiscountTotalCode | String | True |
Three-letter currency code for the discount |
DiscountValue | String | False |
Decimal-string amount |
DueOffsetDays | Integer | False |
Number of days from creation that invoice is due |
ExtArchive | Integer | False |
0 or 1 indicating archived or not |
Fname | String | False |
The first name of client on invoice |
Frequency | String | False |
The frequency the invoice will be created. In the form of xy where x is an integer and y is either d,w,m,y. (example: Every two weeks would be 2w) |
IncludeUnbilledTime | Boolean | False |
True if unbilled time is included, false otherwise |
Language | String | False |
2 letter code representing the language |
Lname | String | False |
The last name of client on invoice |
Notes | Unknown | False |
Notes listed on invoice |
NumberRecurring | Integer | False |
The number of invoices that will be generated, 0 for infinite |
OccurrencesToDate | Integer | True |
Number of invoices that have been generated |
Organization | String | False |
Name of the organization belonging to the client |
OwnerId | Integer | True |
Id of creator of invoice. 1 if business admin, other if created by e.g. a contractor |
PaymentDetails | String | False |
Details for payment for the invoice |
PoNumber | String | False |
Post office box number for address on invoice |
ProfileId | Integer | True |
Profile Id |
Province | String | False |
Province for address on invoice. |
SendEmail | Boolean | False |
True email invoice on creation, false will leave as draft |
SendGmail | Boolean | False |
True to send invoice via ground mail |
Street | String | False |
Street for address on invoice |
Street2 | String | False |
Second street for address on invoice |
Terms | String | False |
Terms listed on invoice |
Updated | Datetime | True |
The date the invoice profile was updated |
Vatname | String | False |
Value added tax name if provided |
Vatnumber | String | False |
Value added tax number if provided |
Visstate | Integer | False |
0 for active, 1 for deleted |
LinesAggregate | String | False |
An aggregate of lines associated with an invoice. See the help for the Invoices table for more information on inserting and updating to this field. |