The COALESCE tag provides extensive capabilities, acting as a first available determination conditional as well as providing the ability to apply multiple formatters to the resulting value. The COALESCE tag can be used for one or more values, and can act as a complete replacement for the FORMAT tag which only provides a single formatter possibility.
The basic concept of COALESCE is simple; iterating through the provided Name and Variable Type values, the system identifies the first value which is NOT EMPTY and assigns that as the source. Then once discovered, it applies the formatters, in order left to right, as prescribed. The structure of the tag consists of Name VariableType pairs, separated by a comma. Optionally, you may use a Name of a column as the value, or, when text is required, a Quoted value can be assigned directly and Text can be used as the Variable Type. Finally, you may optionally append as many formatters as you desire to the COALESCE value (see Format tag).
Whenever developing modules which act as edit forms, and employ AJAX calls to populate items within the form you will require a determination of which value you want to use for your form value. For instance, if you loaded contact information out of the database by a specific ContactID value the determination of whether the [FirstName] column value or the [frmFirstName,Form] form value should be utilized. This comes into question if you are posting back the form when the user selects the Country list to populate the Region selection. In classic ListX you would need to check if the Form element existed in the incoming form postback – if so you would want to use the form value, otherwise you would want to use the column value.
Additionally, you may find times that you want to execute multiple Formatters against a single value. In classic ListX you would need to Nest Format tags into one another to produce the same result as the COALESCE.
Syntax
[COALESCE,Name,VariableType…, FORMAT…]
Example
[COALESCE,frmName,Form,ColumnName,,”Default Value”,Text,{ENCODEHTML},{LEFT:5}]
The following eight examples are repeated three times with different incoming parameters, specified prior to the executions by the Current Settings tag. If the field doesn’t exist in the request, the value is displayed as “Unassigned”. If it is assigned, but it has a zero length value – it is displayed as “Assigned but Empty”. Check the LP area of the website to download this example set.
Column - TabName: Host
Querystring - Tab: Unassigned
Form - frmTabName: Unassigned
Example:[COALESCE,Tab,Querystring,frmTabName,Empty Form,Tabname,,{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form exists in the form (even if it has no content) it is the Source of the COALESCE. Otherwise, go to next element. (This only fails if the form element doesnt exist yet).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Finally, if the Source has been identified, it will be passed through the ENCODEHTML formatter.
Result: Host
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Finally, if the Source has been identified, it will be passed through the ENCODEHTML formatter.
Result: Host
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Finally, if the Source is returned with no formatters.
Result: Host
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Finally, if the Source has been identified, it will be passed through the ENCODEHTML formatter.
Result: Host
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{ENCODEHTML},{LEFT:6}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Next, the Source has been identified, it will be passed through the ENCODEHTML formatter.
6. Finally, the Source will be passed through the LEFT:6 formatter to return the first six characters of the HTML Encoded value.
Result: Host
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{LEFT:6},{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Next, the Source has been identified and will be passed through the LEFT:6 formatter to return the first six characters of the Source.
6. Finally, the First six characters of the original Source will be passed through the ENCODEHTML formatter.
Result: Host
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{LEFT:6},{RIGHT:1}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Next, the Source has been identified and will be passed through the LEFT:6 formatter to return the first six characters of the Source.
6. Finally, the First character of the resulting value will be returned from the RIGHT:1 formatter
Result: t
Example:[COALESCE,Tab,Querystring,{LEFT:6},{RIGHT:3}]
1. The Tab value from the Querystring is the Source of the COALESCE.
2. Next, the Source has been identified and will be passed through the LEFT:6 formatter to return the first six characters of the Source.
3. Finally, the First character of the resulting value will be returned from the RIGHT:1 formatter
Result:
Column - TabName: Host
Querystring - Tab: Unassigned
Form - frmTabName: ABCDEFGHIJKLMNOP
Example:[COALESCE,Tab,Querystring,frmTabName,Empty Form,Tabname,,{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form exists in the form (even if it has no content) it is the Source of the COALESCE. Otherwise, go to next element. (This only fails if the form element doesnt exist yet).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Finally, if the Source has been identified, it will be passed through the ENCODEHTML formatter.
Result: ABCDEFGHIJKLMNOP
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Finally, if the Source has been identified, it will be passed through the ENCODEHTML formatter.
Result: ABCDEFGHIJKLMNOP
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Finally, if the Source is returned with no formatters.
Result: ABCDEFGHIJKLMNOP
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Finally, if the Source has been identified, it will be passed through the ENCODEHTML formatter.
Result: ABCDEFGHIJKLMNOP
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{ENCODEHTML},{LEFT:6}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Next, the Source has been identified, it will be passed through the ENCODEHTML formatter.
6. Finally, the Source will be passed through the LEFT:6 formatter to return the first six characters of the HTML Encoded value.
Result: ABCDEF
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{LEFT:6},{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Next, the Source has been identified and will be passed through the LEFT:6 formatter to return the first six characters of the Source.
6. Finally, the First six characters of the original Source will be passed through the ENCODEHTML formatter.
Result: ABCDEF
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{LEFT:6},{RIGHT:1}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Next, the Source has been identified and will be passed through the LEFT:6 formatter to return the first six characters of the Source.
6. Finally, the First character of the resulting value will be returned from the RIGHT:1 formatter
Result: F
Example:[COALESCE,Tab,Querystring,{LEFT:6},{RIGHT:3}]
1. The Tab value from the Querystring is the Source of the COALESCE.
2. Next, the Source has been identified and will be passed through the LEFT:6 formatter to return the first six characters of the Source.
3. Finally, the First character of the resulting value will be returned from the RIGHT:1 formatter
Result:
Column - TabName: Host
Querystring - Tab: Unassigned
Form - frmTabName:
Example:[COALESCE,Tab,Querystring,frmTabName,Empty Form,Tabname,,{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form exists in the form (even if it has no content) it is the Source of the COALESCE. Otherwise, go to next element. (This only fails if the form element doesnt exist yet).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Finally, if the Source has been identified, it will be passed through the ENCODEHTML formatter.
Result:
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Finally, if the Source has been identified, it will be passed through the ENCODEHTML formatter.
Result: Host
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Finally, if the Source is returned with no formatters.
Result: Host
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Finally, if the Source has been identified, it will be passed through the ENCODEHTML formatter.
Result: Host
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{ENCODEHTML},{LEFT:6}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Next, the Source has been identified, it will be passed through the ENCODEHTML formatter.
6. Finally, the Source will be passed through the LEFT:6 formatter to return the first six characters of the HTML Encoded value.
Result: Host
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{LEFT:6},{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Next, the Source has been identified and will be passed through the LEFT:6 formatter to return the first six characters of the Source.
6. Finally, the First six characters of the original Source will be passed through the ENCODEHTML formatter.
Result: Host
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{LEFT:6},{RIGHT:1}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Next, the Source has been identified and will be passed through the LEFT:6 formatter to return the first six characters of the Source.
6. Finally, the First character of the resulting value will be returned from the RIGHT:1 formatter
Result: t
Example:[COALESCE,Tab,Querystring,{LEFT:6},{RIGHT:3}]
1. The Tab value from the Querystring is the Source of the COALESCE.
2. Next, the Source has been identified and will be passed through the LEFT:6 formatter to return the first six characters of the Source.
3. Finally, the First character of the resulting value will be returned from the RIGHT:1 formatter
Result:
Column - TabName: Host
Querystring - Tab: 1234567890
Form - frmTabName: ABCDEFGHIJKLMNOP
Example:[COALESCE,Tab,Querystring,frmTabName,Empty Form,Tabname,,{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form exists in the form (even if it has no content) it is the Source of the COALESCE. Otherwise, go to next element. (This only fails if the form element doesnt exist yet).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Finally, if the Source has been identified, it will be passed through the ENCODEHTML formatter.
Result: 1234567890
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Finally, if the Source has been identified, it will be passed through the ENCODEHTML formatter.
Result: 1234567890
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Finally, if the Source is returned with no formatters.
Result: 1234567890
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Finally, if the Source has been identified, it will be passed through the ENCODEHTML formatter.
Result: 1234567890
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{ENCODEHTML},{LEFT:6}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Next, the Source has been identified, it will be passed through the ENCODEHTML formatter.
6. Finally, the Source will be passed through the LEFT:6 formatter to return the first six characters of the HTML Encoded value.
Result: 123456
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{LEFT:6},{ENCODEHTML}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Next, the Source has been identified and will be passed through the LEFT:6 formatter to return the first six characters of the Source.
6. Finally, the First six characters of the original Source will be passed through the ENCODEHTML formatter.
Result: 123456
Example:[COALESCE,Tab,Querystring,frmTabName,Form,Tabname,,Some <> Value,Text,{LEFT:6},{RIGHT:1}]
1. If the Tab value from the Querystring contains a value which is not Empty it is the Source of the COALESCE. Otherwise, go to next element.
2. If the frmTabName value from the Form is not Empty, it is the Source of the COALESCE. Otherwise, go to next element. (Unlike Empty Form, if this value is empty it Fails to be assigned as the Source).
3. If the Tabname column from your query contains a value with a length greater than zero, it is the source of the COALESCE.
4. Otherwise, Some <> Value is set as the Default Source as it is returned as plain text.
5. Next, the Source has been identified and will be passed through the LEFT:6 formatter to return the first six characters of the Source.
6. Finally, the First character of the resulting value will be returned from the RIGHT:1 formatter
Result: 6
Example:[COALESCE,Tab,Querystring,{LEFT:6},{RIGHT:3}]
1. The Tab value from the Querystring is the Source of the COALESCE.
2. Next, the Source has been identified and will be passed through the LEFT:6 formatter to return the first six characters of the Source.
3. Finally, the First character of the resulting value will be returned from the RIGHT:1 formatter
Result: 456