469,315 Members | 1,847 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,315 developers. It's quick & easy.

URL Method for 3 tables?

769 512MB
Hey Everyone,

i been working on trying to understand the URL method of retrieving information for the last week, but i am stuck. I been able to get one table of information, but now i need to get 3 tables of information at the same time an i am running into trouble. Each table has a number in common with the other, each has a field that holds the number (but fields have different name). For example ticketMaster table has the field pk_ticketID which holds the number 1, serial has the field pkb_fk_ticketNo which holds the number 1, and parts has the field fk_ticketNo which holds the number 1. Right now i can get the correct table information for ticketMaster but for the serial an parts i get all the records that are in those tables instead of just the one record needed, which in this case is the one record that holds the number 1.

Right now here is what i have


Expand|Select|Wrap|Line Numbers
  1.  <!---Shows what was previously entered into table ticketmaster--->
  2. <cfquery name="ticket" datasource="CustomerSupport">
  3.  SELECT pk_ticketID,title,priority,status,
  4. cost_center,fk_customer_number,
  5. customer_company,customer_Fname,customer_Lname,
  6. customer_add1,customer_city,customer_state,
  7. customer_zip,customer_email,customer_pri_phone,
  8. customer_sec_phone,customer_notes,htpp FROM dbo.tbl_CS_ticketMaster
  9. WHERE pk_ticketID = #URL.pk_ticketID#
  10. </cfquery>
  11.  
  12. <cfquery name="serial" datasource="CustomerSupport">
  13. SELECT pka_serialNo,pkb_fk_ticketNo,model_no,product_type ,
  14. software_hardware,resolution,resolution_date,
  15. verification_date,rma_data,
  16. type_hardware_failure,dept_responsibility,resoluti on_verified_by FROM dbo.tbl_CS_serial
  17. </cfquery>
  18.  
  19. <cfquery name="parts" datasource="CustomerSupport">
  20. SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,
  21. part_returned,defective,submission
  22. FROM dbo.tbl_CS_parts
  23. </cfquery>
  24.  
  25. <form name="page1" id="page1" action="saveticket1edit.cfm?<cfoutput query="ticket">pk_ticketID=#pk_ticketID#</cfoutput><cfoutput query="serial">&pkb_fk_ticketNo=#pkb_fk_ticketNo#</cfoutput><cfoutput query="parts">&fk_ticketNo=#fk_ticketNo#</cfoutput>"
  26.  method="POST" onSubmit="return validate_form();">
  27. </form>
if anyone could explain to me how to get the url method to work with 3 fields in 3 different tables i would really appreciate it.

Thank you in advance,
Rach
Sep 22 '08
107 7600
acoder
16,027 Expert Mod 8TB
Since this is a new problem that's not going to be solved in one or two posts, I've split off and created a new thread for the new question here. Continue the discussion there. Any improvements to the serials/parts display can, of course, continue here.
Oct 3 '08 #101
bonneylake
769 512MB
Since this is a new problem that's not going to be solved in one or two posts, I've split off and created a new thread for the new question here. Continue the discussion there. Any improvements to the serials/parts display can, of course, continue here.
Hey Acoder,

I actually have another question concerning parts. The way the information goes into onsite_flag field in the database it appears as a Y or N and i was wondering how could i make it appear to the user as Yes or No.I tried to do it similar to the
checkbox where you would do this

Expand|Select|Wrap|Line Numbers
  1. <cfif #defective# eq 1>
  2.         Yes
  3.     <cfelse>
  4.     No
  5.     </cfif> 
  6.  
i changed #defective# to #onsite_flag# and the 1 to Y but it didn't seem to work. Anyway to make it do something like this?

Thank you,
Rach
Oct 3 '08 #102
bonneylake
769 512MB
Hey Acoder,

I actually have another question concerning parts. The way the information goes into onsite_flag field in the database it appears as a Y or N and i was wondering how could i make it appear to the user as Yes or No.I tried to do it similar to the
checkbox where you would do this

Expand|Select|Wrap|Line Numbers
  1. <cfif #defective# eq 1>
  2.         Yes
  3.     <cfelse>
  4.     No
  5.     </cfif> 
  6.  
i changed #defective# to #onsite_flag# and the 1 to Y but it didn't seem to work. Anyway to make it do something like this?

Thank you,
Rach
Hey Acoder,

I figured it out. Not sure what i was doing wrong. but in case anyone wanted to know here is what i did

Expand|Select|Wrap|Line Numbers
  1. <cfif #onsite_flag# eq "Y">
  2. Yes
  3. <cfelse>
  4. No
  5. </cfif>
but i do got another question. If you get the date from your table, is there anyway to make the date display different? i use the field #followup_date#
to display the date but when it displays it does this 2008-09-23 00:00:00
an would like to get rid of the zeros on the end and was just wondering how i could make it so it only display the 2008-09-23?

Thank you again for all the help,
Rach
Oct 3 '08 #103
acoder
16,027 Expert Mod 8TB
Use the DateFormat function with the appropriate mask.
Oct 3 '08 #104
bonneylake
769 512MB
Use the DateFormat function with the appropriate mask.
Hey Acoder,

Bit confused on what you mean.This is how i had done it for a previous form

Expand|Select|Wrap|Line Numbers
  1. #DateFormat(now(),'mmm dd yyyy')#
. But the thing is it has to get the date that was entered into #followupdate#

Thank you,
Rach
Oct 10 '08 #105
acoder
16,027 Expert Mod 8TB
Replace now() with followupdate.
Oct 10 '08 #106
bonneylake
769 512MB
Replace now() with followupdate.
Hey Acoder,

Thats it! Thank you, Thank you!!!

Rach
Oct 10 '08 #107
acoder
16,027 Expert Mod 8TB
You're welcome again :)
Oct 11 '08 #108

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Michael | last post: by
2 posts views Thread by Fred Flintstone | last post: by
5 posts views Thread by rn5a | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.