Power BI Connector for FreshBooks

Build 20.0.7587

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.

Copyright (c) 2020 CData Software, Inc. - All rights reserved.
Build 20.0.7587