Field Selection

After selecting a table for an indicator, you need to select the field for calculations. This guide explains field selection in Advanced Spreadsheets and Reporting.

When Field Selection Applies

Field selection is required for these methods:

Sum: Which field to sum
Min: Which field to find minimum
Max: Which field to find maximum
Average: Which field to average
Lookup: Which field to retrieve

Note: Count method doesn't require field selection (it counts records).

Accessing Field Selection

In the Define Indicator page, after setting the table
Click the assist-edit (…) button on the Field Name field
The Select Field page opens
Browse or search for the desired field

Understanding Fields

What is a Field?

Fields are columns within a table:

Customer table has: Name, Address, Balance, etc.
G/L Entry has: Amount, Debit Amount, Credit Amount, etc.
Sales Line has: Quantity, Unit Price, Line Amount, etc.

Field Types

Type	Examples	Usable With
Decimal	Amount, Quantity	Lookup, Sum, Min, Max, Average
Integer / BigInteger	Entry No., Line No.	Lookup, Sum, Min, Max, Average
Date	Posting Date, Due Date	Filter only
Code/Text	No., Description	Filter only
Boolean	Open, Positive	Filter only

The Select Field page shows only Decimal, Integer and BigInteger fields, for every method. Count needs no field at all.

Common Fields by Table

G/L Entry Fields

Field	Purpose	Common Method
Amount	Net amount	Sum
Debit Amount	Positive amounts	Sum
Credit Amount	Negative amounts	Sum
Add.-Currency Amount	Additional reporting currency	Sum

Customer Ledger Entry Fields

Field	Purpose	Common Method
Amount	Original amount	Sum
Remaining Amount	Unpaid balance	Sum
Amount (LCY)	Amount in local currency	Sum

Sales Line Fields

Field	Purpose	Common Method
Quantity	Units ordered	Sum
Outstanding Quantity	Unshipped units	Sum
Line Amount	Extended price	Sum
Unit Price	Price per unit	Average

Item Ledger Entry Fields

Field	Purpose	Common Method
Quantity	Movement quantity	Sum
Remaining Quantity	Available inventory	Sum
Cost Amount (Actual)	Actual cost	Sum
Cost Amount (Expected)	Expected cost	Sum

Selecting the Right Field

For Value Calculations

Financial Values:

Use: Amount fields
Consider: LCY vs. document currency
Example: G/L Entry.Amount

Quantities:

Use: Quantity fields
Consider: Base UOM vs. document UOM
Example: Item Ledger Entry.Quantity

For Balance Calculations

Receivables Balance:

Table: Customer Ledger Entry
Field: Remaining Amount
Filter: Open = Yes

Inventory Balance:

Table: Item Ledger Entry
Field: Remaining Quantity
Filter: (none or by item)

For Average Calculations

Average Order Value:

Table: Sales Header
Field: Amount
Method: Average

Average Days Outstanding:

May require custom calculation
Use a formula row in the row definition

Field Selection Interface

Browsing Fields

The Select Field page shows the numeric fields of the selected table with three columns:

Field: The field caption
Type: Data type (Decimal, Integer or BigInteger)
Number: The field number

Searching Fields

Type part of the field caption in the page search or a column filter
Results filter as you type
Select the matching field and click OK

Field Naming Conventions

Standard Fields

Business Central uses consistent naming:

Amount: Net value
...Amount (LCY): Value in local currency
...Amount (FCY): Value in foreign currency
Remaining...: Unapplied/outstanding portion
...Qty.: Quantity fields

Understanding (LCY) and (FCY)

LCY: Local Currency - your company's base currency
FCY: Foreign Currency - document currency
Use LCY for consistent company-wide reporting
Use document currency for customer-facing reports

Multi-Company Considerations

Currency in Cross-Company

When indicator spans companies:

Consider currency differences
Use LCY fields when possible
Review currency conversion settings

Consistent Field Selection

Ensure field exists in all companies:

Standard fields: always available
Custom fields: verify in each company

Troubleshooting Field Selection

Field Not Found

If expected field doesn't appear:

Verify correct table is selected
Check field name spelling
Confirm field exists in your BC version

Zero or Blank Results

If indicator returns unexpected values:

Verify field contains data
Check field is numeric (for calculations)
Test with known data

Wrong Values

If values are incorrect:

Confirm correct field selected
Check Amount vs. Amount (LCY)
Review for similar-named fields

Best Practices

Field Selection Tips

Use Descriptive Fields: Remaining Amount vs. Amount for open items
Consider Currency: Choose LCY for consistency
Verify Data Type: Ensure field supports chosen method

Validation Steps

Note the field number and caption
Test indicator with simple filter
Verify result against known data
Document field choice for future reference

Performance

Some fields perform better than others:

FlowFields may be slower
Direct data fields are faster
Test with representative data volumes

Example Configurations

Total Revenue

Table: G/L Entry
Field: Amount
Method: Sum
Filter: G/L Account in revenue range

Open Receivables

Table: Customer Ledger Entry
Field: Remaining Amount
Method: Sum
Filter: Open = Yes

Average Order Size

Table: Sales Header
Field: Amount
Method: Average
Filter: Document Type = Order

Inventory Quantity

Table: Item Ledger Entry
Field: Remaining Quantity
Method: Sum
Filter: (by item or location)