I struggled with COALESCE over what turned out to be a simple issue so I thought I would share my experience and hopefully save someone else a bit of time. What follows is an example of a drop down list using subquery and coalesce, where the drop down list has a default (other than the first option).

Here is the scenario: I have a form with a drop down list which allows the end user to select a (US) state. However I wanted the list to default to the end user’s home state, in this case “WA” (#48 in the list).

The bugger was of course, coalesce. The syntax I used was : [COALESCE,USStateID,AddrData,"48",Text,,] What held me up was the second of the two trailing commas. I’m almost embarrassed to say it, but it was a good hour before I figured it out.

The full element is as follows:

<select>
{SUBQUERY,
name="statelookup",
query="SELECT USStateID,Abbr FROM USStates",
format="<option value='\[USStateID\]'>\[Abbr\]</option>",
selectedformat="<option value='\[USStateID\]' selected=\"selected\">\[Abbr\]</option>",
selectedfield="USStateID",
selecteditems="[COALESCE,USStateID,AddrData,"48",Text,,]",
useCache="False"}
</select>

Where USStates is a table, USStateID is the ID column (and index), and Abbr is the column containing state abbreviations.

I assume you are using a “Variable” Action to capture the ID, which in turn is used in your Query Template.

If so, then one approach would be to create a hidden HTML input field and populate the value via javascript in the onchange event of the drop down. When the form is posted to OWS you would use another Variable Action to capture the Abbr.