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.
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 the return column is of any other type, the Default can be set to either of
|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
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.
|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|
|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:
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.