Cross Reference Search Results

	Home Register Search Help Login

Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Cross Reference Search Results

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> Cross Reference Search Results

Page: [1]

sourkrouse

Posts: 28
Joined: 10/13/2007
Status: offline

Cross Reference Search Results - 1/15/2008 11:57:21

Good Morning!
I am needing some help with a search query. I am searching an employee database from a First/Last name or an Employee ID (AKA - RACF). The results produce the employee name, dept and manager RACF.

I want to then take the manager RACF and put that back into a query on the same table to produce the full name of the manager.

I really have no clue where to start other then an If/Then statement in the DRW but I cannot even get an example. I am using a SQL table. Here is my current code:

<%
fp_sQry="SELECT * FROM T_RESOURCE WHERE (FIRST_NM LIKE '::FIRST_NM::%' AND LAST_NM LIKE '::LAST_NM::%' OR RACF_ID =  '::RACF_ID::') ORDER BY LAST_NM ASC"
fp_sDefault="FIRST_NM=Null&LAST_NM=Null"
fp_sNoRecords="<tr><td colspan=6 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="DATABASETABLENAME"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&RESOURCE_ID=3&COST_CTR_CD=200&LOCATION_CD=200&COMPANY_ID=129&RACF_ID=129&JOB_TITLE_NM=200&MAIL_DROP_TXT=200&EMAIL_ADDRESS_TXT=200&MANAGER_RACF_ID=129&NCA_USER_ID=3&LAST_NM=200&MIDDLE_NM=200&FIRST_NM=200&COMMON_NM=200&EMPLOYEE_FLG=129&VOICE_AREA_CODE_NBR=129&VOICE_PREFIX_NBR=129&FLOOR_SUITE_TXT=200&VOICE_SUFFIX_NBR=129&VOICE_INTERNAL_DIAL_PREFIX_NBR=129&SUFFIX_NM=200&VOICE_NTWRK_PREFIX_NBR=129&FAX_AREA_CODE_NBR=129&SURROGATE_RACF_ID=129&FAX_PREFIX_NBR=129&FAX_SUFFIX_NBR=129&FAX_NTWRK_PREFIX_NBR=129&SECRETARY_VOICE_AREA_CODE_NBR=129&SECRETARY_VOICE_PREFIX_NBR=129&FAX_INTERNAL_DIAL_PREFIX_NBR=129&SECRETARY_VOICE_SUFFIX_NBR=129&SECRETARY_VOICE_INTERNAL_DIAL_PREFIX_NBR=129&SECRETARY_VOICE_NTWRK_PREFIX_NBR=129&CELL_AREA_CODE_NBR=129&CELL_PREFIX_NBR=129&CELL_SUFFIX_NBR=129&VOICE_MAIL_AREA_CODE_NBR=129&PAGER_AREA_CODE_NBR=129&PAGER_PREFIX_NBR=129&VOICE_MAIL_INTERNAL_DIAL_PREFIX_NBR=129&PAGER_SUFFIX_NBR=129&VOICE_MAIL_PREFIX_NBR=129&VOICE_MAIL_NTWRK_PREFIX_NBR=129&PHONE_COMMENTS_TXT=200&VOICE_MAIL_SUFFIX_NBR=129&MAIL_ADDR_LINE_1_TXT=200&MAIL_ADDR_LINE_2_TXT=200&MAIL_ADDR_LINE_3_TXT=200&MAIL_ADDR_LINE_4_TXT=200&MAIL_CITY_TXT=200&MAIL_STATE_TXT=200&MAIL_ZIP_CODE_TXT=200&LST_MOD_DTTM=135&EMP_ID=129&"
fp_iDisplayCols=6
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

What I need is something like this:
From this result where Manager_RACF_ID =RACF_ID give me the First_NM Middle_IN Last_NM of that row

One more thing - the search form and results are all on the same page, I can't change this because the form is a popup that then prefills the original form where the popup link is located. I just want to get all the results and then close the form, passing those results to the parent form. I've already got that part working. Hope that wasn't TMI.

Any Ideas? Thanks in advance.


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Cross Reference Search Results - 1/15/2008 13:02:30 quote: put that back into a query on the same table You want that on each line of the table as well? Have you tried a JOIN query? Something like this maybe: <% fp_sQry="SELECT *T_Resource., Manager_table.First_NM, Manager_table.Middle_IN, Manager_table.Last_NM FROM T_RESOURCE INNER JOIN Manager_table ON T_Resource.RACF_ID = Manager_table.Manager_RACF_ID** WHERE (FIRST_NM LIKE '::FIRST_NM::%' AND LAST_NM LIKE '::LAST_NM::%' OR RACF_ID = '::RACF_ID::') ORDER BY LAST_NM ASC" I think that should be pretty close. You'll need to change the "Manager_table" since I didn't quite get the name of that table. That help any? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to sourkrouse)


sourkrouse Posts: 28 Joined: 10/13/2007 Status: offline	RE: Cross Reference Search Results - 1/15/2008 13:32:26 Actually there is only one table (T_Resource) so would I still use a JOIN? I'm a beginner and have only ever worked with one table at a time before but I believe a JOIN is used when there is more then one table. My SQL would look like this: <% fp_sQry="SELECT T_Resource.*, T_Resource.First_NM, T_Resource.Middle_IN, T_Resource.Last_NM FROM T_RESOURCE INNER JOIN T_Resource ON T_Resource.RACF_ID = T_Resource.Manager_RACF_ID WHERE (FIRST_NM LIKE '::FIRST_NM::%' AND LAST_NM LIKE '::LAST_NM::%' OR RACF_ID = '::RACF_ID::') ORDER BY LAST_NM ASC" Is that right? I'm terrible at explaining things so I appreciate your patience I think what is so difficult for me to understand is how I display the First, Middle and last name of both the employee and the manager when they are both coming from the same table. Thanks for the help. (in reply to rdouglass)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Cross Reference Search Results - 1/15/2008 13:51:38 quote: so would I still use a JOIN? In SQL Server you can and I think you can in Access as well. Something like this comes to mind: fp_sQry="SELECT T_RESOURCE .*, T_RESOURCE_1.First_NM As Manager_First, T_RESOURCE_1.Middle_IN As Manager_MI, T_RESOURCE_1.Last_NM As Manager_Last FROM T_RESOURCE LEFT OUTER JOIN T_RESOURCE As T_RESOURCE_1 ON T_RESOURCE.RACF_ID = T_RESOURCE_1.Manager_RACF_ID WHERE (T_RESOURCE.FIRST_NM LIKE '::FIRST_NM::%' AND T_RESOURCE.LAST_NM LIKE '::LAST_NM::%' OR T_RESOURCE.RACF_ID = '::RACF_ID::') ORDER BY T_RESOURCE.LAST_NM ASC" (I hope I got that right) See what I did? I just used the table twice by using this: T_RESOURCE As T_RESOURCE_1 Then, I can join the two by connecting the employee with the manager. I used a LEFT OUTER JOIN in case a record doesn't have a matching manager. Unless of course managers have managers and there is one that manages themselves... That any better? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to sourkrouse)


sourkrouse Posts: 28 Joined: 10/13/2007 Status: offline	RE: Cross Reference Search Results - 1/15/2008 15:16:23 OK, I tried both versions and on the first version I got this: Database Results Error Description: Tables or functions 'T_RESOURCE' and 'T_RESOURCE' have the same exposed names. Use correlation names to distinguish them. On the second version I got a Timeout Error however we have been having some similar performance issues on that SQL server in the last few weeks so I can't be sure it's the SQL statement or just the server. When I mentioned the If/Then, I was thinking more like this: <run the sSQL statement to grab the results from my search form, searching by employee name or their ID> fp_sQry="SELECT * FROM T_RESOURCE WHERE (FIRST_NM LIKE '::FIRST_NM::%' AND LAST_NM LIKE '::LAST_NM::%' OR RACF_ID = '::RACF_ID::') ORDER BY LAST_NM ASC" <then insert an If/then statement here maybe> IF the MANAGER_RACF_ID from the result above = a RACF_ID in the T_RESOURCE TABLE THEN Give me the FIRST_NM, MIDDLE_NM and LAST_NM of that RACF_ID END IF < then somehow diplay those results below> Results are FIRST_NM, MIDDLE_NM and LAST_NM of employee searched for AND FIRST_NM, MIDDLE_NM and LAST_NM of their manager Don't know if this is possible or the way to go. (in reply to rdouglass)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Cross Reference Search Results - 1/15/2008 15:43:24 quote: < then somehow diplay those results below> I guess I'm a little confused with that. Are you looking at 1 person's record or a list of records? If it's just 1, how about a second DRW? _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to sourkrouse)

sourkrouse

Posts: 28
Joined: 10/13/2007
Status: offline

RE: Cross Reference Search Results - 1/15/2008 16:22:53

There are 33,000 employees in the database. If I search by name there could be multiple results (for example last name Smith). I could do a second DRW but I am not sure how that would look.

In each row there is a Last name, Middle name, First name, <their>manager's RACF ID and <their own>RACF ID.

I want to search each row by name or RACF ID. Those results will give me the name, RACF ID and manager's RACF ID.

I then want to plug the manager's RACF ID of that result back into the table to get their full name (not just the RACF/ID) and display all the results.

Back to the second version:
fp_sQry="SELECT T_RESOURCE .*, T_RESOURCE_1.FIRST_NM As Manager_First, T_RESOURCE_1.MIDDLE_NM As Manager_MI, T_RESOURCE_1.LAST_NM As Manager_Last FROM T_RESOURCE LEFT OUTER JOIN T_RESOURCE As T_RESOURCE_1 ON T_RESOURCE.RACF_ID = T_RESOURCE_1.Manager_RACF_ID WHERE (T_RESOURCE.FIRST_NM LIKE '::FIRST_NM::%' AND T_RESOURCE.LAST_NM LIKE '::LAST_NM::%' OR T_RESOURCE.RACF_ID = '::RACF_ID::') ORDER BY T_RESOURCE.LAST_NM ASC"

The server is now responding so it was just a server timeout error and it is pulling something but I am not sure what, it's not correct and in most cases blank. I display both like this

<%=FP_FieldVal(fp_rs,"LAST_NM")%>,<%=FP_FieldVal(fp_rs,"FIRST_NM")%>

Which is the first and last name of the employee they searched for.
Then this for the managers name of that employee:

<%=FP_Field(fp_rs,"Manager_First")%>, <%=FP_Field(fp_rs,"Manager_Last")%>

Is that right?

(in reply to rdouglass)


sourkrouse Posts: 28 Joined: 10/13/2007 Status: offline	RE: Cross Reference Search Results - 1/15/2008 18:01:43 Ok, I kind of see what it's doing now. When I type L in the first name and B in the last name, click search, I get about 25 results. One name is Larry Brown with a manager ID of RC10 (RC being the initials of the managers name) but the name in the managers name space is Nancy Conlin. She is actually an employee of Larry Brown. So it's taking the emplyee ID of the result and plugging it into the manager id. I need it to do the opposite. Take the manager ID of the result (RC10) and plug it back in to get that person's full name (i.e. - Richey Cuttingham who would be the manager of Larry Brown). I will try a couple trial and errors but what about a sub-query if not an If/Then statement? Hopefully I didn't confuse you to much. Thanks a lot! (in reply to sourkrouse)


rdouglass Posts: 9280 From: Biddeford, ME USA Status: offline	RE: Cross Reference Search Results - 1/16/2008 11:16:47 Personally speaking, I am not all that good at hacking DRW's anymore. If you have 1 record returned, that is fairly easy to do. We'd just have to re-work the query to show the correct Manager / Employee relationship. We've apparently built a sucessfull relationship inside the same table so now we'd just fix that one. If you return more than 1 record it becomes more difficult (IMO anyways) for the DRW to show the manager of only the one you select / click / hover over. What you may want to consider is a second DRW that loads the manager below when you click a link in the 'upper' DRW. It would re-load the page but hey, we're dealing with the DRW and I believe this is one area where there'd be some limiting factors. Without the DRW, I would load all the records into an array and display using JavaScript and CSS the manager info below when you hover or click on a record above. With the DRW, you could probably do it using the same JS and CSS with some hacking. I would not dare have you attempt this with the DRW. However, to do it the way I'd do it is not for the ASP-weak-of-heart and you'd have to completely step away from the DRW. _____________________________ Don't take you're eye off your final destination. ASP Checkbox Function Tutorial. (in reply to sourkrouse)