An Alternative to Custom Imports — The JSON Lookup Retrieval

The lookup feature is a commonly used aspect of m-Power where developers wish to pull external appliations into an existing application. Many times developers need to only pull in a specific value from an external application. 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. The JSON Lookup Retrieval can be used instead because the JSON application is only called the first time per session and the resulting dataset is cached for repeated use.

Another limitation of the custom import feature is that imported values are read only. Sometimes you need to import a value and perform calculations on that value. Many convoluted steps would be needed in order to accomplish such a task. A JSON Lookup call can be used in a freemarker variable that can execute calculations from the returned JSON value to other values in the parent application.

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.

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.

This template is shipped with m-Power, however is not registered by default. You will need to register this template to your m-Power development interface. Go to the Admin Menu > System Management > Template Management > Register New Template. The ‘I’ module is for Inquiry/Retrieval. The Application Type # is a unique key for retrievals. Change the value to accommodate for your environment. Make all of the other options match the image below:

Template Registration

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 sequence key will be the uniquely identifying factor for the records. Once built, the output of this application template is formatted in a JSON string:


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

$(Lookup(‘DICTIONARY.IXXXXXs’, key_field, ‘field_name_to_return’)}

  • The first parameter (‘DICTIONARY.IXXXXXs’) is where you call the JSON Lookup Retrieval.
  • The second parameter (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”)}.
  • Note: You may use more than one sequence key to identify the record by including another key field in the second parameter place; (key_field1, key_field2, key_field3, etc).


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.

Retrieval with JSON

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. To implement, simply remove the “$” and the “{” and “}” from your Lookup code. For example:

<#if Lookup('DICTIONARY.IXXXXXs', key_field, 'field_name_to_return') != some_val>	 	 
<#assign x = Lookup('DICTIONARY.IXXXXXs', key_field, 'field_name_to_return')>	 	 
<#assign x = some_val>	 	 

Related Posts:

Leave a Reply

Your email address will not be published. Required fields are marked *