is there a CF 4.0 equivalent to a callable procedure or user defined function?

I want to have the ability to call a db retrieval SQL script varying the ORDER BY values, based upon a user clicking a column header in an HTML table. Then I would call a formatting procedure which handles the query resultset.

I know I can embed JScript functions, which seems like a flaky solution, but can they in turn somehow call a CF procedure which handles the formatting of the data?

Perhaps CF is just not procedurally oriented like VB.

I suppose I could create another CFM file which selects and formats the data and pass the user selected column name as a part of the URL. The 2nd page would have a 'Default Sort' button which would return to the 1st page. Talk about making mountains from molehills!

It's not clear what you are asking for. If you want a dynamic WHERE clause in your SQL statement, you can do this simply by embedding conditional logic (CFIF, etc.) in the cfquery.

Sorry 'bout the confusion. I have a page which is a drill-down from another higher page. It populates a 4-column table from 2 SQL queries, the 1st retrieves 'primary' data and the 2nd retrieves 'secondary' data. The table is by default sorted by the data in column 0. The user would like to be able to click a column header and have the table resort based upon that selection in ascending order. A 2nd click of the column would resort in descending order, i.e. like in Excel or other Windows apps.

At first, I copied code from here which basically resorts a table, ascending only, without re-retrieving the data from the database. Being new to CF, I was unable to get the code to handle descending order. I've now resorted to re-querying the db for each sort selection. We're talking basically 10-20 records from a server side Access db.

My plan at this stage is to utilize the existing page which displays the sorted 'primary' and then 'secondary' records. Upon a user click of a column header, I'll utilize a custom tag file which will query the db based upon the selected column and display the combined 'primary' and 'secondary' data.

When I wrote this thread I was thinking I would write 1 procedure in the CFM to retrieve the data utilizing CF parameters initialized to the 'default' sort. My table column headers would then call the same procedure passing a literal value corresponding to the desired sort column in the table. This may still work if I can figure out a custom tag.

Sure, you can do this. Just pass a URL variable in each column header link so the query knows which column to sort on, and whether to sort ASC or DESC. Then use conditional logic in the query to create the appropriate SQL in the ORDER BY clause.

Example...lets say column 3 header is "Quantity" and the database field for that column is "quantity". The header link would be something like:

<a href="mypage.cfm?sortcolumn=quantity&sortorder=asc">Quantity</a>

Then your database query would do something like:

<cfquery...>
select * from blah where blah
order by #url.sortcolumn# #url.sortorder#
</cfquery>

You could set defaults for url.sortColumn and url.sortOrder in the query file so that by default its sorted on the first column. You'd need to add some conditional logic to the display page as well so that it knows whether to put "asc" or "desc" in the link, but you should get the idea.

Thanks.

In an effort to reuse code, I'm trying to have the initial display separating the 'primary' and 'secondary' data utilize the same custom tag and then have the column header links be column specific. I've just finished writing a custom tag which will handle both the initial display, as well as the column header clicked data.

Per your note a custom tag (CFM) can be the object of a hyperlink.

The code I've written is utilizing a technique to create a "query" to return to the caller, via the QueryNew, QueryAddRow and QuerySetCell methods. It also makes use of the CALLER variable. I wonder if these techniques are supported in CF MX, since we will probably migrate to that version in the not-to-distant future.

CFMX is fully backwards-compatible with previous versions with a few very limited exceptions (custom tags work fine).

Thanks for your assistance. Is Raleigh your home? I used to live there for awhile, in a little town named Bunn, near Louisburg. The people there are the nicest I've ever run into.

Unfortunately, the 'start-up' I was with, making wearable computers went under due to a minor difficulty with copyright infringement and gross mismanagement.

Have a good week!

Per your statement:
<a href="mypage.cfm?sortcolumn=quantity&sortorder=asc">Quantity</a>

Are the parameters specified in this code equivalent to the custom tag 'attributes'? I assume the called custom tag would access them like:
'url.sortcolumn' vs 'attributes.sortcolumn'

Can one actually change the value of a variable in the CALLER's scope via code like:
<CFSET CALLER.variable = "OK">

The URL values are NOT translated to the attributes scope. The only way to get things into the attributes scope is to pass them as attributes to a custom tag, or to explicitly create them (like <cfset attributes.myvar = "foo" />).

Yes, using the CALLER scope from inside the custom tag sets variables in the calling page's local (aka VARIABLES) scope.

would the 'cfset attributes.myvar' occur in the custom tag? If so, then how would the caller set them?

I thought I read something where the custom tag is defined by name with the attributes, within the caller. Then they are set later with the values and the custom tag is referenced.

The attributes are specified by the caller, like this:

<cf_myCustomTagName name="foo" dept="bar">

in this case, attributes.name and attributes.dept would be available inside the custom tag.

Thanks on the attribute settings within the custom tag call. The 'name' attribute I use in the custom tag as the name to assign to a NewQuery structure. It is performing a SQL retrieval and I'm populating this NewQuery structure with the data. I'm attempting to access the query structure back in the caller code as follows:

<CF_Get_Sorted_Data Name="Device_Results" Field="Default">
<CFOUTPUT "Device_Results">

Unforunately, I'm getting a "Just in time compilation error" so I'm not sure this will work. I also don't have the Studio installed, so I don't have online debug capability. Does this code look correct to you?

The problem is that your use of the custom tag is incorrect, as is your use of cfoutput. As these are both extremely fundamental elements of CFML, I'd strongly urge you to get a good book (Ben Forta's are very good) because you're going to have a very difficult time until you get the basics down.

First, custom tags dont explictly return a value; that's what functions are for. A custom tag can output data from within itself or set values in the calling template. If you are using the name attribute to dynamically set a value in the calling template, then that should work, but otherwise I'm getting the impression that you're setting name to "device_results" in the custom tag attributes and you are expecting it to automatically set a variable called "device_results" in the calling template.

Second, even if you are successfully setting a value to "device_results" in the calling template, you'd output it like this:

<cfoutput>#device_results#</cfoutput>

Again, the use of something like cfoutput is so basic to using CF that I must urge you again to go through a good book on CF. Things will be much clearer (and easier)!

I agree that learning Cold Fusion is and probably will be very difficult for some time to come and I appreciate your patience!

I've been referencing a Forta book entitled "the Cold Fusion Web Application Construction Kit - Second Edition". There's a sample in Chapter 33 Custom Tags, named SPIDERSUBMIT.cfm. I've been using the structure within this sample as a guide.

In rereading his notes regarding the creation and usage of a query, I may have assumed incorrectly, that the query would then be available to the caller.

He stated that "if the user requested feedback, another query is built, this time in the calling templates scope. This query is again constructed using the previously mentioned manipulation functions, and it allows the caller to check the status of each submission to each spider or search engine."

In my tag this is where the ATTRIBUTE.Name comes into play, the tag uses it in the NewQuery command.

In conclusion he adds "...the data returned to the caller template is not hard coded to a specific name, rather, the user passes the desired query name, and the Custom Tag creates it."

I may be in way over my head here trying to reverse engineer and enhance a product in CF 4.0, but hey, I'll keep plugging away.

Thanks again and I'll stop bugging you with these elementary questions.

I'd just start with Chapter 1 and work through it. You can't really understand custom tags until you've gone through the supporting information first. Don't worry, you'll get there, it's actually really easy, you just have to go through it from the beginning.