ColdFusion in Context: Gulp Detail

Suppose you have manufacturers and vendors who want to build purchase orders or invoices through the Web. These documents have a header showing such things as the paying party and shipping charges and may have 80 lines of detail or more: one for each style and size of each item. (Picture a clothing contract for a small boutique and you'll get the idea.) Internally, you would typically represent a header-detail relationship as two tables: a header table and a detail table.

It's easy to imagine how to implement creating the header with a form that has a field for each attribute of the overall transaction, but what happens next? The header form creates a single header. The detail table will have a single row for each line of detail. How many lines of detail do you let the vendor add at once? Hint: the answer should NOT be only one line!

Look at Human Factors and Internet Communication

Sometimes the inner design of an application can give false clues to what its user interface should look like. Interfacing with relational databases, in particular, can coax a developer to provide a form that only permits the vendor to enter one line of detail at a time.

Although the database will be updated once for each line of detail, it doesn't always make sense from a communications and human factors standpoint for the user to enter it that way. If you create a form that only permits the user to enter a single line of detail, the user must receive and commit the form through the Internet perhaps 80 times: once for each line. If the invoice fits on a single form, a single commit, a single set of page requests, will suffice. Using a single form for multiple lines of data causes less waiting time for the user and a lower workload for the Web server. For really big invoices, continuation pages could be added as needed. This example won't do continuation pages, but it will demonstrate construction of forms and queries that handle multiple detail lines in one "gulp". Along the way, it will use two different methods of contructing field names on the fly: cfoutput and the evaluate function.

Add Two Empty Tables

For this demonstration, you'll need two tables: head and detail. Head has two fields: HeadID (numeric) and Vendor (text). Detail has five fields: HeadID (numeric), DetailID (numeric), Item (text), Qty (numeric), and Price (numeric). The data will be represented in the usual relational fashion. It's only the access to this data that will look like a hierarchical paper form.

Make a One-page Purchase Form with Many Detail Lines

A one-page purchase order would have a header followed by many rows of detail. The user would fill in the header and detail and submit the form. The server would then redisplay the entire order. The user could edit and resubmit if desired.

A form intended to insert multiple rows of database detail has many similar groups of fields: one group for each row. To make this easier, the fields in the first group might be suffixed with "1", the fields in the second group might be suffixed with "2", and so forth. The field names might be similar to the column names in the database. Here's an sample page to populate a form of 5 lines. Call it OrdAddF.cfm. You can make the form larger by changing the maximum loop value. The numberFormat function gives you consistent leading zeroes to format the row number. 

<form name="add" action="OrdAddQ.cfm" method="post">
OrderID: new
<br>
Vendor: <input type="text" name="Vendor" size="30" value="">
<table>
<tr><td></td><td>ITEM</td>
<td>QTY</td><td>PRICE</td></tr>
<cfloop index="Row" from="1" to="5">
<tr><cfoutput><td>#numberFormat(Row,000)#</td>
<td><input type="text" name="Item_#Row#" value="" size="30"></td>
<td><input type="text" name="Qty_#Row#" value="" size="10"></td>
<td><input type="text" name="Price_#Row#" value="" size="12"></td>
</cfoutput></tr>
</cfloop>
</table>
<input type="submit" name="doit" value="Add">
</form>

Make an Insert Template That Handles Many Detail Lines

Here's an action page to write the form's contents to the database. Call it OrdAddQ.cfm. It inserts the header record and detail records, getting the next available keys. If the update was unsucessful - perhaps two people tried to insert close to the same time - it rolls the insert back and tries again. After a few unsuccessful tries, it warns the user. (GetMax1.cfm and GetMax2.cfm support this template and will be discussed in a moment.)

OrdAddQ.cfm goes through an intermediate step to translate the fields for each row into the field names needed by the database. Looping through the potential rows, it builds field names for each row and does an insert if there is data for the item field. Because the insert will fail if numeric values are missing, it forces them to zero if not specified by the user. 

<cfset try=0>
<cfloop condition="try lt 4">

<cftry>
<cftransaction>
<cfset Qerr=0>
<cfset try=try+1>

<!--- Header --->
<cfset key="HeadID">
<cfset table="Head">
<cfinclude template="GetMax1.cfm">
<cfquery name="AddHead" datasource="context">
insert into Head (HeadID, Vendor)
values (<cfoutput>#ID1#, '#form.Vendor#'</cfoutput>)
</cfquery>

<!--- Detail --->
<cfloop index="Row" from="1" to="5">
<cfset dummy="form.Item_"&#Row#>
<cfset Titem=evaluate(dummy)>
<cfif len(Titem)>
  <cfset dummy="form.Qty_"&#Row#>
  <cfset TQty=evaluate(dummy)>
  <cfif not len(TQty)>
    <cfset TQty=0>
  </cfif>
  <cfset dummy="form.Price_"&#Row#>
  <cfset TPrice=evaluate(dummy)>
  <cfif not len(TPrice)>
    <cfset TPrice=0>
  </cfif>
  <cfset key2="DetailID">
  <cfset table2="Detail">
  <cfinclude template="getMax2.cfm">
  <cfquery name="AddDetail" datasource="context">
  insert into Detail (HeadID, DetailID, Item, Qty, Price)
  values (#ID1#, #ID2#, '#Titem#', #Tqty#, #Tprice#)
  </cfquery>
</cfif>
</cfloop>

</cftransaction>
<!--- Exit here when OK --->
<cfbreak>

<!--- Retry up to max times --->
<cfcatch type="database">
<cfif try gt 3>
  <cfset Qerr=2>
  <cfset Qmsg="database failed three times with good input; retry later or notify maintenance">
  <cfbreak>
</cfif>
</cfcatch>
</cftry>

</cfloop>

<cfif #Qerr#>
  <cfoutput>MyQuery Error #Qerr#: #Qmsg#<br>
  Press your back button to continue.</cfoutput>
  <cfabort>
</cfif>

<cfset ref="OrdSeeQ.cfm?HeadID=#ID1#">
<cflocation URL="#ref#">

In order to insert table rows, it's helpful to be able to determine the next available key for the table. GetMax1.cfm does this for the header table and can be used for other projects. Here, key is "HeadID" and table is "Head". 

<cfquery name="getMax1" datasource="context">
select max(#key#) as Max1
from #table#
</cfquery>
<cfif len(getMax1.Max1)>
  <cfset ID1=getMax1.Max1+1>
<cfelse>
  <cfset ID1=1>
</cfif>

GetMax2.cfm does this for the detail table and can also be used for other projects. Here, key2 is "DetailID" and table2 is "Detail". ID1 comes from GetMax1.cfm and is the value of HeadID corresponding to this overall order. 

<cfquery name="getMax2" datasource="context">
select max(#key2#) as Max2
from #table2#
where #key# = #ID1#
</cfquery>
<cfif len(getMax2.Max2)>
  <cfset ID2=getMax2.Max2+1>
<cfelse>
  <cfset ID2=1>
</cfif>

Make an Update Form That Handles Detail Lines

OrdSeeQ.cfm receives the HeadID in the URL, selects the appropriate records, and calls the form. 

<cfquery name="SeeHead" datasource="context">
select * from Head
where HeadID = #URL.HeadID#
</cfquery>

<cfquery name="SeeDetail" datasource="context">
select * from Detail
where HeadID = #URL.HeadID#
</cfquery>

<cfinclude template="OrdChgF.cfm">

OrdChgF.cfm displays the appropriate records and accepts changes. The HeadID is carried from the URL into a hidden field in the form. The query is unrolled to provide a row of input fields for each row of detail. 

<form name="chg" action="OrdChgQ.cfm" method="post">
<cfoutput query="SeeHead">
OrdID: #HeadID#<br>
<input type="hidden" name="HeadID" value="#HeadID#">
Vendor: <input type="text" name="Vendor" size="30" value="#Vendor#">
</cfoutput>

<table>
<tr><td></td><td>ITEM</td>
<td>QTY</td><td>PRICE</td></tr>
<cfset Row="1">
<cfoutput query="SeeDetail">
<tr><td>#numberFormat(Row,000)#</td>
<td><input type="text" name="Item_#Row#" value="#Item#" size="30"></td>
<td><input type="text" name="Qty_#Row#" value="#Qty#" size="10"></td>
<td><input type="text" name="Price_#Row#" value="#Price#" size="12">
<input type="hidden" name="DetailID_#Row#" value="#DetailID#"></td>
</tr>
<cfset Row=Row+1>
</cfoutput>
</table>
<input type="submit" name="doit" value="Change">
</form>

Make an Update Template That Handles Many Lines

Here's an action page to update the database; call it OrdChgQ.cfm. It updates the header record and detail records. If the update was unsucessful - perhaps two people tried to update close to the same time - it rolls the update back and tries again. After a few unsuccessful tries, it warns the user.

It goes through an intermediate step to translate the fields for each row into the field names needed by the database. Looping through the potential rows, it builds the field names for each row (up to an arbitrary maximum) until it creates a field name that doesn't exist (indicating that no more rows are present to be considered). 

<cfset try=0>
<cfloop condition="try lt 4">

<cftry>
<cftransaction>
<cfset Qerr=0>
<cfset try=try+1>

<!--- Header --->
<cfquery name="chgHead" datasource="context">
update Head set
Vendor = '#trim(form.Vendor)#'
where HeadID = #form.HeadID#
</cfquery>

<!--- Detail --->
<cfloop index="Row" from="1" to="100">
<cfset dummy="form.Item_"&#Row#>
<cfif not isDefined(dummy)>
  <cfbreak>
</cfif>
<cfset Titem=evaluate(dummy)>
<cfif len(Titem)>
  <cfset dummy="form.Qty_"&#Row#>
  <cfset TQty=evaluate(dummy)>
  <cfif not len(TQty)>
    <cfset TQty=0>
  </cfif>
  <cfset dummy="form.Price_"&#Row#>
  <cfset TPrice=evaluate(dummy)>
  <cfif not len(TPrice)>
    <cfset TPrice=0>
  </cfif>
  <cfset dummy="form.DetailID_"&#Row#>
  <cfset TDetailID=evaluate(dummy)>
  <cfquery name="ChgDetail" datasource="context">
  update Detail set
  Item = '#Titem#',
  Qty = #Tqty#,
  Price= #TPrice#
  where HeadID = #form.HeadID#
  and DetailID = #TDetailID#
  </cfquery>
</cfif>
</cfloop>

</cftransaction>
<!--- Exit here when OK --->
<cfbreak>

<!--- Retry up to max times --->
<cfcatch type="database">
<cfif try gt 3>
  <cfset Qerr=2>
  <cfset Qmsg="database failed three times with good input; retry later or notify maintenance">
  <cfbreak>
</cfif>
</cfcatch>
</cftry>

</cfloop>

<cfif #Qerr#>
  <cfoutput>MyQuery Error #Qerr#: #Qmsg#<br>
  Press your back button to continue.</cfoutput>
  <cfabort>
</cfif>

<cfset ref="OrdSeeQ.cfm?HeadID=#form.HeadID#">
<cflocation URL="#ref#">

Discussion

The general method used here was to create a set of fields for each row on the form and then walk through each row to update its detail row in the database. On the form itself, it was handy to use cfoutput to create field names on the fly. For queries, the evaluate function was used to interpret concatenated text as a field name. Other techniques outside the emphasis of this demonstration were used to improve the robustness of database access.

Once you've assembled this example, go to OrdAddF.cfm to add an order and then update it as desired. This example ends by calling OrdSeeQ.cfm to invite another update. In real life, you would add templates to list, process, archive, and otherwise manage these orders at the order level.

So don't stop here. Add javascript to catch obvious errors as the user edits each field. Add templates needed to manage the orders you receive. Then use forms with multiple detail lines to reduce your communications overhead while improving service to your customer. =Marty=