Power BI Connector for FreshBooks

Build 20.0.7587

Estimates

A list of estimates found in FreshBooks. Staff can only access estimates for their assigned clients.

Table Specific Information

Select

The Classic API allows only a subset of the available columns to be used in the WHERE clause to filter results. The following columns can be used:

  • EstimateId
  • ClientId
  • Folder
  • Date

Note: EstimateId cannot be used at the same time as the other columns.

Other filters are processed client side by the connector. See SupportEnhancedSQL for more information.

Contact Aggregate

You can use the ContactAggregate field to flatten contact items into a single field. This XML aggregate contains additional Ids for contacts beyond the primary client associated with an estimate, and can be inserted and updated directly using the proper XML formatting, as indicated by the example below:

INSERT INTO Estimates (ClientId, ContactAggregate)
VALUES ('123', '<contact><contact_id>456</contact_id></contact><contact><contact_id>789</contact_id></contact>')

Line Aggregate

You can use the LineAggregate field to flatten line items into a single field. This XML aggregate contains all of the line items for an estimate and can be inserted and updated directly using the proper XML formatting, as indicated by the example below:

INSERT INTO Estimates (ClientId, LineAggregate)
VALUES ('123',
'<line><name>Yard Work</name><description>Mowed the lawn.</description>
<unit_cost>10</unit_cost><quantity>4</quantity>
<tax1_name></tax1_name><tax2_name></tax2_name>
<tax1_percent></tax1_percent><tax2_percent></tax2_percent><type></type></line>
<line><name>Giant Rock</name><description>It's a large rock.</description>
<unit_cost>200</unit_cost><quantity>1</quantity>
<tax1_name></tax1_name><tax2_name></tax2_name>
<tax1_percent></tax1_percent><tax2_percent></tax2_percent><type></type></line>')

Note: Updating these fields will remove all previously stored information.

Columns

Name Type ReadOnly Description
EstimateId [KEY] String True

The unique identifier for an estimate.

Number Int True

The number associated with an estimate.

ClientId String False

The Id for a client associated with an estimate.

ContactAggregate String False

An aggregate containing contact information associated with an estimate. See the help file for the Estimates table for more information on inserting and updating to this field.

StaffId String True

The Id for a staff member associated with an estimate.

Organization String False

The organization of the client associated with an estimate.

FirstName String False

The first name of the client associated with an estimate.

LastName String False

The last name of the client associated with an estimate.

PStreet1 String False

The first line of a primary street address for the client associated with an estimate.

PStreet2 String False

The second line of a primary street address for the client associated with an estimate.

PCity String False

The city of the primary address for the client associated with an estimate.

PState String False

The state of the primary address for the client associated with an estimate.

PCountry String False

The country of the primary address for the client associated with an estimate.

PCode String False

The postal/ZIP code of the primary address for the client associated with an estimate.

VatName String False

The VAT name associated with an estimate.

VatNumber String False

The VAT number associated with an estimate.

PONumber String False

The purchase order number associated with an estimate.

Status String False

The status of an estimate.

Folder String True

The folder associated with an estimate.

Amount Decimal True

The amount associated with an estimate.

CurrencyCode String False

The currency code associated with an estimate.

Language String False

The language associated with an estimate.

Date Datetime False

The date of an estimate.

Notes String False

A custom note associated with an estimate.

Terms String False

The terms of an estimate.

Discount Decimal False

A discount value associated with an estimate.

Url String True

The URL associated with an estimate. This field is deprecated in favor of the LinksClientView field below.

AuthUrl String True

The auth URL associated with an estimate. This field is deprecated in favor of the LinksView field below.

LinksClientView String True

Client view for a link to an estimate.

LinksView String True

View for a link to an estimate.

LineAggregate 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