Hello, Is anyone familiar with how to separate three concantenated values into three separate variables? I am running a query that generates for example: a,b,c. I'd like to send each of those values separtely in a CFMail so it reads like: Value 1 = a Value 2 = b Value 3 = c Thank you.
Hi, thanks. Right. I'm using 1st I set the vars as <cfset REALID=#form.ID#> <cfset REALNEWPRIORITY=#form.Newpriority#> <cfset REALOLDPRIORITY=#getoldpriority.oldpriority#> then I loop thru a query: <cfloop index="INDEX" from="1" to="#form.maxprio#"> to obtain the number of elements - which in my case is 3. Everything is cool until I get to the CFMail for these two items: New Case Number: #listlen(RealNewPriority)#<br /> Old Case Number: <!--- #ListGetAt(RealOldPriority)# ---> #ListGetAt(RealOldPriority, INDEX)#<br /> I'm getting the RealNewPriority okay, but not the RealOldPriority...it's always incorrect. I have to admit I'm not familiar with using ListGetAt etc. I'm reading up on that now...but LiveDocs is limited in what they have available on the website. Any suggestions would be much appreciated. Thanks!
Well, I don't know what any of values are, or what you mean by "RealOldPriority...it's always incorrect" .. so I am not sure what the question is ;-) Can you give a simple example in the form: 1. Here are the variables: (ie input) REALID = 123 REALNEWPRIORITY= "something" REALOLDPRIORITY= "??" 2. My code snippet: <cfloop ....> </cfloop> 3. Explain the actual versus desired results. ie: "The code doing X, but I would like it to do Y"
Thanks for replying. Sorry for the confusion, just my frustration with this problem. Okay, here goes. This app is to schedule appointments, we call them 'cases'. They have a priority set as 1,2,3, etc. - rarely do we schedule more than 5-6 cases in a day; but anything is possible. Sometimes these cases need to be rearranged by the users. So let's say case a = 1, case b = 2, case c = 3. User wants to rearrange the schedule to say case a = 3, case b = 1, case c = 2. Then send an e-mail to the users who have had their day rearranged. It's all 'close' to working but not quite. My code: First I select the existing priorities via a query, insert them into a temporary table I call 'shuffle'. SELECT priorityfrom schedule order by priority Gets back the existing 'priority' which on the next page I set at 'RealOldPriority' - the priority before I changed it. Next I run this to get how many cases were scheduled that day: SELECT max(priority) as maxprio FROM schedule - so Iknow how many cases there are. Then I insert those values into the shuffle table: INSERT into shuffle_tbl (ID, priority,) SELECT ID, priority, from schedule Next I get those values out: SELECT * from shuffle_tbl order by priority Send those values as hidden form vars to the next page: <cfform name="ShuffleCaseRoom" action="caseshuffleResults.cfm" method="Post"> <input type="hidden" name="maxprio" value="#Find_Max.maxprio#"> and <cfoutput query="Collect_cases"> <input type="hidden" name="ID" value="#ID#"> <input type="hidden" name="priority" value="#priority#"> <td><input type="text" name="newpriority" size="2"></td> This is where the user types in the new case priority. So far, so good...now is where the fun starts. First thing I try to do is determine if I have the old priority: Seems I didn't, so I wrote a simple query: <cfquery name="getoldpriority" datasource = "#Application.DSNNEW#"> Select ID,priority as oldpriority from schedule Then I set these variables: <cfset REALID=#form.ID#> ID #REALID# <br /> <cfset REALNEWPRIORITY=#form.Newpriority#> NewPrio #REALNEWPRIORITY# <br /> <cfset REALOLDPRIORITY=#ListLen(getoldpriority.OldPriority)#> OldPrio #REALOLDPRIORITY# <br /> The values I get are REALID - 123,124,125 (perfect, the ones I expected) REALNEWPRIORITY - 3,1,2 (perfect, the new case order) REALOLDPRIORITY - 1 (huh?) - what I was hoping for was 1,2,3. I've tried using #ListGetAt(getoldpriority.OldPriority)# but get an error: Detail Problem occurred while parsing: REALOLDPRIORITY=#ListGetAt(getoldpriority.OldPriority)# Extended Info Error at line 1, column 27 - The function listgetat requires at least 2 argument(s). I hope this is a better explanation - clearer? Thank you!
Great. Yes, that makes a lot more sense. You have to use ValueList(...) to return a list of all values in the query column. When you use #queryName.columnName#, CF only returns the value in the first record. <!--- will return all values ---> <cfset REALOLDPRIORITY = ValueList(getoldpriority.OldPriority)> But you should also verify the lengths of the two lists are the same. I assume users can add/remove appointments. So for example, there might be 4 appointments when the priorities are adjusted. But by the time the page is submitted, there are only 3. If that happened, your code would error out. <cfif listLen(REALNEWPRIORITY) eq listLen(REALOLDPRIORITY)> do the changes <cfelse> oops! the schedule has changed .. </cfif>
Hey cfStarlight - Thanks for your suggestion - however now I'm getting a little more than I expected as values: I put this in my code: <cfset REALID=#form.ID#> ID #REALID# <br /> <cfset REALNEWPRIORITY=#form.Newpriority#> NewPrio #REALNEWPRIORITY# <br /> <cfset REALOLDPRIORITY = ValueList(getoldpriority.OldPriority)> OldPrio #REALOldPRIORITY# and I'm getting: ID 123,124,125 NewPrio 3,1,2 OldPrio 1,2,3,2,3,1,1,2,2,1 Not sure why I'm getting these items - I was expecting for case #123 - 1, case #124 - 2, case #125 - 3... so the CFmail I'm getting is still incorrect - as follows: ID.123 New Case Number: 3 Old Case Number: 1 - correct ID.124 New Case Number: 1 Old Case Number: 1 - this was supposed to be '3' ID.125 New Case Number: 2 Old Case Number: 1 -correct
Well, you are querying two different tables which is problematic right there. Likely possibilities are: 1) Your two queries are using different filters. Example, one query says get all records on date X and the other just says get _all_ records 2) The data in the tables changed in between the time you generated the form and submitted it Are you using SQL 2005, because if so there is a better way.
Wait... the "priority" column in your database is a single number, right? It is not a delimited list like "1,5,6", correct?
Ok. Then the two queries must be different (ie one has a filter, the other doesn't) .. or the data has changed. (See my previous comment) Are you using SQL 2005?
SOLUTION: I just needed to add the ListGetAt around the form variables. <CFMAIL to, from, etc.> New Case Number: #ListGetAt(form.NEWPRIORITY,index)#<br /> Original Case Number: #ListGetAt(form.PRIORITY,index)#<br /> </CFMAIL> Finally got the new case and the old case to display as it was being passed as a form variable. Thanks for your comments!
Hmm... well if you expect to have concurrent users, you might want to rethink that. If the schedule changes after the form is pulled, you're not getting an accurate picture of what has changed (because you are using stale form variables)
Hi cfStarlight, What would you suggest? I will have concurrent users. Not sure how often they will be trying to change these appointments, but you're right. There's a potential of multiple users trying to manipulate the schedule. Is there a way to 'lock' this transaction once it's started? The users have to select the reschedule page in order to select which day + location + room before they see what's scheduled on that day. Once they view the day and the particular room the appointment is scheduled for, they can see the appointments. At that time they see that appointment a is case 1, appointment b is case 2, appointment c is case 3. There's an input field that allows them to write in the order in which they wish to see it change; so appointment a can be '3' and unfortunately appointment b can be a '3' too (maybe Javascript form checker?)....I'm going to need to address that as well. Any suggestions you have for me to send the values to the next page and prevent anyone else from changing it at the same time would be appreciated. I was just happy to get the values sent out in an e-mail. A small victory, but one of many challenges with this particular effort. Thanks again!
Another small victory: Here's a snippet that keeps the user from writing the same appointment in multiple times: <cfloop index="INDEX" from="1" to="#form.maxprio#"> <cfif listcontains(newpriority, #INDEX#) eq 0> <cfoutput> <span style="color:red">YOU DO NOT HAVE A CASE No. #INDEX# </span> </cfoutput><br /><br /> Click <a href="Javascript:history.back()">here</a> to go back <cfabort> </cfif> </cfloop> I would appreciate your comments on how to best handle the newpriority and priority variables rather than as form variables. Thanks again!
What would you think of using <CFTRANSACTION> on the page where the queries are? Will that prevent another user from updating the db until the 1st user request has been processed? Not sure how to test that...
No, AFAIK CFTRANSACTION will not prevent other users from updating the schedule unless you set it to serializable (ie single thread the action). But you should not do that lightly. But my question would be, what do you think should happen if two users update the schedule at relatively the same time. Example: UserA and UserB both grab the schedule and make different changes. UserA submits their changes first. Wouldn't you want to let UserB know the schedule changed, show them the new schedule and ask if they still want to apply their changes?
Yes, that would be the ideal scenario but I'm not sure how to go about doing that. If UserA and UserB both select the same date/location/room I would love to have a pop up or something saying something like 'This schedule has recently been updated. Please review the schedule prior to submitting your changes'. Some sort of session capturing the date/time of the UserA's change or perhaps even before that? The fact that UserA has already selected date/location/room to re-schedule should temporarily lock UserB out. Would CFLOCK work?