|
| |
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
 |
Updating Tables - 6/11/2004 0:59:35
Ok, here is what I am looking to do.
I have a table that contains a list of my coworkers names and cell number and "rank" (1=Manager / 2=Tech).
I have a page with a form that has 4 DRW's that pull the coworker's names in a drop-down list. The list filters based on "rank".
The DRW's are as follows:
- Primary Tech On Call
- Secondary Tech On Call
- Primary Manager On Call
- Secondary Manager On Call
The point is for someone to access this page and select the primary and secondary techs on call from the list of techs (filtered by rank to list only the techs) and to select the primary and secondary managers on call from the list of managers (filtered by rank to list only managers).
Then when the form is submitted, to update another table with the names and numbers of those selected and placed into the appropriate fields. Another page will then display this information via a DRW. The trick is this second table needs to be UPDATED.
Another thought was to use just the initial table and add a field for on call status that would get flagged with a 1, 2, 3 & 4 appropriately based on the selections made in the drop down lists. However, anything that was previously marked would need to be reset and the changes would need to be applied.
Any thoughts as to the best way to go about this?
Thanks in advance.
Nate
|
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Updating Tables - 6/11/2004 13:14:18
So to where does the form submit? It needs to submit to a new page whereon sits a DRW with a custom query that updates the table you wish updated. Does that make sense?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Updating Tables - 6/11/2004 14:27:13
Right now I am not exactly sure of the most efficient way to do this.
As I see it, I have 2 options:
1. There is a main page that has 4 sections (primary & secondary tech and primary & secondary manager). Each section has a drop down list that pulls from the DB table (ops which includes a record for each on call person with name, number and rank (1=tech / 2=manager)). The lists for the tech are set with the rank criteria to only list techs and the same for the manager (except it only shows managers).
A selection is made in each of the 4 drop downs and submit is pressed.
So far that is all set and working good however, I do not have the data going anywhere as of yet.
In this option I imagine the selected data getting sent to another table (mgr) that has one record and 8 fields ( a name and number for each on call ). Everytime the initial page is changed and submitted, the info in the mgr table would get updated with the newly selected info.
Althoug this part is not done, I can do it but only know how to get the name that is slected in the drop down to update to the mgr table. I do not know how to associate the number for that on call person to be updated in the mgr table as well.
2. I had a thought that we could use one table and add a field for oncallstatus (1=primary tech, 2=secondary tech, 3=primary manager and 4=secondary manager). The form would work the same as above except that based on the selections, the oncallstatus field would get updated with the appropriate status (1,2,3 or 4) and anything that wasn't selected, would get set to 0.
This one I have no clue if it is even feasible but was a thought none-the-less.
I see that I am getting long winded here and apologize but am finding it difficult to explain what it is that I am trying to do.
Here is the primary page
If we go the 2 tables method, this is the page that will display the updated info.
Like I said earlier, I am not sure of the most efficient and practical way of approaching this.
Thanks
Nate
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Updating Tables - 6/11/2004 14:34:27
Oh, I never answered your question Duane, my bad.
Yes I do know that I will need to submit the info from the main form to an update page that contains the SQL query that will update the DB.
So with that said, I still have an issue off associating the phone numbers of the names selected when sent to the update page. I thought about adding a hidden field for each of the displayed fields. Then when a name is selected, their phone number would populate the hidden filed for that selection. Then have the work for each of the on call selections.
However, this is where I fall short on knowledge.
Thanks
Nate
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Updating Tables - 6/11/2004 19:36:22
Create a new table, called tblOnCall. The fields should be Contact (numeric) and Contact_Priority (numeric). The priorities of course are 1-4. Assuming that the table that contains the workers names also has a unique number for each employee, that is the number that should be inserted in the Contact field. You can then use a JOIN to also pull the contact's phone number and any other info. you want from the main table. For example:
SELECT Contact, Contact_Priority, Telephone FROM tblOnCall INNER JOIN MainTable ON tblOnCall.Contact=MainTable.EmployeeID
The difficulty with what I am proposing is that you will need to use pure ASP to insert/update the records in the tblOnCall table as it requires that you update or insert 4 records at a time and to my knowledge the DRW can't do that. Personally, I would also add a date field and just insert new records for each date rather than update the table each day. For example, a record in the db would look like this:
6/25/04 | 72 | 3
Which translated means employeeID number 72 is the 3rd contact on June 25. Would that work for you?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Updating Tables - 6/11/2004 20:04:10
Well actually, I believe that the table will be updated on a weekly basis. At the beginning of the weekly rotation, someone on the tech team will make the on call designations that will stand for the week.
The helpdesk will view the second page for who and what number to reach them at. Whetever is set, is who will be on call and is not really dependant by date. I spose that the query page that the helpdesk views, can just show the record (with the 4 contacts) that has the most recent date.
As for the pure asp, I had done that once out of the sole need for testing the process by bypassing the DRW to see if it resolved that particular issue. However, I am sure that I can set that up.
In the table that lists the contacts with phone numbers, there is also a ID field (autonumber). I just "organized" this table (tblContactInfo) a little and is as follows.
EmployeeID=Autonumber
EmployeeName=Text
EmployeePhone=Text
EmployeeRank=Text
I also created the the table that you mention above.
So that query that you listed above, is the the query for the Pure Asp page?
This is where i get a little lost. ;-)
Thanks
Nate
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Updating Tables - 6/11/2004 20:30:26
quote:
So that query that you listed above, is the the query for the Pure Asp page?
No. That will be the query to pull out the information to be displayed on the page the "helpdesk" will use to determine who to call. Before doing the pure ASP page, we probably need to modify the form that has your four dropdowns. Your dropdowns should all have the same name followed by a digit to specify which priority the particular choice will be placed in. So, the first dropdown will be perhaps Contact1, then the next, Contact2, then Contact3 and Contact4. I would then also add a form field for the effective date of the rotation. You say you do them a week at a time, so the person completing the form would just input the date the rotation begins.
Then open a new page in which we will paste the code to perform the insert. Then, just after the <body> tag insert the following code:
<% DIM conntemp, mySQL, myDSN
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\fpdb\YourDBName.mdb")
'myDSN would be specific to your environment
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
FOR i = 1 to 4
IF Request("Contact" & i) <> "" THEN
mySQL = "INSERT INTO tblOnCall (EmployeeID, Contact_Priority, Effective_Date) VALUES"
mySQL = mySQL & " (" & Request("EmployeeID"& i) & ", " & i & ", #" & Request("Effective_Date") & "#)"
conntemp.execute(mySQL)
END IF
NEXT
conntemp.close
set conntemp=nothing
Response.write "Record added."
%>
That should take care of the inserting of the records into the db. Give it a try and let us know if it works.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Updating Tables - 6/11/2004 23:07:34
Ok, I am confused about the page that the HelpDesk will view but first things first.
I am getting an error when submitting to the pure asp page I called OnCall_Insert.asp.
This is the error:quote:
Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/documentation/OnCall_Insert.asp, line 25
This is the pure asp code as I have it.quote:
<% DIM conntemp, mySQL, myDSN
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("\_db\oncall.mdb")
'myDSN would be specific to your environment
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
FOR i = 1 to 4
IF Request("Contact" & i) <> "" THEN
mySQL = "INSERT INTO tblOnCall (EmployeeID, Contact_Priority, Effective_Date) VALUES"
mySQL = mySQL & " (" & Request("EmployeeID"& i) & ", " & i & ", #" & Request("Effective_Date") & "#)"
conntemp.execute(mySQL)
END IF
NEXT
conntemp.close
set conntemp=nothing
Response.write "Record added."
%>
Line 25 is conntemp.execute(mySQL)
This is also my table structure just in case I have something messed up.quote:
tblContactInfo
EmployeeID AutoNumber
EmployeeName Text
EmployeePhone Text
EmployeeRank Text
tblOnCall
Contact Number
Contact_Priority Number
Effective_Date Date/Time
tblDisplay
Contact1Name Text
Contact1Phone Text
Contact2Name Text
Contact2Phone Text
Contact3Name Text
Contact3Phone Text
Contact4Name Text
Contact4Phone Text
Thanks
Nate
< Message edited by Nathan Goulette -- 6/13/2004 19:19:14 >
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Updating Tables - 6/12/2004 10:34:35
Try this:
mySQL = "INSERT INTO tblOnCall (Contact, Contact_Priority, Effective_Date) VALUES"
mySQL = mySQL & " (" & Request("Contact"& i) & ", " & i & ", #" & Request("Effective_Date") & "#)"
conntemp.execute(mySQL)
Also, tblDisplay is not necessary. We will use a query to combine the needed fields from tblOnCall and tblContactInfo
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Updating Tables - 6/12/2004 15:11:51
I get pretty much the same error but is a little more specific this time:quote:
Microsoft JET Database Engine error '80040e14'
Syntax error (missing operator) in query expression 'Bugs Bunny'.
/documentation/OnCall_Insert.asp, line 25
Thanks
Nate
< Message edited by Nathan Goulette -- 6/12/2004 12:12:47 >
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Updating Tables - 6/12/2004 15:39:43
I think I know what the problem is. On you form with the dropdowns, you need to edit each dropdown so it displays names but submits EmployeeID. Make sense? To do this, in step 3 of the DRW click the Edit fields option and remove all fields except EmployeeID and EmployeeName. Then in step 4, Display EmployeeName and Submit EmployeeID.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Updating Tables - 6/12/2004 18:01:21
Ok, that worked great and it does say that the record was added but I do not see anything in the db.
Also, The query for the helpdesk page is not functioning either. Thinking that it was because the table was empty, I manually put data in it. Here is the exact query:quote:
SELECT Contact, Contact_Priority, Telephone FROM tblOnCall INNER JOIN tblContactInfo ON tblOnCall.Contact=tblContactInfo.EmployeeID
And this is the error:quote:
Database Results Error
Description: No value given for one or more required parameters.
Number: -2147217904 (0x80040E10)
Source: Microsoft JET Database Engine
Thoughts?
Nate
< Message edited by Nathan Goulette -- 6/13/2004 19:22:44 >
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Updating Tables - 6/12/2004 19:17:55
Looks like a couple of the column names in the SQL above do not match the column names in the db. Should be something like:
SELECT EmployeeName, Contact_Priority, EmployeePhone FROM tblOnCall INNER JOIN tblContactInfo ON tblOnCall.Contact=tblContactInfo.EmployeeID
I am unsure as to why it says, "Record added" and yet no record appears in the db. Are you sure you didn't have the db open while testing the page? That may cause the record to not be added because when you close the db you actually save a temp copy over the existing one.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Updating Tables - 6/13/2004 4:57:14
Ok, the submitting to the DB issue is resolved. I had renamed the submitting form drop lists as you described Contact1, Contact2, Contact3 and Contact4. However, since then I had made changes to the DRW which reset the names back to default. That was the reason it was not updating.
The query for the helpdesk page is also displaying the correct info, sort of.
I have 2 final issues and both have to do with the helpdesk page. First off, when the form submits the records for the 4 contacts, it just adds another 4 records to the db. I added a DRW to the top of the already formatted page just to make sure the query was going to work before working it into the page. When the results or displayed, it lists all of the records submitted thus far. This needs to display the latest 4 records submitted rather than the cumulative list. Earlier you had me add an Effective_Date field but do not see it in the queries at all. Is there a way to display one instance of each priority (1, 2, 3 and 4) based on the last Effective_Date entered?
The other option would be to have the information submitted from the form UPDATE the 4 existing records rather than adding 4 more records every time time it is submitted. This would be my preferred method if possible since it would also eliminate the growth of the table/db by not adding 4 more records every time the oncall list changes.
The second issue is that when the DRW displays the results, it lists the 4 records based off one field. How do I make the results unique enough to where I would be able to move them on the page and display them were I need to.
Actually if we can get the first issue resolved and then be able to query based on the latest Effective_Date AND Priority, I would just be able to add 4 separate DRW's to the page and just query each one based on the appropriate Priority.
I hope that makes sense since I just got back from seeing The Day After Tomorrow and I am very tired. If not, I can explain more later when I eventually wake up.
Thanks again.
Nate
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Updating Tables - 6/13/2004 13:55:29
As for displaying only the latest 4 records, I think that I got it. This is what I came up with for the query on the page that the helpdesk will view::quote:
SELECT TOP 4 EmployeeName, Contact_Priority, EmployeePhone, Effective_Date FROM tblOnCall INNER JOIN tblContactInfo ON tblOnCall.Contact=tblContactInfo.EmployeeID ORDER BY Effective_Date DESC
It does seem to work as I wanted but is there a more efficient way of doing this?
Now, I just need a way to single out the 4 returned records so that I can place them on the page in designated locations based on the Contact_Priority.
Thanks
Nate
< Message edited by Nathan Goulette -- 6/13/2004 19:23:14 >
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Updating Tables - 6/13/2004 14:09:58
My suggestion would have been exactly what you did. As far as being able to display the values whereever you want, that would require a pure ASP solution. The DRW isn't capable of that. If you want to go that route, let me know and I will post some code in the next day or so.
As far as the growth of the table, you may be able to get an UPDATE statement to work as opposed to inserting new records. Use similar code as to what I provided but switch to an UPDATE and you will need to enter a WHERE clause. Try replacing the existing INSERT SQL with something like:
UPDATE tblOnCall SET Contact = "&Request("Contact" & i) WHERE Priority = "& i &"
Then just abandon the date idea. I havently tested the above, but it's worth a try for simplicity's sake.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Updating Tables - 6/13/2004 21:03:05
I was missing the closing delimiters on the "Contact" value. Try:
mySQL = "UPDATE tblOnCall SET Contact = "&Request("Contact" & i)&" WHERE Priority = "& i &""
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Updating Tables - 6/14/2004 0:23:43
Bingo!
Damn, I messed with that for awhile and couldn't get it...
Thanks Duane.
Nate
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Updating Tables - 7/18/2004 13:53:06
How would I go about setting this up so that when the page is displayed, that the current contact is selected in the dropdown list as default?
Here is the current code for this one dropdown. There are 4 but once i get one, I can roll that to the others. <td nowrap bgcolor="#e2e2e2" bordercolordark="#ffffff" align="center" width="51%">
<input TYPE="hidden" NAME="VTI-GROUP" VALUE="0">
<!--#include file="../_fpclass/fpdbform.inc"--><!--webbot bot="SaveDatabase" endspan i-checksum="34604" --><p align="center">
<nobr>
<!--webbot bot="DatabaseRegionStart" s-columnnames="EmployeeID,EmployeeName,EmployeePhone,EmployeeRank" s-columntypes="3,202,202,202" s-dataconnection="OpsOnCall" b-tableformat="FALSE" b-menuformat="TRUE" s-menuchoice="EmployeeName" s-menuvalue="EmployeeID" b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="tblContactInfo" s-displaycolumns="EmployeeName,EmployeeID" s-criteria="[EmployeeRank] EQ {EmployeeRank} +" s-order="[EmployeeName] +" s-sql="SELECT * FROM tblContactInfo WHERE (EmployeeRank = '::EmployeeRank::') ORDER BY EmployeeName ASC" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="EmployeeRank=2" s-norecordsfound="No records returned." i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc" u-dbrgn2="../_fpclass/fpdbrgn2.inc" preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Database</span> " startspan --><!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM tblContactInfo WHERE (EmployeeRank = '::EmployeeRank::') ORDER BY EmployeeName ASC"
fp_sDefault="EmployeeRank=2"
fp_sNoRecords="No records returned."
fp_sDataConn="OpsOnCall"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="EmployeeName"
fp_sMenuValue="EmployeeID"
fp_iDisplayCols=2
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="25911" --><!--webbot bot="Validation" s-display-name="Primary Tech Contact" b-value-required="TRUE" b-disallow-first-item="TRUE" --><select NAME="Contact1" SIZE="1">
<option selected>Choose --></option>
<!--webbot bot="AspInclude" clientside u-incfile="../_fpclass/fpdbrgn1.inc" startspan --><!--#include file="../_fpclass/fpdbrgn1.inc"--><!--webbot bot="AspInclude" endspan i-checksum="52766" -->
<option VALUE="<%=FP_FieldHTML(fp_rs,"EmployeeID")%>">
<%=FP_FieldHTML(fp_rs,"EmployeeName")%></option>
<!--webbot bot="AspInclude" clientside u-incfile="../_fpclass/fpdbrgn2.inc" startspan --><!--#include file="../_fpclass/fpdbrgn2.inc"--><!--webbot bot="AspInclude" endspan i-checksum="52830" -->
</select><!--webbot bot="DatabaseRegionEnd" b-tableformat="FALSE" b-menuformat="TRUE" u-dbrgn2="../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside preview=" <span style="color: rgb(0,0,0); background-color: rgb(255,255,0)">Results</span> " startspan --><!--webbot bot="DatabaseRegionEnd" endspan --></nobr></p>
</td>
| |
|
|
|
blockhead
Posts: 50
Joined: 6/22/2004
Status: offline
|
RE: Updating Tables - 7/19/2004 6:53:32
You need to change this:
<option VALUE="<%=FP_FieldHTML(fp_rs,"EmployeeID")%>">
<%=FP_FieldHTML(fp_rs,"EmployeeName")%></option>
to this:
<%
tempEmployeeID = FP_FieldHTML(fp_rs,"EmployeeID")
response.write "<option"
if tempEmployeeID="1" then response.write " selected" end if
response.write " value='1'><%=FP_FieldHTML(fp_rs,"EmployeeName")%></option>"
response.write "<option"
if tempEmployeeID="2" then response.write " selected" end if
response.write " value='2'><%=FP_FieldHTML(fp_rs,"EmployeeName")%></option>"
response.write "<option"
if tempEmployeeID="3" then response.write " selected" end if
response.write " value='3'><%=FP_FieldHTML(fp_rs,"EmployeeName")%></option>"
response.write "<option"
if tempEmployeeID="4" then response.write " selected" end if
response.write " value='4'><%=FP_FieldHTML(fp_rs,"EmployeeName")%></option>"
%>
Basically, you're telling the dropdown box to mark selected the value that's currently in the database
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Updating Tables - 7/19/2004 22:09:24
I get the following error:quote:
Microsoft VBScript compilation error '800a0401'
Expected end of statement
/documentation/DBoncall_admin_sd.asp, line 164
response.write " value='1'><%=FP_FieldHTML(fp_rs,"EmployeeName")
-------------------------------------------------------------^
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Updating Tables - 7/21/2004 19:20:48
Ok, this did not work and I am pretty sure why.
The way that I have the DB setup is there are 2 tables. The first table (tblContactInfo) has 4 fields (EmployeeID [autonumber], EmployeeName [Text], EmployeePhone[Text] & EmployeeRank[Text]) which contain the contact info for the employees. The second table (tblOnCall) has 3 fields (Contact [number], Contact_Priority [number] & Effective_Date [date/time]).
There are 4 levels of priority (1-4) that represent the order in which they are to be contacted. There are also 2 different ranks, 1 (for technical contact) and 2 (for manager contact).
Page1 accesses tblContactInfo and has 4 dropdown lists. The first 1 is the primary contact for technical and the second is the backup contact. The third and fourth dropdowns are for the primary and backup manager contact. The drop downs query the DB and display the contacts for that rank ( the 1st & 2nd query Rank 2 and the 3rd & 4th query Rank 1).
The user selects the primary and secondary contacts for each the technical and manager. The update is made to tblOnCall and actually updates the field CONTACT with the EmployeeID associated with the users name from tblContactInfo (thanks to BeTheBall for all the help with the joins and such).
The on call schedule is then viewed on Page2 in text format.
So with that said, I need to have the dropdown look at tblOnCall and see which contacts are assigned to the Priority 1 - 4 and select the proper employee from the list which pulls the contacts from tblContactInfo.
I had an idea to put a DRW query (actually 4 of them, one for each of the priorities 1-4) at the very top of the page which runs an access query I did (in access). It displays the priorities 1 thru 4 and the associated contact id and contact name. In the access query I have established a relationship between the 2 tables for tblContactInfo.EmployeeID and tblOnCall.Contact. When I display the query it shows me the 4 priorities in order and the employee names and id. I then created a variable [<%tempID1=FP_Field(fp_rs,"EmployeeID")%>] for the contact designated priority 1. This does work and I have a variable created in each of the new DRW's for each of the priorities.- tempID1
- tempID2
- tempID3
- tempID4
The variables also work. I tested this by doing a response.write for each of the variables and they do display the contact name and id for the proper priorities.
What I need to do now, is to take those variables and implement them into the dropdowns and select the employee's id in the list that is associated with the tblOnCall contact for that priority.
This is the existing query that pulls all the contacts from tblContactInfo with an EmployeeRank of 2:<option VALUE="<%=FP_FieldHTML(fp_rs,"EmployeeID")%>">
<%=FP_FieldHTML(fp_rs,"EmployeeName")%></option> This is what I was trying to do: Select the EmployeeID in the list that is equal to tempID1.
When I tried the previous suggestions each contact in tblContactInfo was duplicated four times. So when I dropped down the list, instead of the 8 contacts in the DB, there were now 4 of each resulting in a drop down with 32 names. I trimmed the suggestion down from:<%
tempEmployeeID = FP_FieldHTML(fp_rs,"EmployeeID")
Response.write "<option"
if tempEmployeeID="1" then response.write " selected" end if
response.write " value='1'>"&FP_Field(fp_rs,"EmployeeName")&"</option>"
Response.write "<option"
if tempEmployeeID="2" then response.write " selected" end if
response.write " value='2'>"&FP_Field(fp_rs,"EmployeeName")&"</option>"
Response.write "<option"
if tempEmployeeID="3" then response.write " selected" end if
response.write " value='3'>"&FP_Field(fp_rs,"EmployeeName")&"</option>"
Response.write "<option"
if tempEmployeeID="4" then response.write " selected" end if
response.write " value='4'>"&FP_Field(fp_rs,"EmployeeName")&"</option>"
%> To:<%
tempEmployeeID = FP_FieldHTML(fp_rs,"EmployeeID")
Response.write "<option"
if tempEmployeeID="1" then response.write " selected" end if
response.write " value='1'>"&FP_Field(fp_rs,"EmployeeName")&"</option>"
%> And that brought the list back down to individual listings but it was not selecting the employee that was currently designated in tblOnCall. Then I started playing with the notion of 4 DRWs each with a single criteria of the Contact_Priority.
Please help, this seems so simple but I have spent the entire day trying to figure this out on my own and am no closer.
Thanks
Nate
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Updating Tables - 7/21/2004 20:30:40
Seems to me the solution should look more like this:
<option>Choose --></option>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<option VALUE="<%=FP_FieldHTML(fp_rs,"EmployeeID")%>" <%If FP_FieldHTML(fp_rs,"EmployeeID")=tempID1 Then response.write(" selected") End If%>>
<%=FP_FieldHTML(fp_rs,"EmployeeName")%></option>
</select>
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
Nathan Goulette
Posts: 274
Joined: 1/12/2003
From: Phoenix, AZ
Status: offline
|
RE: Updating Tables - 7/21/2004 23:22:06
This was the fix but not at first.
I realized after that my drop downs displayed names but the values were numbers. The way that I had it was trying to compare (if) the contact id variable (a number) and the EmployeeID (also a number) but as I just stated, it should have been compared to a name (EmployeeNAME). So I had to adjust my variable to be the name of the actual person selected: <%tempID2=FP_Field(fp_rs,"EmployeeNAME")%>. The comparison was then <option VALUE="<%=FP_FieldHTML(fp_rs,"EmployeeID")%>" <%If FP_FieldHTML(fp_rs,"EmployeeNAME")=tempID1 Then response.write(" selected") End If%>>
<%=FP_FieldHTML(fp_rs,"EmployeeName")%></option> which worked perfectly.
Thanks again everyone for helping me through the baby steps I needed.
Later
Nate
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
. It kept giving me an error that had a "pointer" to the WHERE command. 
k¥ 