Estimates
Estimates in FreshBooks provides Owners and Clients a way to agree and negotiate on the price and scope of work before it commences.
Table Specific Information
Insert
To insert, CustomerId and CreateDate fields are required. To insert Estimates with multiple line items, you can either insert into the LinesAggregate field an aggregate value, or use a temporary table. This section provides examples of both procedures.You can insert Estimates with multiple line items using aggregates. For example:
Insert INTO Estimates (CustomerId, CreateDate, LinesAggregate) VALUES ('12717', '2018-02-03', '[\n" + " {\n" + " \"description\": \"\",\n" + " \"taxName1\": \"\",\n" + " \"taxAmount1\": 0,\n" + " \"name\": \"Paperwork\",\n" + " \"qty\": 1,\n" + " \"taxName2\": \"\",\n" + " \"taxAmount2\": 0,\n" + " \"unit_cost\": {\n" + " \"amount\": \"5000.00\",\n" + " \"code\": \"USD\"\n" + " }\n" + " },\n" + " {\n" + " \"description\": \"\",\n" + " \"taxName1\": \"\",\n" + " \"taxAmount1\": 0,\n" + " \"name\": \"TV Ads\",\n" + " \"qty\": 1,\n" + " \"taxName2\": \"\",\n" + " \"taxAmount2\": 0,\n" + " \"unit_cost\": {\n" + " \"amount\": \"3000.00\",\n" + " \"code\": \"USD\"\n" + " }\n" + " }\n" + " ]')"
You can also insert Estimates with multiple line items using a temporary table.
The temporary table you are populating is dynamic and will be created at run time the first time you insert to it. Temporary tables are denoted by a # appearing in their name. When using a temporary table to insert, the temporary table must be named in the format of [TableName]#TEMP, where TableName is the name of the table you will be inserting to. For example:
INSERT INTO EstimatesLineItems#Temp (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20) INSERT INTO EstimatesLineItems#Temp (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10) INSERT INTO EstimatesLineItems#Temp (Name, Amount) VALUES ('C', 300 ) INSERT INTO EstimatesLineItems#Temp (Name, Amount) VALUES ('D', 150 ) INSERT INTO EstimatesLineItems#Temp (Name, Amount) VALUES ('E', 200 )
Once your temporary table is populated, it is now time to insert to the actual table in FreshBooks. You can do this by performing an INSERT to the actual table and setting the name of the temporary table as a value for LinesAggregate. For example:
Insert INTO Estimates (CustomerId, CreateDate, LinesAggregate) VALUES ('12717', '2018-02-03', 'EstimatesLineItems#Temp')
Update
To update, the Id value is required in the WHERE clause. To update LinesAggregate, you need to override the aggregate LinesAggregate field, either by setting an aggregate value, or by using a temporary table. For example:INSERT INTO Estimate2195LineItems#Temp (Name, Amount, TaxName1, TaxAmount1) VALUES ('A', 500, 'tax1', 20) INSERT INTO Estimate2195LineItems#Temp (Name, Amount, TaxName2, TaxAmount2) VALUES ('B', 100, 'tax2', 10) UPDATE Estimates SET EstimateNumber = 5, DiscountValue = 15, LinesAggregate = 'Estimate2195LineItems#Temp' WHERE id = 2195
Columns
Name | Type | ReadOnly | Description |
Id [KEY] | Integer | True |
The unique identifier of an estimate. |
AccountingSystemId | String | True |
The unique identifier for the system. |
UIStatus | String | True |
Estimate UI status |
Status | String | True |
Estimate status |
Accepted | Boolean | True |
Indicates whether estimate has been accepted. |
Amount | Deicmal | True |
Total amount of estimate, to two decimal places. |
AmountCode | String | True |
Three letter currency code associated with the amount. |
DiscountTotalAmount | Decimal | True |
Amount of discount, to two decimal places. |
DiscountTotalCode | String | True |
Three letter currency code associated with the discount. |
Description | String | True |
Description of first line of estimate. |
CurrentOrganization | String | True |
Name of organization being estimated. |
Invoiced | String | True |
Indicator of whether this estimate has been converted to an invoice that was sent. |
OwnerId | Integer | False |
Identifier of the creator of the estimate. (writable on create only) |
SentId | Integer | False |
Identifier of user who sent the estimate, typically 1 for admin. (writable on create only) |
CreatedAt | Datetime | True |
The time the estimate was created. (writable on create only) |
Updated | Datetime | False |
The time the estimate was last updated. (writable on create only) |
DisplayStatus | String | False |
Description of status shown in FreshBooks UI. Either draft, sent, or viewed. (writable on create only) |
ReplyStatus | String | False |
Description of status shown in Classic FreshBooks UI. Either replied or resolved. (deprecated) |
EstimateNumber | String | False |
User-specified and visible estimate Id. |
CustomerId | Integer | False |
Unique identifier of client. |
CreateDate | Date | False |
The date the estimate was created. |
DiscountValue | Decimal | False |
Decimal-string amount of discount. |
PONumber | String | False |
Post Office box number for address on estimate. |
Template | String | False |
Choice of rendering style. (internal, deprecated) |
CurrencyCode | String | False |
Three-letter currency code for estimate. |
Language | String | False |
Two-letter language code. |
Terms | String | False |
Terms listed on estimate. |
Notes | String | False |
Notes listed on estimate. |
Address | String | False |
First line of address listed on estimate. |
ExtArchive | Integer | False |
0 or 1 indicating whether estimate is archived or not. |
VisState | Integer | False |
0 indicates active, 1 indicates deleted. |
Street | String | False |
Street for address on estimate. |
Street2 | String | False |
Second line of address on estimate. |
City | String | False |
City of address on estimate. |
Province | String | False |
Province of address on estimate. |
Country | String | False |
Country of address on estimate. |
Organization | String | False |
Name of organization being estimated. |
FirstName | String | False |
First name of client on estimate. |
LastName | String | False |
Last name of client being estimated. |
VatName | String | False |
Value Added Tax name if provided. |
VatNumber | String | False |
Value Added Tax number if provided. |
LinesAggregate | String | False |
An aggregate of lines associated with an estimate. See the help for the Estimates table for more information on inserting and updating to this field. |