Prerequisites

• You already went through the previous cookbook 2. Add PSP Dependency Level Fallbacks.

• Your CustomCostTable already has a column with "Valid From"/"Valid After" date.

Step 0: Task

Cost changes every month. I want to add validity dates to my PX entries.

I want to look up Cost from a table using Dependency Mapping. On algorithmical level, I need to know the following:

• What makes cost entry valid?

• What is the time of calculation?

Step 1: Prepare Lookup Configuration

Using data from Step 0, "Valid From" requirement will be implemented by:

• Setting up "Custom Filter" which filters out all outdated entries.

• Setting up "Sort By Field" to choose the earliest among valid entries.

Configurable Lookups accept filters in the format of JSON encoded Pricefx Filter.

1. Open Pricefx Studio.

2. Open any logic.

3. Click Groovy Console.

4. Enter:

   api.jsonEncode(Filter.lessOrEqual("ValidAfterColumn", "targetDate"))

5. Click Run.

6. In the PfxResult window click Raw.

7. You will see the encoded filter.

Open

You can put any working filter in the "customFilter" field in LookupConfiguration. However, the above filter is not correct:

• It does not have attributeId but the name of the column.

• "targetDate" would not dynamically resolve into date.

LookupConfiguration supports two tags which will solve this kind of issues.

Let's change:

• "ValidFromColumn" to "<<FIELD_NAME(ValidFromColumn)>>"

• "targetDate" to "<<TARGET_DATE>>"

These tags will be dynamically resolved at runtime.
Before:
{"_constructor":"AdvancedCriteria","criteria":[{"fieldName":"ValidFromColumn","value":"targetDate","operator":"lessOrEqual"}],"operator":"and"}

After:
{"_constructor":"AdvancedCriteria","criteria":[{"fieldName":"<<FIELD_NAME(ValidFromColumn)>>","value":"<<TARGET_DATE>>","operator":"lessOrEqual"}],"operator":"and"}

Put that last filter with tags in the "CustomFilter" field.

Then put -ValidFromColumn in sortByField as shown below. We want sorting from the earliest date to latest.

Open

Fields:

• customFilter – Any filter which will be applied to data lookup. It supports some tags which are dynamically resolved at runtime. Usually utilized to implement validation of entries due at the pricing date.

• sortByField – How data should be sorted on the database level if more than one entry is returned. If you are interested in smallest/biggest/latest, fill in this field.

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.