Data Analytics Fields and Joins Reference

This page lists the fields and join paths available in Data Analytics for GL Entries, Customers, Vendors, Chart of Accounts, Invoice Lines, and Bill Lines. Data Analytics is the report builder under Reports → Data Analytics that builds reports directly from DualEntry records. For the steps to build, filter, group, and schedule a report, see How to Build a Custom Report with Data Analytics.

How fields appear in the field picker

Each field in the Data Analytics field picker carries a type mark and is added to the report as one column. Every field is optional, and a blank report starts with no columns, so none of the fields below is in a report until you add it. The Type column in the tables on this page uses these values:

Type	Mark in the field picker	What the column holds
Text	abc	Names, codes, numbers used as identifiers, and addresses
Currency	$	An amount in the currency stated in the field description
Number	123	A count, rate, or percentage
Date	date	A calendar date
Status	status	One value from a fixed list, such as Yes and No or Debit and Credit

Status fields that answer a yes-or-no question read Yes or No. Fields marked with a Line badge come from a line record, so a report that uses them returns one row per line. Columns that share a label show the same header, with nothing to mark which record each one comes from. For example, a GL Entries report that joins both a customer and a vendor and adds Country from each shows two Country columns. Use the column order under Columns to keep track of which is which.

GL Entries fields: record and dates

The GL Entries record type returns one row per posted general ledger entry. The fields in this group identify the source record and its dates.

Field	Type	Description
Entry #	Text	The GL entry’s ID
Entry Date	Date	The date of the entry’s transaction
Transaction ID	Text	The ID of the transaction the entry belongs to. Entries from the same transaction share it.
Transaction Date	Date	The transaction date of the source record
Transaction Created Date	Date	The date the transaction was created
Record Type	Status	The type of source record, such as Invoice, Bill, or Journal Entry
Record ID	Text	The ID of the source record
Record No	Text	The source record’s number with its prefix, for example `IN-000123` for an invoice
Memo	Text	The memo on the source record
Line Memo	Text	The memo or description on the source record’s line that produced the entry
Reference Number	Text	The reference number on the source record, for record types that have one
Check Number	Text	The check number on the source record, for record types that have one

GL Entries fields: account and company

These GL Entries fields describe the account and company an entry posts to. To add every field of the account itself, such as its type or parent, join Chart of Accounts (see GL Entries join paths).

Field	Type	Description
Account Code	Text	The code of the account the entry posts to
Entry Account Number	Text	The number of the account the entry posts to
Entry Account	Text	The account the entry posts to, shown as its number and name
Offset Account	Text	The other account on the same transaction. Reads `-split-` when the transaction posts to more than one account.
Entry Direction	Status	Debit or Credit
Entry Company	Text	The company the entry’s transaction belongs to
Entry Base Currency	Text	The base currency of the entry company
Elimination Company	Status	Yes when the entry company is an elimination company

GL Entries fields: amounts and currency

The GL Entries amount fields come in two currencies: the transaction currency the record was entered in, and the base currency of the entry company. Debit and Credit stay in the transaction currency.

Field	Type	Description
Debit	Currency	The debit amount in the transaction currency, or zero for a credit entry
Credit	Currency	The credit amount in the transaction currency, or zero for a debit entry
Transaction Currency	Text	The currency code of the transaction
Transaction FX Rate	Number	The exchange rate from the transaction currency to the entry company’s base currency
Transaction Currency Amount	Currency	The signed amount in the transaction currency
Debit (Base)	Currency	The debit amount in the entry company’s base currency, or zero for a credit entry
Credit (Base)	Currency	The credit amount in the entry company’s base currency, or zero for a debit entry
Amount (Base)	Currency	The signed amount in the entry company’s base currency

Base amounts are the transaction amount multiplied by Transaction FX Rate, rounded to two decimal places. Amount (Base) and Transaction Currency Amount are signed by the account’s normal balance: positive when the entry increases the account’s normal balance and negative when it decreases it. A debit to an expense account is positive, and a debit to a revenue account is negative.

GL Entries fields: customer, vendor, and billable

These GL Entries fields show who an entry relates to and whether its source line is billable to a customer.

Field	Type	Description
Customer/Vendor	Text	The customer or vendor on the source record, as the General Ledger report shows it
Entry Customer Unique ID	Text	The Unique ID of the customer in Customer/Vendor
Line Customer/Vendor	Text	The customer or vendor on the source line that produced the entry, when the line has one
Billable	Status	Yes when the source line is marked billable
Billable Status	Status	Where the billable line is in the billing cycle
Billable Customer	Text	The customer the line is billable to
Billable Project	Text	The project the billable line is assigned to
Billable Invoice	Text	The invoice the billable line was billed on
Billable Source Company	Text	The company that incurred the billable cost
Billable AR Company	Text	The company that invoices the customer for the billable cost
Intercompany Invoice ID	Text	The ID of the intercompany invoice linked to the entry
Intercompany Invoice	Text	The number of the intercompany invoice linked to the entry

GL Entries fields: ship-to and audit

These GL Entries fields carry the source record’s ship-to address and its audit history.

Field	Type	Description
Ship To	Text	The full ship-to address on the source record
Ship To Address Line 1	Text	The first street line of the ship-to address
Ship To Address Line 2	Text	The second street line of the ship-to address
Ship To City	Text	The ship-to city
Ship To State/Province	Text	The ship-to state or province
Ship To ZIP/Postal	Text	The ship-to ZIP or postal code
Ship To Country	Text	The ship-to country
Record Created By	Text	The user who created the source record, or System when DualEntry created it
Record Last Modified Date	Date	The date the source record was last changed
Record Last Approved By	Text	The user who last approved the source record
Record Last Approved Date	Date	The date the source record was last approved

GL Entries custom fields and classifications

GL Entries also list your organization’s custom fields and classifications, so you can report on GL activity by the same dimensions you use on transactions.

Custom fields: Each active custom field in your organization appears as one column named after the custom field. The value comes from the entry’s source record, so the column is empty for entries whose source record type the custom field doesn’t apply to. A custom field that applies only to customers, vendors, or accounts is always empty here. To report on those values, join Customers, Vendors, or Chart of Accounts and use the field from the joined record.
Classifications: Each classification tree, such as Department or Location, appears as one column named after the tree, holding the entry’s value in that tree.

A custom field limited to certain companies appears only for users who can access one of those companies.

GL Entries join paths

Join paths from GL Entries add the fields of a related record to each entry row. Each path below is many-to-1, so it never adds rows. Paths appear under Suggested join paths from GL Entries when you choose Add join.

Join path	What it adds
GL Entry → Account (via account_id)	Every Chart of Accounts field for the account the entry posts to
GL Entry → Company (via company_id)	The Companies fields for the entry company
GL Entry → Customer (via party_customer_id)	Every Customers field for the customer shown in Customer/Vendor
GL Entry → Vendor (via party_vendor_id)	Every Vendors field for the vendor shown in Customer/Vendor
GL Entry → Line Customer (via line_customer_id)	Every Customers field for the customer on the source line
GL Entry → Line Vendor (via line_vendor_id)	Every Vendors field for the vendor on the source line

A report joins each record type once. Once you add GL Entry → Customer, the GL Entry → Line Customer path no longer appears, and the same applies to the two vendor paths. Pick the record-level or line-level path based on which customer or vendor you want to report on.

Customer fields

The Customers record type returns one row per customer, and the same fields are available when you join a customer from another record type. The tables below list the identity and contact fields first, then terms, tax, and company settings.

Field	Type	Description
Customer Name	Text	The customer’s name
Customer #	Text	The customer’s ID
Customer Code	Text	The customer’s code
Customer Unique ID	Text	The customer’s Unique ID
Customer Type	Status	The customer’s type
Customer Status	Status	The customer’s status
Customer Currency	Text	The customer’s default currency
Country	Text	The customer’s country
Customer Email	Text	The customer’s email address
Customer Phone	Text	The customer’s phone number
Customer Website	Text	The customer’s website
Parent Customer	Text	The customer’s parent, for a sub-customer
Customer Has Sub-customers	Status	Yes when other customers have this customer as their parent
Customer Created Date	Date	The date the customer was created
Customer Last Modified Date	Date	The date the customer was last changed

Customer terms, tax, and company fields

These Customers fields hold the defaults DualEntry applies to the customer’s transactions.

Field	Type	Description
Customer Terms	Text	The customer’s default payment terms
Customer Terms Days	Number	The number of days in the customer’s default payment terms
Customer Default AR Account	Text	The customer’s default accounts receivable account
Customer Default AR Account Code	Text	The code of the customer’s default accounts receivable account
Customer Taxable	Status	Yes when the customer is taxable
Customer Default Tax Code	Text	The customer’s default tax code
Customer Tax Registration #	Text	The customer’s tax registration number
Customer Tax Exemption Reason Code	Text	The reason code recorded for a tax-exempt customer
Customer VAT Registration #s	Text	The customer’s VAT registration numbers
Customer GST Registration #s	Text	The customer’s GST registration numbers
Customer Billable Consolidation	Status	How the customer’s billable expenses are grouped on invoices
Customer Default Billable Expense Item	Text	The item used by default for the customer’s billable expenses
Customer Default Billable Markup %	Number	The markup percentage applied by default to the customer’s billable expenses
Customer Subsidiary	Text	The company set as the customer’s subsidiary
Customer Intercompany Company	Text	The company the customer represents in intercompany transactions
Customer Companies	Text	The companies that can use the customer, separated by a vertical bar, or All companies when access isn’t limited

Customer addresses, custom fields, and classifications

Each Customers address is available as one full-address field and as separate parts, for both the billing and the shipping address.

Field	Type	Description
Customer Billing Address	Text	The full billing address
Customer Billing Street	Text	The first street line of the billing address
Customer Billing Line 2	Text	The second street line of the billing address
Customer Billing City	Text	The billing city
Customer Billing State	Text	The billing state or province
Customer Billing Postal Code	Text	The billing ZIP or postal code
Customer Billing Country	Text	The billing country
Customer Shipping Address	Text	The full shipping address
Customer Shipping Street	Text	The first street line of the shipping address
Customer Shipping Line 2	Text	The second street line of the shipping address
Customer Shipping City	Text	The shipping city
Customer Shipping State	Text	The shipping state or province
Customer Shipping Postal Code	Text	The shipping ZIP or postal code
Customer Shipping Country	Text	The shipping country

The customer’s custom fields appear as one column per custom field, and each classification tree appears as one column named after the tree.

Vendor fields

The Vendors record type returns one row per vendor, and the same fields are available when you join a vendor from another record type.

Field	Type	Description
Vendor Name	Text	The vendor’s name
Vendor #	Text	The vendor’s ID
Vendor Code	Text	The vendor’s code
Vendor Unique ID	Text	The vendor’s Unique ID
Vendor Type	Status	The vendor’s type
Vendor Status	Status	The vendor’s status
Country	Text	The vendor’s country
Vendor Email	Text	The vendor’s email address
Vendor Phone	Text	The vendor’s phone number
Vendor Website	Text	The vendor’s website
Vendor Name on Check	Text	The name printed on checks to the vendor
Account # at Vendor	Text	Your organization’s account number with the vendor
Vendor Created Date	Date	The date the vendor was created
Vendor Last Modified Date	Date	The date the vendor was last changed

Vendor payment, tax, and company fields

These Vendors fields hold the defaults DualEntry applies to the vendor’s bills and payments.

Field	Type	Description
Vendor Terms	Text	The vendor’s default payment terms
Vendor Terms Days	Number	The number of days in the vendor’s default payment terms
Vendor Preferred Payment Method	Text	The vendor’s preferred payment method
Vendor Bank Account Type	Status	The type of the vendor’s bank account
Vendor Default AP Account	Text	The vendor’s default accounts payable account
Vendor Default AP Account Code	Text	The code of the vendor’s default accounts payable account
Vendor Default Expense Account	Text	The vendor’s default expense account
Vendor Default Expense Account Code	Text	The code of the vendor’s default expense account
Vendor 1099 Eligible	Status	Yes when the vendor is eligible for 1099 reporting
Vendor TIN Type	Status	The type of the vendor’s taxpayer identification number
Vendor VAT Registration #s	Text	The vendor’s VAT registration numbers
Vendor GST Registration #s	Text	The vendor’s GST registration numbers
Vendor Subsidiary	Text	The company set as the vendor’s subsidiary
Vendor Intercompany Company	Text	The company the vendor represents in intercompany transactions
Vendor Companies	Text	The companies that can use the vendor, separated by a vertical bar, or All companies when access isn’t limited

Vendor address, custom fields, and classifications

The Vendors address is available as one full-address field, as the address entered in a single text box, and as separate parts.

Field	Type	Description
Vendor Address	Text	The full vendor address
Vendor Address (text)	Text	The vendor address as entered in a single text box
Vendor Address Street	Text	The first street line of the vendor address
Vendor Address Line 2	Text	The second street line of the vendor address
Vendor Address City	Text	The vendor’s city
Vendor Address State	Text	The vendor’s state or province
Vendor Address Postal Code	Text	The vendor’s ZIP or postal code
Vendor Address Country	Text	The vendor’s country

The vendor’s custom fields appear as one column per custom field, and each classification tree appears as one column named after the tree. The vendor’s taxpayer identification number and bank account and routing numbers aren’t available in Data Analytics.

Chart of Accounts fields

The Chart of Accounts record type returns one row per account, and the same fields are available when you join an account from GL Entries or Bill Lines, or join an income account from Invoice Lines.

Field	Type	Description
Account Code	Text	The account’s code
Account Name	Text	The account’s name
Account ID	Text	The account’s ID
Account Number	Text	The account’s number
Account Description	Text	The account’s description
Account Type	Text	The account’s type
Account Type Name	Status	The display name of the account’s type
Account Normal Balance	Status	Debit or Credit
Account Financial Statement	Status	Income Statement or Balance Sheet. Empty for statistical accounts.
Account Status	Status	Active or Inactive
Account Currency	Text	The account’s currency
Account Monetary Distinction	Status	Monetary or Non-Monetary
Account EBITDA Category	Status	The account’s category in the EBITDA income statement
Account 1099 Category	Status	The 1099 category the account maps to
System Account	Status	Yes for a system account
Account Sum Duplicate Postings	Status	Yes when the account is set to sum duplicate postings
Account Created Date	Date	The date the account was created
Account Last Modified Date	Date	The date the account was last changed

Account hierarchy, company, and bank fields

These Chart of Accounts fields place the account in the account tree and show its company and bank settings.

Field	Type	Description
Parent Account Code	Text	The code of the account’s parent
Parent Account Name	Text	The name of the account’s parent
Account Path	Text	The account’s full path from the top of the account tree
Top-Level Account	Text	The top-level account above this account
Account Level	Text	The account’s depth in the account tree
Account Company	Text	The company the account belongs to
Account Company Access	Text	The companies that can use the account, separated by a vertical bar, or All companies when access isn’t limited
Linked Financial Account	Text	The name of the financial account linked to the account
Account Bank Alias	Text	The account’s bank alias
Account Next Check Number	Text	The next check number for a bank account

The account’s custom fields appear as one column per custom field. Bank account numbers aren’t available in Data Analytics.

Invoice Lines fields

The Invoice Lines record type returns one row per invoice line, and every field carries the Line badge.

Field	Type	Description
Line #	Text	The invoice line’s ID
Parent Invoice #	Text	The number of the invoice the line belongs to, for example `IN-000123`
Line Position	Number	The line’s position on the invoice
Line Item	Text	The item on the line
Line Item SKU	Text	The item’s SKU
Line Item Code	Text	The item’s code
Line Item Type	Text	The item’s type
Line Memo	Text	The line’s memo
Line Quantity	Number	The quantity on the line
Line Rate	Currency	The unit rate on the line
Line Subtotal	Currency	Quantity times rate, before discounts and tax
Line Discount Type	Status	Percentage or Fixed Amount
Line Discount Value	Number	The discount percentage or amount entered on the line
Line Discount	Currency	The discount amount on the line
Line Allocated Invoice Discount	Currency	The line’s share of a discount applied to the whole invoice
Line Net Amount	Currency	The line amount after line and invoice discounts, before tax
Line Amount	Currency	The line amount after discounts, including tax
Line Taxable	Status	Yes when the line is taxable
Line Tax Code	Text	The tax code on the line
Line Tax Rate	Number	The tax rate on the line
Line Tax	Currency	The tax amount on the line
Line Base Amount	Currency	The line amount in the invoice company’s base currency
Line Income Account	Text	The income account the line posts to
Line Income Account Code	Text	The code of the line’s income account
Line Discount Account	Text	The account the line discount posts to
Line Company	Text	The line’s company, or the invoice’s company when the line has none
Invoice Customer	Text	The customer on the invoice

Amounts on a line for a discount item are negative. Each classification tree also appears as one line-level column named after the tree.

Bill Lines fields

The Bill Lines record type returns one row per bill line, and every field carries the Line badge.

Field	Type	Description
Line #	Text	The bill line’s ID
Parent Bill #	Text	The number of the bill the line belongs to, for example `BI-000123`
Line Position	Number	The line’s position on the bill
Line PO Line #	Text	The ID of the purchase order line linked to the bill line
Line Type	Status	Item or Expense
Line Description	Text	The line’s description
Line Memo	Text	The line’s memo
Line Item	Text	The item on the line
Line Item SKU	Text	The item’s SKU
Line Item Code	Text	The item’s code
Line Quantity	Number	The quantity on the line
Line Rate	Currency	The unit rate on the line
Line Amount	Currency	Quantity times rate, including tax
Line Net Amount	Currency	Quantity times rate, before tax
Line Tax Code	Text	The tax code on the line
Line Tax Rate	Number	The tax rate on the line
Line Tax	Currency	The tax amount on the line
Line Base Amount	Currency	The line amount in the bill company’s base currency
Line Account	Text	The account the line posts to
Line Account Code	Text	The code of the line’s account
Line Account Type	Text	The type of the line’s account
Line Customer	Text	The customer on the line
Line Vendor	Text	The vendor on the line
Line Company	Text	The line’s company, or the bill’s company when the line has none
Bill Vendor	Text	The vendor on the bill

Bill line billable fields

These Bill Lines fields track a bill line that you bill on to a customer.

Field	Type	Description
Line Billable	Status	Yes when the line is marked billable
Line Billable Status	Status	Where the billable line is in the billing cycle
Line Billable Customer	Text	The customer the line is billable to
Line Billable Project	Text	The project the billable line is assigned to
Line Billable Markup %	Number	The markup percentage applied when the line is billed
Line Billable Invoice #	Text	The number of the invoice the line was billed on

Each classification tree also appears as one line-level column named after the tree.

Invoice Lines and Bill Lines join paths

Join paths from Invoice Lines and Bill Lines add the fields of the header invoice or bill and of the records the line or its header points to. Some paths reach the header’s customer, vendor, terms, or company directly, so you don’t need to join the invoice or bill first. Every path in the two tables below is many-to-1, so none of them adds rows.

Invoice Lines join paths

This table lists every join path from Invoice Lines, by its label in the join list.

Join path	What it adds
invoiceLines → invoices	The Invoices fields for the invoice the line belongs to
invoiceLines → Item	The Items fields for the item on the line
Invoice Line → Invoice Customer (via invoice_customer_id)	Every Customers field for the customer on the invoice
Invoice Line → Invoice Term (via invoice_term_id)	The Terms fields for the invoice’s payment terms
Invoice Line → Income Account (via income_account_id)	Every Chart of Accounts field for the line’s income account
Invoice Line → Company (via company_id)	The Companies fields for the company set on the line
Invoice Line → Line or Invoice Company (via effective_company_id)	The Companies fields for the line’s company, or the invoice’s company when the line has none
Invoice Line → Performance Obligation (via performance_obligation_id)	The Performance Obligations fields for the line’s performance obligation

Bill Lines join paths

This table lists every join path from Bill Lines, by its label in the join list.

Join path	What it adds
Bill Line → Bill (via bill_id)	The Bills fields for the bill the line belongs to
Bill Line → Item (via item_id)	The Items fields for the item on the line
Bill Line → Account (via account_id)	Every Chart of Accounts field for the account the line posts to
Bill Line → Customer (via customer_id)	Every Customers field for the customer on the line
Bill Line → Vendor (via vendor_id)	Every Vendors field for the vendor on the line
Bill Line → Bill Vendor (via bill_vendor_id)	Every Vendors field for the vendor on the bill
Bill Line → Bill Term (via bill_term_id)	The Terms fields for the bill’s payment terms
Bill Line → Company (via company_id)	The Companies fields for the company set on the line
Bill Line → Line or Bill Company (via effective_company_id)	The Companies fields for the line’s company, or the bill’s company when the line has none
Bill Line → Purchase Order Line (via purchase_order_line_id)	The Purchase Order Lines fields for the linked purchase order line
Bill Line → Billable Invoice (via billable_invoice_id)	The Invoices fields for the invoice the line was billed on
Bill Line → Billable Customer (via billable_customer_id)	Every Customers field for the customer the line is billable to

A report joins each record type once, so paths that lead to the same record type are alternatives. For example, on Bill Lines you can join Bill Line → Vendor or Bill Line → Bill Vendor, not both.

Header amounts on line reports

When a line report joins its header record, for example Invoices from Invoice Lines, grouped totals count each header amount once per invoice or bill, not once per line. A header total such as the invoice amount therefore doesn’t multiply by the number of lines.

Fields not available in Data Analytics

Some values aren’t offered as fields, either to protect sensitive data or because Data Analytics doesn’t calculate them. The fields on this page don’t include:

A vendor’s taxpayer identification number, bank account number, or routing number
Bank account numbers on accounts
A running balance or beginning balance on GL entries, or an account’s current balance
Item and quantity on GL entries
Custom fields on invoice and bill lines

To report by item and quantity, use Invoice Lines or Bill Lines as the primary record. For balances by account, run the trial balance or another of the standard financial statements.

Get Started

Core Financials

Revenue Recognition

Reporting and Analytics

Platform Configuration

AI and Automation

Integrations

Data Analytics Fields and Joins Reference

How fields appear in the field picker

GL Entries fields: record and dates

GL Entries fields: account and company

GL Entries fields: amounts and currency

GL Entries fields: customer, vendor, and billable

GL Entries fields: ship-to and audit

GL Entries custom fields and classifications

GL Entries join paths

Customer fields

Customer terms, tax, and company fields

Customer addresses, custom fields, and classifications

Vendor fields

Vendor payment, tax, and company fields

Vendor address, custom fields, and classifications

Chart of Accounts fields

Account hierarchy, company, and bank fields

Invoice Lines fields

Bill Lines fields

Bill line billable fields

Invoice Lines and Bill Lines join paths

Invoice Lines join paths

Bill Lines join paths

Header amounts on line reports

Fields not available in Data Analytics

​How fields appear in the field picker

​GL Entries fields: record and dates

​GL Entries fields: account and company

​GL Entries fields: amounts and currency

​GL Entries fields: customer, vendor, and billable

​GL Entries fields: ship-to and audit

​GL Entries custom fields and classifications

​GL Entries join paths

​Customer fields

​Customer terms, tax, and company fields

​Customer addresses, custom fields, and classifications

​Vendor fields

​Vendor payment, tax, and company fields

​Vendor address, custom fields, and classifications

​Chart of Accounts fields

​Account hierarchy, company, and bank fields

​Invoice Lines fields

​Bill Lines fields

​Bill line billable fields

​Invoice Lines and Bill Lines join paths

​Invoice Lines join paths

​Bill Lines join paths

​Header amounts on line reports

​Fields not available in Data Analytics

​Related pages

How fields appear in the field picker

GL Entries fields: record and dates

GL Entries fields: account and company

GL Entries fields: amounts and currency

GL Entries fields: customer, vendor, and billable

GL Entries fields: ship-to and audit

GL Entries custom fields and classifications

GL Entries join paths

Customer fields

Customer terms, tax, and company fields

Customer addresses, custom fields, and classifications

Vendor fields

Vendor payment, tax, and company fields

Vendor address, custom fields, and classifications

Chart of Accounts fields

Account hierarchy, company, and bank fields

Invoice Lines fields

Bill Lines fields

Bill line billable fields

Invoice Lines and Bill Lines join paths

Invoice Lines join paths

Bill Lines join paths

Header amounts on line reports

Fields not available in Data Analytics

Related pages