Using CFAJAXPROXY to Check Yourself Before You Wreck Yourself!

Many web apps that are used for CRUD activities (INSERT, UPDATE and DELETE statements) employ tables that simply have what is often referred to as a manufactured key. Most people more commonly know this as an auto-increment or auto-numbered identity style of primary key and it is typically stored as a data type that's an integer of some sort. This is great until you're working on something a bit bigger, say a business app, and you want the key to a subsidiary table to hold meaning to the end user throughout the app. Perhaps you're setting up location codes or category codes for instance, the value 5 will probably not mean a lot to an end user when filtering inventory reports by location code and it unfortunately forces the user to perform mental lookups to remember that 5 truly means a location of 'Chicago'.

So let's say that you make the decision to structure lookup tables using user defined key values - would you know how to use CF to help maintain referential integrity in your database while not endlessly frustrating your end users?

Take into account that when you're working with a manufactured primary key you can perform an INSERT and simply move on - clean and easy - which is why most web apps work using manufactured keys. If you want to use a table where users define the key values then you will most definitely add a level of complexity to the simple task of inserting a record. Now you'll have to add logic to check that a user's new key value doesn't already exist in the table before you try to insert their newly created record. This type of primary key setup will also affect updates as you'll now have to compare the key of an existing record against the records that are already in the table to make sure that you're not allowing the user to change a value like 'Chicago' to 'Indianapolis' only to return an error because there is already a key value of 'Indianapolis' in the table.

A great way to gracefully handle these types of scenarios is by using CFAJAXPROXY to call functions which check for the existence of a key before trying to INSERT or UPDATE your table.

For example, we have a table named Warehouse which holds Warehouse Code values. There are two cfc methods that work together to maintain uniqueness in the table while avoiding nasty errors for our user on the client side.

Our first function is a simple query that gets a Warehouse table record...

Our second function in warehouse.cfc is responsible for checking for the existence of a Warehouse and we will use checkForExistingWarehouse in conjunction with getWarehouse before we allow a user to execute an INSERT or UPDATE statement against our table.

On the client side we will make use of CFAJAXPROXY to remotely call checkForExistingWarehouse as illustrated below...

There are a few important concepts to cover here but I'll stick to the basics. First, recognize that I'm using some FORM variables to set the original value of the primary key, WarehouseCode in this case, if we're allowing a user to UPDATE an existing record. You'll notice in the validateFormData js function that if our run mode is Create then we automatically must check for the existence of the key value that the user is trying to create. If we're doing an Update then we only check if the original value is not the same as the current value of the cfinput control named warehouseCode. This is simple to do using the ColdFusion.getElementValue function as shown above.

So if we encounter either of these scenarios then we simply call checkForExistingWarehouse using an instance of proxyWarehouse and it will in turn take advantage of getWarehouse. If getWarehouse returns a record then we've got a potential collision and checkForExistingWarehouse will return a true and we can notify our user that they need to enter a unique value. If not then we submit the form and live happily ever after.