|
|
|
Technical Support
|
 |
This section contains descriptions and field lists for record types involved in the sales cycle - including: customers, customer types, jobs, job types, price levels, sales taxes, and customer messages.
Customers and jobs are treated almost identically - jobs are nested under customers and have a few additional fields. There is a strong similarity between customers, vendors, employees, and other names - often referred to as 'entities'. Many of the fields are identical between them.
Customers are companies / people and to which services and products are sold. A current balance (amount of money they currently owe) is maintained for each customer. This record type has lots of field but most of them are 'inert' (don't affect balances or other processing).
Customers and Jobs are treated almost identically in QuickBooks®. Jobs can be either jobs or projects depending on the type of business. Each job belongs to a customer. Jobs can be nested 4 levels deep under a customer. The sEntityType field can be used to distinguish between customers and jobs.
Eight transaction types typically involve a customer or job:
cash sale (CashSale), estimate (Estimate), sales order (SalOrder),
invoice (CustInvc),
statement charge (CustChrg), payment receipt (CustPymt), card refund
(CustRfnd),
credit memo (CustCred)
| Field Name | Type | How Used | Description |
| nKey | Long | PKey | Unique customer or job record identifier |
| sListID | S36 | Req | Unique record identifier within QB file |
| sName | S41 | Req | Short name of this customer or job Does not include colon separators or names of parent jobs or customer. |
| sFullName | S209 | Req | Full name with colon separators - w/ name(s) of parents if any |
| nReportSort | Long | Opt | Sort order used for nested level reporting, To be available in version 23.2.0.0+. |
| nNestLevel | Long | OptDft0 | Depth of nesting. Parent (root) if missing |
| kParent | Long | FKeyOpt | Key of parent in this set of records This does not exist for customers, only for jobs. If top level job, this is the customer's key. If nested job, this is it's parent job's key. There is a maximum of 4 nested levels of jobs under each customer. |
| sEntityType | S8 | Req | Entity type - always "Customer" or "Job" |
| sCompanyName | S41 | Opt | Name of company used on invoices and other forms (box Company Name) |
| sSalutation | S15 | Opt | Salutation (Mr., Mrs., etc. of contact) |
| sFirstName | S25 | Opt | First name of person |
| sMidName | S5 | Opt | Person's middle name or initial |
| sLastName | S25 | Opt | Person's last name |
| sBillAddr1 | S41 | Opt | Billing address line1 |
| sBillAddr2 | S41 | Opt | Billing address line2 |
| sBillAddr3 | S41 | Opt | Billing address line3 |
| sBillAddr4 | S41 | Opt | Billing address line4 |
| sBillAddr5 | S41 | Opt | Billing address line5 - sometimes combines city, state, and zip code |
| sBillCity | S31 | Opt | Billing address - city |
| sBillState | S21 | Opt | Billing address - state |
| sBillPostalCode | S13 | Opt | Billing address - zip or postal code |
| sBillCountry | S31 | Opt | Billing address - country |
| sBillAddrNote | S41 | Opt | Billing address - miscellaneous note |
| sShipAddr1 | S41 | Opt | Shipping address line1 |
| sShipAddr2 | S41 | Opt | Shipping address line2 |
| sShipAddr3 | S41 | Opt | Shipping address line3 |
| sShipAddr4 | S41 | Opt | Shipping address line4 |
| sShipAddr5 | S41 | Opt | Shipping address line5 - sometimes combines city, state, and zip code |
| sShipCity | S31 | Opt | Shipping address - city |
| sShipState | S21 | Opt | Shipping address - state |
| sShipPostalCode | S13 | Opt | Shipping address - zip or postal code |
| sShipCountry | S31 | Opt | Shipping address - country |
| sShipAddrNote | S41 | Opt | Shipping address - miscellaneous note |
| sPhone | S21 | Opt | Main telephone number |
| sPhone2 | S21 | Opt | Second telephone number |
| sPhoneFax | S21 | Opt | Fax telephone number |
| sEmail | S1023 | Opt | Email address or customer |
| sContact | S41 | Opt | Primary contact name |
| sContact2 | S41 | Opt | Secondary contact name |
| kCustType | Long | FKeyOpt | Key of customer type (box Type). Applies to Customer only. |
| kTerms | Long | FKeyOpt | Key of payment terms for payments from this customer (box Terms). Applies to Customer only. |
| kSalesRep | Long | FKeyOpt | Key of sales rep assigned to this account |
| mBalance | Curr | Opt | Current balance owed for this customer or job only by this customer. Not visible on form. |
| mTotalBalance | Curr | Opt | Total balance for customer - including all jobs |
| kSalesTax | Long | FKeyOpt | Sales tax code - indicating if taxable and why |
| kTaxItem | Long | FKeyOpt | Key of sales tax (in inventory items) to use for this customer item for this customer (box Tax Item). Applies to Customer only. |
| sSalesTaxCountry | S9 | Opt | Identifies the country collecting sales taxes for this customer. One of: Australia, Canada, UK. (non US only) |
| sResaleNum | S15 | Opt | State resale certificate number when sales tax is not charged. Equivalent to Canadian sBusinessNum - but both never occur simultaneously. |
| sAccount | S99 | Opt | Customer's account number (not an account in chart of accounts) |
| mCreditLimit | Curr | Opt | Maximum credit limit for this customer. Applies to Customer only. |
| kPayMeth | Long | FKeyOpt | Key of payment method customer normally uses |
| sCardNum | S25 | Opt | Credit card number |
| sCardMonth | Long | Opt | Month credit card expires (such as 02, 11) |
| sCardYear | Long | Opt | Year credit card expires (such as 2009) |
| sCardName | S41 | Opt | Name of credit card |
| sCardAddr | S41 | Opt | Address associated with credit card |
| sCardPostalCode | S41 | Opt | Postal code associated with credit card |
| sJobStatus | S12 | Opt | Current job status (box Job Status) Applies to job only. One of: "Pending", "Awarded", "In progress", "Closed", "Not awarded" |
| dJobStart | Date | Opt | Date job was started or will start (box Start Date) Applies to job only. |
| dJobProjectedEnd | Date | Opt | Date job was last projected to be completed (box Projected End) Applies to job only. |
| dJobActualEnd | Date | Opt | Date job was actually completed (box End Date) Applies to job only. |
| sJobDescr | S99 | Opt | Description of job Applies to job only. |
| kJobType | Long | FKeyOpt | Key of job type Applies to job only. |
| sNotes | Memo | Opt | Memo notes about this customer or job Unlimited length. |
| kPriceLevel | Long | FKeyOpt | Price level for this customer or job |
| sTaxRegNum | S30 | Opt | Tax registration number (non US only) |
| kCurrency | Long | Opt | Key of currency used for this customer |
| nCustomCnt | Long | OptDft0 | Count of custom fields (0 to 7). Each will have a label and field value. Indexes of labels and field values run from 1 to 15 - although not all are used. |
| sCustomLabel1... | S31 | Opt | Custom field label - number corresponds to field |
| sCustomLabel15 | S31 | Opt | Custom field label - number corresponds to field |
| sCustomField1... | S30 | Opt | Custom field value used in customer or job - number corresponds to field |
| sCustomField15 | S30 | Opt | Custom field value used in customer or job - number corresponds to field |
| bInactive | Bool | OptDftF | T if currently inactive (Inactive checkbox is checked) |
Customer types are only used to categorize customers for reporting purposes. They do not affect transaction processing.
Customer types can be nested up to 4 deep. If the kParent field is 0 (does not exist), then it is a root customer type and does not have a parent.
| Field Name | Type | How Used | Description |
| nKey | Long | PKey | Unique customer type record identifier |
| sListID | S36 | Req | Unique record identifier within QuickBooks file. Combination of object ID and creation time, separated by a hyphen. |
| sName | S31 | Req | Short name of customer type Does not include colon separators or names of parent customer type(s). |
| sFullName | S159 | Req | Full name with colon separators - w/ name(s) of parents if any |
| nReportSort | Long | Opt | - |
| kParent | Long | FKeyOpt | Key of parent in this set of records |
| nNestLevel | Long | OptDft0 | Depth of nesting. Parent (root) if missing |
| bInactive | Bool | OptDftF | T if currently inactive (Inactive checkbox is checked) |
Job types are used to categorize jobs for reporting purposes - they do not affect transaction processing.
Job types can be nested up to 4 deep. If the kParent field is 0 (does not exist), then it is a root job type and does not have a parent.
| Field Name | Type | How Used | Description |
| nKey | Long | PKey | Unique record identifier within this record type |
| sListID | S36 | Req | Unique record identifier within QuickBooks file. object ID and creation time, separated by a hyphen. |
| sName | S31 | Req | Short name Does not include colon separators or names of parent job type(s). |
| sFullName | S159 | Req | Full name with colon separators - w/ name(s) of parents if any |
| nReportSort | Long | Opt | Sort order used for nested level reporting, |
| kParent | Long | FKeyOpt | Key of parent in this set of records |
| nNestLevel | Long | OptDft0 | Depth of nesting. Parent (root) if missing |
| bInactive | Bool | OptDftF | T if currently inactive (Inactive checkbox is checked) |
Price levels are for setting custom pricing for different customers or jobs. Price levels are created first - then associated with particular customers or jobs. Whenever a sales type transaction is created with that customer or job - a lookup is done and the correct price is entered for each transaction line.
There are 2 types of price levels - PerItem and FixedPercentage. With PerItem price levels, a hard dollar amount is provided for each item of interest - multiple items are listed. With FixedPercentage, the normal price is adjusted down (or up) as specified and the price level applies to any item.
| Field Name | Type | How Used | Description |
| nKey | Long | PKey | Unique price level record identifier |
| sListID | S36 | Req | Unique record identifier within QuickBooks file. Combination of object ID and creation time, separated by a hyphen. |
| sName | S31 | Req | Name given to price level |
| sPriceLevelType | S15 | Req | Type of price level. Either "PerItem" or "FixedPercentage" |
| rAdjustPercent | Doub | Opt | Positive or negative fixed percentage adjustment to apply to items. Only applies if field sPriceLevelType is FixedPercentage. |
| nGroupCnt | Long | FKeyOpt | Count of items (per item only). This is the occurence count of the kItem and rUnitPrice fields below. The first item / price will be named kItem1 with price rUnitPrice1. Subsequent items will have suffixes of 2, 3, etc. - up to nGroupCnt. |
| kItem... | Long | FKeyOpt | nKey of item in InvtItem that this discount applies to |
| rUnitPrice... | Curr | Opt | Price of item to use as basis for price level adjustment, or percentage |
| kCurrency | Long | FKeyOpt | Key of currency used for this price level |
| bInactive | Bool | Opt | T if this price level is not currently active |
Sales Taxes are taxes that are collected when a sale takes place. The money collected is then remitted to a tax agency on a regular schedule. Note that the main sales tax items and item groups are in the InvtItem table.
Sales tax codes here are used to distinguish taxable from non-taxable sales. For example, some customers and some inventory items are non taxable and an appropriate sales tax code is assigned to them.
For Canadian QuickBooks, this table is also used for purchase and sale tax separation.
| Field Name | Type | How Used | Description |
| nKey | Long | PKey | Unique sales tax record identifier. Note that individual sales tax items and groups of sales tax items are contained in InvtItems. See Intuit help for details. |
| sListID | S36 | Req | Unique record identifier within QuickBooks file |
| sName | S3 | Req | Short name that appears on documents to mark tax status |
| bTaxable | Bool | Opt | T if this tax item is tracking a chargeable sales tax |
| sDescr | S31 | Opt | Description of this sales tax |
| kItemPurchTax | Long | Opt | Tax item for purchases (non US only) |
| kItemSaleTax | Long | Opt | Tax item for sales (non US only) |
| bInactive | Bool | Opt | T if this sales tax is not currently active |
Customer messages are messages that can be reused in different customer transactions. Each message does not have a real name - the name field is the text of the message itself.
They typically are printed at the bottom of sales invoices. They exist primarily to maintain uniformity and speed data entry. They do not affect transaction processing.
| Field Name | Type | How Used | Description |
| nKey | Long | PKey | Unique customer message record identifier |
| sListID | S36 | Req | Unique record identifier within QuickBooks file. Combination of object ID and creation time, separated by a hyphen. |
| sName | S101 | Req | The message |
| bInactive | Bool | OptDftF | T if currently inactive (Inactive checkbox is checked) |