GetCachedValue

Looks up a value from a cached query. The query runs once per report and is stored in memory — subsequent calls use the cached result with no database round-trip. Use this for any denormalized or unpivoted data that multiple rows need to reference.

---

Function Signature

function GetCachedValue(
  QueryName    : String;   // TfrxFDQuery component name on the report
  KeyField     : String;   // field name to search on
  ValueField   : String;   // field name to return
  KeyValue     : Variant;  // value to search for — any type
  ConnDef      : String;   // FDManager connection definition name
  DefaultValue : Variant   // returned if key not found
): Variant;

Parameter	Type	Description
QueryName	String	Exact name of the TfrxFDQuery component on the report (case-sensitive)
KeyField	String	The field to search on — must exist in the query result
ValueField	String	The field whose value is returned when a match is found
KeyValue	Variant	The value to search for — can be String, Integer, Date, etc.
ConnDef	String	FDManager connection definition name (e.g. ‘Sage50_Live’)
DefaultValue	Variant	Returned if no matching row is found

---

How It Works

• On the first call for a given QueryName and ConnDef, the function opens the TfrxFDQuery, copies the results into a memory table, and caches it.
• On every subsequent call it uses the cached memory table — no SQL is executed again.
• The cache is shared across all rows in the report, so even if GetCachedValue is called thousands of times, the database query only runs once.
• The cache is automatically cleared when the report is saved in the designer, ensuring SQL changes take effect on the next run.

---

Setting Up the Query Component

1. In the Articles designer, drop a TfrxFDQuery component onto the report.
2. Give it a clear name — e.g. QTaxFlags, QShippers, QPeriods.
3. Enter the SQL in the SQL property. The query should return two columns: a key column and a value column.
4. Leave the component inactive (Active = False) — GetCachedValue opens it automatically.
5. Save the report before running — the function reads the SQL from the saved report file.

---

Examples

Example 1 — Sales Tax Flags

Sage 50 stores tax authority flags as columns (Taxable1 through Taxable27) in General_AR. The query unpivots them into rows so you can look up whether a given tax authority is taxable.

Query SQL on component QTaxFlags:

SELECT 1 AS TaxNum, Taxable1 AS Taxable FROM General_AR WHERE AcctgModule = 1
UNION ALL SELECT 2,  Taxable2  FROM General_AR WHERE AcctgModule = 1
UNION ALL SELECT 3,  Taxable3  FROM General_AR WHERE AcctgModule = 1
-- continue through 27
UNION ALL SELECT 27, Taxable27 FROM General_AR WHERE AcctgModule = 1

Articles script — OnBeforePrint of detail band:

var V: Variant;
V := GetCachedValue('QTaxFlags', 'TaxNum', 'Taxable',
                     <JrnlRow."TaxAuthorityCode">, 'Sage50_Live', -1);
if V = -1 then
  MemoTax.Text := 'Unknown'
else if Integer(V) <> 0 then
  MemoTax.Text := 'Taxable'
else
  MemoTax.Text := 'Non-Taxable';

---

Example 2 — Shipper Names

Sage 50 stores shipper names as columns (Shipper1 through ShipperN). Unpivot them the same way to get a name from a numeric code.

Query SQL on component QShippers:

SELECT 1 AS ShipCode, Shipper1 AS ShipName FROM General_AR WHERE AcctgModule = 1
UNION ALL SELECT 2, Shipper2 FROM General_AR WHERE AcctgModule = 1
UNION ALL SELECT 3, Shipper3 FROM General_AR WHERE AcctgModule = 1

Articles script:

var V: Variant;
V := GetCachedValue('QShippers', 'ShipCode', 'ShipName',
                     <JrnlRow."ShipCode">, 'Sage50_Live', 'Unknown');
MemoShipper.Text := VarToStr(V);

---

Example 3 — Period End Dates

Look up a fiscal period end date by period number. Returns the date as a Variant — use VarToDateTime to format it.

Query SQL on component QPeriods:

SELECT 1 AS PeriodNum, PeriodEnd1 AS PeriodEnd FROM FiscalYear WHERE Year = :Year
UNION ALL SELECT 2, PeriodEnd2 FROM FiscalYear WHERE Year = :Year
UNION ALL SELECT 3, PeriodEnd3 FROM FiscalYear WHERE Year = :Year

Articles script:

var V: Variant;
V := GetCachedValue('QPeriods', 'PeriodNum', 'PeriodEnd',
                     <JrnlRow."Period">, 'Sage50_Live', Now);
MemoDate.Text := FormatDateTime('mm/dd/yyyy', VarToDateTime(V));

---

Example 4 — String Key Lookup

The key does not have to be numeric. You can look up by any string value — for example a department code.

Query SQL on component QDepartments:

SELECT DeptCode, DeptName FROM Departments

Articles script:

var V: Variant;
V := GetCachedValue('QDepartments', 'DeptCode', 'DeptName',
                     <Employee."DeptCode">, 'Sage50_Live', 'No Department');
MemoDept.Text := VarToStr(V);

---

Choosing a DefaultValue

Use case	Suggested DefaultValue
Numeric flag (0/1)	-1 — clearly not a valid flag value
Name or description	‘Unknown’ or ‘Not Found’
Date	0 or Now
Boolean	False
Amount or quantity	0

---

Important Notes

• Save the report before running. The function reads the SQL from the saved .fr3 file. If you change the SQL and do not save, the old SQL will be used.
• Leave the query component inactive. Set Active = False on the TfrxFDQuery. The function opens and closes it automatically.
• QueryName is case-sensitive. It must exactly match the component name in the Articles designer.
• Multiple connections are supported. The same query component can be used with different ConnDef values — each combination is cached separately.
• The cache is per report run. It starts fresh each time the report runs and is cleared when the report is saved in the designer.
• Key matching is case-insensitive for string keys — ‘fedex’ and ‘FedEx’ will match the same row.

---

Quick Reference

Topic	Detail
Function name	GetCachedValue
Parameters	QueryName, KeyField, ValueField, KeyValue, ConnDef, DefaultValue
Return type	Variant — any type
Query runs	Once per QueryName + ConnDef combination per report run
Query component type	TfrxFDQuery — must be on the report, set inactive
Key match	Case-insensitive for strings
Not found	Returns DefaultValue
Multiple connections	Supported — cached separately per ConnDef