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. 使用できる値は次のとおりです。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.

デフォルト値はTRUEです。

Power BI Connector for QuickBooks

Customers

Table Specific Information

Select

Columns

Pseudo-Columns