CData Python Connector for FreshBooks

Build 20.0.7587

Invoices

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.

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