DataBlox Technical Support - Transaction fields

Detailed Field Listings - Transactions

Transactions are the most complicated part of QuickBooks - but they are also where almost all of the significant detailed data is. A transaction is created and posted whenever something happens in QuickBooks that affects fields in other record type(s). Posting is the activity that updates the other records. Transactions always occur at a particular point in time - although when a transaction 'occurs' can be very different than when its information is entered into QuickBooks.

Most tranactions are created through user menus - although some are created internally by QuickBooks. Most transactions are posted at the time they created - except purchase orders, sales orders, and estimates, which hold information used to create other transactions at a later date.

Each transaction has a header (TranDoc) and 2 or more transaction lines (TranLine). Transaction documents contain header information that applies to the transaction as a whole - but no postable transaction data. Each transaction line typically contains a single dollar amount to be posted to a single account - as well as other fields that may (for example) update inventory item quantities or payment information, etc. - depending on the transaction type. All activity within a single TranDoc is treated as if it occured at the same instant in time. For this reason, TranLinks are also needed to tie together activities that occurred at a later time to the initial event - such as payments later received against an invoice.

Even though QuickBooks uses 27 different types of transactions, internally it stores them all in the same set of records. This is a flexible and powerful technique that allows very powerful database techniques to be used for reporting and extracting. Even though this may seem complex - in reality it is a much simpler way to arrange the data that having it spread across dozens of record types.

Some fields - such as dDate, sTranType, and kDoc exist in every transaction line. Other fields - such as kAcct and mAmount exist about 90% of the time. Many of the fields exist for some transaction types and not for others. A few fields have different meanings in different transaction types.

The data in this section is well worth studying deeply - especially TranLine. If you master TranLine, you can do just about any conceivable query or report from QuickBooks - at any level of detail.

Transaction Documents

Transaction documents contain the 'header' fields for each transaction. These are fields that appear only once in each document - and are not repeated for each line. For example, a sales invoice transaction (type Invoice) will have fields that specify the the customer, address, invoice number, etc. that belong to the customer as a whole.

Transaction lines (TranLine below) are the individual line items typically listed in the lower part of a printed document.

Fields in record type: TranDoc

Field Name	Type	How Used	Description
nKey	Long	PKey	Unique transaction document record identifier. This is always identical to the nKey of first transaction line belonging to this doc. OfficeQ code artificially generates this key (as well as the nKey for for TranLines). The number used for this key is sequential - in the order the transactions occurred.
sTranType	S8	Req	Type of transaction. See list of possible values at beginning of this record section.
dDate	Date	Req	Date of transaction This date will match field dDate in all TranLines belonging to this TranDoc.
sDocNum	S11	Opt	Main document identifier, such as the check number or invoice number as printed on the document.
sDocNum2	S25	Opt	Auxiliary document number, such as customer's PO number or check number.
sAddr1	S41	Opt	Main address line1 This line and the other address lines are typically copied from the customer or vendor record when the document is created - although they can be modified.
sAddr2	S41	Opt	Main address line2
sAddr3	S41	Opt	Main address line3
sAddr4	S41	Opt	Main address line4
sAddr5	S41	Opt	Main address line5
sCity	S31	Opt	Main address city
sState	S21	Opt	Main address state
sPostalCode	S13	Opt	Main address zip or postal code
sCountry	S31	Opt	Main address country. Often blank.
sAddrNote	S41	Opt	Main address note
sCompAddr1	S41	Opt	Shipping address line1
sCompAddr2	S41	Opt	Shipping address line2
sCompAddr3	S41	Opt	Shipping address line3
sCompAddr4	S41	Opt	Shipping address line4
sCompAddr5	S41	Opt	Shipping address line5
sCompCity	S31	Opt	Shipping address city
sCompState	S21	Opt	Shipping address state
sCompPostalCode	S13	Opt	Shipping address postal code
sCompCountry	S31	Opt	Shipping address country
sCompAddrNote	S41	Opt	Shipping address note
kCurrency	Long	FKeyOpt	Key of foreign currency used for this document. If non existent, home currency was used.
kTerms	Long	FKeyOpt	Key of payment terms. If this exists, it will always exactly match the nKey field of a record in table Terms.
dShip	Date	Opt	Date that product was or will be shipped
kShipMeth	Long	FKeyOpt	Key of shipping method used. If this exists, it will always exactly match the nKey field of a record in table ShipVia.
nTranLineCnt	Long	Req	Total count of tran lines for this doc. The first TranLine of each document typically contains fields that are an extension of the document itself. Often, more than 1 TranLine is generated for each line visible in the user interface because of different postings involved.
kFirstTran	Long	FKeyReq	Key of first tran line for this doc. The first transaction line usually has a different set of fields than subsequent transactions - because it contains the totals. This will always exactly match the nKey field of a record in table TranLine.
kFirstLink	Long	FKeyOpt	Key of first link record belonging to this doc. If there are no TranLinks for this doc (usually true), this field will not exist.
sFob	S13	Opt	Shipping location for which customer pays shipping charges. Freeform text.
kSalesRep	Long	FKeyOpt	Key of sales rep responsible for this sale
kCustMessage	Long	FKeyOpt	Key of customer message used in this doc. If this exists, it will exactly match the nKey field of a record in table CustMessage.
sDocMessage	S99	Opt	Message to vendor or customer. Free form text.
sFlags	S16	Opt	Individual characters representing less important characteristics of this TranDoc. Case sensitive. Can contain up to 16 of the following characters - not in any specific order. C (PurchOrd, SalOrder) - document was manually closed E - (many tran types) - document is waiting to be emailed F - (CustChrg, CustInvc, VendBill) - all payments paid in full H - (Journal) - dollars amounts were entered in the home currency I - (SalOrder) - all line items have been invoiced J - (Journal) - journal entry is an adjustment (orig info retained) P - (many tran types) - document is waiting to be printed R - (PurchOrd) - all line items have been fully received T - (many tran types) - tax in included (non U.S. only) U - (Journal) - journal entry is a home currency adjustment V - (Estimate) - estimate is active W - (many tran types) - transaction pending (has not occurred) Z - (CustInvc) - invoice contains a finance charge item
nFormTemplate	Long	OptDft0	ID of form template used. The actual form templates are not exported - but this can indicate that a customized form is being used. Users can delete and rearrange positions of fields and enable custom fields. There are some fields in setup dialogs that the user can enable that are not enabled by default. The data for these fields is always in sCustomField... fields.
sCustomLabel1...	S31	Opt	1st user customizable field label
sCustomLabel8	S31	Opt	8th user customizable field label
sCustomField1...	S30	Opt	1st user customizable field data value
sCustomField8	S30	Opt	8th user customizable field
nOrigKey	Long	Req	Original key (TRNSID) of this TranDoc as stored in the QB file. This is mainly of interest to IIF developers. Possibly useful for modifying existing transactions.
sTxnNumber	Long	Opt	An internally generated identifying number for this transaction. Possibly useful for modifying existing transactions.
sTxnDocID	S36	Req	Unique string transaction document ID generated internally by QuickBooks. . Possibly useful for modifying existing transactions.

Contents of this page
OfficeQ Overview

Transaction Lines

Transaction lines are the individual line items for each of document. Most transaction lines are used to post a dollar amount to a specific account. It could also add or subtract inventory quantities to what's currently in stock, on order, etc. or update A/R, A/P, or payroll information.

Transaction lines also exist that do not appear in the user interface. All documents include a 'main' transaction line that does not correspond to a visible line item in the document.

Most transactions use only a small subset of the fields listed below. Some fields (nKey, dDate, sTranType, kDoc) exist in every transaction. Some (kAcct, mAmount) almost always exist - but there may be exceptions. Other fields (bToSend, bItemValueAdjust, mWageBase, mYearToDate, ...) may be used in only 1 or 2 types of transactions. A typical transaction will have 7 to 10 fields.

Fields in record type: TranLine

Field Name	Type	How Used	Description
nKey	Long	PKey	Unique transaction line record identifier.
dDate	Date	Req	Date on which transaction occurred This will always be identical for the transaction document and all transaction lines that belong to it. For multiple transactions occuring on the same day, precise order can be determined using the nKey field.
sTranType	S8	Req	Type of transaction. See list of possible values at beginning of TranDoc record section.
kDoc	Long	FKeyReq	Tran doc this tran line belongs to. QuickBooks guarantees that the total of all amounts for all transaction lines with the same kDoc number is $0.00. It is impossible for the user to enter multiple transaction lines that do not balance.
nLine	Long	Req	Relative line number of this line in doc. Line 0 (same as bDocMain) typically has a different set of fields than the other lines because it contains composite data for the entire document. These line numbers don't always correspond directly to lines in the printed document, although they are in the same order.
kNextTran	Long	FKeyOpt	Key of the transaction that follows this. If this field doesn't exist, this is the last transaction in the chain.
sDocNum	S11	Opt	Document identifier (check #, invoice #, etc.). Not necessarily unique.
sLineRoles	S16	Opt	Special field containing single characters indicating 'roles' played by this line - such as sales tax, group item, asset posting, cost of goods posting, etc. This field is new as of January 2010 and will be augmented and documented in the future.
kAcct	Long	FKeyOpt	Key of main account to be debited or credited. The account is not debited or credited if flag bNoPost is true. Note that kAcct is optional - it is possible to have line items (such as comments) that do not post to any account.
sAcctType	S12	Opt	Type of account for kAcct One of: "Bank", "AcctRec", "OthCurrAsset", "FixedAsset", "OthAsset", "AcctPay", "CredCard", "OthCurrLiab", "LongTermLiab", "Equity", "Income", "COGS", "Expense", "OthIncome", "OthExpense", "NonPosting".
mAmount	Curr	Opt	Dollar amount of tran line. Negative if account is credited - regardless of the type of the account. This amount is always in the home currency, even if this transaction occured using a foreign currency.
mAmountFC	Curr	Opt	Dollar amount of tran line in foreign currency. Otherwise same as field mAmount.
rExchangeRate	Doub	Opt	Exchange rate. Only exists if this transaction occurred in a foreign currency and the exchange rate was other than 1.0. This field uses as most 6 significant digits. Example values: .000056, .929282, 1.66295, 7.85546.
kCurrency	Long	FKeyOpt	Key of foreign currency used for the TranDoc this line belongs to. If non existent, home currency was used.
bNoPost	Bool	OptDftF	T if not to be posted. Always applies to PurchOrd, Estimate. Can also apply to Invoice, CashSale, or CustCred if marked as pending. Whenever this field occurs, it is propagated across all TranLines of the containing TranDoc.
kEntity	Long	FKeyOpt	Key of associated Customer, Job, Vendor, Employee, or OtherName
sEntityType	S9	Opt	Type of kEntity. One of CustJob, Vendor, Employee, OtherName.
kEntity2	Long	FKeyOpt	Key of primary document entity from main doc tran line. This is often useful to know which other CustJob, Vendor, Employee, or OtherName is involved. For example, in the TranLine indicating the sales tax in a CustInvc, kEntity will be the vendor to whom the sales tax is paid - while kEntity2 will be the customer. Often, this is the same as kEntity - indicating that no other entity was involved.
sEntity2Type	S9	Opt	Type of kEntity2 One of CustJob, Vendor, Employee, OtherName. ...exact meaning...
kItem	Long	FKeyOpt	Key of inventory item. Note that this could be an item of any type - including sales tax, group, etc.
sItemType	S11	Opt	Type of kItem. One of "Service", "InvtPart", "NonInvtPart", "OthCharge", "Subtotal", "Group", "Discount", "Payment", "TaxItem", or "TaxGroup".
rItemCount	Doub	Opt	Count of items. For main transaction lines (bDocMain = T), this is the total item count for all line transactions in the document.
kUnitOfMeas	Long	Opt	Key of unit of measure set used for rItemCount
sMeasSubUnit	S31	Opt	Name of the unit within the UnitOfMeas set used for this transaction.
rUnitPrice	Doub	Opt	Either wholesale cost or retail price per unit - depending on transaction type. For purchases, this is the wholesale cost. For sales / invoices, this is the retail price. For some item types (such as TaxItem), this can be a percentage. Percentage amounts are exported with a "%" character as the rightmost character of the value. When importing into a database - you should check for this character and deal with it appropriately. One technique is to have a separate boolean field that indicates if the amount is a percent.
rUnitPriceFC	Doub	Opt	Foreign currency equivalent of rUnitPrice
kClass	Long	FKeyOpt	Key of associated class. Usually used to divide tranaction lines by company division, customer type, etc.
kPayMeth	Long	FKeyOpt	Key of customer payment method
bAutoTax	Bool	OptDftF	T if this is an automatically generated sales tax item - using the rate for the tax item at the time generated. If the transaction line in which this occurs has no mAmount field - the sales tax was not charged and this line should be ignored.
kSalesTax	Long	FKeyOpt	For TranLines containing items that are taxable, this is the key of the SalesTax code used. There will be one or more separate TranLines containing the sales tax amount for all items in the document.
kCustJob	Long	FKeyOpt	Key of secondary customer or job this TranLine is associated with - such as for pass through billing.
rCountMisc	Doub	Opt	Miscellaneous count. Meaning depends on transaction type. If there are multiple items (rItemCount > 0), and only some have been received or sent - this is the count that has been 'processed' or not 'processed'. For example, on a PurchOrd main line, it's the total item count received so far. On other than a main line, it's the count of that item that has not yet been received. On a SalOrder - it's the count of lines or items invoiced, etc. Note that this field will not exist if it's value is 0. This is consistent with other zero or false values in the API.
kPayItem	Long	FKeyOpt	Key of payroll item used in calculation. Payroll only.
sPayItemType	S13	Opt	Type of pay item for kPayItem. One of ...
dPayThrough	Date	Opt	The final date of the time period covered by this TranLine. "Date paid through".
mWageBase	Curr	Opt	Dollar amount of base gross wages or salary used for calculations in this TranLine.
mPayTaxable	Curr	Opt	Dollar amount of taxable pay used for for calculations in this TranLine.
sMemo	Memo	Opt	Memo text. This can be either data the user entered, or data entered by QuickBooks. If entered by QuickBooks, matching the exact text in code can be useful for determining why the transaction was created - such as for opening balances or condensed transactions.
sCustomField1...	S30	Opt	1st user defined string field
sCustomField7	S30	Opt	7th user defined string field
sFlags	S16	Opt	Individual characters representing less important fields for this TranLine. Case sensitive. Currently used: V - (InvAdjst) - mAmount is $ only (not count)
bDocMain	Bool	OptDftF	Main transaction line for document. This line may have a different set of fields. It acts as an extension of the document and often has totals from other lines - depending on the type of transaction.
mSumIncome	Curr	-	Currency total of all income type account postings for this TranDoc. Useful for gross profit calculations - especially with database queries. Only found in main TranLine (nLine = 0, bDocMain = T).
mSumExpense	Curr	-	Currency total of all expense type account postings for this TranDoc. Useful for gross profit calculations - especially with database queries. Only found in main TranLine (nLine = 0, bDocMain = T).
dDue	Date	Opt	Exact meaning varies with tran type - but generally the date due or next action anticipated. Used mainly with bills and invoices. Only found in main TranLine (nLine = 0, bDocMain = T).
mBalanceDue	Curr	Opt	Initial balance due in home currency. This is the balance due immediately after the document was created. Types CustInvc, CustCred, CustChrg, and VendBill only. Only found in main TranLine (nLine = 0, bDocMain = T).
mBalanceDueFC	Curr	Opt	Same as mBalanceDue - but in foreign currency.
mStillOwing	Curr	Opt	Amount of mBalanceDue that's still unpaid. Types CustInvc, CustCred, CustChrg, and VendBill only. Only found in main TranLine (nLine = 0, bDocMain = T).
dPaidInFull	Date	Opt	If document fully paid - the date of the last transaction that paid it off. Only found in main TranLine (nLine = 0, bDocMain = T).
sBillableStatus	S13	Opt	Status if this line item is billable back to a customer. Possible values are: NotBillable, Billable, HasBeenBilled.
dService	Date	Opt	Date manually entered for when service was performed. Is not significant for any other function.
nOrigKey	Long	Req	Original key (reference) from this record in the QuickBooks file. This field was added in case it's useful in IIF import files in the future.
sTxnDocID	S36	Opt	Unique string transaction document ID for the TranDoc this TranLine belongs to, generated internally by QuickBooks. Possibly useful for modifying existing transactions.
sTxnLineID	S36	Opt	Unique string transaction line ID generated internally by QuickBooks. Possibly useful for modifying existing transactions.

Contents of this page
OfficeQ Overview

Transaction Links

Transaction links are used where multiple transactions have information that must be passed from one to the other - such as an invoice (CustInvc) to the payments on that invoice (CustPymt).

They are simply links that link one TranDoc to another with a date and (typically) a dollar amount or parts count. For example, a single invoice (CustInvc) could have several payments applied to it that are in several payment receipts (CustPymt) that occurred on different days.

Fields in record type: TranLine

Field Name	Type	How Used	Description
nKey	Long	PKey	Unique transaction link record identifier identifier. Sequential.
sTranTypeFrom	S8	Req	Type of transaction the link is from. For example, in the case of a customer making a payment on a previously created invoice (the most common case where links are used) - the payment (CustPymt) transaction will have a TranLink where sTranTypeFrom = 'CustPymt' and sTranTypeTo = 'CustInvc'. The invoice will have a complementary TranLink with the opposite field values.
kDocFrom	Long	Req	Numeric document identifier of the From document
sTranTypeTo	S8	Req	Type of transaction the link is to.
kDocTo	Long	Req	Numeric document identifier of the To document
dDate	Date	Req	Date of TranLine generating the link occurred
sLinkType	S4	Req	Type of quantity the link is for. Either Amt (dollar amount) or Quan or Quan (count of parts). In many files, Quan (count of parts) type links will not have an mAmount field. This appears to be a bug in QuickBooks.
mAmount	Doub	Opt	Link home currency amount or count of parts.
mAmountFC	Doub	Opt	Link foreign currency amount (if file uses multiple currencies).
mBalance	Curr	Opt	Remaining balance (after subtracting mAmount) to be dealt with in another document - in home currency.
mBalanceFC	Curr	Opt	Remaining balance in foreign currency (if file uses multiple currencies)
kNextLink	Long	Opt	Next link - if there are more TranLinks for this doc

Contents of this page
OfficeQ Overview

Tran Type	QuickBooks Long Name	Keyword(s)	Description
Journal	Journal	-----	Debit or credit entry (general journal). Accessed through the Company / Make Journal Entries... menu or the register of any asset, liability, or equity type account. Journal entries are the simplest type of transactions in that they only involve dates, accounts, and dollar amounts. All of the other types of transactions use the same fields as journal transactions - but they add other fields with data specific to that type of transaction.
Transfer	Transfer	-----	Transfer from one account to another. QuickBooks also uses this type of transaction importing data from Quicken.
Check	Check	Cash- Inventory+	Check issued by this company - drawn on a bank account. Accessed through the Banking / Write Checks menu or the register of a bank type account. Can also include receipt of inventory items.
Deposit	Deposit	Cash+	Deposit to a bank account. Accessed through the Banking / Make Deposits menu or the register of a bank type account.
CashSale	Cash Sale	Customer Cash+ Inventory-	Cash sale (receipt). Accessed through the Customer / Enter Sales Receipts menu.
Estimate	Estimate	Customer NonPost	Estimate for a customer. Accessed through the Customers / Create Estimates menu. Dollar amounts and inventory quantities in estimates are never posted to accounts. Later, if and when the estimate is turned into a sale - the appropriate amounts will be posted.
SalOrder	Sales Order	Customer NonPost	Sales Order ... Accessed through the Customers / Create Sales Orders menu. Sales Orders are similar to Estimates. Although they represent a commitment from the customer to buy - they are still non posting until an invoice or cash sale transaction is created.
CustInvc	Invoice	ARCycle+ Customer Inventory-	Invoice to a customer. Accessed through the Customers / Create Invoices menu. Invoices are the most complex type of transaction. Several transaction line fields (see TranLine below) are only used for invoices.
CustChrg	Statement Charge	ARCycle+ Customer	Statement charge (usually interest) billed to a customer. Accessed through the Customers / Assess Finance Charges menu or the register of the customer or the accounts receivable register.
CustPymt	Payment	ARCycle- Cash+ Customer	Payment from a customer. Accessed through the Customers / Receive Payments menu.
CustCred	Credit Memo	ARCycle- Customer Inventory+	Credit memo for a customer. Accessed through the Customers / Create Credit Memos/Refunds menu.
CustRfnd	Credit Card Refund	ARCycle- Customer Inventory+	AR refund that was initially paid by credit card. This is a special type of transaction because of how it is handles by credit card processors. Accesses through the Customers / Create Credit Memo/Refunds menu.
Assembly	Build Assembly	Inventory	Accessed through the Vendors / Inventory Activities / Build Assemblies menu.
InvAdjst	Inventory Adjustment	Inventory	Inventory adjustment. Accessed through the Vendors / Inventory Activities / Adjust Qty/Value on Hand menu.
PurchOrd	Purchase Order	NonPost Vendor	Purchase Order to a vendor. Accessed through the Vendors / Create Purchase Orders menu. Dollar amounts and inventory quantities in purchase orders are never posted to accounts. Later, if and when the items in the purchase order are received or billed - the appropriate amounts will be posted.
ItemRcpt	Item Receipt	Inventory+ Vendor	Receipt of an item that has been ordered - where no bill has been received from the vendor yet. Accessed through the Vendors / Receive Items menu. After the associated bill is later entered, the type of this transaction will change to VendBill.
VendBill	Bill	APCycle+ Inventory+ Vendor	Bill from a vendor. Accessed through the Vendors / Enter Bills menu or the register of the accounts payable type account.
VendPymt	Bill Payment	APCycle- Cash- Vendor	Payment to a vendor by other than credit card. Accessed through the Vendors / Pay Bills menu.
VendCard	Bill CCard	Inventory+ Vendor	Payment to a vendor by credit card. Accessed through the Vendors / Pay Bills menu.
VendCred	Bill Credit	APCycle- Inventory- Vendor	Credit from a vendor. Accessed through the Vendors / Enter Bills menu.
CardChrg	Credit Card (charge)	-----	General credit card charge. Accessed through the Banking / Enter Credit Card Charges menu.
CardRfnd	CCard Refund	-----	General credit card refund or credit. Accessed through the Banking / Enter Credit Card Charges menu.
TaxPymt	Sales Tax Payment	APCycle- Vendor	Sales tax payment. Accessed through the Vendors / Sales Tax / Pay Sales Tax... menu.
Paycheck	Paycheck	Employee Payroll	Payroll check to an employee. Includes separate TranLines for each payroll item (such as salary components and deductions). Accessed through the Employees / Pay Employees... menu.
LiabPymt	Payroll Liability Check	APCycle- Payroll Vendor	Check for payroll taxes or other payroll liability. These are typically checks to government agencies. Accessed through the Employees / Payroll Taxes and Liabilities / ... menus.
YtdAdjst	YTD Adjustment	Payroll Employee Vendor	Year to Date Payroll Adjustment. Normally created when payroll is set up. QuickBooks also automatically generates entries of this type that are not visible in any QuickBooks reports.
LiaAdjst	Liability Adjustment	Payroll Vendor	Payroll Liability Adjustment. Accessed through the Employees / Payroll Taxes and Liabilities / ... menus. QuickBooks also automatically generates entries if this type that are not visible in any QuickBooks reports.