CFSET for Creating a Column in Excel Output?

[wilcoHead]

Hello;

I have a memo filed output for Specialties. Unfortunatly, I am told that the way I have excel outputing the specialties (Specialty1, specialty2, specialty3) does not work for them. Is there anyway to use a CFSET tag to apply each different specialty output to a column with headers rather than having them all together?

Like:

Headers = Specialty1 Specialty2 Specialty3

X X

So, by looking at the top here, my database would read:
Specialty1, Specialty3.

Here is my current code for the Excel Output.

----------------------------------------------------

<CFQUERY NAME="GetDealers" DATASOURCE="HEINE_USERS">
SELECT
Dealer_specialties_tbl.*,
Specialty_tbl.*,
Dealer_tbl.*

FROM
Dealer_specialties_tbl,
Specialty_tbl,
Dealer_tbl

WHERE
Dealer_specialties_tbl.specialtyID = Specialty_tbl.SpecialtyID AND
Dealer_tbl.DealerID = Dealer_specialties_tbl.DealerID AND
Dealer_tbl.Region2 = 1

ORDER BY Country, Dealer_tbl.company_nm

</CFQUERY>
<CFCONTENT type="application/vnd.ms-excel">
<cfoutput>Dealer Name#chr(9)# First Name#chr(9)# Last Name#chr(9)# Address#chr(9)# City#chr(9)# State#chr(9)# Zip#chr(9)# Country#chr(9)# Phone#chr(9)# Fax#chr(9)# Email#chr(9)# Web#chr(9)# Specialties#chr(10)#</CFOUTPUT>
<cfoutput query = "GetDealers" group = "company_nm">#company_nm##chr(9)# #first_nm##chr(9)# #last_nm##chr(9)# #address##chr(9)# #city##chr(9)# #state##chr(9)# #zip##chr(9)# #Country##chr(9)# #phone##chr(9)# #fax##chr(9)# #email##chr(9)# #url##chr(9)# <cfoutput> #specialty#</cfoutput>#chr(10)#</cfoutput>

-----------------------------------------------



Thanks for your input;
WilcoHEAD

 

[TruthInSatire]

huh? you have multiple specialties in one memo field in a db and you want them in seperate columns in excel? am i getting that correct?

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[wilcoHead]

Yes, you are correct.

Thanks for your input;
WilcoHEAD

 

[TruthInSatire]

what does the memo field look like?

I feel left, right, and mid functions coming up...

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[wilcoHead]

There are 3 Tables:

Dealer_specialties_tbl = Links "Dealer Name" to "Specialty" (Detail Table)
--------------

Specialty_tbl = Name of Specialty (Specialty ID)
--------------

Dealer_tbl = Name of Dealer
---------------

My mistake, it is not a Memo field. So what is happening is that the query is grabing all of the specialties associated with that Dealer. See query and output below:


Query
--------------
<CFQUERY NAME="GetDealers" DATASOURCE="HEINE_USERS">
SELECT
Dealer_specialties_tbl.*,
Specialty_tbl.*,
Dealer_tbl.*

FROM
Dealer_specialties_tbl,
Specialty_tbl,
Dealer_tbl

WHERE
Dealer_specialties_tbl.specialtyID = Specialty_tbl.SpecialtyID AND
Dealer_tbl.DealerID = Dealer_specialties_tbl.DealerID AND
Dealer_tbl.Region2 = 1

ORDER BY Country, Dealer_tbl.company_nm

</CFQUERY>
------------------------

Output
------------------------
<CFCONTENT type="application/vnd.ms-excel">
<cfoutput>Dealer Name#chr(9)# First Name#chr(9)# Last Name#chr(9)# Address#chr(9)# City#chr(9)# State#chr(9)# Zip#chr(9)# Country#chr(9)# Phone#chr(9)# Fax#chr(9)# Email#chr(9)# Web#chr(9)# Specialties#chr(10)#</CFOUTPUT>
<cfoutput query = "GetDealers" group = "company_nm">#company_nm##chr(9)# #first_nm##chr(9)# #last_nm##chr(9)# #address##chr(9)# #city##chr(9)# #state##chr(9)# #zip##chr(9)# #Country##chr(9)# #phone##chr(9)# #fax##chr(9)# #email##chr(9)# #url##chr(9)# <cfoutput> #specialty#</cfoutput>#chr(10)#</cfoutput>

Thanks for your input;
WilcoHEAD

 

[TruthInSatire]

so you're roughly You're getting

dealer1 | Name1 | specialty1
dealer1 | Name1 | specialty2
dealer1 | Name1 | specialty3

but you want

dealer 1 | Name1 | specialty1 | specialty2 | specialty 3

are we on the same page still? it sounded like all your specialies were in one memo field, this is a little different.



We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[TruthInSatire]

actually a closer look says you're getting something that looks like

dealer 1 | Name1 | specialty1 specialty2  specialty 3

if you add the #chr(9)# to the inside of the cfoutput you should get each specialty in its own cell.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[wilcoHead]

Yes, at first I thought it was a memo field but I was mistaken.

I want to create the headers in the excel output:

-------------------------------------
specialty1 | specialty2 | specialty 3
-------------------------------------

That is easy enough.

But then I need to input something like:

If Specialty1 exists for Dealer1 place an X in the cell for Specialty1. etc..

So it would look like this in excel.

---------------------------------------------------
Dealer Name | specialty1 | specialty2 | specialty 3
---------------------------------------------------
Dealer1 | X | |
---------------------------------------------------



Thanks for your input;
WilcoHEAD

 

[TruthInSatire]

ah i see now.

try changing
<cfoutput> #specialty#</cfoutput>
to
<cfoutput>
<cfif specialty neq "">X#chr(9()#<cfelse>#chr(9()#</cfif>
</cfoutput>



We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[TruthInSatire]

duh, that wont exactly work. does the description actually say "specialty 1" "specialty 2" ect...?

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[wilcoHead]

Actually, no.

The Output is now #specialty#

and the specialties are as followed:

Anesthesiology
Dentistry
Dermatology
General Practice
Ophthalmic
Veterinary
Student Dealer

Thanks for your input;
WilcoHEAD

 

[TruthInSatire]

try using QoQ and valuelist()

<CFCONTENT type="application/vnd.ms-excel">
<cfoutput>
Dealer Name#chr(9)# First Name#chr(9)# Last Name#chr(9)# Address#chr(9)# City#chr(9)# State#chr(9)# Zip#chr(9)# Country#chr(9)# Phone#chr(9)# Fax#chr(9)# Email#chr(9)# Web#chr(9)# Specialties#chr(10)#
</CFOUTPUT>
<cfoutput query = "GetDealers">
#company_nm##chr(9)# #first_nm##chr(9)# #last_nm##chr(9)# #address##chr(9)# #city##chr(9)# #state##chr(9)# #zip##chr(9)# #Country##chr(9)# #phone##chr(9)# #fax##chr(9)# #email##chr(9)# #url##chr(9)#
<cfquery name = "qGetSpec" dbtype = "query">
SELECT specialty from GetDealers
</cfquery>
<cfset specList = valueList(qGetSpec.specialty)>
<!--- each specialty col has a cfif in it depending on what column it is... --->
<cfif listFindNoCase(specList, "Anesthesiology")>X#chr(9)#<cfelse>#chr(9)#</cfif>
<cfif listFindNoCase(specList, "Dentistry")>X#chr(9)#<cfelse>#chr(9)#</cfif>
<!--- continue for the rest of the specialties --->
#chr(10)#
</cfoutput>

rudy may have a better sql solution but this should work fine if you want to use cf.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[TruthInSatire]

crap.
change
<cfquery name = "qGetSpec" dbtype = "query">
SELECT specialty from GetDealers
</cfquery>


to
<cfquery name = "qGetSpec" dbtype = "query">
SELECT specialty from GetDealers where dealerid = #getDealer.dealerid#
</cfquery>


We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[wilcoHead]

I am getting an error:

Element DEALERID is undefined in GETDEALER.


Here is the code:


<CFQUERY NAME="GetDealers" DATASOURCE="HEINE_USERS">
SELECT
Dealer_specialties_tbl.*,
Specialty_tbl.*,
Dealer_tbl.*

FROM
Dealer_specialties_tbl,
Specialty_tbl,
Dealer_tbl

WHERE
Dealer_specialties_tbl.specialtyID = Specialty_tbl.SpecialtyID AND
Dealer_tbl.DealerID = Dealer_specialties_tbl.DealerID AND
Dealer_tbl.Region2 = 1

ORDER BY Country, Dealer_tbl.company_nm
</CFQUERY>
<CFCONTENT type="application/vnd.ms-excel">
<cfoutput>Dealer Name#chr(9)# First Name#chr(9)# Last Name#chr(9)# Address#chr(9)# City#chr(9)# State#chr(9)# Zip#chr(9)# Country#chr(9)# Phone#chr(9)# Fax#chr(9)# Email#chr(9)# Web#chr(9)# Specialties#chr(10)#</CFOUTPUT>
<cfoutput query = "GetDealers">#company_nm##chr(9)# #first_nm##chr(9)# #last_nm##chr(9)# #address##chr(9)# #city##chr(9)# #state##chr(9)# #zip##chr(9)# #Country##chr(9)# #phone##chr(9)# #fax##chr(9)# #email##chr(9)# #url##chr(9)#<cfquery name = "qGetSpec" dbtype = "query">SELECT specialty from GetDealers where dealerid = #getDealer.dealerid#</cfquery><cfset specList = valueList(qGetSpec.specialty)><cfif listFindNoCase(specList, "Anesthesiology")>X#chr(9)#<cfelse>#chr(9)#</cfif><cfif listFindNoCase(specList, "Dentistry")>X#chr(9)#<cfelse>#chr(9)#</cfif><cfif listFindNoCase(specList, "Dermatology")>X#chr(9)#<cfelse>#chr(9)#</cfif><cfif listFindNoCase(specList, "General Practice")>X#chr(9)#<cfelse>#chr(9)#</cfif><cfif listFindNoCase(specList, "Ophthalmic")>X#chr(9)#<cfelse>#chr(9)#</cfif><cfif listFindNoCase(specList, "Veterinary")>X#chr(9)#<cfelse>#chr(9)#</cfif><cfif listFindNoCase(specList, "Student Dealer")>X#chr(9)#<cfelse>#chr(9)#</cfif>#chr(10)#</cfoutput>



Thanks for your input;
WilcoHEAD