Lookup Configuration

This guide explains how to configure data lookups in Advanced Spreadsheets and Reporting indicators, including the Lookup method and filter configuration.

Understanding Lookup Configuration

Lookup configuration determines:

Which records to include
What data to retrieve
How to filter results

The Lookup Method

What is Lookup?

The Lookup method retrieves a single value from a table:

Returns the value from the first matching record
Useful for configuration values or specific data points
Different from Sum/Count which aggregate multiple records

When to Use Lookup

Use Lookup For	Don't Use Lookup For
Single value retrieval	Totaling amounts
Configuration settings	Counting records
Specific record data	Aggregate calculations

Lookup Example

Scenario: Get the credit limit for a specific customer

Table: Customer
Field: Credit Limit (LCY)
Method: Lookup
Filter: No. = 'CUST001'

Result: Returns the credit limit value for customer CUST001

Filter Configuration

Filter Types

Indicators support several filter types:

Filter Type	Applies	Purpose
Static	Always	Fixed criteria
Global Dim 1	From the report's Dimension 1 Filter	Department/Cost Center
Global Dim 2	From the report's Dimension 2 Filter	Project/Division
Column Period	From the column	Date-based filtering (Date fields only)

Static Filters

Static filters apply constant criteria. There is no separate operator field; the operator is part of the filter expression entered in the Filter column:

Field: Document Type
Filter: Order

Field: Status
Filter: <>Cancelled

Dimension Filters

Global Dim 1 and Global Dim 2 filters take the financial report's Dimension 1 Filter and Dimension 2 Filter (the filters applied to the row definition lines when the report runs), not anything on the column definition:

Apply the report's Dimension 1 Filter to the selected field
Apply the report's Dimension 2 Filter to the selected field
Enables a single indicator definition to follow the report's dimension filters

Period Filters

Column Period filter applies date range from column:

Posting Date within column period
Due Date within column period
Automatically adjusts to report's date settings

Setting Up Filters

In the Indicator Card

Open indicator configuration
Navigate to Filters section
Add filter entries:
- Select Field
- Set Filter Type
- Enter the Filter expression (for static filters)

Filter Operators

Operator	Meaning	Example
=	Equals	Status = Open
<>	Not equals	Status <> Cancelled
>	Greater than	Amount > 0
<	Less than	Amount < 100
>=	Greater or equal	Quantity >= 10
<=	Less or equal	Amount <= 1000
..	Range	Posting Date = 010124..311224
&#\|;	Or	Status = Open&#\|;Released
&	And	(combined filters)
*	Wildcard	No. = ITEM*

Complex Filter Expressions

Examples:
- "1000..2000" (range)
- "1000|2000|3000" (specific values)
- "<>0" (not zero)
- "01/01/24..12/31/24" (date range; literals are parsed in the session's regional date format)

Client-side tokens such as TODAY and WORKDATE are not resolved in indicator filters and cause an error. Use a Column Period filter for date-relative filtering.

Configuring Lookup Queries

Simple Lookup

Table: General Ledger Setup
Field: Inv. Rounding Precision (LCY)
Method: Lookup
Filter: (none)

Returns: The rounding precision from the setup record

Only Decimal, Integer and BigInteger fields can be selected, so text fields such as a company name cannot be looked up.

Filtered Lookup

Table: Customer
Field: Balance (LCY)
Method: Lookup
Filter: No. = specified customer

Returns: Balance for specific customer

Conditional Lookup

Table: Price List Line
Field: Unit Price
Method: Lookup
Filters:
  - Asset No. = specified item (Static)
  - Source Type = Customer (Static)
  - Starting Date (Column Period)

Advanced Configuration

Multiple Filter Conditions

Combine filters for precise data selection:

Table: Customer Ledger Entry
Field: Remaining Amount
Method: Sum
Filters:
  - Open = Yes (Static)
  - Posting Date (Column Period)
  - Global Dimension 1 Code (Global Dim 1)

Date-Relative Filters

Static filters cannot use TODAY or WORKDATE. For dates that move with the report period use a Column Period filter, optionally with a Date Formula to shift the column's dates.

FlowFilter Fields

Some tables have FlowFilter fields:

Date Filter
Global Dimension 1 Filter
Global Dimension 2 Filter

These affect calculated FlowFields like Balance.

Filter Best Practices

Performance

Add Specific Filters: Narrow results early
Use Indexed Fields: Filter on key fields
Avoid Complex Wildcards: Simple patterns perform better

Accuracy

Test Filters: Verify expected results
Document Logic: Record filter purpose
Consider Edge Cases: Empty results, zero values

Maintainability

Use Dimension Filters: Reuse indicators across dimensions
Use Column Period: Reuse across time periods
Minimize Hardcoded Values: Use static filters sparingly

Common Configuration Patterns

Pattern 1: Period-Based Summary

Configuration:
- Table: G/L Entry
- Method: Sum
- Field: Amount
- Filters:
  - G/L Account No. (Static: account range)
  - Posting Date (Column Period)
  - Global Dimension 1 Code (Global Dim 1)

Pattern 2: Open Items

Configuration:
- Table: Customer Ledger Entry
- Method: Sum
- Field: Remaining Amount
- Filters:
  - Open = Yes (Static)
  - Document Type = Invoice (Static)

Pattern 3: Aging Analysis

Configuration:
- Table: Customer Ledger Entry
- Method: Sum
- Field: Remaining Amount
- Filters:
  - Open = Yes (Static)
  - Due Date < [calculated date] (Static)

Pattern 4: Cross-Company Count

Configuration:
- Table: Sales Header
- Method: Count
- Filters:
  - Document Type = Order (Static)
  - Status = Open|Released (Static)

Row definition line (or column): Company = [Other Company]

The company is not part of the indicator; it is set in the Company field on the row definition line or on the column definition, and the indicator reads from that company.

Troubleshooting Lookups

No Data Returned

Verify filter values match data
Check filter operators
Test with fewer filters

Wrong Data Returned

Review filter logic
Check for multiple matching records
Verify field contains expected data

Performance Issues

Add more specific filters
Use indexed fields for filtering
Review table size

Testing Configuration

Manual Verification

Run report with single row
Compare result to expected value
Verify filters applied correctly

Using the Test Field

The Define Indicator page has a Test Indicator group:

Set the test period and Test Column Layout
Click Calculate Indicator (for test)
Review the value and elapsed time in Test Result