4. ValidFrom and ValidTo
Prerequisites
You already went through the previous cookbook 3. ValidFrom.
Your CustomCost table already has a column with "ValidTo" date.
Step 0: Task
As data sanitation, I want to change my lookups to use lower and upper boundary for time filter. On algorithmical level, I need to know the following:
What is the upper boundary for targetDate for an entry to be valid?
Step 1: Prepare Lookup Configuration
Using data from Step 0, do the following to fulfill the "ValidFrom - ValidTo" requirement:
Set up "Custom Filter" which filters out all outdated entries.
Warn users if multiple entries are found for a given timeframe.
Theoretically, no sorting for this type of lookup is needed – only one entry is expected. However, for consistency between runs in Price Setting Package, we add “-validFrom” sorting anyway. It is up to users if they want this fallback mechanism or not.
We will go through these steps to produce a new custom filter. In Pricefx Studio Sandbox, put the following code:
api.jsonEncode(
Filter.and(
Filter.lessOrEqual("<<FIELD_NAME(ValidFromColumn)>>", "<<TARGET_DATE>>"),
Filter.greaterThan("<<FIELD_NAME(ValidToColumn)>>", "<<TARGET_DATE>>")
)
)
This will produce the below filter. Put that value as customFilter:{"_constructor":"AdvancedCriteria","criteria":[{"fieldName":"<<FIELD_NAME(ValidFromColumn)>>","value":"<<TARGET_DATE>>","operator":"lessOrEqual"},{"fieldName":"<<FIELD_NAME(ValidToColumn)>>","value":"<<TARGET_DATE>>","operator":"greaterThan"}],"operator":"and"}
Then, we need to set up a warning in case more than one entry is in a specific time frame. LookupConfiguration supports multiple types of warnings:
Unexpected errors
Checking entries
Checking values
Let's ignore the last one and configure the first two. Inside ChecksConfig, let's put this data:
{"LOOKUP":"PSP_THROW","MORE_THAN_1":"PSP_WARNING"}
This is a JSON Map. Keys of this map are error codes, values are ENUMs. We defined that in case of:
Unexpected lookup error, throw an exception in a way that PSP does it.
More than 1 entry found, add a warning to PSP warning manager.
Fields:
checksConfig – Defines behavior in case of corrupted data or incorrect lookup configuration.
Step 2: Call Lookup Library
Calling lookup library has not changed from the previous cookbook.
Step 3: Read Results
Reading results has not changed since the previous cookbook.