ColdFusion in Context: Valid Values Maintenance

Suppose you have a dozen or so valid values tables, and you would like to be able to maintain them without creating separate forms and queries for each one. This tip shows a way to do that.

Here's the Menu

You would like to call the same page regardless of which table you need to work with. Just tell the page which content to use. Call this sample menu menu.cfm. 

<a href="OneForm.cfm?Content=Exhibit&Mode=Add">Exhibits</a>
<p>
<a href="OneForm.cfm?Content=ShopLocation&Mode=Add">Shop Locations</a>
<p>
<a href="OneForm.cfm?Content=StateOrCountryCode&Mode=Add">State/Country Codes</a>

Describe the Tables

For this demonstration, create three files: Exhibit.cfm, ShopLocation.cfm, and StateOrCountryCode.cfm. Each file describes a table of the same name (when the .cfm is ignored). Here's Exhibit.cfm. 

<cfscript>
Title="Exhibits";
AllQuery="allExhibit";
OneQuery="oneExhibit";
NotQuery="oneExhibitNot";
AddQuery="addExhibit";
DelQuery="delExhibit";
UpdQuery="updExhibit";
Field1="Exhibit";
Field1Max=20;
Field2="";
Field2Max=0;
FieldX="";
FieldXMax=0;
FieldW="Description";
FieldWMax=240;
</cfscript>

An explanation is in order. Title is used in captions. The query names OneQuery, NotQuery, etc. are the names of the corresponding query files when .cfm is added to them. Yes, this application only uses six query files, no matter how many valid values tables you have. Their names are self-explanatory except perhaps for the one beginning in "Not"; NotQuery.cfm actually uses the same query name as OneQuery.cfm except that it brings back no records. Field1 is the key. Field2 is a text field; if it's given a length greater than zero (Field2Max), the user must enter it. It has no length in this example. FieldW is a Wide field. FieldX is either "X" or empty. The "max" fields indicate the length of the corresponding field in the database. These are all text fields.

Create the Exhibit table to go with this description. Give it fields Exhibit and Description with the lengths shown above (20 and 240).

Here's ShopLocation.cfm. It has only one field, which makes it pretty typical for a valid values table. Create ShopLocation. Give it the field ShopLocation with the length shown below. 

<cfscript>
ContentFile="ShopLocation.cfm";
Title="Shop Locations";
AllQuery="allShopLocation";
OneQuery="oneShopLocation";
NotQuery="oneShopLocationNot";
AddQuery="addShopLocation";
DelQuery="delShopLocation";
UpdQuery="updShopLocation";
Field1="ShopLocation";
Field1Max=20;
Field2="";
Field2Max=0;
FieldX="";
FieldXMax=0;
FieldW="";
FieldWMax=0;
</cfscript>

Here's StateOrCountryCode.cfm. It has a field that is either X (indicating a country) or empty (indicating a state). Create the corresponding table: StateOrCountryCode. (Give it fields StateOrCountryCode, Name, and CountryFlag with the lengths indicated.) 

<cfscript>
ContentFile="StateOrCountryCode.cfm";
Title="State and Country Codes";
AllQuery="allStateOrCountryCode";
OneQuery="oneStateOrCountryCode";
NotQuery="oneStateOrCountryCode";
AddQuery="addStateOrCountryCode";
DelQuery="delStateOrCountryCode";
UpdQuery="updStateOrCountryCode";
Field1="StateOrCountryCode";
Field1Max=3;
Field2="Name";
Field2Max=25;
FieldX="CountryFlag";
FieldXMax=1;
FieldW="";
FieldWMax=0;
</cfscript>

Make an List Query Page

Consider this query page: AllOne.cfm. If you know the Content (passed by the menu), you can add .cfm to get the table description (above). You can expand Content and Field1 from the table description with pound signs to get the real table name and key field. (AllQuery is expanded to a logical name in the description that doesn't really matter as long as it's consistent and doesn't interfere with anything else.) This page can list all the rows of any table, given the proper parameters. 

<cfquery name="#AllQuery#" datasource="context">
select * from #Content#
order by #Field1#
</cfquery>

Make a Get Query Page

Now let's consider OneOne.cfm, a page that can pick a single record from a simple table. Because it's handy to turn portions of a list into links that point to detail regarding a list entry, assume for a moment that the link passes KeyName and Key in the url. The urlDecode function is needed to humor a major browser. 

<cfquery name="#OneQuery#" datasource="context">
select *
from #Content#
where #url.KeyName# = '#urlDecode(url.Key)#'
</cfquery>

Make a Fake Query Page for Add

If you want to add a record, the initially empty field values have to come from somewhere. Why not from a query that has the same name, table, and columns as the real query but will never be satisfied? Here's NotOne.cfm. Its query name is the same as for the add query; it just has an impossible condition. 

<!--- This pretends to be OneQuery but is empty --->
<cfquery name="#OneQuery#" datasource="context">
select *
from #Content#
where 1 = 0
</cfquery>

Make a Delete Query Page

These queries have just brought records back, but if you're trying to change the database, you need to trap errors and let the user know if the change failed. DelOne.cfm uses a try-catch combination to send an error message if there was a problem with deleting a record. As with OneOne.cfm, it relies on url variables for the input key name and value. Your list page would normally include this one and then regain control. When the list is redrawn, it will be obvious that the row has been removed. However, if there is a problem and the row isn't removed, this code goes to the add/edit page with a message warning that there's a problem and does it in add mode, the default. To keep spaces or special characters in the key and other content of the message from causing the message to be truncated, use urlEncodedFormat function to convert such characters so they can be passed in a url. 

<cftry>

<cfquery name="#DelQuery#" datasource="context">
delete *
from #Content#
where #url.KeyName# = '#urlDecode(url.Key)#'
</cfquery>

<cfcatch>
<cfset Temp="../common/OneForm.cfm?">
<cfset Temp=Temp&"Context=#form.Context#">
<cfset Temp=Temp&"&Mode=Add">
<cfset Message=urlEncodedFormat("#urlDecode(url.Key)# not deleted: reason unknown")>
<cfset Temp=Temp&"&Message=#Message#">
<cflocation url=#Temp#>
</cfcatch>
</cftry>

Make an Update Query

The update query page, UpdOne.cfm, isn't much harder. If the description of a field indicates that it has a length - the "Max" fields hold the length of the field if the field is to be used - then make the field part of this query. Otherwise, leave it out. The evaluate function exposes the content of the field. Trim the field content when you update it. When reporting the error, use Field1 to represent the key and change the message slightly from the one used for delete. 

<cftry>

<cfquery name="#UpdQuery#" datasource="context">
update #Content# set
#Field1# = '#trim(evaluate(Field1))#'
<cfif Field2Max>
  , #Field2# = '#trim(evaluate(Field2))#'
</cfif>
<cfif FieldWMax>
  , #FieldW# = '#trim(evaluate(FieldW))#'
</cfif>
<cfif FieldXMax>
  <cfset FX="form.#FieldX#">
  <cfif len(trim(evaluate(FX)))>
    , #FieldX# = 'X'
  <cfelse>
    , #FieldX# = ''
  </cfif>
</cfif>
where #Field1# = '#evaluate(Field1)#'
</cfquery>

<cfcatch>
<cfset Temp="../common/OneForm.cfm?">
<cfset Temp=Temp&"Context=#form.Context#">
<cfset Temp=Temp&"&Mode=Add">
<cfset Message=urlEncodedFormat("#evaluate(Field1)# unchanged for reasons unknown")>
<cfset Temp=Temp&"&Message=#Message#">
<cflocation url=#Temp#>
</cfcatch>
</cftry>

Make an Add Query

The add query page, addOne.cfm, is similar to the update query page. The logic for determining if a field should be part of the query is the same. Expose and trim the field content as you add it. If there's anything in the X field, store a big "X"; otherwise, store an empty string. The code to execute if there's a problem is the same as for update except for the message. 

<cftry>

<cfquery name="AddQuery" datasource="context">
insert into #Content# (
#Field1#
<cfif Field2Max>
  , #Field2#
</cfif>
<cfif FieldWMax>
 , #FieldW#
</cfif>
<cfif FieldXMax>
 , #FieldX#
</cfif>
)
values (
<cfset F1="form.#Field1#">
'#trim(evaluate(F1))#'
<cfif Field2Max>
  <cfset F2="form.#Field2#">
  , '#trim(evaluate(F2))#'
</cfif>
<cfif FieldWMax>
  <cfset F3="form.#FieldW#">
  , '#trim(evaluate(F3))#'
</cfif>
<cfif FieldXMax>
  <cfset FX="form.#FieldX#">
  <cfif len(trim(evaluate(FX)))>
    , 'X'
  <cfelse>
    , ''
  </cfif>
</cfif>
)
</cfquery>

<cfcatch>
<cfset Temp="OneForm.cfm?">
<cfset Temp=Temp&"Content=#form.Content#">
<cfset Temp=Temp&"&Mode=Add">
<cfset Message=urlEncodedFormat("#evaluate(Field1)# not added; probable duplicate")>
<cfset Temp=Temp&"&Message=#Message#">
<cflocation url=#Temp#>
</cfcatch>
</cftry>

Make the List Page

The list page, OneForm2.cfm, displays each record and provides controls to delete it or to enable editing through another page. The general approach is to see why this page was invoked and then perform an action prior to (or instead of) displaying the list. Let's take it a chunk at a time.

If the value of "Content" is provided in the URL (as it will be if this page passes it to itself), then pretend it came from a form for consistency (as it would if it had come from the add/edit page). What you might not expect is that you can define a new form variable for this page even though the page has no form of its own. The variable won't be treated as a real form variable would; because, it won't be passed out of the page, but it's close enough for our purpose here. Use Content to get the name of the page that contains the table description and include the description. Display the title from the description page. 

<cfif not isDefined("form.Content")>
  <cfif not isDefined("url.Content") and len(url.Content)>
    Content must be provided: e.g., filename?Content={something}
    <cfabort>
  </cfif>
  <cfset form.Content=url.Content>
</cfif>
<cfset Script="#form.Content#"&".cfm">
<cfinclude template="#script#">

<h3><cfoutput>#Title#</cfoutput></h3>

If the function to be performed is to add a record (based on input from the add/edit form), then include the add query page to add the record. When control returns from the add query page, compose a "success" message and go back to the add/edit page where the message will be displayed. (Remember that if there is a failure, control doesn't return here; the add query page composes its own message and changes directly to the add/edit page.) 

<cfif isDefined("form.Mode") and (form.Mode is "Add")>
  <cfinclude template="AddOne.cfm">
  <cfset Temp="OneForm.cfm?">
  <cfset Temp=Temp&"Content=#form.Content#">
  <cfset Temp=Temp&"&Mode=Add">
  <cfset Message=urlEncodedFormat("#evaluate(Field1)# has been added")>
  <cfset Temp=Temp&"&Message=#Message#">
  <cflocation url=#Temp#>
</cfif>

If the function to be performed is to edit a record (based on input from the add/edit form), then include the update query page. When control returns from the update query page, compose a "success" message and go back to the add/edit page where the message will be displayed. (Remember that if there is a failure, control doesn't return here; the update query page composes its own message and changes directly to the add/edit page.) 

<cfif isDefined("form.Mode") and (form.Mode is "Edit")>
  <cfinclude template="UpdOne.cfm">
  <cfset Temp="OneForm.cfm?">
  <cfset Temp=Temp&"Content=#form.Content#">
  <cfset Temp=Temp&"&Mode=Add">
  <cfset Message=urlEncodedFormat("#evaluate(Field1)# has been changed")>
  <cfset Temp=Temp&"&Message=#Message#">
  <cflocation url=#Temp#>
</cfif>

If the function to be performed is to edit a record (based on a link from THIS page), then include the delete query page. If there is a failure, the delete query page will go directly to the add/edit page with a message. Otherwise, change the URL mode to something useless to avoid confusion, and let the rest of this page do its work. 

<cfif isDefined("url.Mode") and (url.Mode is "Del")>
  <cfinclude template="DelOne.cfm">
  <cfset url.Mode="x">
</cfif>

Now display an add link, an edit link, and the table headers for the list. The add link tells the add/edit page what Content to use and tells it to work in Edit mode. The exit link merely returns to the menu. Assume that the key (Field1) exists. To avoid database integrity problems when a valid values table is considered in the context of how it will be used, you won't let the user edit the key. So, if the key field is the only field - the combined length of the other fields is zero - don't display the edit column. Otherwise, do display it. Display of the columns for the other fields is field-dependent. If the field description says the field has length, then display the column. Finish with a delete column. 

<a href="OneForm.cfm?<cfoutput>Content=#form.Content#&Mode=Add</cfoutput>">Add</a>
<a href="Menu.cfm">Exit</a>

<cfinclude template="AllOne.cfm">

<table border=1>
<cfoutput><tr>
<cfif evaluate(Field2Max+FieldWMax+FieldXMax)>
  <td>EDIT</td>
</cfif>
<td>#Field1#</td>
<cfif Field2Max>
  <td>#Field2#</td>
</cfif>
<cfif FieldWMax>
  <td>#FieldW#</td>
</cfif>
<cfif FieldXMax>
  <td>X if #FieldX#</td>
</cfif>
<td>DELETE</td>
</tr></cfoutput>

Here's the list. Include the query on which the list will be based. As above, if a non-key field will be used, then an edit link will be needed. The edit link brings the user to the add/edit page, indicates edit mode, and supplies the key name and key value to be used. Assume Field1 will be used. If the length of other fields in the description is not zero, use the evaluate function to expose their content. The delete link comes back to this page in delete mode with the key name and value so the delete code above will be invoked with the proper parameters. 

<cfoutput query="#AllQuery#">
<cfif evaluate(Field2Max+FieldWMax+FieldXMax)>
  <td><a href="OneForm.cfm?Content=#form.Content#&Mode=Edit&KeyName=#Field1#&Key=#urlEncodedFormat(evaluate(Field1))#">Edit</a></td>
</cfif>
<td>#evaluate(Field1)#</td>
<cfif Field2Max>
  <td>#evaluate(Field2)#</td>
</cfif>
<cfif FieldWMax>
  <td>#evaluate(FieldW)#</td>
</cfif>
<cfif FieldXMax>
  <td>#evaluate(FieldX)#</td>
</cfif>
<td><a href="OneForm2.cfm?Content=#form.Content#&Mode=Del&KeyName=#Field1#&Key=#urlEncodedFormat(evaluate(Field1))#">Del</a></td>
</tr>
</cfoutput>
</table>

Make the Add/Edit Page

The Change page, OneForm.cfm, lets a user add a new record or edit a record selected by the list page. It also lets the user go to the list page or exit back to the menu.

This page needs Content and Mode. If the value of "Content" is provided in the URL, then pretend it came from a form for simplicity. Do the same for the Mode. Use Content to determine the name of the file containing the table description. Display the title from the description page. 

<cfif not isDefined("form.Content")>
  <cfif not isDefined("url.Content") and len(url.Content)>
    Content must be provided: e.g., filename?Content={something}
    <cfabort>
  </cfif>
  <cfset form.Content=url.Content>
</cfif>

<cfif not isDefined("form.Mode")>
  <cfif not isDefined("url.Mode") and len(url.Mode)>
    Mode must be provided.
    <cfabort>
  </cfif>
  <cfset form.Mode=url.Mode>
</cfif>

<cfset Script="#form.Content#"&".cfm">
<cfinclude template="#script#">

<h3><cfoutput>#Title#</cfoutput></h3>

A message passed to this page indicates if the attempt to add or edit a record succeeded or failed. Success is routine. However, a message indicating failure should be formatted to catch the user's attention. Use a regular expression in the reFindNoCase function to look for a word beginning with "no" or "not". If the message contains this expression, give it more importance on the page.

The mode makes a difference. If the form is being used to add a record, then you want empty variables to be associated with the query. If it is being used to edit, you want those variables to come from the selected record. Include NotOne.cfm for a query that will return no rows but otherwise is named for the real query. Include OneOne when you want real values for this query. 

<cfparam name="url.Message" default="">
<cfif reFindNoCase(" no[t]? ", Message)>
  <!--- Message contains " no " or " not " --->
  <h2><cfoutput>#url.Message#</cfoutput></h2>
<cfelse>
  <h3><cfoutput>#url.Message#</cfoutput></h3>
</cfif>

<cfif form.Mode is "Add">
  <cfinclude template="NotOne.cfm">
<cfelse>
  <cfinclude template="OneOne.cfm">
</cfif>

For each field that has a length on the description page, the name of the variable containing its value has to be constructed from two separate halves: the name of the query and the name of the field. The name of the field must also be displayed. By performing both tasks at once, the same set of cfif statements can be used for both. 

<table border=1>
<cfset Field1Source="#OneQuery#.#Field1#">
<cfoutput><tr><td>#Field1#</td>
<cfif Field2Max>
  <cfset Field2Source="#OneQuery#.#Field2#">
  <td>#Field2#</td>
</cfif>
<cfif FieldWMax>
  <cfset FieldWSource="#OneQuery#.#FieldW#">
  <td>#FieldW#</td>
</cfif>
<cfif FieldXMax>
  <cfset FieldXSource="#OneQuery#.#FieldX#">
  <td>X if #FieldX#</td>
</cfif>
</tr></cfoutput>

Make the edit/add form post to the list page for its action. If the mode is edit, display Field1 (instead of letting the user change it) and use a hidden field to carry its value. Otherwise, use the cfinput tag. Set its required attribute to yes and provide a message; so, ColdFusion will write javascript that displays the message if the user tries to submit the field when it's empty. The maxlength attribute should be a little larger than the size attribute for most fonts; so, add 2 to the length given in the description and use the result as the maxlength for the field. Open additional fields for entry if the length in their description indicates that they are used. FieldX works the same way as Field1 and Field2 for data entry. The insert and update pages will replace any character entered for Field X with a capital X, but that makes no difference to this form. 

<cfform action="OneForm2.cfm" name="OneForm" method="post">
<tr><td>
<cfif form.Mode is "Add">
  <cfinput name="#Field1#" type="text"
maxlength="#Field1Max#" size="#evaluate(Field1Max+2)#"
required="yes" message="#Field1# cannot be empty"
value=#trim(evaluate(Field1Source))#>
<cfelse>
  <cfoutput>#evaluate(Field1Source)#</cfoutput>
  <input type="hidden" name=<cfoutput>"#Field1#"</cfoutput>
  value=<cfoutput>#evaluate(Field1Source)#</cfoutput>>
</cfif>
</td>
<cfif Field2Max>
  <td><cfinput name="#Field2#" type="text" 
maxlength="#Field2Max#" size="#evaluate(Field2Max+2)#"
required="yes" message="#Field2# cannot be empty"
value=#trim(evaluate(Field2Source))#></td>
</cfif>
<cfif FieldXMax>
  <td><cfinput name="#FieldX#" type="text"
maxlength="1" size="2"
value=#trim(evaluate(FieldXSource))#></td>
</cfif>

The textarea tag will hold as much data as the browser version will let it. It is not a ColdFusion tag; so, wrap ColdFusion variables in a cfoutput tag to use them. For some applications, you would want to use physical wrap to preserve carriage returns. In this application, however, it's more appropriate to use virtual wrap so the result becomes one long string that other displays can eventually format differently if desired. A window 60 colums wide by 4 rows tall should be enough in this context. 

<cfif FieldWMax>
  <td><textarea name=<cfoutput>"#FieldW#"</cfoutput>
  wrap="virtual" name="comments" cols="60" 
  rows="4"><cfoutput>#evaluate(FieldWSource)#</cfoutput></textarea>
</td>
</cfif>
</tr>
</table>

The action page will need the Content to select the right table description and the mode to know what to do with the data. When adding or editing, they are passed in hidden form fields. If the user wants to list records, the List button changes the location to the list page and provides these fields in a URL. If the user wants to exit, the Exit button changes the location to the menu. 

<input name="Content" type="hidden" value=<cfoutput>"#form.Content#"</cfoutput>>
<input name="Mode" type="submit" value=<cfoutput>"#form.Mode#"</cfoutput>>
<input name="List" type="submit" value="List"
onClick="javascript:document.location='OneForm2.cfm?<cfoutput>Content=#form.Content#</cfoutput>'; return false;">
<input name="Exit" type="button" value="Exit" onClick="javascript:document.location='Menu.cfm'; return false;">
</cfform>

Use and Modify This Approach

This tip's most valuable contribution may be in rapidly prototyping applications; in three minutes (depending on your typing speed), you can extend your prototype to work with an additional valid values table. You can add a valid values table to the prototype in three minutes. To use this tip, browse menu.cfm. Add records, list them, edit them, create more tables and description pages, and satisfy yourself that this approach actually works. Currently, it uses the field name for table column headers. For a more polished appearance, you could add a column display field to the description pages and reference that field on the add/edit and list pages. It would be easy to use small images in place of text for the links on the list page. You could replace these links with form buttons and modify the add/edit and list pages to accept them. You can modify a copy of this code as the core for working with more complex tables by replacing the generic information with the specifics of the table you're working with. =Marty=