Database-based dropdowns with Dialog API

Quite a few people asked how to extend Dialog API by adding dropdown boxes populated by the database content. Since Dialog API is Javascript based there will be a little trick that would help us retrieve the data from the database on the server-side and pass it to Javascript.

Final result:

Let’s take a look at the sample Dialog API code with the dropdown:

return Runner.Dialog( {
       title: 'Preferences',
       fields: [{
               name: 'color',
           type: 'lookup',
                 value: 2,
         options:  
                 [
                 [1,'red'],
                 [2,'green'],
                 [3,'yellow']
                 ]
      }],
       ok: 'Save',
       cancel: 'Cancel',
beforeOK: function( popup, controls ) {
swal('Success', 'Selected color: ' + controls[0].val(), 'success');
}
});

As you can see, we hardcode data in the lookup wizard, passing a two-dimensional array (id and value for each entry).

options:  
[
   [1,'red'],
   [2,'green'],
   [3,'yellow']
]

Now let’s do our magic. The following code goes to the BeforeDisplay event of the page where we will be using Dialog API.

PHP code:

$lookup = array();
$rs = DB::Query("select id, color from carsbcolor");
while( $data = $rs->fetchAssoc() )
{
$row = array();
$row[] = $data["id"];
$row[] = $data["color"];
$lookup[] = $row;
}
$pageObject->setProxyValue("lookup", $lookup);

C# code:

dynamic lookup, rs, row, data;
lookup = XVar.Clone(XVar.Array());
rs = XVar.Clone(DB.Query(new XVar("select id, color from carsbcolor")));
while(XVar.Pack(data = XVar.Clone(rs.fetchAssoc())))
{
	row = XVar.Clone(XVar.Array());
	row.InitAndSetArrayItem(data["id"], null);
	row.InitAndSetArrayItem(data["color"], null);
	lookup.InitAndSetArrayItem(row, null);
}
pageObject.setProxyValue(new XVar("lookup"), (XVar)(lookup));
return null;

You can see that we execute a SQL query, loop through results, populate an array with data and then pass to Javascript using setProxyValue function.

Now here is the updated Dialog API code.

return Runner.Dialog( {
       title: 'Preferences',
       fields: [{
                name: 'color',
		label: 'What is your favorite color?',
		type: 'lookup',
                value: 2,
		options: proxy['lookup']
		}],
       ok: 'Save',
       cancel: 'Cancel',
beforeOK: function( popup, controls ) {
swal('Success', 'Selected color: ' + controls[0].val(), 'success');
}
});

This code is even cleaner now than the previous version. Instead of the hardcoded dropdown entries we just use proxy[‘lookup’] and voilà!

Enjoy.