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).

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. Any bindings in comments will also show up in the field list of the On-demand app generation link, which may be undesirable.

Other quantity constraints are possible. The following table summarizes the different combinations of selection quantity constraints.

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.

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.

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.