Using the list boxes

The two functions are DBDisplayValue and DBEditValue. These functions can and should be used to display any data, not just fields with foreign keys, so all the special formatting is kept in these functions. The former displays the data, while the latter allows editing using the relevant form input or list box.

// loop through all fields in this record for ( var i=0; i<nFields; i++)    Out ( '<td>' + DBDisplayValue ( oRecordSet, sTableName, i ) + '</td>' );

You can see above an example of how to call these functions (they have the same arguments). Here I am looping through a recordset passing in the recordset itself, the table name and the field number. We'll see below how DBDisplayValue uses this information to display the data using the most appropriate method.

function DBDisplayValue ( oRecordSet, sTableName, nColumn ) {    var sColumnName = oRecordSet.Fields ( nColumn ).Name;    var oValue = oRecordSet ( nColumn );    // get dropdown list if a foreign key    var sHTML = Application ( sTableName + ':' + sColumnName );    if ( sHTML )    {

The first thing to do is test if there is a matching Application variable for this field, so I get the field name from the recordset using the Name method of the Field item. I save the actual data from the field as I'm passing through, then get whatever is in the relevant Application variable.

If there is no Application variable, then the field has no foreign key associated with it and will be displayed using "normal" methods. That's not interesting to this article, so let's see what happens when sHTML does contain the HTML for a list box.

// disable control (it's read-only for display purposes) var nIndex = sHTML.indexOf ( ' name' ); if ( nIndex != -1 )    sHTML = sHTML.slice ( 0, nIndex ) + ' disabled' + sHTML.slice ( nIndex );

For the DBDisplayValue function only I make the list box disabled - admittedly it is wasteful to output all that HTML just to display a value, but it's easy and, hey, if you optimize it you'll send me the update, right?

To disable the list box I search for the name in <select name=...> and replace it with disabled name. The string slice method takes the start and optional end position of the substring you want returned.

// place 'selected' in the correct spot var nIndex = sHTML.indexOf ( ' value="' + oValue ); if ( nIndex != -1 )    sHTML = sHTML.slice ( 0, nIndex ) + ' selected' + sHTML.slice ( nIndex );

The, to display the correct item in the list box by default I do the same thing again - this time searching for the correct value=" and prefixing it with selected.

That's it - easy-peazy-lemon-squeezy. Now I just use DBDisplayValue and DBEditValue everywhere, and foreign keys automatically appear as list boxes!