Overview
The Invoices dataset is the primary data source for Charts related to Accounts Receivable in CORE. It provides both summary-level and detail-level invoice data, giving you the flexibility to build charts that show high-level AR balances or drill into individual project phases.
Since the dataset operates at two levels simultaneously, understanding how to filter it correctly is essential before building any chart. Used without the correct filter, the dataset will combine invoice-level and phase-level rows, resulting in inflated totals and misleading aging figures.
Dataset Structure
The Invoices dataset is a multi-level dataset. Each invoice can appear as either a single summary row or as multiple detail rows, one per phase on that invoice. The Is Summary field controls which level the dataset returns:
- When Is Summary is set to Yes, the dataset returns one record per invoice.
- When Is Summary is set to No, the dataset returns one record per phase per invoice.
Note: Always apply an Is Summary filter to any chart built on this dataset. If the filter is not set, the chart will display both invoice-level and phase-level records simultaneously, resulting in duplicate amounts and other inaccuracies.
Fields
| Type | Column | Description | Calculation |
|---|---|---|---|
| Category | 0-30 Days (Invoice Date) | Open AR if Invoice Date is 0–30 days before today. | If Days between Invoice Date and Current Date <= 30 THEN Invoice Amount - Amount Paid |
| Category | 121+ Days (Due Date) | Open AR if Due Date is 121+ Days before today. | If Days between Due Date and Current Date > 120 THEN Invoice Amount - Amount Paid |
| Category | 121+ Days (Invoice Date) | Open AR if Invoice Date is 121+ Days before today. | If Days between Invoice Date and Current Date > 120 THEN Invoice Amount - Amount Paid |
| Category | 1-30 Days (Due Date) | Open AR if Due Date is > 0 and <= 30 Days before today. | If Days between Due Date and Current Date > 0 and <= 30 THEN Invoice Amount - Amount Paid |
| Category | 31-60 Days (Due Date) | Open AR if Due Date is > 30 and <= 60 Days before today. | If Days between Due Date and Current Date > 30 and <= 60 THEN Invoice Amount - Amount Paid |
| Category | 31-60 Days (Invoice Date) | Open AR if Invoice Date is > 30 and <= 60 Days before today. | If Days between Invoice Date and Current Date > 30 and <= 60 THEN Invoice Amount - Amount Paid |
| Category | 61-90 Days (Due Date) | Open AR if Due Date is > 60 and <= 90 Days before today. | If Days between Due Date and Current Date > 60 and <= 90 THEN Invoice Amount - Amount Paid |
| Category | 61-90 Days (Invoice Date) | Open AR if Invoice Date is > 30 and <= 60 Days before today. | If Days between Invoice Date and Current Date > 30 and <= 60 THEN Invoice Amount - Amount Paid |
| Category | 91-120 Days (Due Date) | Open AR if Due Date is > 90 and <= 120 Days before today. | If Days between Due Date and Current Date > 90 and <= 120 THEN Invoice Amount - Amount Paid |
| Category | 91-120 Days (Invoice Date) | Open AR if Invoice Date is > 90 and <= 120 Days before today. | If Days between Invoice Date and Current Date > 90 and <= 120 THEN Invoice Amount - Amount Paid |
| Category | Accounting Date | Date this Invoice was recorded to the GL. | |
| Category | Aging Segment - Due Date | Any invoice with outstanding balance; a bucket on how old the AR is calculated from the Due Date of the Invoice. Current, 1-30, 31-60, 61-90, 91-120, 120+ | |
| Category | Aging Segment - Invoice Date | Any invoice with outstanding balance; a bucket on how old the AR is calculated from the Invoice Date. Current, 1-30, 31-60, 61-90, 91-120, 120+ | |
| Category | BaseMaxLastUpdated | MaxLastUpdated of the actual data, not inclusive of deleted data. | |
| Category | Client | ClientID of the client this expense entry will be billed to. | |
| Category | Client Fee Schedule | Fee Schedule ID associated with the Client of this Invoice. | |
| Category | Client First Name | Client's First Name. | |
| Category | Client Last Name | Client's Last Name. | |
| Category | Client Manager | Display name of the employee set as the Client Manager. | |
| Category | Client Memo | Memo on the Client. | |
| Category | Client Middle Initial | Client's Middle Initial. | |
| Category | Client Status | Active or Inactive. | |
| Category | Client Terms | The name of the terms definition associated with this customer. | |
| Category | Client Type | Company or Individual. | |
| Category | Client UID | Unique Identifier of the Client for this invoice. | |
| Category | Client_Id | Unique Identifier of the Client for this invoice. | |
| Category | Company_id_Aging | Unique identifier of the company that this data is for. | |
| Category | Contract Type | Hourly, Fixed, HNTE, Marketing, Overhead, Percentage, Recurring, Recurring With Cap, Recurring Plus Expense, Cost Plus Percentage, Cost Plus Fixed Fee, Recurring Plus Hourly. | |
| Category | Current (Due Date) | Open AR if Due Date is <= 0 Days before today. | If Days between Due Date and Current Date <= 0 THEN Invoice Amount - Amount Paid |
| Category | Default Client Group | GroupID of the group that is setup as the default for the client this time entry was logged against. | |
| Category | Default Project Group | GroupID of the group that is setup as the default for the project/phase this time entry was logged against. | |
| Category | Due Date | The payment due date for this invoice based on the terms. | |
| Category | Income Account | Name of GL Income Account this invoice is associated with. | |
| Category | Invoice Custom Fields | Up to 20 custom fields associated with this invoice. | |
| Category | Invoice Date | The date the invoice was issued. | |
| Category | Invoice Memo 1 | ||
| Category | Invoice Memo 2 | ||
| Category | Invoice Number | The internal tracking number used for this invoice. | |
| Category | Invoice UID | Unique Identifier of the Invoice. | |
| Category | Invoicedetail_ID | Unique identifier of the Invoice Detail. | |
| Category | Is Deleted | true or false. | |
| Category | Is Joint Invoice | Yes or No. | |
| Category | Is Late Fee Invoice | Yes or No. | |
| Category | Is Manual Invoice | Yes or No. | |
| Category | IsDetail | Yes or No. Indicates if this is a header/full invoice or a detail row showing only a specific portion of the invoice. | |
| Category | Latest Payment Date | The date of the last payment applied to this invoice. | |
| Category | MaxLastUpdated | Column used as the max last updated of all related tables. Used for incremental updates. | |
| Category | PO Number | The PO Number associated with the project/phase this invoice is associated with. | |
| Category | Project City | The city portion of the address associated with the project/phase this invoice is for. | |
| Category | Project Class | The accounting class of the address associated with the project this invoice is for. | |
| Category | Project Country | The country portion of the address associated with the project this invoice is for. | |
| Category | Project Currency | The currency associated with the project this invoice is for. | |
| Category | Project Display Name | DisplayName of the project/phase this invoice is for. | |
| Category | Project Due Date | The end date of the project/phase this invoice is for. | |
| Category | Project Fee Schedule | The fee schedule of this project/phase that this invoice is for. | |
| Category | Project Level | The level in the project hierarchy (0-3) where 0 is root project, 1 is first level phase, 2 is second level phase, and 3 is third level phase. | |
| Category | Project Manager | First Name + Last Name of the project manager, or the EmployeeID if name fields are not populated. | |
| Category | Project Name | ProjectName of the project/phase this expense was logged against. | |
| Category | Project Originator | First Name + Last Name of the project originator, or the EmployeeID if name fields are not populated. | |
| Category | Project Principal | First Name + Last Name of the project principal, or the EmployeeID if name fields are not populated. | |
| Category | Project Sort Order | Numeric representation of the project/phase sort sequence string. | |
| Category | Project Start Date | The start date of the project/phase this invoice is for. | |
| Category | Project State | The state portion of the address associated with the project this invoice is for. | |
| Category | Project Status | Active, Completed, Hold, Inactive, Canceled, Draft, Unknown. | |
| Category | Project Terms | The name of the terms definition associated with the project/phase this invoice is for. | |
| Category | Project Type | Standard, Main, Unknown. | |
| Category | Project UID | Unique identifier of the project/phase this invoice is for. | |
| Category | Project Zip | The zip code portion of the address associated with the project this invoice is for. | |
| Category | Root Project Name | DisplayName of the root project for the project/phase this time entry was logged against. | |
| Category | Show Unpaid Only | Yes or null. Flag indicating if the invoice is fully paid. | |
| Category | Sort Sequence | Unique string used to order projects, phases, and subphases based on display name and phase order. | |
| Category | Transaction Date | The transaction date of this invoice. | |
| Category | Type | Invoice or GNRLJRNL. | |
| Category | UniqueID | Unique identifier of the invoice detail. | |
| Value | Aging Segment Sort - Due Date | Numeric order of the Aging Segment buckets. | Current = 1, 1-30 = 2, 31-60 = 3, 61-90 = 4, 91-120 = 5, 130+ = 6 |
| Value | Aging Segment Sort - Invoice Date | Numeric order of the Aging Segment buckets. | Current = 1, 1-30 = 2, 31-60 = 3, 61-90 = 4, 91-120 = 5, 130+ = 6 |
| Value | Balance | Value of outstanding balance. | Invoice Amount - Paid Amount |
| Value | Days Outstanding | Days since the invoice date for any invoice with an outstanding balance. | If Invoice Amount - Paid Amount > 0 THEN Today - Invoice Date in Days |
| Value | Days Past Due | Days since the due date for any invoice with an outstanding balance where due date is in the past. | If Invoice Amount - Paid Amount > 0 AND Due Date < Today THEN Today - Invoice Date in Days |
| Value | Discount | Total value of discount taken on this invoice. | |
| Value | Extra Expenses | The value of the expense portion of the invoice related to expenses flagged as Extra (not Contract). | |
| Value | Extra Services | The value of the services portion of the invoice related to services flagged as Extra (not Contract). | |
| Value | Fixed Fee | The value of the fixed fee portion of the invoice. | |
| Value | Invoice Amount | The total value of the invoice that was billed, includes tax. | |
| Value | MET Amount | ||
| Value | Miscellaneous Adjustment | The amount of adjustment made to this invoice that was not a discount or accounted for in WUD. | |
| Value | MST Amount | ||
| Value | Paid Amount | The total amount of payments that have been applied to this invoice. | |
| Value | Payment Status | Paid On-Time, Paid Late, Partially Paid, Unpaid. | |
| Value | Pre Tax Amount | The total value of the invoice that was billed, exclusive of tax. | |
| Value | Retainage Amount | Amount of retainage held on this invoice. | |
| Value | Retainer Applied | Amount of retainer money applied to this invoice. | |
| Value | Total Invoice Amount |