Mutliple select list box basics

When you submit the product select, the multiple values are sent in a comma delimited list. You'll use that list as follows:

SELECT COLOR FROM qry_screen_output
WHERE ContactID = '#ContactID#'
AND Team = '#Team#'
AND PRODUCT IN ('#ListChangeDelims(Product,"','",",")#')
Order by COLOR

mike g?

Anyways, that answer does not help me, however I appreciate a start.

First, can you answer by the format i layed out (a) (b) and (c) , sorry i should have specified that.

Second, i believe you have started to answer (b) , however, i am lost as to how to write this line

 AND PRODUCT IN ('#ListChangeDelims(Product,"','",",")#')

What do all the apostrohpes mean, they are not consistent either...

Will this code return every value that meets the condition? i dont see how the code does that..do i need to define '#ListChangeDelims(Product,"','",",")# anywhere?

rob

i didn't check his syntax, but i'm just letting you know you may need to use the preservesinglequotes() function like:


AND PRODUCT IN ('#preservesinglequotes(ListChangeDelims(Product,"','",","))#')

(a) the listbox values are returned by the form in a comma delimted list.

(b)you'll have to use javascript or vbscript.  you would basically load all the possible sets of values, and then according to what is selected in the first listbox, you would have a function to change listbox 2 accordingly.  this isn't very trivial, but the javascript isn't very hard either.

http://www.atgconsulting.com/doublelist.asp may be similar to what you are looking for.  i'd also check
http://javascript.about.com/


(c) you will also have to use javascript.

i don't think its goign to be easy, what you are trying to do.  i'm not even sure it's possible dynamically.  

from what i know, you'll basically have to predetermine all your potential combinations and set them up by hand if you want to do this on one page.  that or change technologies.  =) of course, i could be wrong.

i would sugguest that you keep a small 0 px wide and 0px high "calculation" frame, where you can do the queries "dynamically" and pass your data back and forth between the frames.    

ex: in your display frame you would basically have you're form action pointed to the calculation frame.

then, when the user selected something on the listbox 1, you can do an onchange="form.submit();" this would then submit to the calculation frame for processing your query to find the items in listbox 2.  in the calculation page, you would then set up a javascript function that would point to listbox2 in the first frame, and populate its contents.  

do the same thing with part (c).

you see where I'm going with this?

Wow..i am increasing the points if someone can walk me to the finish line on this.

substand! i do not follow you. what is a display frame, we are talking coldfusion, yes..? are you just referring to the code view? or are you referring to the actionscript i write in flash mx (onchange doesnt look like coldfusion to me) to solve this? is this being solved in actionscript then?

could you expand your descriptions by providing the code to solve (a) (b) (c)...?

i guess mike had ur answere in the first go ...

incase u r still having problems with figuring things out - then i wld like to have a look at ur file ...

so that it cld be analysed & experts here can help u out quickly

K'Rgds
Anand

anand, he may have but i do not know how to write the Coldfusion to do it. I am also going to be using Flash to call this stuff , but I first need to write the right coldfusion to pull multiple selections. I hope you can help, I know we can get there on an iterative process. is it safe to give out my AIM screen name so that we could chat? or will it be retrieved by a spammer?

 Lets do it..can i send the file to you (OR ANYONE ELSE WHO NEEDS IT TO SOLVE MY PROBLEM) then. i only have a database right now, but will that help you visualize? i basically laid out the critical desired output and what i am doing pretty well above. let me know if i need to explain better.

I don't think I can give you a clear answer at this point 'cause I do not know how your tables are structured. But, basically you have to store the multiple values in a list, array, or structure and then determine the length(size). Then in the SQL command, it should be something like below. (Please note that there are always better and other ways to do things).

<CFSET ContactIDArray = ListToArray(ContactIDList)>
<CFSET TeamArray = ListToArray(TeamList)>
<CFSET FixArraySize = ArrayLen(ContactIDArray)>

<CFQUERY NAME="Your_CF_Name" DATASOURCE="Your_DS" USERNAME="Your_UserName" password="Your_Password">
SELECT PRODUCT FROM qry_screen_output
WHERE (
<CFSET ArraySize1 = FixArraySize>
<CFLOOP INDEX="LoopCount" FROM="1" TO="#ArraySize1#">
<CFSET ContactID_Temp = ContactIDArray[LoopCount]>
     ContacID = #ContactID_Temp# <CFIF FixArraySize NEQ LoopCount>OR<CFELSE>)</CFIF>
<CFSET ArraySize1 = ArraySize1 - 1>
</CFLOOP>

AND (
<CFSET ArraySize2 = FixArraySize>
<CFLOOP INDEX="LoopCount" FROM="1" TO="#ArraySize2#">
<CFSET Team_Temp = TempArray[LoopCount]>
     Team = #Team_Temp# <CFIF FixArraySize NEQ LoopCount>OR<CFELSE>)</CFIF>
<CFSET ArraySize = ArraySize - 1>
</CFLOOP>
</CFQUERY>

Myst, thanks, but i dont get it at all..however i am glad because it seems like this is some headway and maybe we can all work together to solve this. i have a sample table i could send you, i could also see if i can make the sample interface if my description does not explain what i am trying to make. bascially you have two list boxes where multiple values can be selected. the 2nd listbox is dependent on the first. then you have a text field which gives a sum , this sum depends on everything selected in lb_one and lb_two.

Do you understand what i am trying to do. I can let the user select as many PRODUCTS from lb_one and then lb_two values will change depending on what was selected in lb_one.

wHY ARE You altering the SELECT PRODUCT ...is that to enable it to select multiple values from the list box, i dont think i need the code for that do i? cant i just set it to multiple select value in flash for lb_one.

what about lb_two..the SELECT COLOR ..that is the trcky one because it depends on what was  selected in PRODUCT...hmmmm?

the table we are working on looks like this

User  ContactID  Team  PRODUCT  COLOR  SALES

of course there is another table wit user names and passwords because this is going to be a login app, i cna tell you already figured that out by looking at your code above. the other table is like this

Contact ID   User   LoginName    LoginPassword

Let me get this straight, product is lb_one, and depending on lb_one (multiple products), you retrieve the according colors which is lb_two?

Let me get this straight, product is lb_one, and depending on lb_one (multiple products), you retrieve the according colors which is lb_two?

I don't think I got your question right. I thought it was more complex. TallerMike already gave you the short answer. When you submit multiple values using HTML FORM, you get a delimited list with comma (ex) apple, orange, banana. However, you cannot just use the list in SQL statement 'cause it's not in the correct syntax. You have to change the list from apple, orange, banana to 'apple', 'orange', 'banana'. That's why TallerMike gave you ('#ListChangeDelims(Product,"','",",")#'). The syntax is listchangedelims(list, new_delimiter [, delimiters ]).
If you change the Product = apple, orange, banana using listchangedelims you'll get the perfect SQL syntax.

finnstone,

if you are using flash, it may be simpler. i don't know any flash, so i can't help there.

myst- yes, tallermike did answer that part of the question, but i think finnstone will also have trouble in figuring out how to display the data properly.  i think he knows what he wants, but from what i get out of the question is "how do i do it?".  Am I right or wrong finnstone?

Regarding the frames, i was talking regular html frames.  you would have a frameset with 2 frames, and use one of them to display everything, and another (hidden) frame to go back to the server and do your queries.

if you can do this in 3 separate pages, that would get you up an running the quickest, while you figure out the rest.  I'm not sure of the capabilities of flash, but just from what i've seen of it, i would think you could do what you are trying to do, with a lot less hassle than in html, javascript, and cold fusion.


post more details and let us know.

(1) I am not trying to do this in HTML.

(2) The list boxes/interface will be made in Flash. Basically this enables me to use Coldfusion SQL to populate the list boxes with values that are passed from the Flash log in screen and list boxes to the database and then back to the Flash list boxes and also to the final ouput which is amount of sales that goes in a textbox.... please look at this link to see a somewhat similar picture of what I am doing. please note this example does not use multiple selections which is why i am stumped

http://www.macromedia.com/devnet/mx/coldfusion/articles/remoting.html

(3) The difficulty in the application is that I am allowing the user to select multiple values from lb_one, multiple values from lb-two, and then depending on those multiple values, some all of their sales column info up and put that number into the text box.

*********

Myst, you are correct, products are listed in lb_one according to what user is signed on (i can do this already), what i cannot do is make the appropriate colors populate in lb_two if multiple products were selected in lb_one.

Question , you wrote "The syntax is listchangedelims(list, new_delimiter [, delimiters ])."

(1)how do i know how many delimiters were selected, how do i write the all encompassing SQL to retrieve all delimiters selected from lb_one ? this list length could vary ?

(2) then how do i manipulate this list selected in lb_one to populate lb_two .... (i would need the same answer for getting the list from lb_one and lb_two to populate the text box output of total sales number)

The comment that i know what i want to do but just dont know how to do it is correct.

I added more points again, thanks guys,,, this will be amazing if we figure it out!

SCHEMA
As for thhe sum.. A LITTLE clarification...this will be a text box that will contain the sum of all the sales for all the lb_one and lb_two selections that were made.

my db looks like this

User  ContactID  Team  PRODUCT  COLOR  SALES

bob    122       hp     labtop   bl     $120,000
bob    122       hp     labtop   gr     $120,000
bob    122       hp     monitor  gr     $96,000
bob    122       hp     monitor  red    $96,000
bob    122       hp     speakers  red   $96,000
bob    122       hp     speakers  red   $96,000


the SALES column will have numbers in it
lb_one selections come from PRODUCT
lb_two selection come from COLOR

in this example if bob had logged in on the login page, and then had chosen labtop and monitor from lb_one and bl and gr from lb_two, then the sum i would want to output in a textbox would be $356,000... this number comes from 120+120+96.!!
*******

the tricky part is that i cannot preprogram all the possible combinations, i want to read it from the database....

i hope thats someone can help me with the coldfusion to create the SQL statements necessary.. PLEASE NOTE i already could do the necessary coldfusion if this was a simple select one item from lb_one and lb_two...the fact that i want it to seelct multiple is what is tripping me up!

****
I would also want to show 2 other text boxes...

one would say "this 356,000 is made up of products : labtops,monitors."

the other would say "this 356,000 is made up of colors: bl and gr"

i'm about to post a more coded solution if this doesn't work, but since you said "PLEASE NOTE i already could do the necessary coldfusion if this was a simple select one item from lb_one and lb_two...the fact that i want it to seelct multiple is what is tripping me up!", I'm going to try this simple solution:

as TallerMike said, use this (and modify it to fit the other query) as your query:
----------------------------------------------------------
SELECT COLOR FROM qry_screen_output
WHERE ContactID = '#ContactID#'
AND Team = '#Team#'
AND PRODUCT IN ('#ListChangeDelims(Product,"','",",")#')
Order by COLOR
---------------------------------------------------------
i would change "#ListChangeDelims(Product,"','",",")#" to #preservesinglequotes(ListChangeDelims(Product,"','",","))# just to be safe.

then, to deal with the multiple rows returned by the query, you would do something like:

<cfset sum=0>
<cfloop query="nameOfTheQuery">
<cfset sum=sum+newValueFromQuery>
</cfloop>

ok, try this coldfusion.  do you have the dynamic population of the listboxes figured out?  if so, here's how you can get the data to be how you want it.

its not the most efficient code possible, but it will work to get the data, and you shouldn't have much trouble with it as long as the recordcounts are not in the hundreds of thousands, or as long as there aren't millions of people using this at the same instant.  It should make it so you don't have to "preprogram" all the possibilities, as I had said earlier when i didn't have as clear an understanding of the problem as I do now.

from what i read, using flash remoting mx, you probably can generate much better code, but i don't have access to it.

anyway, here's the code:

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

<cfquery name="getdata" datasource="yourDataSource">
select User, ContactID, Team, PRODUCT, COLOR, SALES
from yourTableName <!--- which i think was qry_screen_output--->
where ContactID = '#ContactId#'
and Team = '#Team#'
order by product, color;
</cfquery>
<!---
we will set up a "virtual table" so we can do this with one trip to the server/with one query.
we will use an array for each column (rather than 1 multidimensional array) so
it will be easier to read and understand which columns we are accessing.  
---->
<cfset vuser=arraynew(1)>
<cfset vcontactID=arraynew(1)>
<cfset vteam=arraynew(1)>
<cfset vproduct=arraynew(1)>
<cfset vcolor=arraynew(1)>
<cfset vsales=arraynew(1)>
<!--- the following loop fills our virtual table with data.  technically, we don't have to do this,
since coldfusion returns the results of a query in this format anyway.  but for readability, i do it.
 ---->
<cfloop query="getdata">
      <cfset vuser[currentrow]=user>
      <cfset vcontactID[currentrow]=contactID>
      <cfset vteam[currentrow]=team>
      <cfset vproduct[currentrow]=product>
      <cfset vcolor[currentrow]=color>
      <cfset vsales[currentrow]=sales>
</cfloop>



<!--- here is where you need to find out what they selected in lb_one ---->
<!--- whereClause should be the comma delimited list of items found in form.lb_one ---->
<cfset whereClause=form.lb_one>
<cfset tot_records=getdata.recordcount>
<!--- please note that the above variables don't really need to be declared, and declaring them will
      slow down the page a little.  i just declare them so we get a better idea of what the original
        ones will be used for... to make the code a little more readable and understandable.
--->

<!--- now we need to find which rows qualify to be shown in lb_two--->
<cfset rowlist=""> <!--- this will be the list of array indices we will use that contain the
correct info to be shown in lb_two --->
<cfloop from="1" to="#tot_records#" index="i">
      <cfloop list="#whereClause#" index="itemValue">
            <cfif vproduct[i]=itemValue>
                  <cfset rowlist=listappend(rowlist,i)>      
            </cfif>
      </cfloop>
</cfloop>

<!--- now we have all the products that should be shown in lb_two, you you will output lb_two like: ---->
<select multiple name="lb_two">
<cfloop list="#rowlist#" index="row">
      <cfset val=vproduct[row]>
      <option value="#val#">#val#
      <!--- again, this code is somewhat redundant, and can be simplified.  you could obviously use evaluate here to get it
      done in one line, but to simplify it, i made it two lines.  also, this entire loop can be gotten rid of by placing it
      in the above loop.  ie, put the <select> tag before the above loop, and close it after.  then instead of making
      rowlist, you could place the <option> tags there.  just to simplify it, i'm doing it this way.
      ---->
</cfloop>
</select>
<!--- of course that is html, so you'll need to use flash solution.  since you know flash and i don't i'll leave that up to you ---->

<!--- we now have lb_two populated. when the user then selects something from lb_two, we now need to find out what is selected in
lb_one and lb_two, and find the sum of the results ---->
<cfset whereClause2=form.lb_two>
<cfset rowlist2="">
<cfloop from="1" to="#tot_records#" index="i">
      <cfloop list="#whereClause2#" index="itemValue">
            <cfif vproduct[i]=itemValue>
                  <cfset rowlist2=listappend(rowlist2,i)>      
            </cfif>
      </cfloop>
</cfloop>
<!--- now we have all of the rows selected from lb_two in rowlist2.  
rowlist2 now contains only the rows that match both "queries", so we loop through these, adding to the sum for output
--->
<cfset sum=0>
<cfloop list="#rowlist2#" index="i">
      <cfset sum=sum+vsales[i]>
</cfloop>

<!--- now just output the value to the textbox as #sum#.  I hope this helps. ----->
--------------------------------------------------------------

the theory is sound, as long as you can do the dynamic updates.  i don't guarantee that there aren't syntax errors though.
i checked it as much as possible, but i didn't set up a DB like yours to make sure, so there may be some syntax errors.

if the above 2 don't work, and you want to send the code to sam@powersource3.com, i can take a look at it and send it back.

substand, i am digesting what you last sent.. i am going to analyze it before i move to the next round of questions.

btw if it turns out this works out i will give you another 500.

in response to your question which i first stumled on when reading...

"ok, try this coldfusion.  do you have the dynamic population of the listboxes figured out?  "

no i do not have the dynamic population figured out. but isnt this what the code you have provided is doing?

to populate lb_one i am going to use the following code..

SELECT PRODUCT FROM qry_screen_output
WHERE ContactID = '#ContactId#'
AND Team = '#Team#'
Order by PRODUCT

i do not know how to populate lb_two though based on lb_one.. does this mean i will need more help than the code you just provided.

i will work on getting a fla file together as well as a cfm to better help our conversation, hopefully this wont be necessary but i see it might help .. ! in the meantime i hope you can answer this comment above.


rob

well, you said if it wasn't multiple selections, then you could do it.  I wasn't sure if this was multiple rows of data or multiple queries or multiple list boxes.

the code I wrote above would work for the first two.  

as far as the "dynamic" part of your question- yes and no.  Like I said, I haven't worked with flash much- so depending on how much you know, it might or might not work.

the code above was meant to be used as data for what I consider the "dynamic" part.  lb_two's population (along with the sum) was what I was trying to do with that code.  That code provides the DATA you will need to populate lb_two and the sum, it does not provide the "HOW TO."

If you can send me the code, I can look at it and probably figure out the "HOW TO".  I'm really good at figuring out languages I don't know too much about (in fact, a couple of days ago I just got a whole bunch of points for figuring out something I started out knowing nothing about. at least with this one, I'm starting from something I know.)

I read a little bit of the link you sent (and the surroundign pages), and from what I know, you'll need the flash remoting to do it in flash.  Once I see it, I'll be able to better inform you, and at the lease, provide a javascript final solution if i can't figure out a flash one.  

in the mean time, I'll be on for probably another hour tonight, as I'm working myself trying to find out some solutions to vc++ graphics programming.  so write back.

sam



 

i'm going to look at some flash stuff before you get back to me...

one other thing you mention summing the results,

<!--- we now have lb_two populated. when the user then selects something from lb_two, we now need to find out what is selected in
lb_one and lb_two, and find the sum of the results ---->


i dont see any reerence to the SALES column in the database. you have to sum the SALES based on lb_one and lb_two , look at SCHEMA for calrification

maybe i just dont understadn your code, i will keep trying to understadn more

the sales column is referenced in the query, and the very end part adds them up.

the first loop gets us the columns that are dependant on your first query.  the second loop drills down those columns so that by the third loop, we are only checking the sales of the required columns.

going through right now...do the cfsets have to be case sensitive to how i have them defined as field names in the table??

     <cfset vuser[currentrow]=user>
     <cfset vcontactID[currentrow]=contactID>
     <cfset vteam[currentrow]=team>
     <cfset vproduct[currentrow]=product>
     <cfset vcolor[currentrow]=color>
     <cfset vsales[currentrow]=sales>

so does color have to be COLOR since this is how i have it in the database? if so i will just change all the field names in the database to be lowercase to be consistent.

sorry for the slowness and small questions ,more coming..still digesting.

thats awesome that you can learn languages like that.. i am like the junior junior version of you, its exciting to try new stuff. i hope you end of trying flash because i have done nothing in it, am wil probably need you to learn it too! but tonight i am going to put together to the best of my ability your code and my resources and see where i am

cool!

substand... i think i found something that will help.

please read this because i think it will be key to helping us.

i can see how it might fit in, but i am not proficient enough to know how to implement..pay particular attention to the part about Flash.params and Flash.Result!!!

I hope you see this as a learning opporunity , you will probably be able to pick up points in flash after we get done here.

btw, i will award 1500 (possibly more) when all is said and done ,this is becoming a beast.

i am still trying to put some base documents together to work off of...stay tuned.

sorry i didnt provide the link... this is basically how we can define your logic in flash and coldfusion.... we can talk back and forth to flash using this stuff, i think...i hope you can use the resource better than i :) but i think this is what you may need to translate your html solution to a sleek flash coldfusion solution.

http://www.macromedia.com/support/coldfusion/documentation/using_frmx_with_cfmx/

to answer your question regarding "do the cfsets have to be case sensitive..??"

no, it is not case sensitive. so VariAble is the same a vArIaBle ... etc.

send me the file at anand@direct2s.com & i'll see if i can see u thru with this ...

K'Rgds
Anand

User  ContactID  Team  PRODUCT  COLOR  SALES

bob    122       hp     labtop   bl     $120,000
bob    122       hp     labtop   gr     $120,000
bob    122       hp     monitor  gr     $96,000
bob    122       hp     monitor  red    $96,000
bob    122       hp     speakers  red   $96,000
bob    122       hp     speakers  red   $96,000


now finnstone as per your schema there are no id's for product and color.

now do you have tables for product and color separately.

I am currently assuming that you do not have any other tables.

SELECT distinct(COLOR) FROM qry_screen_output
WHERE ContactID = '#ContactId#'
AND Team = '#Team#'
Order by PRODUCT

The above query is for lb_one and the options shown will be labtop,monitor and speakers. These won't be shown more than once.

Now after you select single or multiple values from lb_one
the following query has to be run to form the colors in the lb_two

SELECT distinct(COLOR) FROM qry_screen_output
WHERE ContactID = '#Contactid#'
AND Team = '#Team#'
AND PRODUCT in ('labtop','monitor')
Order by COLOR

now you must be wondering that i have put hard  coded values in the above query. i.e AND PRODUCT in (...

now this should be replaced with the following code
#preservesinglequotes(ListChangeDelims(lb_one,"','",","))#

in the above code lb_one is your select box having the products selected by you.

now the value passed in the lb_one would be in the form of coma delimited values for eg : laptop,monitor or just laptop (if the user has selected only laptop.)

but in the query you need to have 'laptop','monitor' since they are strings so the ListChangeDelims() is used.

i hope this will clear all your doubts.

now since distinct has been used in the query for color (lb_two). no duplicate colors will be shown.

ie. gr and red and all will be only shown once.

now after you select your required colors then the following query has to be executed to get the sum

SELECT sum(sales)  FROM qry_screen_output
WHERE ContactID = '#Contactid#'
AND Team = '#Team#'
AND PRODUCT in ('labtop','monitor')
AND COLOR in ('bl','gr')

both the in (.. has to be replaced by the following codes
AND PRODUCT in (#preservesinglequotes(ListChangeDelims(lb_one,"','",","))#)

AND COLOR in (#preservesinglequotes(ListChangeDelims(lb_two,"','",","))#)

I think you must have already understood why the above code has to be put.

Now lastly the three queries that have to be used one after another have been listed below

---------------------------------------------------------
For forming the lb_one select box (product)

SELECT distinct(COLOR) FROM qry_screen_output
WHERE ContactID = '#ContactId#'
AND Team = '#Team#'
Order by PRODUCT

--------------------------------------------------------
for forming the lb_two select box (color as per product from lb_one)

SELECT distinct(COLOR) FROM qry_screen_output
WHERE ContactID = '#Contactid#'
AND Team = '#Team#'
AND PRODUCT in (#preservesinglequotes(ListChangeDelims(lb_one,"','",","))#)
Order by COLOR

---------------------------------------------------------
for getting the sum of sales using the products and its colors

SELECT sum(sales)  FROM qry_screen_output
WHERE ContactID = '#Contactid#'
AND Team = '#Team#'
AND PRODUCT in (#preservesinglequotes(ListChangeDelims(lb_one,"','",","))#)
AND COLOR in (#preservesinglequotes(ListChangeDelims(lb_two,"','",","))#)

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


This should solve your problems.


Regards
Harish(hart)

sorry a slight change

SELECT distinct(COLOR) FROM qry_screen_output
WHERE ContactID = '#ContactId#'
AND Team = '#Team#'
Order by PRODUCT

should be changed to

SELECT distinct(PRODUCT) FROM qry_screen_output
WHERE ContactID = '#ContactId#'
AND Team = '#Team#'
Order by PRODUCT

this is the query for getting the product in the select box

Hart, this looks great, i am now working on getting this to work with flash actionscript :) i will let you know, this is proving a bit difficult to integrate with flash, any help would be appreciated there. i can send files to show you what i am working off of in actionscript and the inerface. thank you for your time and help thus far.

here is a link to the actionscript file, check out the zip posted here...it has something that we may be able to alter.

https://www.experts-exchange.com/questions/20543490/500-points-depenent-list-boxes-and-multiple-values-can-be-selected.html

here we go . next question . please refer to this post before reading my question

https://www.experts-exchange.com/questions/20543490/500-points-depenent-list-boxes-and-multiple-values-can-be-selected.html

sO Close, I am going to give some bonuses here.!

OK , now the CFM. I would like to reference the ContactID in the CFC. How can i do this. I read that in order to do this I must use CFINVOKE in the cfm... we can call this in CFC using the code above and CFARGUMENT...my question then is how to write CFINVOKE on my logincfm page....?

here is part of my coldfusion code on the login page cfm..
     <CFQUERY NAME="GetUser" DATASOURCE="#DataSource#">
     SELECT ContactID, FirstName, UserRoleName
     FROM Login_table LEFT OUTER JOIN UserRoles
       ON Login_table.UserRoleID = UserRoles.UserRoleID
     WHERE LoginID    = '#Form.UserLogin#'
       AND Password = '#Form.UserPassword#'

So I selected ContactID from the table already, now I must pass it to the CFC ,how do i do that?

What is the specific question...how do i definte the '#Contactid#' in the CFM from the following code in the CFC above? here it is again from the cfc

WHERE ContactID = '#Contactid#'


Here is what I know I have to write...

<CFINVOKE
COMPONENT = "productdetails.cfc"  (this is the cfc)
METHOD = "list_products"   (this is the fuction in cfc)
Contactid = '#form.contactid#' >

Obviously this line needs to be edited because contactid is not a entry on the web page but resides in the database..

Contactid = '#form.contactid#'

How can i change it to reflect the contacid in the database needs to be read from this query on the cfm page (above at beg of post)?

Would this following code work by referncing the cfm query..?

<CFINVOKE
COMPONENT = "productdetails.cfc"  (this is the cfc)
METHOD = "list_products"   (this is the fuction in cfc)
Contactid = '#Getuser.CONTACTID#' >

Here is a recap of my CFC so that you can answer my question on the CFM above.

another question will be do i need to have a CFINVOKE of contactid for every function in cfc that uses it , i guess so.

<CFCOMPONENT>
   
    <CFFUNCTION name="list_products" returntype="list" hint="Getting the list of products">
     <CFARGUMENT name="Contactid" type="numeric" required="true">
     <CFARGUMENT name="Team" type="string" required="true">
         <!--- for now i am harcoding contactid and team to 122 and hp, you can pass these as arguments --->
        <!--- <CFSET ContactID = "122">
         <CFSET Team = "hp">--->
         <CFQUERY NAME="get_products" DATASOURCE="db1" DBTYPE="ODBC">
              SELECT distinct(product) FROM qry_screen_output
              WHERE ContactID = '#ContactId#'
              AND Team = '#Team#'
              Order by PRODUCT
         </CFQUERY>
         <CFSET products = Valuelist(get_products.product)>
         <CFRETURN products>
    </CFFUNCTION>
   
    <CFFUNCTION name="list_colors" returntype="list" hint="Getting the list of colors">
     <CFARGUMENT name="Contactid" type="numeric" required="true">
     <CFARGUMENT name="Team" type="string" required="true">
         <CFARGUMENT name="selProduct" type="string" required="true">    
         <CFSET selProduct = ListChangeDelims(selProduct,"','",",")>
         <!--- for now i am harcoding contactid and team to 122 and hp
         <CFSET ContactID = "122">
         <CFSET Team = "hp"> --->
         <CFQUERY NAME="get_colors" DATASOURCE="db1" DBTYPE="ODBC">
              SELECT distinct(COLOR) FROM qry_screen_output
              WHERE ContactID = '#Contactid#'
              AND Team = '#Team#'
              AND PRODUCT in (#preservesinglequotes(selProduct)#)
              Order by COLOR
         </CFQUERY>
         <CFSET colors = Valuelist(get_colors.color)>
         <CFRETURN colors>
    </CFFUNCTION>    
   
    <CFFUNCTION name="str_sales" returntype="string" hint="Getting the list of colors">
     <CFARGUMENT name="Contactid" type="numeric" required="true">
     <CFARGUMENT name="Team" type="string" required="true">
         <CFARGUMENT name="selProduct" type="string" required="true">    
         <CFARGUMENT name="selColor" type="string" required="true">
         <CFSET selProduct = ListChangeDelims(selProduct,"','",",")>
         <CFSET selColor = ListChangeDelims(selColor,"','",",")>
         <!--- for now i am harcoding contactid and team to 122 and hp
         <CFSET ContactID = "122">
         <CFSET Team = "hp"> --->
         <CFQUERY NAME="get_sales" DATASOURCE="db1" DBTYPE="ODBC">
              SELECT sum(sales) as sumsales FROM qry_screen_output
              WHERE ContactID = '#Contactid#'
              AND Team = '#Team#'
              AND PRODUCT in (#preservesinglequotes(selProduct)#)
              AND COLOR in (#preservesinglequotes(selColor)#)
         </CFQUERY>
         <CFSET sales = get_colors.sumsales>          
         <CFRETURN sales>
    </CFFUNCTION>    
   
</CFCOMPONENT>

Here is the CFM Recap that I want to make work with the above CFC...please check 2 posts back to see question. .. note this is the CFM that gets called by the login page, it is the checker , please let me know if CFINVOKES (which are on this cfm ) should be moved to another CFM in order to give values to the CFC.

<CFLOGIN>
 
  <!--- If the user hasn't gotten the login form yet, display it --->  
  <CFIF NOT (IsDefined("FORM.UserLogin") AND IsDefined("FORM.UserPassword"))>
    <CFINCLUDE TEMPLATE="UserLoginForm.cfm">
    <CFABORT>
   
  <!--- Otherwise, the user is submitting the login form --->  
  <!--- This code decides whether the username and password are valid --->
  <CFELSE>    
       
    <!--- Find record with this Username/Password --->
    <!--- If no rows returned, password not valid --->
      <!--- userroleID stuff superflous, im not going to use it like contactid --->
    <CFQUERY NAME="GetUser" DATASOURCE="#DataSource#">
      SELECT ContactID, Team, FirstName, UserRoleName
      FROM Login_table LEFT OUTER JOIN UserRoles
        ON Login_table.UserRoleID = UserRoles.UserRoleID
      WHERE LoginID    = '#Form.UserLogin#'
        AND Password = '#Form.UserPassword#'
    </CFQUERY>
   
    <!--- If the username and password are correct... --->
    <CFIF GetUser.RecordCount EQ 1>
      <!--- Tell ColdFusion to consider the user "logged in" --->
      <!--- For the NAME attribute, we will provide the user's --->
      <!--- ContactID number and first name, separated by commas --->
      <!--- Later, we can access the NAME value via GetAuthUser() --->
      <CFLOGINUSER
        NAME="#GetUser.ContactID#,#GetUser.User#"
        PASSWORD="#FORM.UserPassword#"
        ROLES="#GetUser.UserRoleName#">              
   
    <!--- Otherwise, re-prompt for a valid username and password --->
    <CFELSE>    
      Sorry, that username and password are not recognized.
      Please try again.
      <CFINCLUDE TEMPLATE="UserLoginForm.cfm">
      <CFABORT>
    </CFIF>


  </CFIF>  
 
  <CFINVOKE
  component = "productdetails.cfc"
  method = "list_products"
  Contactid = '#Getuser.ContactID#'
  Team = 'Getuser.Team#' ></CFINVOKE>
 
    <CFINVOKE
  component = "productdetails.cfc"
  method = "list_colors"
  Contactid = '#Getuser.ContactID#'
  Team = 'Getuser.Team#' ></CFINVOKE>
 
    <CFINVOKE
  component = "productdetails.cfc"
  method = "str_sales"
  Contactid = '#Getuser.ContactID#'
  Team = 'Getuser.Team#' ></CFINVOKE>          
 
 
 
</CFLOGIN>