Filtration of records on Coldfusion end.

[Coldfusion_Newbie]

Hi All,

I am a newbie to Coldfusion. My experience is predominantly in Oracle. I am caught up in an issue and would like to discuss the same in this forum.

Technical Specifications : Database - Oracle 8i, Front End - ColdfusionMX 6.1

Requirement : I have five drop down's in a page. They are District, Property, Floor, Room Types and Room. The desire is to have them dynamically populated. By default based on user's login credentials the drop down would be populated with default values. When the user decides to change the district, the property drop down should contain properties corresponding to the chosen district, similarily, Floor depends on Property, Room Types depend on Floor and Room depend on Room Types.

Technical Approach : As of now, i have developed a stored procedure that returns all the various combinations for the five drop downs. There are about 80,000 rows for these combinations. The backend is in Oracle and the query joining the tables takes about 3 seconds in the backend. The entire result set is sent across to the front end. The front end loads all the drop downs with the relevant details and does some amount of filtration based on user selection. The page load takes 93 seconds !!

Alternative approaches : Have tried, cfquery. But the limiation with this, was we had to use a form within a form and hence, that was not possible. Either the form is not recognised or , when we try to submit the form, the page itself is submitted and the error thrown, is that not all the values are available. We have tried arrays on the coldfusion side, but not much has improved.I have even tried optimising the database query, which takes insignificant time at the backend itself.

Requesting Suggestions : If you all have solved such an issue, please let me know alternative ways to tackle the same. The issue seems to take too much of time on the front end and the page load alone takes 93 seconds. Also, if there is a way in which a form can be called within a form, in a manner that submitting the child form does not submit the parent, or if there is any other out of the box solution, please share the same.


Cheers,
Jay

PS: Apologies if the description was too verbose.

[TurboMini]

Use ajax to query the db for the necessary rows after they select an item in the dropdown. You will need a second coldfusion page that outputs only data to the ajax process.

[Coldfusion_Newbie]

Hi Phil,

Thanks for the reply. I havent used AJAX ane my team does not possess this skill set. Within the framework of Coldfusion and Oracle, would there be a solution to this problem?


Cheers,
Jay

[cf_dev2]

The old way of doing it was to use a single form. When a selection in the district list changes, submit the form. When the form is submitted, run the appropriate queries to populate the other lists based on the selected district. When another list selection changes, submit the form... etc.

[cf_dev2]

If you're using CF8 it has some built-in ajax functionality IIRC.

[Guest]

I would suggest using iFrames for your selections. I realize it's not on the level of AJAX or Flex for being robust but you could have your five selections each within an iFrame.

"The entire result set is sent across to the front end. The front end loads all the drop downs with the relevant details and does some amount of filtration based on user selection. The page load takes 93 seconds !!"

If your selections won't change often could you cache this "entire result set" in CF instead of using a SP? This could at least reduce your query time quite a bit. Maybe look at caching the top 20 or so drop-down choices as well?

[Newsgroup_User]

You are dealing with a fairly sophisticated user interface here. Most
of these issues are language independent but rather deal with the nature
of internet applications and the stateless nature of HTTP requests and
responses. You are dealing with a basic "related selects" problem,
where the choices of one select control are dependent on the selected
value in another control. There are many solutions to this interface,
but the size of your requirements limits some of your choices.

The oldest, and thus the most backwards compatible, is to divide the
steps into separate forms. But this is also the most primitive and
least user friendly solution. To implement it you would just make a
form for the first value, once the user has selected the value it is
submitted to the server and a new interface is delivered with the second
control and it's choices relative to the selected value of the first
control.

With the addition of some JavaScript these forms could be included into
separate pop-up windows or divisions, but this does not really improve
the interface much and could be impacted by users with pop-up blocker
add-ons to their browser.

You could deliver all the data to the client as JavaScript arrays and
then build JS functions to access and filter the various controls
depending on the choices of other controls. But the size of your
dataset probably prohibits this. It would take a very long time to
deliver that much data to the client every time.

The next level would be to use modern AJAX techniques. This allows you
to make behind the scenes requests to the server to get the required
data to update select controls after the relevant value has been chosen
in the related control. This would be one of the top choices if you can
rely on the JavaScript that AJAX requires and have the capability to
learn the various technologies involved.

The final solution I can think of, is something Flex based. One of the
strengths of Flex is that it is not stateless. It can maintain a
connection with the server to send and receive data as the state of the
interface changes with user interaction. But as with AJAX this is even
more new technology to familiarize oneself with.

Feel free to ask more questions about the various options or how to
implement one or more of them.

Ian