Lookup Rule/Bag method return value

Posted over 5 years ago by Lars Kjaersgaard

Post a topic
Lars Kjaersgaard
Lars Kjaersgaard Admin

Update: This proposal was implemented with Release 2019-02-26, please see Defining a Lookup Rule in Studio for the write-up of the final implementation.

Hopp is proposing to enhance the lookup rules and lookup bag methods to provide richer options for return values and defaults.

Present situation

At present, the options for the return value on a lookup rule or bag method are somewhat limited. The screenshot below shows the present Lookup Method panel in Studio (from the Workshop Target Map of our internal training exercises).

The column to return from the Valueset if a row is found
A default value to be returned
  • if no row is found
  • if a row is found, and the return column is nullable, and the row contains null for the return column. 
If the return column is of type Boolean (or nullable Boolean?) the Default changes from a drop down to radio buttons True/False.
If the return column is of any other type, the Default can be set to either of
  • The Null value (default selection)
  • A Constant value
  • A Literal value
The columns with the Lookup check mark becomes parameters to the Lookup method

MigFx automatically determines the resulting return type of the lookup rule as

  • The type of the return column
  • Nullable if the Default is the Null value, in all other cases non-nullable


  • There is no provision to return true or false simply based on whether a row is found or not.

    Some installations have routinely included a Boolean column set to true on all rows. With this approach it is possible to return true, if a row is found. Likewise, if the column is set to false on all rows, it is possible to return false if a row is found.

    However, this approach is cumbersome and – in the case of static or translation Valuesets – causes unnecessary and confusing data entry. And if the requirement for a Found/Not found rule appears after the Valueset has been defined and populated with data, it can be a major effort to add the necessary Boolean column.

  • The same Default return value is returned with no distinction between these 2 cases
    • No row found
    • Row found, but the nullable return column contains null

  • In case of Not found, the Default options available do not include the possibility to simply return the value of one of the input parameters of the generated Lookup method.  

    If available, this option would provide easy ability to translate an input value, if found by the lookup method - and leave it be if not found.


Hopp proposes to extend the return value functionality of lookup rules and bag methods as follows.

  • In addition to the present option of selecting a Returns column, to add 2 new return options: True if found and False if found
  • To extend the Default to 2 options: Default for Not found and default for Found, but null

The Returns and Default parts of the Lookup Method panel in Studio would change to something along the lines of the sketch below.

The entire Default box is hidden if either radio button True if Found or False if found is checked
The Found, but null default value option is only visible, if the Returns Column is nullable
For each of the 2 Default options, the present selections remain in place - but with an addition, see below.

Additional Default selection

Bearing in mind that the entire Default box is only visible if Returns is Column, the options for the Default selection are extended to provide the possibility to return the value of one of the input parameters in case of Not found.

Referring to the point 3 in the sketch above, the possible selections for each of the 2 Default options (Not found and Found, but null) are sketched below:

The present selection options for Default remain in place
  • True/False if the Returns column is Boolean
  • Otherwise, the Null value, a Constant value or a Literal value
In addition to the present selections a new selection Input is added. If Input is selected, a secondary drop down appears for the selection of one of the columns marked as Lookup.

Note that only lookup columns with the same data type as the Returns column can be selected, though length and scale values do not have to be identical. 
If Input is selected as default, a default value must be provided in case the input parameter to be returned is null. The available selection are the Null value, a Constant value or a Literal value. Obviously the Input selection is not available in this instance.

Note that input parameters are always generated as nullable, regardless whether the corresponding lookup column is nullable or not.
For ease of use, the As above selection is added for the Found, but null default, causing migFx to use the same selection as specified for the Not found default.

As above will be selected for Found, but null by default.

Return value type

First of all, if Returns is True if found or False if found the return value type will be a non-nullable Boolean.

If Returns is Column, the return value type will match the type of the selected column. Nullability will be determined like so:

  • If the Returns column type is nullable
    • Returns Nullable, if either one of the 2 available Default options is 
      • the Null value
      • or Input, and the default for this is null
    • Returns Non-nullable, if the reverse is true (none of the 2 available Default options is the Null value or Input with the Null value as default)
  • If the Returns column type is non-nullable
    • as above, but only considering the Not found default

Impact and refactor

No impact or refactor is expected. The present state of the Studio lookup rules/bag methods will map to

  • Returns: Column is preserved
  • Default Not found equal to the present Default 
  • If Returns column is nullable
    • Default Found, but null set to As above

This will result unaltered behavior.

0 Votes


Login or Sign up to post a comment