|
|
OfficeQ6 (Jan 2010) Transactions |
 |
|
|||||||||||
|
|
Transactions are the most complicated part of QuickBooks - but they are also where
allmost 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 also 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 Types
Transaction Documents (TranDoc)
Transaction Lines (TranLine)
Transaction Links (TranLink)
Back to OfficeQ Overview
|
The following table lists each of the 27 transaction types. Most transactions are entered manually, but some, such as YtdAdjst and LiaAdjst, are only entered automatically by QuickBooks. The specific menu item used to access each type of transaction is also listed - although there are often other ways of accessing transactions through registers that are not listed here. TIP: ... use OfficeQ6 interactively ... Keywords Cash - typically involves receipt(+) or disbursement(-) of cash/check to/from an asset account Inventory - Often contains movement of inventory or service items from InvtItem table APCycle - typically involves adding(+) or removing(-) accounts payables payment obligations ARCycle - typically involves adding(+) or removing(-) accounts receivables payment obligations Customer - typically involves a customer or job. Vendor - typically involves a vendor Employee - always involves an employee Payroll - payroll related NonPost - setup for future transaction which contains postings that are not active yet Infrequent - seldom seen in real world files |
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- |
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 sale (receipt). Accessed through the Customer / Enter Sales Receipts menu. |
| Estimate | Estimate |
Customer |
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 |
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+ |
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+ |
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- |
Payment from a customer. Accessed through the Customers / Receive Payments menu. |
| CustCred | Credit Memo |
ARCycle- |
Credit memo for a customer. Accessed through the Customers / Create Credit Memos/Refunds menu. |
| CustRfnd | Credit Card Refund |
ARCycle- |
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 |
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+ |
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+ |
Bill from a vendor. Accessed through the Vendors / Enter Bills menu or the register of the accounts payable type account. |
| VendPymt | Bill Payment |
APCycle- |
Payment to a vendor by other than credit card. Accessed through the Vendors / Pay Bills menu. |
| VendCard | Bill CCard |
Inventory+ |
Payment to a vendor by credit card. Accessed through the Vendors / Pay Bills menu. |
| VendCred | Bill Credit |
APCycle- |
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- |
Sales tax payment. Accessed through the Vendors / Sales Tax / Pay Sales Tax... menu. |
| Paycheck | Paycheck |
Employee |
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- |
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 |
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 |
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. |
Contents of this page
OfficeQ Overview
|
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 |
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 of 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 fields for this TranDoc. New January 2010 - to be expanded. Case sensitive. Currently used: C (PurchOrd, SalOrder) - document was manually closed E - (many) - bDocMain only - document is waiting to be emailed I - (SalOrder) - all line items have been invoiced P - (many) - document is waiting to be printed R - (PurchOrd) - all line items have been received |
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 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 |
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. |
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. |
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 |
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. |
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 |
dDue |
Date |
Opt |
Exact meaning varies with tran type - but generally the date due or next action anticipated. Used mainly with bills and invoices. Whenever this field occurs, the same value is propagated across all TranLines of the containing TranDoc. |
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. 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. |
mBalanceDue |
Curr |
Opt |
Balance due in home currency. 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. |
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. New January 2010 - to be expanded. Case sensitive. Currently used: V - (InvAdjst) - mAmount is value only adjust - no count adjust |
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 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: TranLink |
Field Name |
Type |
How |
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 |