Binding expressions in on-demand template apps
Data bindings in a template app specify which data from a corresponding selection app is used to formulate the queries issued when loading data into an on-demand app.
The basic form of binding expressions--$(odag_FIELDNAME)--is not the only method available to bind expressions. Other prefixes can be used to refine selections and to ensure that the template app loads data correctly.
Available binding prefixes
The general prefix form is odag[s|o][n][cnt] where:
- s - include only selected values
- o - include only optional values
- n - pick the numeric version, by default unquoted
- cnt - insert the number of values instead of actual values
The following table provides a list of all versions of the binding prefixes available. The examples assume a field named MyField with 1,2,3 as the selected values (green values) and 4 as an optional selected value (white value).
Prefix | Description | Example | Replaced with |
---|---|---|---|
odag_ | Replaced by the selected (green) and optional (white) values. Picks the text version of the values. This is the standard prefix for string values. | $(odag_MyField) | '1','2','3','4' |
odagcnt_ | Replaced by the number of values in the corresponding odag_ binding. This prefix is used for optimization of queries. | $(odagcnt_MyField) | 4 |
odagn_ |
Replaced by the selected (green) and optional (white) values. Picks the numeric version of the values. This is the standard prefix for numeric values. Information note
If the data model is such that there can be no selected or optional values of the field, a noValue must be specified in the expression. For example, $(odagn_MyField){"noValue":"-99999"}. For more information, see Changing the value quotation and delimiter characters. |
$(odagn_MyField) | 1,2,3,4 |
odagncnt_ | Replaced by the number of values in the corresponding odagn_ binding. This is for optimization of queries. | $(odagncnt_MyField) | 4 |
odago_ |
Replaced by the optional (white) values. Picks the text version of the values. This is for optimization of queries. |
$(odago_MyField) | '4' |
odagocnt_ | Replaced by the number of values in the corresponding odago_ binding. This is for optimization of queries. | $(odagocnt_MyField) | 1 |
odagon_ |
Replaced by the optional (white) values. Picks the numeric version of the values. This is for optimization of queries. |
$(odagon_MyField) | 4 |
odagoncnt_ | Replaced by the number of values in the corresponding odagon_ binding. This is for optimization of queries. | $(odagoncnt_MyField) | 1 |
odags_ |
Replaced by the selected (green) values. Picks the text version of the values. This is for optimization of queries. |
$(odags_MyField) | '1','2','3' |
odagscnt_ | Replaced by the number of values in the corresponding odags_ binding. This is for optimization of queries. | $(odagscnt_MyField) | 3 |
odagsn_ |
Replaced by the selected (green) values. Picks the numeric version of the values. This is for optimization of queries. |
$(odagsn_MyField) | 1,2,3 |
odagsncnt_ | Replaced by the number of values in the corresponding odagsn_ binding. This is for optimization of queries. | $(odagsncnt_MyField) | 3 |
Empty values are filtered away in the text versions. Non numeric and NaN values are filtered away in the numeric versions.
Optimizing for large database
The odags_ and odagsn_ prefixes are intended for optimization of queries. When there are no selections in the bound fields, odag_ includes all the values while odags_ includes no values. In some cases, it is more efficient to use the odags_ and odagscnt_ prefixes. This enables you to test if the set of values are empty. For example, the following is more efficient when no selections are made in MyField than testing for all values in odag_MyField:
odags_ cannot be used when there is an alternate field to select from in the selection app that is not an on-demand field. For example, if the user makes selections in CountryName, but the binding expression is on the associated field CountryCode, odags_ could not be used. In these cases, odago_ can be used instead. If there are no values in a odago_ binding, it could either mean that either all values should be included or that no values should be included.
Binding numeric values
When the data to be bound to the on-demand app consists of numbers instead of strings, it is useful to disable the quote wrapping behavior on the numeric fields. For example, if the sales records include a numeric DAY_OF_WEEK column and you want the user of the selection app to choose arbitrary combinations of DAY_OF_WEEK, you would augment the aggregation query used for loading the selection app to include DAY_OF_WEEK in both the SELECT list as well as the GROUP BY list. If quotation marks are wrapped around DAY_OF_WEEK values when they are selected, a runtime query error could result if the database does not support automatic type conversion from string to numeric.
To handle this situation, you can use a numeric version of the binding expression suffix. This forces the field binding to use the numeric values from the selection app rather than string values. The following numeric version are available:
- odagn_
- odagon_
- odagsn_
By using numeric versions, the values are picked up from the numeric part in the duals that store selected values and the values are unquoted by default.
Requiring a certain number of selections
In some situations, it may be necessary to require that the on-demand app query contain a specific number or range of values for a specific field. For example, if the on-demand app's query contains a BETWEEN clause to obtain all sales between a start and end date, the bind expression for the YEARQUARTER field can have a suffix syntax of [2] that will require exactly two values be selected for YEARQUARTER, as in:
The on-demand app navigation point on the selection app will remain disabled as long as there are not exactly two values of YEARQUARTER selected. A message will display to indicate that exactly two values of YEARQUARTER must be selected.
Selection quantity constraints create a prerequisite linkage between the selection app and the on-demand app. This is different from bind expressions that do not use quantity constraints. For example, when the template app's script contains a bind expression without a quantity constraint, as in:
there is no requirement that the selection app contain a field named MYFIELD nor for there to be any selected values of that field if it does exist. If the selection app does not contain a field named MYFIELD or if the user simply neglects to make any selections from it, the on-demand app navigation point can still become enabled when other selections are made to fulfill the record-limit value condition.
If on the other hand, the bind expression is:
there are now two requirements placed on the selection app:
- The selection app must contain a field named MYFIELD.
- The user must select at least one value for MYFIELD.
This type of bind expression must be used carefully because it limits which selection apps can be used with the template app. You should not use this quantity constraint on bindings of a template app unless you are certain you want to impose that selection quantity requirement on all selection apps that link to that template app.
To perform the data binding process, the On-demand app service uses a string substitution approach that is insensitive to comments in the script. This means you should not use bind expressions in comments unless you want those comments to contain the list of bound values following app generation.
Other quantity constraints are possible. The following table summarizes the different combinations of selection quantity constraints.
Constraint pattern | Selection requirement |
---|---|
$(odag_YEARQUARTER)[2] | Exactly 2 values of YEARQUARTER must be selected. |
$(odag_YEARQUARTER)[2-4] | Between 2 and 4 values of YEARQUARTER must be selected. |
$(odag_YEARQUARTER)[2+] | At least 2 values of YEARQUARTER must be selected. |
$(odag_YEARQUARTER)[2-] | At most 2 values of YEARQUARTER can be selected. |
Changing the value quotation and delimiter characters
When a list of values from a field selected in a selection app is inserted into the script of a template app, the values are surrounded by single quotation marks and separated by commas. These are the default characters for quotations and delimiters. These values can be changed in syntax appended to binding statement for each field. For example:
These new values are then used when formulating the list of binding values taken from the selection app. For example, if the selected values are the first three months of the year, the list would be constructed as:
The default values for the quotation and delimiter characters work for most standard SQL databases. But they might not work for some SQL databases and do not work for many dynamic data sources such as NoSQL and REST. For those sources, you need append this binding expression to change the quotation and delimiter characters.
The following table outlines format parameters for changing quotation and delimiter characters.
Parameter | Default value | Definition |
---|---|---|
quote |
' (single quote) for text prefixes empty for numeric prefixes |
Will be added before and after each value |
delimiter | , (comma) | Will be added between all values |
quoteReplace | '' (double single quotes) |
When the value is not empty and the quotation is not empty, then all occurrences of the of the quote inside the values will be replaced by the specified string. Information notequoteReplace is not supported as a parameter for numeric prefixes such as odagn_. quoteReplace is ignored by numeric prefixes.
|
noValue | (empty) |
When there are no values selected for a field, this value will be used instead. This parameter is useful when there can be no values of a particular field in the selection. The value should be set to a value that does not exist in the source data. For numeric values, for example, use a negative value if all values in the database are positive. Information note
For unquoted values, noValue must be specified if selected values of the field can be an empty set.
|
The following tables outline the format specification and generated values for odag_ and odagn_. The generated inserted values are based on the default data values of VAL1, VAL2.
Format specification | Description | Generated inserted values |
---|---|---|
not specified |
Comma separated list of values, quoted with ' . |
'VAL1','VAL2' |
{"quote": "", "delimiter": ""} | Concatenated values | VAL1VAL2 |
{"quote": "X", "delimiter": "Y"} |
Values quoted by X and delimited by Y. | XVAL1XYXVAL2X |
{"quote": "XX", "delimiter": "YY"} | Values quoted by XX and delimited by YY. | XXVAL1XXYYXXVAL2XX |
{"quote": "X"} | Values quoted by X and delimited by , (default). | XVAL1X,XVAL2X |
{"delimiter": "YY"} | Values quoted by ' (default) and delimited by YY | 'VAL1'YY'VAL2' |
{"quote": ""} | Unquoted values delimited by ,. | VAL1,VAL2 |
{"quote": "A", "quoteReplace": "\\A"} |
Values quoted by A and delimited by comma (default). A values inside the field will be replaced by \A. Information note
In this example, there needs to be double \ since \ is the escape character in json format. |
AV\AL1A,AV\AL2A |
Format specification | Description | Generated inserted values |
---|---|---|
not specified |
Comma separated list of unquoted values. Note that the numeric values will be used. |
VAL1,VAL2 |
{"delimiter": "YY"} | Unquoted values delimited by YY | VAL1YYVAL2 |
{"quote": "A", "quoteReplace": "\\A"} | Compared to the odag_ prefix the quoteReplace parameter will be ignored. | AVAL1A,AVAL2A |
Processing individual values
When individual processing of field values is required, you can use an inline method to generate values in the variable Values and perform arbitrary processing with Replace or another function. In the example below, Replace is used with placeholder values.