URL Method for 3 tables?

[bonneylake]

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

 <!---Shows what was previously entered into table ticketmaster--->
<cfquery name="ticket" datasource="CustomerSupport">
 SELECT pk_ticketID,title,priority,status,
cost_center,fk_customer_number,
customer_company,customer_Fname,customer_Lname,
customer_add1,customer_city,customer_state,
customer_zip,customer_email,customer_pri_phone,
customer_sec_phone,customer_notes,htpp FROM dbo.tbl_CS_ticketMaster
WHERE pk_ticketID = #URL.pk_ticketID#
</cfquery>
 
<cfquery name="serial" datasource="CustomerSupport">
SELECT pka_serialNo,pkb_fk_ticketNo,model_no,product_type ,
software_hardware,resolution,resolution_date,
verification_date,rma_data,
type_hardware_failure,dept_responsibility,resoluti on_verified_by FROM dbo.tbl_CS_serial
</cfquery>
 
<cfquery name="parts" datasource="CustomerSupport">
SELECT pk_partID,fk_serialNo,fk_ticketNo,hc_partNo,
part_returned,defective,submission
FROM dbo.tbl_CS_parts
</cfquery>
 
<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>"
 method="POST" onSubmit="return validate_form();">
</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

[acoder]

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.

[bonneylake]

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

<cfif #defective# eq 1>
        Yes
    <cfelse>
    No
    </cfif> 
 

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

[bonneylake]

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

<cfif #defective# eq 1>
        Yes
    <cfelse>
    No
    </cfif> 
 

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

<cfif #onsite_flag# eq "Y">
Yes
<cfelse>
No
</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

[acoder]

Use the DateFormat function with the appropriate mask.

[bonneylake]

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

#DateFormat(now(),'mmm dd yyyy')#

. But the thing is it has to get the date that was entered into #followupdate#

Thank you,
Rach

[acoder]

Replace now() with followupdate.

[bonneylake]

Replace now() with followupdate.

Hey Acoder,

Thats it! Thank you, Thank you!!!

Rach

[acoder]

You're welcome again :)