To lock an input form field on the client side to only allow numeric entries, you can use a short piece of JavaScript that will limit the keys pressed. For example, a form field that is intended to record a monetary value, but the user types letters, dollar signs, commas etc. If you do not sanitize these entries before attempting to update your datasource, the page will error.

The first function limits what can actually be typed into the form field using "0123456789." as the key. Just add a minus sign to allow the recording of financial deficits "0123456789.-". This string can be modified in any way you wish to limit the input of a specific form field.

Just because you prevent them from typing spurious characters into your form field, does not mean the user will not be able to paste them from their buffer. The next two functions will lock the input so that the right mouse will not function. The second prevents the contextual menu from appearing and allowing the user to paste directly into the form field. The third prevents the CTRL key which in combination with the V keyboard key would allow the user to also paste directly into the form field.

<script type="text/javascript">
   fnNumberOnly=function(myfield, e) {
      var key;
      var keychar;
      if (window.event)
         key=window.event.keyCode;
      else if (e)
         key=e.which;
      else
         return true;
      keychar=String.fromCharCode(key);
      if ((key==null)||(key==0)||(key==8)||(key==9)||(key==13)||(key==27))
         return true;
      else if ((("0123456789.").indexOf(keychar) > -1))
         return true;
      else
         return false;}

   fnNoRightMouse=function(event) {
      if (event.button==2) {
         alert("RIGHT-CLICK is disabled for this field.   ");}}

   fnNoCTRL=function(e) {
      var code=(document.all) ? event.keyCode:e.which;
      var msg="'CTRL' key is disabled for this field.   ";
      if (parseInt(code)==17) {
         alert(msg);
         window.event.returnValue=false;}}
</script>

Usage:

<input name="TextBox" type="text" onKeyPress="return fnNumberOnly(this, event)" onmousedown="fnNoRightMouse(event)" onkeydown="return fnNoCTRL(event)" />

If you find this post useful please leave a comment and let me know how you used the information.

The old way of creating two relational select statements involved JavaScript and the more items that were in the select statements, the longer the arrays and the slower the page. Every possible combination was programmed into the JavaScript array which meant that the client browser had to receive every item from the total sum of both select statements. If you used ColdFusion to shorten the programming for the two selects you only made it easy for yourself, the client browser still had to be stuffed.

With Ajax, we have a better way of conducting this business. The second select statement is only built after the first selection is made. This reduces the amount of information that the client browser must have and simplifies both the content and the ColdFusion source document.

Normally, I would just build an Ajax lookup into the same page (especially if it was the only place I was going to use it), the result would be escaped by placing it at the top of the page and using CFABORT at its conclusion. The two selects would also be populated by two queries, but to give you real data to test this procedure with I am performing a query of queries in the second select.

To start we build our first pseudo query, naturally you wouldn't hardcode into the page like this but instead pull from a datasource. This method is only to give you a working example.

<cfscript>
   GetColor=QueryNew("ColorID, ColorName");
   QueryAddRow(GetColor, 2);
   QuerySetCell(GetColor, "ColorID", 1, 1);
   QuerySetCell(GetColor, "ColorName", "Red", 1);
   QuerySetCell(GetColor, "ColorID", 2, 2);
   QuerySetCell(GetColor, "ColorName", "Blue", 2);
</cfscript>

Next wrap the query around the OPTION of a SELECT tag to build your first decision model. As a result of changing the option, a CFDIV will be bound to a page to drive the contents of the second decision model. In this example I am sending it back to the same page using the CGI.SCRIPT_NAME substitution.

<select name="SelectColor">
   <cfoutput query="GetColor">
      <option value="#GetColor.ColorID#">#GetColor.ColorName#</option>
   </cfoutput>
</select>
<cfdiv bind="URL:#CGI.SCRIPT_NAME#?ColorID={SelectColor@change}" />

The first decision model presents the viewer with two color choices; red and blue. From that decision, a second decision model is formed with choices of physical objects that possess that color choice. At the top of this same page, check for the existence of the ColorID URL variable as passed by the CFDIV BIND change. In this example we are hardcoding a second query, but for practical use you would be referencing a real data source and not using a query of queries. Using the results of the URL filtered second query you build a second SELECT tag with relational OPTION values based on the first decision model.

<cfif StructKeyExists(URL, "ColorID")>
   <cfscript>
      GetItem=QueryNew("ItemID, ItemName, ColorID");
      QueryAddRow(GetItem, 4);
      QuerySetCell(GetItem, "ItemID", 1, 1);
      QuerySetCell(GetItem, "ItemName", "Apple", 1);
      QuerySetCell(GetItem, "ColorID", 1, 1);
      QuerySetCell(GetItem, "ItemID", 2, 2);
      QuerySetCell(GetItem, "ItemName", "Rose", 2);
      QuerySetCell(GetItem, "ColorID", 1, 2);
      QuerySetCell(GetItem, "ItemID", 3, 3);
      QuerySetCell(GetItem, "ItemName", "Sky", 3);
      QuerySetCell(GetItem, "ColorID", 2, 3);
      QuerySetCell(GetItem, "ItemID", 4, 4);
      QuerySetCell(GetItem, "ItemName", "Ocean", 4);
      QuerySetCell(GetItem, "ColorID", 2, 4);
   </cfscript>
   <cfquery name="GetItem" dbtype="query">
      SELECT   ItemID, ItemName
      FROM   GetItem
      WHERE   ColorID = <cfqueryparam value="#URL.ColorID#" cfsqltype="CF_SQL_INTEGER">
   </cfquery>
   <select name="SelectItem">
      <cfoutput query="GetItem">
         <option value="#GetItem.ItemID#">#GetItem.ItemName#</option>
      </cfoutput>
   </select>
   <cfabort>
</cfif>

This same model could be chained together to give you an unlimited number of relational selects, not just two. Each successive CFDIV would have to reside in the previous page. So, for a third relational select we would put a CFDIV on the same page as the SELECT that drives the change.

If you find this post useful please leave a comment and let me know how you used the information.

How to loop through form names and update a database where the form names are variable and without using the Evaluate() function.

Naming form elements the same as the database record element makes sense. It also helps keeps the two scopes aligned which will make troubleshooting and modifications a lot easier. In other words, FORM.Record should equal QueryName.Record. What can we do if the existence of the form elements is dependant on a query result and not always contains the exact same results when editing the represented record?

For example, there are four tables in a database (TablePeople, ListCourse, ListDuty, and BridgeTraining). The TablePeople is tracking the personnel who are accomplishing training and their duty type by ID, ListCourse is listing the courses to be accomplished, ListDuty is a listing of the different duty types in the organization, and BridgeTraining is holding the accomplished course items by ID and the personnel by ID. Each person in TablePeople will have entries in BridgeTraining, but depending on which job they hold in the organization, may have a different number of entries.

TablePeople (PeopleID, PeopleName, DutyID)
ListCourse (CourseID, CourseName)
ListDuty (DutyID, DutyName)
BridgeTraining (RecurID, PeopleID, CourseID, CourseDate)

When selecting people to update, you could just list all course possibilities in the company and cleverly disable input for those that are not available for the specific duty type or conditionally check each one before it is rendered to see if it should appear. You could also filter our those records that are not needed during the UPDATE query. I would prefer to limit the list of possible choices from ListCourse to only those that are specified as a result of ListDuty.

Assuming each CourseName was a single word (not sentence, not just a number, and not starting with a number), if you need more text to explain the course title then add an addition element to ListCourse and call it CourseTitle; we are only interested in CourseName as a name for the input into a form element. So, for brevity, a nice short course listing might look something like:

ListCourse
1 = CPR
2 = ISO9000
3 = HIPAA

When we select the first employee to edit, they only have course ID 1 and 3 required. So our form input has form names CPR and HIPPA. During the update query, we are going to loop over ListCourse and check to see if the corresponding form element exists and update that specific record.

<cfoutput query="ListCourse">
   <cfif StructKeyExists(FORM, "#ListCourse.CourseName#")>
      <!--- update BridgeTraining using #FORM["#ListCourse.CourseName#"]# where FORM.PeopleID --->
      <cfquery name="UpdateTraining" datasource="MyDatabase">
         UPDATE   BridgeTraining
         SET      CourseDate = <cfqueryparam value="#FORM['#ListCourse.CourseName#']#" cfsqltype="CF_SQL_TIMESTAMP">
         WHERE   PeopleID = <cfqueryparam value="#FORM.PeopleID#" cfsqltype="CF_SQL_INTEGER"> AND
               CourseID = <cfqueryparam value="#ListCourse.CourseID#" cfsqltype="CF_SQL_INTEGER">
      </cfquery>
   </cfif>
</cfoutput>

Notice that in the StructKeyExists and in the update query I am not using the Evaluate() function. Search Google for "avoid using evaluate" for more information. This examples presumes that you have already created entries in the BridgeTraining table when you either input the new employee or you changed their DutyID.

If you find this post useful please leave a comment and let me know how you used the information.

Let's suppose that you needed to provide a customer with a page that would allow them to update personnel records; 20 records at a time to set a value from a drop-down. On the page would be a table with 20 names and 20 identical HTML SELECT drop downs. When you submit the form, all the selects combine into one long list.

Here is how to handle looping through that returned list to only update those records that have new data selected:

In this example I am submitting the form page back to itself so the top of my ColdFusion page will be a conditional statement checking for the existence of the FORM structure. You can target specific elements of the form, not just the FieldNames.

<cfif StructKeyExists(FORM, "FieldNames")>
   <cfset LoopPosition=0>
   <cfloop index="LoopIndex" list="#FORM.FruitID#">
      <cfset LoopPosition=LoopPosition+1>
      <cfif LoopIndex NEQ 0>
         <cfquery name="SetFruit" datasource="MyDatabase">
            UPDATE   TablePerson
            SET      FruitID = #LoopIndex#
            WHERE   PersonID = #ListGetAt(FORM.PersonID, LoopPosition)#
         </cfquery>
      </cfif>
   </cfloop>
</cfif>

Next, we include two different queries. The first query is the succession set of 20 employees to update. The query is designed to allow the user to start at the beginning and progress through all employees and set the value from a drop down select. The second query is used to populate the select; in this case I'm setting each employee to a favorite fruit.

<cfquery name="GetPerson" datasource="MyDatabase">
   SELECT      TOP (20) PersonID, PersonName
   FROM      TablePerson
   WHERE      FruitID IS NULL
   ORDER BY   PersonName
</cfquery>
<cfquery name="GetFruit" datasource="MyDatabase">
   SELECT      FruitID, FruitName
   FROM      ListFruit
   ORDER BY   FruitName
</cfquery>

Last section is the actual form, fields, and submit button. The form action is set to submit back to itself no matter what the name of the CFM file using CGI.SCRIPT_NAME. The results from the first query build 20 rows at a time because of the TOP (20) limitation I placed on the query. The CFLOOP builds the drop down of fruit by ID and name. I have to use a CFLOOP while inside the CFOUPUT because I'm already progressing through the GetPerson query.

<form action="<cfoutput>#CGI.SCRIPT_NAME#</cfoutput>" method="post" name="FruitForm">
   <table border="1" cellpadding="2" cellspacing="0">
      <tr>
         <th>NAME</th>
         <th>FRUIT</th>
      </tr>
      <cfoutput query="GetPerson">
         <tr>
            <td>#GetPerson.PersonName#</td>
            <td>
               <input name="PersonID" type="hidden" value="#GetPerson.PersonID#" />
               <select name="FruitID">
                  <option value="0"></option>
                  <cfloop query="GetFruit">
                     <option value="#GetFruit.FruitID#">#GetFruit.FruitName#</option>
                  </cfloop>
               </select>
            </td>
         </tr>
      </cfoutput>
      <tr>
         <td colspan="100%" align="right">
            <input name="SubmitFruit" type="submit" value="SUBMIT" />
         </td>
      </tr>
   </table>
</form>

Once you submit the form, the conditional statement at the top checks to see if the FORM structure exists and sets a LoopPosition to zero. There isn't a CurrentRow for looping through a list, so as we loop over the contents of FORM.FruitID we update our current position by one.

If the index is not equal to zero (the value of the first OPTION in our form, and not set by the query) then we need to update the database. We have two lists inside two form field values; FruitID contains a list of any selections made, and PersonID is unique identifier for each PersonName.

The LoopPosition gives us the position inside the list of FORM.FruitID numbers that are not zero, which we can then use to determine which list position to get from the list of FORM.PersonID and perform the update.

If you find this post useful please leave a comment and let me know how you used the information.

Savvy web developers who are always looking for ways to reduce their web clutter may want to compact the view of their client side product. Although it could be argued that compacting the HTML side may reduce the amount of "benchmarking" performed by an over enthusiastic newbie, for ColdFusion writers having someone being able to clearly read the HTML side does little to obfuscate their endeavors, it's only the HTML after all.

There are still a great many people surfing the web with slow connections. There are still dial-up modems out there, but these days I'm just referring to cell phones and slow reception.

A great many actions can be performed to minimize the footprint of a web application; reduce the number of graphics, simplify those graphics, reduce the fidelity of the graphics used, save the showboating for large projects (Adobe Flash, complex Cascading Style Sheets, remoting, etc.), but compressing the HTML can make a contribution as well.

Pick your favorite spot to store the User Defined Function and call it when delivering long pages, compressing a short page really won't make any difference at all for obvious reasons.

<cfscript>
   function fnCompactHTML(ObjectHTML) {
      ObjectHTML=REReplace(ObjectHTML, "[[:space:]]{2,}", " ", "ALL");
      ObjectHTML=REReplace(ObjectHTML, "/\*[^\*]+\*/", " ", "ALL");
      ObjectHTML=REReplace(ObjectHTML, "[ ]*([:{};,])[ ]*", "\1", "ALL");
      return ObjectHTML;}
</cfscript>

This example takes the finished page when called, passing the variable of a CFSAVECONTENT to the UDF works particularly well. The UDF itself is just a set of three Regular Express arranged to remove spaces, tabs and linefeeds from the HTML client-side of the page (does not affect the CFM page) to deliver a compacted view of the HTML to the browser.

The page will display exactly the same, compressed or not, this is because the tabbed (or spaced) code encapsulation we do is for our benefit. Reading, modifying, and continued development on non-compressed code is a lot easier for a human to read than a paged compressed to one continuous line without breaks, tabs, or spaces! Browsers don't have that problem and don't need the extra content to display the end result

If you find this post useful please leave a comment and let me know how you used the information.