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".
No comments:
Post a Comment