Hi All,
Let me say in advance that this will be a lengthy description of my problem,
as I believe the more info you have, the better position you will be in to
help me...
[deep breath] Okay. I have (for the most part) a simple app that allows
employees to request approval for training. There is an administrator
section that allows the training coordinator to view all of the requests and
their status (registered, pending approval, not approved, cancelled, wait
list) as well as change their registration status and the approver's name.
Here is the form that displays the editable fields generated for each
approval request brought up by the search(admin_search_action.cfm). (The
records are pulled based upon the class id):
<cfform action="admin_edit.cfm?classid=#classid#" method="POST"
enablecab="Yes">
<cfoutput>
<INPUT TYPE = "hidden" NAME = "NumRecords" VALUE = "#Data.RecordCount#">
</cfoutput>
<cfloop query="data">
<cfoutput>
<TR bgcolor="###IIF(data.currentrow mod 2, de ( 'FFFFCC' ), de (
'FFFFFF'
))#">
<TD valign="top" class="medtext" width="26%">#student_name#</TD>
<TD valign="top" class="medtext" width="20%">#student_phone#
</TD>
<TD valign="top" class="medtext" width="22%">#student_location#
</TD>
<TD valign="top" class="medtext" width="22%">#status_name#
</TD>
<TD
valign="top" class="medtext" width="5%">
<input type="hidden" name="requestid#currentRow#" value="#data.requestid#">
<cfselect name="status_name#currentRow#" query="status" value="statusid"
display="status_name">
<option value="" selected>
</cfselect>
</TD>
<td>
<cfif actual_approver_name is "">
<cfinput type="Text" name="actual_approver_name#currentRow#"
value="#manager_name#" required="No" size="15" maxlength="30">
<cfelse>
<cfinput type="Text" name="actual_approver_name#currentRow#"
value="#actual_approver_name#" required="No" size="15" maxlength="30">
</cfif>
</td>
</TR>
</cfoutput>
</CFloop>
<TR>
<td colspan="7"><font color="#ff6600"><b><div align="center">Please verify
that your changes are correct. When you are ready to save your
changes, click: </b><input
type="submit" value="submit
changes">.</div></font></td>
</tr>
</cfform>
Now, when this form is displayed on the page, I also query the classes table
to determine the maximum number of students the class will allow. I also
determine the number of records in the request table that have a
registration_status of 1 (for registered) and subtract this from the maximum
capacity to come up with the number of seats left. There is also code to
determine the number of students on the wait list. We'll come back to that
later. This is the important part.
Okay, now this form obviously posts to admin_edit.cfm (passing the classid).
This is the code from the action form:
<!--- query to get maximum seating info on the selected class --->
<CFQUERY name="classname" datasource="trainreg">
SELECT DISTINCT * from class
where classid=#url.classid#
</CFQUERY>
<!--- Loop through the form fields and update the table. --->
<CFLOOP FROM = "1" TO = "#NumRecords#" INDEX = "ThisRow">
<CFQUERY DATASOURCE = "TrainReg">
UPDATE Request
SET Actual_Approver_Name =
'#Evaluate("actual_approver_name" & ThisRow)#'
<cfif (#evaluate("status_name" & ThisRow)# is NOT "")>
, registration_status = #Evaluate("status_name" & ThisRow)#
</cfif>
<cfif #evaluate("status_name" & ThisRow)# is NOT "" AND
#evaluate("status_name" & ThisRow)# is 1>
, actual_approval_date=#now()#
</cfif>
WHERE requestID = #Evaluate("form.requestID" & ThisRow)#
</CFQUERY>
</CFLOOP>
<!--- find new number of people registered --->
<CFQUERY name="registered" datasource="trainreg">
SELECT Count(*) AS registered_count
FROM request
WHERE registration_status=1 and classid=#url.classid#
</CFQUERY>
<!--- Find number of people currently on the wait list --->
<CFQUERY name="wait_list" datasource="trainreg">
SELECT Count(*) AS wait_count
FROM request
WHERE registration_status=2 and classid=#url.classid#
</CFQUERY>
<CFSET seats_available=#classname.class_size#-#registered.registered_count#>
<!--- re-query the database to display the new data --->
<CFQUERY name="data" datasource="TrainReg">
SELECT *
FROM Request, Class, status
Where request.classid=#url.classid# AND class.classid=request.classid
AND status.statusid=request.registration_status
ORDER BY status.statusid, request.request_date
</CFQUERY>
Whew! I promise, we're almost done here...
Now, the idea is that the following should happen when the training
coordinator updates a record:
1) If there is still space in the class and the training coordinator changed
the status to "registered", the request table should update accordingly and
a confirmation page should display with the updated class roster (got that
part okay) AND the new figures for maximum class size, seats available, and
students on the wait list (got this part, too). Assuming all goes well, an
email should be kicked off to the student and to his/her manager, confirming
the registration (I have not placed this code in yet, until after I get all
of these other checks in place).
2) If the training coordinator changes any of the student registration
statuses to anything other than "registered" (assuming they had previously
been registered) and that frees up a seat for someone else, they should be
prompted to choose a student or students from the wait list to register for
the class.
3) If the training coordinator tries to register a number of students that
exceeds the maximum class size, I do NOT want the database to update and I
do NOT want any emails to be generated until she goes back and modifies the
form.
Now, somehow I need to determine which records from the form had a status
change, and what that status change was, BEFORE they are processed by my
update statement. I then need to take this new number and compare it to the
database, and generate any error messages as appropriate based upon this
comparison. I tried using a loop and evaluate (without an update query) to
set a new variable, but this did not work.
I'm thinking that my best bet would be to use a javascript on the actual
form page itself that evaluates all of the status changes in the drop-down
menus and then utilizes my CF variables in that page for the maximum seating
capacity, seats available, wait list, etc. So, I fear that the time has come
for me to finally learn WDDX?
Does anyone have any examples of code that they could share to help me out
with this? JavaScript is not my forte. In fact, I don't even know where to
start.
Sorry for the lengthy explanation, but better to get it all out right
upfront...
TIA
Terri
_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
Share information about yourself, create your own public profile at
http://profiles.msn.com.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
