The JSON Lookup Retrieval

Overview

The JSON Lookup is a commonly used template of m-Power where developers wish to pull external applications into an existing application. Many times developers need to only pull in a specific value from an external application.

The JSON Lookup Retrieval is an extremely versatile template that has helped mrc consultants and customers solve many application design and building dilemmas. Thinking of this another way, if you are familiar with Excel documents and VLOOKUPS, this feature is designed to mimic its functionality within m-Power.

Benefits

A JSON Lookup retrieval provides a few benefits, which are described here:

• Often times when m-Power developers want to bring a value from one application into another, they will use a custom import to accomplish this. However, if this value needs to be on all rows of the dataset, the imported application must be executed once for every single row and the database queried every single time as well. Using custom imports for this purpose can cause severe performance issues. Comparatively, the JSON Lookup Retrieval should be used instead because the JSON Lookup Retrieval is only called the first time per session and the resulting dataset is cached for repeated use.
• A value returned from a JSON Lookup call can be stored into a custom Freemarker variable, to then be used for runtime calculations, IF/ELSE blocks, or other display purposes.
• One last benefit of using a JSON Lookup Retrieval are situations where you need to pull data from another table that you cannot join to. For example, a maintainer with a one-to-many join is not ideal when trying to display records. Instead, a JSON lookup can be used to pull data into your parent app without the join.

Implementation

To demonstrate one of the uses of the JSON Lookup Retrieval we will be using a multiple record retrieval listing basic customer information. We also want to bring in the last date the customer placed an order, however we cannot join out to the orders table. Therefore you decide to create a custom import to bring in this date for every row. As mentioned before, using the import method can take a severe performance hit because it must run the imported application for every row separately. Instead, I would recommend using the JSON Lookup.

The build process for the JSON Lookup Retrieval is like any other m-Power application. Select the fields you wish to make available. The dimension key will be the uniquely identifying factor for the records. Once built, the output of this application template is formatted in a JSON string:

"100001":{"CNUM":"100001","ONUM":"301533","OORDERDATE":"2016-07-10"}

To call the JSON Lookup from the parent application, we will add the following Freemarker substitution in the desired cell HTML:

${Lookup("DICTIONARY.IXXXXXs", row.key_field, "field_name_to_return")}

The following notes should be heeded:

• The first parameter DICTIONARY.IXXXXXs is the dictionary and application number of your JSON Lookup Retrieval.
• The second parameter row.key_field is the field to key off from the JSON retrieval and your parent app.
• The third parameter field_name_to_return is the field you wish to pull into your parent app.
• Live example: ${Lookup("MRCMPOWER.I00106s", row.CNUM, "OORDERDATE")}.

You may use more than one sequence key to identify the record by including another key field in the second parameter place:

ex. ${Lookup("MRCMPOWER.I00106s", row.KEY2, row.KEY1, "OORDERDATE")}

When I run the parent app, the JSON lookup is called once and the returning dataset is cached in session to be reused. Specifically, in this case, The entire contents for I00106 is queried and stored in session. Then, for each row, the application takes the value for CNUM and uses this value to lookup the “OORDERDATE” from the dataset in session.

The resulting value is returned to the screen. As I mentioned earlier, the primary advantage here is that of scale. With a JSON lookup, I00106 was queried once. Without it, the application would have to be called 10 separate times via a custom import. Imagine if I had a report with 5,000 records? The JSON app is still only called once, allowing for a substantial increase in performance over a custom import.

Additional Notes & Uses

It is worth repeating that values accessed via JSON Lookup applications can be used in Freemarker logic, such as IF/ELSE statements as well as ASSIGN statements. This is useful when wanting to use a lookup value in calculations or conditional visibility.

To ASSIGN a lookup value into a Freemarker variable, use the following syntax, ensuring to omit the “$” and the “{” and “}” from your Lookup code.

<!--<#assign myVariable = Lookup("DICTIONARY.IXXXXXs", row.key_field, "field_name_to_return")>-->

In the above example, note that even if myVariable returns a numeric value, the variable is considered a string! This is important, as you will not be able to use the value in a runtime calculation until you explicitly cast the result to numeric.

To use a lookup value in a Freemarker IF/ELSE block, use the following syntax, again esuring to omit the “$” and the “{” and “}” from your Lookup code.

<!--<#if Lookup('DICTIONARY.IXXXXXs', row.replace_key_field_name, 'replace_field_name_to_return') == 'replace_with_comparison_value'>-->

<!-- <#else> -->

<!-- </#if> -->