dynamically get url or form parameters and set variables or query strings for cflayout calls, ajax binds, iframe or cfincludes, links or in-page use.

I have some 'main' pages which use cflayout to actually hold LOTS of pages. Some are content 'in' the main page inside the cflayout code and some are 'bound' within a cflayoutarea source or ajax call and some are 'cf includes'.

Some might even be a secondary situation, like an iframe inside a cfinclude inside a cflayoutarea bind which is inside the main page which had params passed to it from somewhere. Got all that?! Not saying I'm going to do that, but I might want to.

Because (thanks to cflayout) this holds what amounts to a whole website, directly or indirectly, all being called via the same page technically, it gets complicated.

There are three things I don't 'know' for this page. Partly because there are so many different files involved and calls to them coming from everywhere. Partly because I'm unlikely to remember later every possible detail in the whole site, which is what happens when you do hobby sites at 2am 'cause it ain't your real job. All these unknowns and possibilities need to be handled right up top, generically no matter what their situation, so it's simply all available to me in any form I need it later on in the page, and can even be applied to everything 'generically'.

That way, whatever it is that particular thing needs, will be included in there somewhere (and extra stuff is ignored by what we call).

Things I need to know:

Q: What parameters were passed when I loaded this page and what are their values?

Q: Did those parameters come a form or a url?

Q: Do those need to be written into an inpage var, or conjoined on a url-query string?

Bear in mind var names should be distinct in the site (so one thing coming in for page X, if applied to a query string for page Y, would not affect it), and that any bound/included/called pages using the vars we have here of course must use default cfparams and/or check for existence of their needed params.

<!--- get any and all parameters passed on the url --->

<cfset paramsurl = structKeyList(url)>

<!--- put a parameter in place in a new var, this will be ignored (like 1=1 in SQL) --->

<cfset paramsurllist = "x=0">

<!--- loop through the list of params --->

<cfloop index="ndx01" list="#paramsurl#">

<!-- see it for debug: <cfoutput><div>#ndx01# = #url[ndx01]#</div></cfoutput>-->

<!--- build the query string for use in links if needed --->

<cfset paramsurllist = '#variables.paramsurllist#' & '&#ndx01#=#url[ndx01]#'>

<!--- set the var for use in-page if needed --->

<cfset #ndx01# = "#url[ndx01]#">

</cfloop>

<!-- see it for debug: show me my query string <cfoutput>#paramsurllist#</cfoutput>-->

<!--- get any and all parameters passed on the form --->

<cfset paramsform = structKeyList(form)>

<!--- put a parameter in place in a new var, this will be ignored --->

<cfset paramsformlist = "x=0">

<!--- loop through the list of params --->

<cfloop index="ndx02" list="#paramsform#">

<!-- see it for debug: <cfoutput><div>#ndx02# = #form[ndx02]#</div></cfoutput> -->

<!--- build the query string for use in links if needed --->

<cfset paramsformlist = '#variables.paramsformlist#' & '&#ndx02#=#form[ndx02]#'>

<!--- set the var for use in-page if needed --->

<cfset #ndx02# = "#url[ndx02]#">

</cfloop>

<!-- see it for debug: show me my query string <cfoutput>#paramsformlist#</cfoutput> -->

<!--- now, work out generic all-inclusive string to add as needed to anything "pulled in" --->

<!--- if incoming url params existed --->

<cfif variables.paramsurllist is not 'x=0'>

<!--- make a generic caller param string from the url stuff. --->

<cfset addcallerparams = "#variables.paramsurllist#">

<!--- if instead any params came from a form --->

<cfelseif variables.paramsformlist is not 'x=0'>

<!--- make a generic caller param string from the form stuff. --->

<cfset addcallerparams = "#variables.paramsformlist#">

<!--- worst case just put our default param on there. --->

<cfelse><cfset addcallerparams = "x=0">

</cfif>

Now, every iframe, or cflayoutarea source, or other ajax bind, or a regular link, can simply have "?#variables.addcallerparams#" added to it.

<cfset mylink = "_2012a_daily.cfm?#variables.addcallerparams#">

(then use in context)

And in the cases when using that doesn't help at all, because passing these vars on a source parameter in cflayoutarea apparently doesn't work (you'd think they could mention that...), we already have the variables set in the page in that case. So any incoming content that is bound or cfinclude, will pick up the var's existence and value from there instead.

PJ

Validate Amazon URL in ColdFusion

All my images are on Amazon S3. Some of my records don't have images there -- or they do, but they don't have thumbnails. In listings, I tried the following things, with the following results.

1. Verify file exists. Doesn't work unless you do relative URL it appears.

2. Use HTTP 'trace'. That didn't work either. Got a 'forbidden' response to everything.

3. Use "IsImage" with the full URL. That didn't work either, it believed everything to be ok.

4. Use HTTP 'head'. Then check for 200 OK response code. That worked!

<cfset myfileref="full-path-to-image-filename-goes-here">
<cfhttp method="head" url="#variables.myfileref#">
<cfif cfhttp.statusCode is not '200 OK'>
<cfset myfileref="#variables.path-to-file#/icon-spacer.gif">

So when variables.myfileref is called it will either be the valid image or the spacer replacement image I have, if needed.

PJ

SMF, Simple Portal, and Cross-Domain Home Pages

SMF (Simple Machines Forum) is great software. One of the mods called Simple Portal is equally cool (if less complex probably). Another mod called Custom Pages (even more simple but robust) is as well. The 'custom pages' and 'portal blocks' can be html, php, or a variety of pre-set forum-based options. Between this forum software and these two mods, a person can do just about anything needed for a website as complex as you want to make it. This also means that you can pretty much click a button and make a logo and change the entire visual theme, because custom themes are also available for SMF. Redesign in 20 minutes.

For me, the problem is I use Coldfusion, so my forum is on a Linux php server (.info) while all my dynamic software is over on an NT cfml server (.com). I would really like to have more integration between these two, but it requires more coding-learning-curve than I have time for to get the forum and/or dojo's registration "integrated" in the two websites with different languages. I already spend hours I should be sleeping, working free for the cfml site. If anybody should know of something existing like this, I would trade money, sex or volunteer work for it. Maybe.

So I finally got the SMF+SP+CP combination set up, and the dilemma is:

How can I get my various cool-stuff 'display-only' from my coldfusion comserver, where all the action happens in my projects, to function or display "inside" my developing website in php on the infoserver?

Better yet, could it be possible to make it so people could browse my normally login-only coldfusion website from INSIDE the portal on the other server? Because I can control the permissions on that side, after all. They might get a nice "you need to register over here too" message if they want to add something but that would still make it way easier than having two completely separate worlds, which is the case right now.

These three php apps/mods (SMF+SP+CP) actually make this possible! Wow, the possibilities seem endless now!

For my first super-simple experiment, I made the index.cfm page on my coldfusion comserver work in both places. It sets a parameter called portal to default to 0 and uses IF. If someone arrives at the home domain there, they see some titles at the top, and a link to the forum at top right. Also the 'home' links are a bit different.



If they arrive at the portal home which is my PhP driven infoserver, they see that same included page but without the titles, icon, and different 'home' links, because the forum/portal already has a big title/nav. In my Simple Portal I created a block called 'welcome' which is Custom HTML, ignore permissions, show block on portal:



So what this proves to me is that I can make pages in coldfusion on one server, set them for certain parameters that will pull them directly into the framework in php on another server, and then control the access of those pages in the portal via permissions over there, so even if I normally would not want something to display outside the login, in this case if they're logged in over-there-instead that'll still work.

I think this will go a long way toward integration as well as to making more groovy stuff available to people who frequent the forum.

PJ

Multiple Records Insert (cfloop)

I was asked about this as a comment on a previous post on sortable lists. It used to be a question for me too so I thought I'd post it up top here.

Sometimes you need to take data in a form (or keep it in a table that is part or all form) that spans multiple records, so there are multiple 'groups' of values.

For example let us say we have these form fields: FirstName, LastName, Email. Normally, submitting these on a page is obvious. But what if we want to allow 5 of those? In fact what if the number is even adjustable by the user themselves (such as a javascript that lets the user add new form fields)?

Personally I use counter-loops and evaluate. I think there are more fluent ways to do this to be honest I just haven't had time to look them up and haven't had to do this for awhile. See end of the post for a note on doing the CFSET more properly on the post page.

Note: I'm ignoring most formatting, form validation, etc. in this example.

For our form, let's say we want to make 5 entries possible at a time. We'll do this:

<cfset recordQty = 5>

<form name="myForm" method="post" action="myPostScript.cfm">
<cfoutput><input type="hidden" name="recordQty" value="#variables.recordQty#"></cfoutput>

<cfloop from="1" to="#variables.recordQty#" index="ii">
<p>First <input type="text" name="FirstName#variables.ii#" size="30" maxlength="40" value="">
Last <input type="text" name="LastName#variables.ii#" size="30" maxlength="40" value="">
Email <input type="text" name="Email#variables.ii#" size="30" maxlength="45" value=""></p>
</cfloop>

<input type="submit" name="addNames" value=" Add ">
</form>

Notice we appended the loop counter number to each field. So on the posting page we can just loop through with a counter again. (See the posts on ROTA as I think some version of this was in there too.)

Posting page, I make sure my recordQty field exists and is numeric and set it as a variable. You may want to do some if-exists logic on the numbered fields. I don't always.

<cfloop from="1" to="#variables.recordQty#" index="n">
<!--- first we set up field names to match what the form should be passing. --->
<cfset newfirst = "FirstName" & variables.n>
<cfset newlast = "LastName" & variables.n>
<cfset newemail = "Email" & variables.n>
<!--- then we get whatever values are in those field names and set them as vars --->
<cfset finalfirst = "#Evaluate(variables.newfirst)#">
<cfset finallast = "#Evaluate(variables.newlast)#">
<cfset finalemail = "#Evaluate(variables.newemail)#">
<!--- then we do something with this, maybe say IF a key field isn't blank before we get into SQL --->
[SQL insert or update here, or create list or array for one or more of those fields, etc.]
</cfloop>

Quick and dirty that's it. Here's a note on the sets: Apparently it's better to use array notation when doing this, such as:

<cfset newfirst = variables["FirstName" & variables.counter]>

Haven't done it again since the ROTA to try it but that's a tip.

PJ

Coldfusion RSS Atom Feed: CFML for Making, Creating, Generating, Valid RSS

One of the things about 'helpful blogs' that show you code is that sometimes, they show you little snippets of code. You have to basically already know everything about the subject in question, already know how to put everything together, already know how to call it, and THEN yes, that little snippet of code in the middle will be helpful. What that mostly means is that a lot of people searching will find page after page of how-to and not be able to use any of them.

I was feeling less oblivious than usual when I found Pete Freitag's old Sep 2005 blog post on 'How to Create an RSS Feed', but reading the comments made me feel better. First he left a ton of people in the dark and then he didn't respond to much of any of them. This is the honor of being a code guru I guess. Anyway, since I managed (thanks to him, and to one of his comments, and to further tweaking using the validator and trying-again) to get it working, I thought I would post the full code for my solution for others.

Possibly helpful points for new folks:

• Generating an RSS feed (information about your website) is ONE thing; actually reading that RSS into a page is ANOTHER different thing.Two separate scripts/efforts.
  
• Getting the RSS to take a user to choose their reader etc. to put that RSS into, is merely a matter of calling the file -- I mean providing the link to your file with that RSS XML in it.
  
• If you have to use CFML to generate your code for the XML, then obviously, it has to be sitting in a .cfm file. It turns out it doesn't matter what the file extension is--as long as the XML inside it is recognized/validated as an RSS feed.

So we have the first part, akin to technical META headers:

1. XML version and encoding tag
2. RSS version and content type tag; I'm using an IF statement from Pete F there from a different one of his blog posts
3. CHANNEL tag which opens the feed
4. Atom link with application type tag
   

The next section is the RSS equiv of non-tech, content-based META headers:

5. TITLE the actual title of the feed itself, maybe same as website
6. LINK the link to the website the feed is representing
7. DESCRIPTION basic info about what this site/feed is
8. LASTBUILDDATE this is "lastupdate" info about the overall feed itself. Since mine is building from the datatable when called I put Now() on it.
9. LANGUAGE that's pretty obvious I think
   

The next section is the actual content of the feed -- in my case, looping records from a CFQUERY call.

Note that any CFML code I needed to put in here, I put to the right of a basic tag -- so it would not mess up the display of the XML itself when generated to the final file, except the query cfoutput which I let take up lines so the output would display better in code. I have no idea if this is necessary, it just seemed like the thing to do... now I'm thinking, probably doesn't matter. I dunno. You can try it without that.

10. ITEM open tag
11. TITLE the title of this individual record, post, entry, etc.
12. LINK this is the link to the individual record, post, entry, etc. so if someone clicked it, it would take them right to the page in question. (In my case this is behind a login but oh well. If logged in, the link works fine.)
13. GUID with the isPermalink parameter--for me this is the same as the item above.
14. PUBDATE the date of the individual post/entry/etc. in question.
15. DESCRIPTION whatever description, listing, etc. you want for the item in question
16. ITEM close tag
    

and when the loop is finished then

17. CHANNEL close tag
18. RSS close tag
    

and you're done.

You will want to post your code file to your remote server and then go to a validator and enter the link to it, so it can check it for you. The validator I used is: http://feedvalidator.org/

Some notes on the details of this code that might be useful:

• It wants date formats in this format: #DateFormat(Now(), 'Ddd, dd mmm yyyy')# #TimeFormat(Now(), 'HH:MM:SS')# EST although it would prefer GMC timezone.
• The XML must be perfect and there's a good chance that something, at least your 'description' area, might violate that once inawhile, with a character it doesn't accept. There are two different ways to deal with this, and I used them both, nested:
• The CDATA tag, and XMLFORMAT function, which looks like this in practice:
• <![CDATA["#XmlFormat(myquery.mydescription)#"]]>
• although you'll note in the code below I had other info in there and a few separate calls to the function. Note that Pete's article had an error in this part, but blessedly some commenter corrected it or I would have been there all night I imagine.
  

The full code is below, albeit with query details changed.
The link to this code in practice is: http://www.dojopsi.com/tkr/rss1.cfm

<?xml version="1.0" encoding="utf-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">
<cfif cgi.user_agent contains "Mozilla"><cfheader name="Content-Type" value="text/xml"><cfelse><cfheader name="Content-Type" value="application/rss+xml"></cfif>
<channel>
<atom:link href="http://www.dojopsi.com/tkr/rss1.cfm" rel="self" type="application/rss+xml" />
<title>Remote Viewing at Ten Thousand Roads: TKR at the Dojo Psi</title>
<link>http://www.dojopsi.com/tkr/</link>
<description>Double-blind Remote Viewing Sessions Posted at TKR</description>
<cfset nowdt = "#DateFormat(Now(), 'Ddd, dd mmm yyyy')# #TimeFormat(Now(), 'HH:MM:SS')# EST">
<lastBuildDate><cfoutput>#variables.nowdt#</cfoutput></lastBuildDate>
<language>en-us</language><cfquery ... name="dslist" maxrows="50">select id, entrydt, tasktype, target, method from mytable where ... order by entrydt desc </cfquery>
<cfoutput query="dslist"><cfset target = "#trim(dslist.target)#">
<item>
<title>#XmlFormat(variables.target)#</title>
<link>http://www.dojopsi.com/tkr/rv/galleries/peanutview.cfm?dsid=#dslist.id#</link>
<guid isPermaLink="true">http://www.dojopsi.com/tkr/rv/galleries/peanutview.cfm?dsid=#dslist.id#</guid><cfset dst = "#DateFormat(dslist.entrydt, 'Ddd, dd mmm yyyy')# #TimeFormat(dslist.entrydt, 'HH:MM:SS')# EST">
<pubDate>#variables.dst#</pubDate>
<description><![CDATA["RV #XmlFormat(dslist.id)# > Type: <cfif dslist.tasktype is 2>Practice<cfelseif dslist.tasktype is 6>Mission<cfelse>Solo</cfif> > Method: #XmlFormat(dslist.method)#"]]></description>
</item>
</cfoutput>
</channel>
</rss>

Hope this helps someone else someday.

I'll have to be doing the script to compile and display an RSS feed at some point, and I'll post it when I do.

PJ
.

Date Loops for a ROTA

As part of the ROTA application I had to build a very simple way for a user to say, "Starting on this date, assign something weekly for X weeks." This meant I had to get the start date, the quantity of weeks, write the dates and then loop them in as rows in a table. Later I'd use the row IDs for the outside of a nested loop to assign people to these date-events.

Not much time to explain laboriously here but am posting my code just in case it ever helps someone searching.

I get from a form the qty of weeks and the start date.

<cfset rotaweeks = "#form.rotaweeks#">
<cfset rotadt = "#dateformat(form.rotadt,'mm/dd/yyyy')#">

I actually go back a week so the script can then regularly go forward a week in the loop.

<cfset newday = #DateAdd("d", -7, "#variables.rotadt#")#>
<cfset newdaylist = "">
<cfoutput>
<cfloop from="1" to="#variables.rotaweeks#" step="1" index="i">
<cfset newdayA = #DateAdd("d", 7, "#variables.newday#")#>
<cfset newday = "#DateFormat(variables.newdayA,'mm/dd/yyyy')#">
<cfset newdaylist = "#ListAppend(variables.newdaylist, '#variables.newday#')#">
</cfloop>
</cfoutput>

That leaves me with a list of dates. I set and format some vars for the edges of that date range.

<cfset firstdt = "#ListFirst(variables.newdaylist)#">
<cfset afterA = '#DateAdd("d", -1, "#variables.firstdt#")#'>
<cfset after = '#DateFormat(variables.afterA,"mm/dd/yyyy")#'>
<cfset lastdt = "#ListLast(variables.newdaylist)#">
<cfset beforeA = '#DateAdd("d", 1, "#variables.lastdt#")#'>
<cfset before = '#DateFormat(variables.beforeA,"mm/dd/yyyy")#'>

<cfset listqty = '#ListLen(variables.newday)#'>

I give users the option to remove all previous entries from that date range.

<!--- if checked, remove previous entries for this team between those dates (if they are replacing/modifying the rota) --->
delete from myrotatable WHERE
teamid = #variables.teamid#
and rotaid = #variables.rotaid#
and rotadt > '#variables.after#'
and rotadt < '#variables.before#'

Then the dates get written into rows in a table

<!--- then insert the dates in table --->
<cfloop list="#variables.newdaylist#" index="ii">
insert into myrotatable
(rotadt, rotaid, rotateamid, ...)
values
('#ii#',#variables.rotaid#,#variables.teamid#, ...)
</cfloop>

I can grab the record IDs of those dates and loop through them, sub-looping my list of userIDs, to create the rota. See the post Rota, Dynamic Variables and Nested Links for the rest of it.

ROTA, Dynamic Variables and Nested Loops

Fresh from the land of nobody-can-help-me-I'm-doomed, I discovered (as usual) that I was trying to make something a lot harder than it needed to be. Hate it when I do that. Then the relief of solving it is tempered by knowing I was an idiot and it should have been obvious in the first place. In any case, onward:

I never knew why I might 'need' something like 'dynamic variables' but here is where I did. I've been working on a ROTA. This is a list of user assigned slots/dates. A baseball team lineup is a good example of a rota. A list of three kids and the chores they take turns doing, or a few employees and the tasks they take turns doing, would be a rota.

In my application, there are "teams" (groups) of people who change off weekly setting up a fun assignment for the rest of the team. This means in my case, there are generally far more dates (weekly assignments) than people (team members), so there is the big loop through dates and then the smaller sub-loop through the members, which repeats until all the dates are filled.

Example:

John (userID 93), Jane (userID 156), and Alex (userID 418) are the team;
The dates are weekly, every Monday for 7 weeks.

So when we end this we should have something like:

week 1: john (93)
week 2: jane (156)
week 3: alex (418)
week 4: john (93)
week 5: jane (156)
week 6: alex (418)
week 7: john (93)

This was easy to code except I screwed up the dynamic variables part. Just overcomplicated it.

In general the building process worked like this:

* build rota info including settings, rota name and rota ID, start date and qty of weeks
* get team info including list of user IDs, Aliases and team ID
* show team members for a drag&drop sequence (see Drag & Drop Sortable Lists)
* that list is going to post as a list of the userIDs: form.userid = 93,156,418

Then on the next page the assignments happen.

These team members are being looped in sequentially, and this is going to be controlled with a simple counter variable. So up top we set a list of our user IDs in dynamic variables using the counter. That way, LATER when we are doing the loop through dates, we can use the counter to rebuild what the variable should be named and then evaluate the name to get the user ID.

<!--- set up a numbering list for the user IDs. eg 'theuser2' = 156. --->
<cfset counter = 1>
<cfset qtyusers = "#ListLen(variables.userlist)#">
<cfloop list="#variables.userlist#" index="i">
<cfset "theuser#variables.counter#" = "#i#">
<cfset counter = #variables.counter# + 1>
</cfloop>

* query for the record IDs of the list of dates, now we have a list to big-loop through

To see how I created the weekly dates, see Date Loops for Rota

* our list of user IDs is our small list to sub-loop through

<cfset counter = 1>
<cfloop query="myquery">
<!--- we have fewer users than events. user numbering starts over. --->
<cfif variables.counter GT variables.qtyusers><cfset counter = 1></cfif>
<cfset thisuserA = 'theuser' & '#variables.counter#'>

{another query here gets user info}
then the query that updates the record says:

update table SET
rota_userid = #evaluate(variables.thisuserA)#

<cfset counter = #variables.counter# + 1>
</cfloop>

And that's it. I really can't believe that I've started and stopped on this several times over 3 years -- previously it was insanely complicated. A couple months ago the solution fell into my head and I thought "wow, so simple!" and I wrote the code superquick, and it worked perfectly right up till the end -- when it crashed and burned on the dynamic variable.

It was telling me the dynamic variable I had set was not found. Even though I could display both its name and its value right above the line where it insisted it didn't exist. It turned out to be something funky about the way I'd been coding it that the evaluate function was crashing against. The above code works fine.

PJ

Simple Machines Forum SMF Lessons

Trivia. Who knows I may need this again someday.

V 2.0 Beta 4

Reported bug: display in 'attachment max size'. 0 is supposed to be unlimited but it looks like it's just spec-ing zero.

Avatars: Can only be one folder deep. Cannot be seen from admin, only from profile. Uploaded avatars go to attachments folder not avatars folder.

Smileys: Can only be one folder deep in 'smileys'. It will only use one folder at a time so whatever is in the folder you set as the default folder (not to be confused with the folder actually called 'default', unless you spec'd that as the default set) is all that is going to show for the users, so dump everything in the default set and then sequence them to put what you like on the post area vs. pop-up (more) area.

Message Icons: reside in themes/default/images/post no matter what theme you are using. So any message icons you want avail you have to copy over there, then manually add each filename in the admin area.

Profile fields: You can add a field like 'about' for each user in admin custom profile fields long-text. Unfortunately it doesn't seem to show more than a fraction in the actual profile display... might be a bug.

Posting javascript mods: I was able to mod the .js to allow a lot more fonts, but when I added any sizes at all it broke it sorta.

I really love SMF. I've hacked the hell out of the babylon theme to remove a ton of crap from all over the place, I've replaced all icons with 32x32 square clean ones, and it's the CLEANEST forum design I've ever seen.

Creative Error Messages

My brain is frying. While trying to figure out a bug in Taskerbot -- which mysteriously has assigned just SOME tasks of SOME viewers SOMEtimes to the mysterious userid 9 which does not even exist -- I finally gave up and decided to just remake the whole form/script and do it differently.

I originally coded it in the context of TKR which is such a pain in the butt because of the identity/privacy issues and not being able to just use the USERID all over the place like normal. tBot doesn't have that issue and is going to be standalone so I'm redoing it the way it oughtta be.

While I'm at it I redesigned the top border, and the footer, and so on.

Then I decided my error messages need to display more prettily.

Then I decided that if I'm going to be writing code I could at least be creative.

So my new rule is that all error messages have to be in some form of poetry. By default, haiku, but it could be something else.

This is my sense of humor. Users will have to live with it.



This is the code that calls it (inside a CFIF tag):

<cfset errtype="notice">
<cfset errdetail = "localtask_detail p=step3 IF check viewer tasker must be session identity">
<cfset errmsg = "Avast, Imposter!<br /> You're not tasker, nor viewer!<br /> We hoard it from you.">
<cfinclude template="localtask_error.cfm">

And this is the localtask_error.cfm template:

<cfparam name="errtype" type="string" default="notice">
<cfparam name="errdetail" type="string" default="no code defined">
<cfparam name="errmsg" type="string" default="Programmer failed you<br />No error detail is here<br />Sorry. Try again.">

<div align="center"><table width="600" style="border:0px solid black; color:666666; padding:0px; margin:5px; margin-top:50px; ">
<tr valign="middle"><td align="left">

<fieldset style="clear: both; font-size: 125%; border-color: 990000; background-color: ivory; border-width: 1px 2px 3px 2px; border-style: solid solid solid solid; padding: 30 15 20 20; margin: 4 4 4 4; font-family:Calibri,Candara,Trebuchet,sans-serif; color:black;">

<legend style="font-size: 85%; font-weight: normal; color: 990000; margin: 0 0 0 0; padding: 0 5px;"><span style="color:gray;">tBot TASKER</span>  
<cfif errtype is 'notice'><span style="color:990000; font-size:150%;">Notice</span> <span style="color:silver;">| Error | Omission</span>
<cfelseif errtype is 'omit'><span style="color:silver;">Notice | Error |</span> <span style=" font-size:150%; color:990000;">Omission</span>
<cfelseif errtype is 'error'><span style="color:silver;">Notice |</span> <span style=" font-size:150%; color:990000;">Error</span> <span style="color:silver;">| Omission</span>
</cfif>
</legend>

<cfoutput>

#errmsg#

<br />
<br/ >
~ dojobots
<br />
<br />
<br />
<small><span style="color:gray; font-size:80%;">#errdetail#</span></small>

</cfoutput>

</fieldset>
</td></tr></table>
</div>

While I was at it, I also replaced the main application file error template. This is the one that comes up for CFML or SQL errors (or just before the webserver reboots, or just after it has and the database isn't fully up yet again, or...) to be something a lot more cheerful than the last error page, for both TKR and TBOT:



PJ

CF is Less Girly

OK so the girls can sue me, but I think this image a couple folks use as sig blocks on a forum I frequent is just HILARIOUS. Go Arnold!

Drag & Drop Sortable Lists

I'm working on the ROTA module of Taskerbot now, which I've started and stopped at least three times. Par for the course, I've mostly forgotten whatever the hell I was doing last time I was working on it, so I'm starting over. Again. On the bright side, three years of occasionally working on the idea finally seems to have gelled in the back of my little tiny brain, and now I actually think I can do it, and make it fairly simple in fact, unlike the complex coding behemoth it began.

Tonight the challenge was the basic creation of the sort. The manager takes a list of his members and using simple drag & drop javascript, puts them in the sequence that he wants for event-assignment (usually, tasking).

First of all, there are some precompiled javascripts that help do all kinds of things more easily and more cross-browser compatible, so I went and got those. They are Prototype and Scriptaculous. I got the latest .js (javascript) file from each of those and put it in my directory, and added a head call to each.

<script language="JavaScript" src="../tbotscripts/prototype.js"></script>
<script language="JavaScript" src="../tbotscripts/scriptaculous/scriptaculous.js"></script>

OK, so that is set. (Note that scriptaculous is actually a small library of files; you need to put them all in the same directory, then just call the one.)

Then after searching I found a script (and I can't recall the source or I'd link to it) for the "sortable" feature.

However I could not figure out how to make it work with DIVs as I allegedly can use. Nor could I figure out how to make it write to a hidden HTML form field. After screwing with both of them for quite some time, I finally thought, why am I making this so hard? Yes it would be nice if I had the neat canned javascript for that but I don't, and there is definitely a manual way around it. These items are going to be looped out from a cfquery after all so it's not rocket science.

So I formatted the LI list elements to look like DIVs. And then I put a hidden field inside each of the list elements that would carry the userid (UID) value. When I call the name of that form field, it will automatically give me a comma delimited list of all the values set with that field name, in the sequence they are shown on the HTML form. I tested it and it works just fine.

So I added this to my HEAD section:

<style>
li { list-style-type: none; border: 1px black solid; padding:3px; margin:4px; 
     margin-top:6px; background-color: FFFFCC; color: black; width: 300px; }
</style>

And then I added this to my HEAD section:

<script language="JavaScript">
function getOrder() {
  var orderList = '';
  orderedNodes = document.getElementById("sortable_list").getElementsByTagName("li");
  for (var i=0;i < orderedNodes.length;i++) {
    orderList += orderedNodes[i].getAttribute('recordid') + ', ';
    }
  alert(orderList);
  }
</script>

And here is a simple static version of the code for the sortable list in the page. Note the little script in there -- it must come AFTER the sortable list.

<form name="myform" method="post" action="working1.cfm">
<ul class="sortlist" id="sortable_list" style="cursor: move">
   <li id="1" class="sortlist"><input type="hidden" name="uid" value="1">name 1</li>
   <li id="2" class="sortlist"><input type="hidden" name="uid" value="2">name 2</li>
   <li id="3" class="sortlist"><input type="hidden" name="uid" value="3">name 3</li>
   <li id="4" class="sortlist"><input type="hidden" name="uid" value="4">name 4</li>
   <li id="5" class="sortlist"><input type="hidden" name="uid" value="5">name 5</li>
</ul>
<script language="JavaScript">
Sortable.create("sortable_list");
</script>
<input value="Submit" type="submit" name="sub">
</form>

Of course if it were dynamic, the output would be more like:

<cfoutput query="myqueryname">
   <li id="#myqueryname.uid#" class="sortlist"><input type="hidden" name="uid" 
value="#myqueryname.uid#">#trim(myqueryname.username)#</li>
</cfoutput>

(No wrap in the LI line above--the PRE code puts it outside the display is all.)

>> Here is a full simple demo <<

Anybody should be able to copy the view-source from that and make it work.

Of course, this is actually the EASY part. It's working out all the dates and the looping that is considerably more work. That comes next!

PJ

Little Import Utility

Me and a friend were talking about setting up Remote Viewing tasks as an experiment, using a number, words, etc. to see if we would get symbol, or be able to dowse for numbers / letters, etc. It turns out the utility I wrote for that last night will server as a lovely improvement to the existing "bulk self-tasking" upload option in Taskerbot.

Basically, in the new utility, you have a 'settings' page where you can write your own 'psychic intent' as a default, and set the visual style you want your feedback in. (So you can make big red letters on yellow background in Impact font, for more effect.) Then you put whatever you want to 'view' -- numbers, words, phrases, whole specific tasker intents, reference to other existing task numbers, whatever! -- in a single column in a spreadsheet (simple) and click to import. It will create up to 250 new tasks on the fly. The system would let you have 3000 chars but spreadsheets will limit it to 250.

The existing 'bulk self-tasking' is a simple form that lets you type in "task number references". But (a) it's all online and (b) it only allows 25 characters. Because that is tying into the 'my task reference# field', whereas the new utility is actually using the text feedback field. And so in this one you could even use html or an img ref or brief EMBED code if you wanted so it could be pretty robust.

***

The "style" thing is a quick-and-dirty HTML that I made with as much find/replace in Textpad (my beloved Textpad...) as possible:



***

The combo CFLAYOUT-CFGRID I've been working on for the RV Galleries has not only fried my brain but is at the point of making me cry. The same queries that work just fine on the live site cause a timeout when I try to run them for the grid on my local... I have no idea why. Every time I get something working, nearly every thing I do after that breaks it (and breaks each thing I accomplished before them) and it needs re-re-re-fixing. Some of it's because the middleware just needs more/better development... there are 'quirks' especially in multiple things working together and often stuff just flat out doesn't work, or works fine alone but even though they are supposed to be wholly separate pages, when combined into the tab layout, changing a stylesheet on one screws up another for example. It's never ending. I have to take a brief break from that before I burn out entirely, and do something else that's constructive. So last night it was "Strings" (Numbers-Words-Phrases or NWP) bulk import utility.

***

Back to the utility: So basically it is using the groovy CF to Excel custom tag that my server company makes available. It's ridiculously easy. Basically you spec the tag, and it saves the content of the spreadsheet as a query set. Then you do a "query of query" for the data. And in my case, then loop it out so you can put a variety of unique/other info into the insert.

<cfx_excel2query r_qResults="importthis" file="#form.importxls#" startRow="1" maxRows="250">

That creates the query set of all the values in the spreadsheet.

<cfquery name="importit" dbtype="query">>
select COLUMN1 as myfeedbackraw
from importthis
</cfquery>

This goes and gets just the first column of values, in this case.

Then I loop through. Weird thing, I have scripts used in a dozen forms for generating random task numbers and so on, and they cracked in the loop, giving me all kinds of bizarre results. I couldn't figure out why, but decided not to deal with it, and instead just used a couple generated randoms for both the random task number and the unique 'task reference' number.

<cfset thenow = "#TimeFormat(Now(),'LL')#">
<cfset therand = "#RandRange(32,999)#">
<cfset therand2 = "#RandRange(32,999)#">
<cfset vwrtn = '#therand2#-' & '#variables.thenow#' & '-#variables.therand#'>

<cfset theTN1 = "#RandRange(2001,9999)#">
<cfset theTN2 = "#RandRange(3111,8999)#">
<cfset tasknum = '#theTN1# - #theTN2#'>

That used two randoms and "milliseconds" of a time for insert, to create a 3-3-3 digit 'task reference number' which really means nothing to the viewer but the system needs something unique there. It creates a 4x4 task number for actual assignment (the system task number).

Meanwhile, I use CFDUMP all over the place for quick outputs when nothing needs to be clicked on in that output. But the one on the import page has like a 5 point font. Go figure. I haven't figured out why it's so tiny yet.

Cleaning up the Display

CFGRID, combined with CFLAYOUT (tabbed variant) and CFMENU (simple, not dropdown, as the dropdown does not play well with the CFGRID where they overlap), certainly does make the pages look more clean and professional. And the amount of code in the actual CFM file is drastically reduced, over what I had to hand code (I do everything by hand, old school!) for a paginated list previously.

Old version of the Galleries comments page:



Coming-soon version of that, and the other pages (note the tabs near top):




All that white space at the top right makes me think I need to develop a logo.

**

My current pages take a long time to load, because they are going back to the database a zillion times for detail. My database server and webserver are both suffering far too heavy a load for the amount of data that's actually happening in the site. There's a lot of stuff that I "should have" done more of up front on that website, like views, stored procedures, and much better SQL that would put more of the processing in the database and less in the middleware, and much better middleware coding, that would minimize the amount of work and time.

The new grids load all the data in at once. The page/tab loads much faster than my ordinary pages, although then waiting for the grid to populate seems like it takes a bit but that's mostly just because instead of waiting for the page to change/load, that happened right away and now you're just waiting on the data. But, once it loads, it's all there. You can do a radical subset or opposite sort and pagination and it is nearly instant -- no waiting another long time for another page to load. So I can not only show the most recent comments in the galleries, but I can let someone click a filter to show comments to them, or from them, or anything from the oldest rather than newest date, and bam there it is. Stuff that would have taken a page load and another wait the old way.

I think not only is this going to end up saving a whole lot of load on my server, but I think it's also going to result in more pages visited, in part due to the speed and in part because I think the tabbed pages make it more intuitive to click through each one, than to 'go find' other pages in the former layout.

The one thing I expect will get to people is that we are all used to putting as many records on a page as possible so we don't have to change pages so often. The grid refreshes only the grid, not the page. That means if you add records so you have to scroll, when you press the 'next' button, the grid shifts--but it doesn't take you back up to the top of the page. You'd have to manually scroll up for that. But the grid is so fast to change pages that it really doesn't matter if there are fewer records in the main display. (I made an option for people to change it.) But I think that will take users some getting adapted to.

In the meantime though, I'll be much more willing to 'claim' the site to other programmers if it looks better. I once made this lovely, clean, elegant, white with blue and silver design there and showed it to a dozen people and like 1 or 2 liked it. All the rest said they LIKED what I called the 'circus nav', different colored buttons along the top of the page, etc. It was just painful. I'd worked out these lovely flash-based forms and everything... ah well. I didn't want to alienate them by making it actually seem SO much more classy and professional that they weren't comfortable in their hokey walmart website anymore, haha! I used to do a site for a guy who ran this multimill company selling satellite TV stuff. He deliberately wanted it to be slightly hokey. He felt that was his audience and their print catalogs had that in mind too. Kinda interesting.

It took me nearly a week of nights and all weekend just to get two pages done seen above, and the filters aren't even working right yet. It's exhausting. But hopefully I've learned enough now -- I'm having to learn a LOT of stuff! -- that the rest of the galleries will go much more smoothly. Ironically I didn't even go in there to do anything in this part of the website. I went in to work on this project called Taskerbot, but got distracted.

PJ

Hacking SQL to Save CFGRID: LEN, CHARINDEX, Filename parsing

OK, so then I ran into the same problem I had that I blogged about previously, which is this: getting a full link, image, with unique record ID, into the single column value from SQL so I can have it in my CFGRID.

With one hitch: this time, I needed to use logic to evaluate the image filename. The filename that is in the database is the one for the actual photo, but the filename I needed is a mini-thumbnail that is based on that photo. Now, the thumbnail naming is consistent, at least in regards to matching the database's photo filename. But there are two big caveats, that kept causing SQL errors:

1 - some columns didn't have any value at all there. Their original upload was a PDF for example so there are no files and no value in the database.

2 - the filename entries in that column varied in length. So I couldn't just say, give me the first 5 characters only, then add a new extension (as all the mini thumbnails are JPG, but the filetypes in the database may vary).

This means I had to do four things:

1 - Get the real length of the filename entry

2 - Get what the length would be MINUS 3 digits (the filename extension)

3 - Get only that quantity (#2 above) of characters from the original filename

4 - Then add strings to it, to add the 'mini_' and 'jpg'. And while we're at it, add the strings that will give it the IMG REF and A HREF because CFGRID is not going to be doing that for me either.

5 - And during all this, we have to make sure we are NOT trying to do database math on column values that are incapable of having it done to them or it will probably trigger an error for the user. So if there is no filename or it's too short we just have to do something else.

The first thing I had to figure out was how to stop the logic if the value wasn't up to it. So my first "condition" in my CASE WHEN statement related to that:

CASE WHEN LEN(dsfbfile) > 5

If the filename isn't 5 digits long this is all gonna fail and/or it isn't a real file anyway, so we've taken care of that.

Then I tried to combine the LEN (char length in SQL Server) function and the math subtraction function, to put it only one statement, both 'get the length' and 'remove the length of the extension'. That worked fine.

Unfortunately when I tried to use that it failed, because this is not like CFML, that column name you create is not creating a 'variable' you can just go using everywhere else from then on. It would give me the error INVALID COLUMN every time I tried to use it "as if" it were a number. This is what I tried to do, which failed:

SELECT myrecordID,

LEN(dsfbfile) -4 as THENEWLENGTH,

LEFT(dsfbfile, THENEWLENGTH) as FILETRIM,

'mini_' + filetrim + 'jpg' AS minipic

You see in line 3 I was trying to make that new 'thenewlength' column that I had created, stand in place for an actual number, which SQL Server was not happy about. I actually don't think it would have been happy about me using the calculated column in the last line either but I didn't get that far before I changed it.

Then I found reference to this cool function called PARSENAME:

PARSENAME retrieves parts of string delimited by dots.
It is used to split DataBaseServer, DataBaseName, ObjectOwner and ObjectName
but you can use it to split IP addresses, names etc.

DECLARE @ParseString VARCHAR(100)
SELECT @ParseString = 'DataBaseServer.DataBaseName.ObjectOwner.ObjectName'
SELECT PARSENAME(@ParseString,4)

got that from this address

I wasn't entirely sure how it worked but I got to something else before then so I didn't use it, but thought I'd mention it as it'd be a cool thing I bet for dealing with "filename" manipulation (getting full name or extension easily).

Then I happened to see another example on that same page I linked above, that did something neat I hadn't seen before. First, it used a function called CHARINDEX. Here's a neat article on CHARINDEX and PATINDEX (pattern-index). Basically this function works for 'searching strings' in a database. You can look for a pattern (PATINDEX does wilcards) or something specific like a single character or string (CHARINDEX does that).

Well I didn't know about the use of it in search (I'm still using LIKE % which is probably why my search forms suck), but I saw this example of it used LIKE A NUMBER in a LEFT function -- exactly what I'd been trying to do, but SQL-Server was balking at my using my 'generated value' (or even the logic for the generated value) "as" a number.

This logic, and I'm not even sure why but it does work, looks at my column value, goes to the period in it, goes back one character (-1, to BEFORE the period), and then gets me the string of text that is left of that location, all from the 'imagefile' column exampled below:

LEFT(imagefile,CHARINDEX('.',dsfbfile)-1)

I was able to drop that into a string manipulation, and from an input of an 18 character filename (WHEN it exists), output a link, and image (a different image), with the unique record ID on the link, and if the column wasn't a real picture or any value it would write in a filename value, so I can put a 'placeholder' image file there to keep displays from being funky.

So the good news is, here's the code that worked:

SELECT recordid,
CASE WHEN LEN(imagefile) > 5
THEN '<a href="rvg_showpractice.cfm?id=' + CAST(recordid AS varchar(12)) + '"><img ref="http://www.mysite.com/folder/' + 'mini_' + LEFT(imagefile,CHARINDEX('.',imagefile)-1) + '.jpg" /></a>'
ELSE 'nofilehere.gif' END
AS 'See Session',
anothercolumn here
FROM table

Now the bad news: it took >59000ms seconds to run on 1000 records. I kept taking the quantity of records down, down, down, until I only had 150 records, and still it was taking like >41000ms.

RATS!!

So I ditched that whole phrase about the mini-thumbnail with link. It is still a long query, even on fairly few records, but it's not so long it's at risk of timeout at least. I will have to put the images somewhere else I guess, where the query is not on a view table made of two other view tables, but something more straightforward so assumed fast.

Still. I was damn proud of getting it to work at all.

Hacking SQL to Save CFGRID: CASE functions

OK, now that I'm using CFGRID that means I can't use all the simple logic that CFML is so famous for making easy.

The irony of this is that it's a big deal how 'easy and lovely' CFGRID is. But it's mostly so freakin easy because it forces you do all the hard work you used to do writing CFML code, instead writing SQL code.

It's rather like a thing at work recently. We used to take content from editorial and build a product. Then we got this automated script tool that builds all the XML almost instantly. Unfortunately it requires the editorial people spend eons making this absolutely perfect, must be precisely formatted, multipage excel workbook which in some cases amounts to 55 sheets of hundreds of lines each... screw up a field and it's a problem, screw up something important and it won't run at all. These people are far less technical than we are, so it puts a larger burden on them. Now production is happy because we can say, "Hey, now we only charge a fraction what we used to, and it's much faster, to build that product." But editorial is doing at least as much work as our PM's used to, except on the front-end, rather than the back end, and frankly they didn't have enough time to begin with (probably why their turnover % is ridiculous). In the bigger picture, the equation and the answer did not improve.

Where was I.

Oh yeah. In my primary coding project there is a gallery of sorts, and the artists (we call "viewers") have the choice as to whether they use their name, their alias, or 'anon' on their contributions (which we call "sessions"). The problem is, this means if you are making a list of the last entered sessions, you don't know what you can put in the space that has the artist's name. Not only that, you don't even know which column it should come from, until you look at the value of a totally different column called REVEAL, that is for their settings.

So you look at REVEAL and say, if it's zero, anon, it's one, alias, if it's two, get both first and last name and combine them. That's all very well in ColdFusion. You have to loop the query out anyway in the page, and you do a CFIF statement with it, so what displays just depends.

<cfif query.reveal is 0>'anon'<cfelseif query.reveal is 1>#trim(query.alias)#<cfelseif query.reveal is 2>#trim(query.fname)# #trim(query.lname)#<cfelse>?</cfif>

But now to CFGRID, ah, yes... it only displays what the database spits out, which means if you want something like a combined field value, a variety of formatting values (with some exceptions), or god-forbid a CONDITIONAL value, you're doomed. The only way to get it is to go back to SQL which feeds the grid and figure out how to get your final answer out of that to begin with.

So this evening I got to learn about CASE functions.
This is SQL Server's version of IF THEN ELSE in most code languages.

It turns out, it's not that hard, once you get the syntax down. At first I thought I could only do two options (like some spreadsheet formulas) but I'm not sure there is even a limit. In the stuff I found online, they never used in their example real data, like the value of a column in that table -- they always used some string, like CASE WHEN saleprice > 500 THEN 'bigsale' or whatever. I wasn't sure using a column value rather than string would work, but it does.

Also, while I'm at it, I go ahead and rename all my columns to whatever I want the title to be in the grid, since then I don't even have to put that into the cfml file, as CFGRID will automatically use the column names if the header isn't defined. And some of my column names are pretty damn obscure (because they may have begun life for a different usage) so this kind of makes the queries clearer in retrospect, since I have the memory of a gnat.

If you want to use a column name that has spaces, dashes etc. just put the name in single quotes.

Here is the logic I used to get the identity column working right:

CASE WHEN reveal = 1 THEN alias
WHEN reveal = 2 THEN fname + lname
ELSE 'anon' END
AS Viewer

So it uses the actual value of columns depending on the value of another, and then uses a string in the last case. And it names the final value "Viewer".

CFGRID and TEXT WRAP, hover row color and HEADER row style

You would think text wrap would be a parameter on the tag itself. It took me quite some searching to find the answer to that one.

There is actually CSS styling you can do in huge detail on CFGRID, but it isn't provided with the standard documentation, so it is totally metaphysically obscure. None of it seems able to remove the invasive underline should you be crazy enough to use HREF in your CFGRID, but aside from that, everything else seems do-able.

Here are the three little things I critically needed to find and use:

1. TEXT WRAPPING in CFGRID

It would just truncate my text in every row. Since it won't let you size the grid by % but insists on a hard coded pixel number, that just made it worse.

Your CFGRID must be in a CFFORM. Give that an ID value. Mine:

<cfform name="myform" id="myformid">
<CFGRID . . .

Then in your stylesheet do the following:

<style>
#myformid
.x-grid-row td {white-space:normal;}
</style>

That will make it wrap. The above means it affects everything 'inside' the 'myformid' ID value, and it affects the entire grid, and all the row, and the table cells within the grid rows, and the white-space:normal makes that wrap.

2. HEADER ROW STYLING in CFGRID

I wanted the header row of labels to look different than the default. I know everyone loves those silver gradient skins but the top row seemed to sort of fade into unnoticed in this case. There is another style that lets you mess with that:

.x-grid-hd-text {background-color: #D9CCFF; color: #661A99; font-size: 120%; font-weight: bold;}

That'll do it.

3. ROW HOVER STYLING in CFGRID

I'd set all these style values but the entire row color change when you mouse over it wasn't changing, and it was some kind of cyan-like color by default. Looked really interesting with my purple-based grid as you might imagine. I finally found the code to change that row-sized hover color:

.x-grid-row-over td { background:#661A99;}

So there you have it. To set my row styling, text wrapping, and a decent hover, I had this:

<style type="text/css">
#myformid
.x-grid-row td {white-space:normal; text-decoration: none;}
.x-grid-hd-text {background-color: #D9CCFF; color: #661A99; font-size: 120%; font-weight: bold;}
.x-grid-row-over td { background:#661A99;}
</style>

And it looks like this:

Hacking SQL to Save CFGRID: Image as column value, HREF link with RECORD ID

This is only part of solving a larger problem. The problem is using the HREF on CFGRID underlines every damn thing and I couldn't figure out how to get rid of it. I did find extensive notes on CSS styling for the grid, down to every single cell, but nobody crying about it on the internet had gotten any answer on how to get rid of the underline.

So I took the HREF parameter off my CFGRID because the underline looked so horrible and made it so unreadable I wouldn't even use the grid if I had to suffer that. Which left me with the following dilemma: since the grid no longer links to something semi-automatically (posts to a form, click chooses a whole row, you can pass the ID value you need), I would have to link to it manually.

As part of doing this I decided that much like the simple HTML table I've been using prior to trying to implement CFGRID, I would use a little icon that would intuitively tell people (a) to click that for a link, and (b) that it would open in a new window. So I had three things to figure out:

1. How to get an image as a column value, because CFGRID only lets you insert a query column name, not html, and

2. How to get an html HREF link into a column value, for the same reason, and

3. How to get the unique record ID into that link.

To get a single (just one, same image on every record) into a column value, I did the below. Note that if you need a different image for each record, and you can build that filename/loc from whatever values are in the query, you could do that too. Use the logic of the Record ID link below, and modify that.

SQL Server uses + to concatenate strings and columns. Other DBs may use things like CONCAT function. You use single quotes around every string, and no quotes around actual column names.

You have to render, aka CAST or CONVERT, integer fields to strings before you can concatenate them.

SELECT
'<a target="_blank" href="myactionfile.cfm?id=' + CAST(p.myid AS varchar(12)) + '">' + '<img src="../shared/icons/external2.gif" border="0" /></a>' as 'See',

then in CFGRID, I just did

<cfgridcolumn name="See" width="35" display="yes" >

and that's it! That changed my ID to a string, and it tied it into a link and an image, so now in my column it looks like this:



You click on the little image and it opens that record in a new page.

Hacking SQL to Save CFGRID: Date Format

In the HTML format CFGRID, my date "mask" formatting, using the CF documentation, didn't seem to work at all. I don't know why but my impression from cfdocs was that it wasn't going to work in the HTML format grid I was using. I hacked this by changing my SQL to begin with, rather than doing it in CF.

Formerly it was:

SELECT mydatefield as DateEntered

then

<cfgridcolumn
name="mydatefield"
display="yes"
header="Date"
mask="EEE DD-MMM-YY H:NN A">

Which sucked and continued to show me whatever it wanted, rather than the "mask" attributes the cfdocs had. So I changed it to this:

SELECT CONVERT(VARCHAR(11), mydatefield, 106) AS DateEntered

then

<cfgridcolumn
name="mydatefield"
display="yes"
header="Date">

And the resulting output looks like this:

22 Jun 2008

You can google for SQL DATE FORMAT or something like that and see the whole huge list of numbers (mine 106, above) that will format dates differently.

CFGRID and CFC Binding How To

I avoided using a CFC binding initially because I didn't grok how and, before I knew that my route made pagination impossible, a query in the HTML CFGRID seemed so much easier. But here is how the binding part is done.

In the CFC file you're going to have the following:

1. A function name with arguments
2. Your query
3. Not always but in my case, a query return for the grid.

The function is going to have remote access and return a structure. Here is my code for it:

<cffunction name="comments" access="remote" returntype="struct">
<cfargument name="page" required="true">
<cfargument name="pageSize" required="true">
<cfargument name="gridsortcolumn" required="true">
<cfargument name="gridsortdirection" required="true">

Page means, "Do you want this to do paging, where things are broken into separate pages?" PageSize means, "How big (how many records) should each page be?" The sorts are fairly obvious I think...

If you have the above it means you will have to have the page and pageSize defined in your CFGRID tag in your CFM page, and you can control it from there. I did not define my grid sorts and it didn't break.

Next you have your query. Whatever query you were using to output records prior to CFGRID will probably work fine, but I ended up having to hack mine to do all kinds of stuff that CFGRID made a royal pain in the butt, so I fixed it through SQL instead of CFML. Will cover that in another post.

Then you have your query "return". You find this in documentation under functions, queryconvertforgrid function.

In my case it has three values:
1 - the name of my query
2 - page
3 - pagesize

And I mean those strings (page and pagesize) not true/numeric. Here's my code, where my query is called selectAll:

<cfreturn queryconvertforgrid(selectAll,page,pagesize)/>

Now to put the CFC together you'll want these three things in order. I have a file called getcomments.cfc and here is the code, with the query revised for public use of course. Note that the CFC did not find my datasource which is a variable set in my Application.cfm file, so I had to manually CFSET that value before running the query.

<cffunction name="comments" access="remote" returntype="struct">
<cfargument name="page" required="true">
<cfargument name="pageSize" required="true">
<cfargument name="gridsortcolumn" required="true">
<cfargument name="gridsortdirection" required="true">

<cfquery datasource="myds" name="selectAll" maxrows="1000">
My query goes here
</cfquery>

<cfreturn queryconvertforgrid(selectAll,page,pagesize)/>
</cffunction>

Then in my CFM file, my HTML format CFGRID used this code to call it:

<cfform name="myform" id="myformid">
<cfgrid
name = "myGrid"
format="html"
bindOnLoad="yes"
bind="cfc:getcomments.comments({cfgridpage},{cfgridpagesize},{cfgridsortcolumn},{cfgridsortdirection})"
pageSize="10"
preservePageOnSort="true"
selectMode="single"
selectOnLoad="no"
font="Verdana"
fontsize="11"
label="True"
stripeRows="true"
stripeRowColor="##D9CCFF"
selectColor="##994FE6"
>
<cfgridcolumn name="id" display="no" >
<cfgridcolumn name="DateEntered" display="yes" header="Date" mask="EEE DD-MMM-YY H:NN A">
<cfgridcolumn name="Alias" italic="yes" display="yes" header="From">
<cfgridcolumn name="See" width="35" display="yes" >
<cfgridcolumn name="theComment" fontsize="1.2em" textColor="##661A99" width="700" display="yes" header="Comment">
</cfgrid>
</cfform>

Notice that there is some styling in one of the columns and that works, and date formatting in the date column which did not work at all so I did something different, which I'll cover in another post. The 'see' value there is a big string from a special SQL call that gave me an HREF link, and an image, and appended the record ID, because stupid CFGRID wouldn't let me get rid of underlining every damn thing if I used the HREF attribute.

CGRID and Auto Pagination

Everyone raved about how cool it was that ColdFusion's CFGRID tag automatically would do the pagination for you.

All the little examples I looked at, had that nifty little page number and button at the bottom.

Except mine. I could do code pretty much just like the examples, except that I was using an HTML form and a QUERY in it, but the little pagination thing never appeared.

It turns out that you cannot do pagination in a QUERY HTML CFGRID in CF8. Instead, you have to put your query out in a CFC page and "bind" that to the CFGRID. It is the CFC page that actually works out the pagination stuff.

I'll cover the CFC and binding separately.

The Blog

Yes. I know this is a canned design theme and I don't care to spend the time making one pretty like my other five blogs on other topics.

Since I only do coding on my "extra" hours -- read: hours I should be sleeping, not working for free for hobby sites -- my time to figure things out is limited. And since I only use Cold Fusion (CFML), HTML and CSS -- I do not know jack about javascript except what I can steal, and I don't know much about any other kind of coding, nor have I time to learn -- this state of semi-illiteracy in programming means that every smallest problem is a true Day In Hell for me. I don't know how to script hack workarounds. And ColdFusion's documentation, while quite robust in places, in others leaves something seriously to be desired.

I have googled more than any human should have to, to find solutions to my Cold Fusion coding problems. What I often find is

a) A solution by accident, e.g. someone is doing something entirely different but after enough of those I chance on an idea that I try and it actually works for my own problem too, and

b) An internet of people bitching about some problem but absolutely nobody who has posted an answer or solution to it.

I finally decided that although I will only post occasionally, I'm going to post my issues and solutions or workarounds on a blog, so the next poor sucker trying to google for answers on these problems might actually find MY blog and get one.

PJ