ColdFusion in Context: Query Cell Access

Suppose you have fields that you could display on a form depending on the context, and you want a metadata table to indicate for each field if the field should be displayed and what its label should be. Assuming that each field corresponds to a row of the query, how do you get access to the individual cells of your query (such as "IsRequired", and "Label") without running another query? That's the subject of this tip.

Make Sample Metadata

Build a table about the fields that might be used. Call it Meta4. [pun alert!] Give it fields Fieldname, IsRequired, Label, and Context. Here's some sample data: 

FIELDNAME,ISREQUIRED,LABEL,CONTEXT
ContractNr,1,Contract No.,1
OrderNr,1,Order No.,1
CallNr,0,Call No.,1
VendorCode,1,Vendor Code,1
InvoiceNr,1,Invoice,1
ShipTo,0,Ship To,1
ContractNr,1,Contract,2
OrderNr,1,Purchase Order,2
CallNr,1,Call No.,2
VendorCode,1,Vendor Code,2
InvoiceNr,1,Invoice Number,2
ShipTo,1,Ship To,2

Consider Cell Access

One (terrible) approach each time we want to know if the VendorCode field is required would be to query the original query "where Fieldname = 'VendorCode'" and read the value for the column named "IsRequired". Clearly, this won't do.

Another approach is to generate a variable on the fly for EVERY cell of the query and read it when we need it. This is a little better but wastes resources. The data's already stored once; we don't need to do it twice.

Still another approach is to use the query "as is" without storing all its cells in a different structure, and that's the approach to be discussed next.

Each row of the query corresponds to a field on the form. If you know what row of the query corresponds to each field, you can get the value of a specific column (such as Label) for a specific field (such as OrderNr) by saying "{query name}.{column name}[{row number}]". (Try this manually without any fancy tools; it works.) You could can the label of the OrderNr field by saying {query name}.Label[{row number for the Order Number}].

Let's review. You provide the attribute you want to know about, provide the row of the fieldname/group you want that attribute for, and you get the attribute's value in return.

So how do you know what row number to ask for? You build an index to the query. Your key is the fieldname; the value to be stored is the row number.

Build It

Put this code in show.cfm. You'll need a query, the query index, and the form. The query, which can be much more complex than this, simply brings back the metadata needed for the form. The query name - MD - is deliberately short for reasons we'll see in a moment. Define a URL parameter above the query so we can look at the form in different ways. Because Context isn't surrounded with single quotes in the query - it's intended to be a number - use the val function to work with its value as a number instead of with the raw unquoted input that could otherwise be used to ruin your database day. 

<cfparam name="url.Context" default="1">

<cfquery name="MD" datasource="context">
select * from Meta4
where Context = #val(url.Context)#
</cfquery>

In this code, QR (for QueryRow) is the name of the index to the query. The name of this structure should be as short as possible; you'll be using it a lot. MD (for MetaData) is the name of the query. Again, the query name will be used multiple times for every field on the page. The bare code to build such an index is almost anti-climactic. 

<cfset QR=structNew()>
<cfset Row=0>
<cfloop query="MD">
<cfset Row=Row+1>
<cfset Qkey="#Fieldname#">
<cfset dummy=structInsert(QR,"#Qkey#",Row)>
</cfloop>

If you dump QR at this point, you'll see a key for every row in the query. For example, ShipTo points to row 6 (in context 1). That's all you need. Use this code once after the query to build this index.

Here's a sample form that varies depending on the context. For each field, a cfif statement checks to see if the value of the IsRequired column for this field is other than zero. If the field is required, the Label is read directly from the query. 

<form method="post">
<cfoutput>
<cfif val(MD.IsRequired[QR.ContractNr])>
  <div>#MD.Label[QR.ContractNr]#
  <input type="text" name="ContractNr"
  value="yours" maxlength="13" size="23"></div>
</cfif>
<cfif val(MD.IsRequired[QR.OrderNr])>
  <div>#MD.Label[QR.OrderNr]#
  <input type="text" name="OrderNr"
  value="yours" maxlength="8" size="14"></div>
</cfif>
<cfif val(MD.IsRequired[QR.CallNr])>
  <div>#MD.Label[QR.CallNr]#
  <input type="text" name="CallNr"
  value="yours" maxlength="20" size="35"></div>
</cfif>
<cfif val(MD.IsRequired[QR.VendorCode])>
  <div>#MD.Label[QR.VendorCode]#
  <input type="text" name="VendorCode"
  value="yours" maxlength="5" size="8"></div>
</cfif>
<cfif val(MD.IsRequired[QR.InvoiceNr])>
  <div>#MD.Label[QR.InvoiceNr]#
  <input type="text" name="InvoiceNr"
  value="yours" maxlength="8" size="14"></div>
</cfif>
<cfif val(MD.IsRequired[QR.ShipTo])>
  <div>#MD.Label[QR.ShipTo]#
  <input type="text" name="ShipTo"
  value="yours" maxlength="20" size="35"></div>
</cfif>
</cfoutput>
</form>

Operation and Discussion

Browse show.cfm?Context=1. Now browse show.cfm?Context=2 to see a different form. Both contain dummy data, but the point is that different fields are displayed, and labels differ from one context to another.

One thing to note about this technique as presented here is that every field has a row in the table for every context. This way, we didn't have to test the field to see if it was defined (to keep the code from blowing up) before we checked to see if it was required. This also let us use a customer questionnaire directly to prototype a form without having to drop unused rows.

You might try an isDefined test against this lookup instead of having rows that aren't used today (but might be used tomorrow). Pulling the maxlength and generating the field size from the database was omitted from this example for brevity, but you might pull maxlength and format information for each field directly from your database to build your form and your edit checks. Build prototypes on the fly; amaze your customers. Enjoy! =Marty=