Power BI Connector for QuickBooks

Build 23.0.8839

Customers

Create, update, delete, and query QuickBooks Customers.

Table Specific Information

To add a Customer, you must specify the Name field.

This table has a Custom Fields column. See the Custom Fields page for more information.

Select

By default, SupportEnhancedSQL is set to true, and the following will be honored if present. Other filters will be executed client side. If SupportEnhancedSQL is set to false, only the following filters will be honored.

QuickBooks allows only a small subset of columns to be used in the WHERE clause of a SELECT query. These columns can typically be used with only the equals or = comparison. The available columns for Customers are Id, Name, Balance, IsActive, and TimeModified. TimeModified may be used with the >, >=, <, <=, or = conditions and may be used twice to specify a range. Balance may be used with the >=, <=, or = conditions but cannot be used to specify a range. Name may be used with the = or LIKE conditions to establish a starts-with, ends-with, or contains syntax. For example: 

SELECT * FROM Customers WHERE Name LIKE '%George%' AND TimeModified > '1/1/2011' AND TimeModified < '2/1/2011' AND Balance > 100.00 AND Balance < 200.00

Columns

Name Type ReadOnly References ColumnSize Description
ID [KEY] String True 255

The unique identifier of the customer.

Name String False 41

The name of the customer. This is required to have a value when inserting.

FullName String True 159

The full name of the customer, including parents in the format parent:customer.

Salutation String False 15

A salutation, such as Mr., Mrs., etc.

FirstName String False 25

The first name of the customer as stated in the address info.

MiddleInitial String False 5

A middle name or middle initial of the customer.

LastName String False 25

The last name of the customer as stated in the address info.

AccountNumber String False 99

The account number for the customer.

Company String False 41

The name of the company of the customer.

Balance Double True

The balance owned by this customer including subcustomers.

CustomerBalance Double True

The balance owned by only this customer not including subcustomers.

Contact String False 41

The name of the main contact person for the customer.

Type String False 1000

A predefined customer type within QuickBooks. Typical customer types, if defined, might be Commercial, Residential, etc.

TypeId String False

CustomerTypes.ID

255

A predefined customer type within QuickBooks.

Phone String False 21

The main telephone number for the customer.

Fax String False 21

The fax number number for the customer.

AlternateContact String False 41

The name of an alternate contact person for the customer.

AlternatePhone String False 21

The alternate telephone number for the customer.

Email String False 1023

The email address for communicating with the customer.

Cc String False 1023

A CC email address associated with the customer. Requires QBXML Version 12.0 or higher.

AdditionalContactInfo String False

Additional contact information for the customer.

ContactsAggregate String False 5000

An xml aggregate of the additional contacts associated with the customer. Aggregate must include the contact FirstName. Requires QBXML Version 12.0.

ClassName String False

A reference to the class of a customer. Requires QBXML Version 12.0 or higher.

ClassId String False

A reference to the class of a customer. Requires QBXML Version 12.0 or higher.

Notes String False 5000

The first note for a customer. To retrieve all notes for a customer, use the NotesAggregate column or the CustomerNotes table.

NotesAggregate String False 5000

An xml aggregate of the additional notes associated with the customer. Aggregate must include NoteId and Note elements. Requires QBXML Version 12.0.

ParentName String False

The parent name of the job.

ParentId String False

Customers.ID

255

The parent Id of the job.

Sublevel Integer False

The number of ancestors this customer has.

JobStatus String False 10

The current status of the job.

The allowed values are Awarded, Closed, InProgress, None, NotAwarded, Pending.

JobStartDate Date False

The start date of the job.

JobProjectedEndDate Date False

The expected end date for the job.

JobEndDate Date False

The actual end date for the job.

JobDescription String False 99

A description of the job.

JobType String False 1000

The full name of the job type.

JobTypeId String False

JobTypes.ID

255

A job type reference Id.

CreditCardAddress String False 41

The address associated with the credit card.

CreditCardExpMonth Integer False

The expiration month associated with the credit card.

CreditCardExpYear Integer False

The expiration year associated with the credit card.

CreditCardNameOnCard String False 41

The name as it appears on the credit card of the customer.

CreditCardNumber String False 25

The credit card number on file for this customer.

CreditCardPostalCode String False 41

The postal code associated with the address and number on file for this customer.

CreditLimit Double False

The credit limit for this customer. If it is equal to 0, there is no credit limit.

BillingAddress String True

Full billing address returned by QuickBooks.

BillingLine1 String False 41

First line of the billing address.

BillingLine2 String False 41

Second line of the billing address.

BillingLine3 String False 41

Third line of the billing address.

BillingLine4 String False 41

Fourth line of the billing address.

BillingLine5 String False 41

Fifth line of the billing address.

BillingCity String False 31

City name for the billing address of the customer.

BillingState String False 21

State name for the billing address of the customer.

BillingPostalCode String False 13

Postal code for the billing address of the customer.

BillingCountry String False 31

Country for the billing address of the customer.

BillingNote String False 41

Note for the billing address of the customer.

ShippingAddress String True

Full shipping address returned by QuickBooks.

ShippingLine1 String False 41

First line of the shipping address.

ShippingLine2 String False 41

Second line of the shipping address.

ShippingLine3 String False 41

Third line of the shipping address.

ShippingLine4 String False 41

Fourth line of the shipping address.

ShippingLine5 String False 41

Fifth line of the shipping address.

ShippingCity String False 31

City name for the shipping address of the customer.

ShippingState String False 21

State name for the shipping address of the customer.

ShippingPostalCode String False 13

Postal code for the shipping address of the customer.

ShippingCountry String False 31

Country for the shipping address of the customer.

ShippingNote String False 41

Note for the shipping address of the customer.

ShippingAggregate String False 5000

An XML aggregate of the shipping addresses associated with the customer. Requires QBXML Version 12.0

ResaleNumber String False 16

The resale number of the customer, if he/she has one. This field can be set in inserts but not in updates.

SalesRep String False

SalesReps.ID

5

A reference to a sales rep for the customer.

SalesRepId String False 255

A reference to a sales rep for the customer.

Terms String False 100

A reference to terms of payment for this customer. A typical example might be '2% 10 Net 60'. This field can be set in inserts but not in updates.

TermsId String False 255

A reference to terms of payment for this customer.

CurrencyName String False 64

The name of the currency for this customer. A minimum QBXML Version of 8.0 is required for this feature.

CurrencyId String False

Currency.ID

255

The unique Id of the currency for this customer. A minimum QBXML Version of 8.0 is required for this feature.

TaxCode String False 3

This is a reference to a sales tax code predefined within QuickBooks. This field can be set in inserts but not in updates.

TaxCodeId String False

SalesTaxCodes.ID

255

This is a reference to a sales tax code predefined within QuickBooks. This field can be set in inserts but not in updates.

TaxItem String False 100

A sales tax item refers to a single sales tax that is collected at a specified rate and paid to a single agency.

TaxItemId String False

SalesTaxItems.ID

255

A sales tax item refers to a single sales tax that is collected at a specified rate and paid to a single agency.

SalesTaxCountry String False 100

Identifies the country collecting applicable sales taxes. Only available in international editons of QuickBooks.

PriceLevel String False 100

Reference to a price level for the customer.

PriceLevelId String False

PriceLevels.ID

255

Reference to a price level for the customer.

PreferredDeliveryMethod String False 20

The preferred delivery method for the customer. Enter either: fax, email, or none. This column requires QBXML Version 12.0.

PreferredPaymentMethodName String False 100

The preferred method of payment.

PreferredPaymentMethodId String False

PaymentMethods.ID

255

The preferred method of payment.

IsActive Boolean False

Whether or not the customer is active.

CustomFields String False

Custom fields returned from QuickBooks and formatted into XML.

EditSequence String True 16

An identifier used for versioning for this copy of the object.

TimeModified Datetime True

When the customer was last modified.

TimeCreated Datetime True

When the customer was created.

Pseudo-Columns

Pseudo Column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.

Name Type Description
IncludeJobs Boolean

Whether or not to include job information in the results.

The default value is TRUE.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839