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).
Returns
The column to return from the Valueset if a row is found
Default
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
Lookup
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
Limitations
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.
Proposal
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.
1
The entire Default box is hidden if either radio button True if Found or False if found is checked
2
The Found, but null default value option is only visible, if the Returns Column is nullable
3
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:
4
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
5
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.
6
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.
7
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
Lars Kjaersgaard
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).
- 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
MigFx automatically determines the resulting return type of the lookup rule as
Limitations
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.
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.
Proposal
Hopp proposes to extend the return value functionality of lookup rules and bag methods as follows.
The Returns and Default parts of the Lookup Method panel in Studio would change to something along the lines of the sketch 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:
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.
Note that input parameters are always generated as nullable, regardless whether the corresponding lookup column is nullable or not.
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:
Impact and refactor
No impact or refactor is expected. The present state of the Studio lookup rules/bag methods will map to
This will result unaltered behavior.