?
Solved

Mutliple select list box basics

Posted on 2003-03-07
38
Medium Priority
?
343 Views
Last Modified: 2013-12-24
I want to establish a listbox (call it lb_one) where a user can select mutliple values. The values that are available for choosing depend on who the user is...

i then have another list box (call it lb_two) that displays values according to the values that were selected in lb_one. These values are pulled from

My question is this,

(a) BASIC LIST BOX FUNCTIONALITY QUESTION

how can i tell Coldfusion to read multiple values of the list box (lb_one) that have been selected.

(b) LIST BOX MULTIPLE DEPENDENCY QUESTION

how can i tell Coldfusion to pull the corresponding values (in db) into lb_two according to what was selected in lb_one

   >(b) would be simple if one value was selected ,  i already know how to do this. for example if only one value could be selected in lb_one and i assigned this value towards lb_two the SQL for the two list boxes would be

********
  //lb_one

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

 //lb_two

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

******* Notice that this line !!AND PRODUCT = '#Product#'!! is what connects lb_two to lb_one....once again all well and good for one selected item, how do i do multiple items??

(c) SUM OF SALES QUESTION

This is the hardest part of my question. I want to output Sales info into a text box (tx_one) which will be determined by what was selected in the list boxes. ... For this part , assume you can select multiple values in lb_one as well as lb_two...whooo!!!!!!!  

So from lb_one and lb_two you may get a multiple selection of Products(PRODUCT) as well as a multiple selection of Product colors(COLORS)..The database is set up like so

 User  ContactID  Team  PRODUCT  COLOR  SALES

If the user had selected just one Vendor and Department it would have been easy to produce a text box showing the SALES for that PRODUCT,COLOR combination. I can do that again for just one by the following code in SQL

SELECT User, SALES
WHERE Team = '#Team#'
AND ContactID = '#ContactID#'
AND PRODUCT = '#Product#'
AND COLOR = '#Color#'

BUT I will need a sum of SALES if one or more PRODUCT and COLOR list box values are selected. Help!!!!!!!!!!

Rob
0
Comment
Question by:finnstone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 12
  • 4
  • +3
38 Comments
 
LVL 8

Expert Comment

by:TallerMike
ID: 8091177
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
0
 

Author Comment

by:finnstone
ID: 8091414
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
0
 
LVL 10

Expert Comment

by:substand
ID: 8091574
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,"','",","))#')

0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 
LVL 10

Expert Comment

by:substand
ID: 8091627
(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.    
0
 
LVL 10

Expert Comment

by:substand
ID: 8091655
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?

0
 

Author Comment

by:finnstone
ID: 8092426
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)...?
0
 
LVL 17

Expert Comment

by:anandkp
ID: 8092658
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
0
 

Author Comment

by:finnstone
ID: 8092784
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.
0
 

Expert Comment

by:myst007
ID: 8093088
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>


0
 

Author Comment

by:finnstone
ID: 8093139
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
0
 

Expert Comment

by:myst007
ID: 8093183
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?
0
 

Expert Comment

by:myst007
ID: 8093185
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?
0
 

Expert Comment

by:myst007
ID: 8093232
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.

0
 
LVL 10

Expert Comment

by:substand
ID: 8094183
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.


0
 

Author Comment

by:finnstone
ID: 8094446
(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!

0
 

Author Comment

by:finnstone
ID: 8095060
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"
0
 
LVL 10

Expert Comment

by:substand
ID: 8095286
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>





0
 
LVL 10

Expert Comment

by:substand
ID: 8095340
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.

0
 
LVL 10

Expert Comment

by:substand
ID: 8095397
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.
0
 

Author Comment

by:finnstone
ID: 8095518
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

0
 
LVL 10

Expert Comment

by:substand
ID: 8095619
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



 
0
 
LVL 10

Expert Comment

by:substand
ID: 8095628
i'm going to look at some flash stuff before you get back to me...
0
 

Author Comment

by:finnstone
ID: 8095629
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

0
 
LVL 10

Expert Comment

by:substand
ID: 8095799
the sales column is referenced in the query, and the very end part adds them up.
0
 
LVL 10

Expert Comment

by:substand
ID: 8095845
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.
0
 

Author Comment

by:finnstone
ID: 8096748
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!
0
 

Author Comment

by:finnstone
ID: 8096938
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.

0
 

Author Comment

by:finnstone
ID: 8096948
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/
0
 
LVL 10

Expert Comment

by:substand
ID: 8099305
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.
0
 
LVL 17

Expert Comment

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

K'Rgds
Anand
0
 
LVL 11

Expert Comment

by:hart
ID: 8109904
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)
0
 
LVL 11

Expert Comment

by:hart
ID: 8109914
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
0
 

Author Comment

by:finnstone
ID: 8111484
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.

0
 

Author Comment

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

http://www.experts-exchange.com/Web/WebDevSoftware/Flash/Q_20543490.html
0
 
LVL 11

Accepted Solution

by:
hart earned 2000 total points
ID: 8117592
for flash and cfmx to integrate u require to make cfc's

i will try to find this out and let you know
0
 

Author Comment

by:finnstone
ID: 8121179
here we go . next question . please refer to this post before reading my question

http://www.experts-exchange.com/Web/WebDevSoftware/Flash/Q_20543490.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#' >
0
 

Author Comment

by:finnstone
ID: 8121366
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>

0
 

Author Comment

by:finnstone
ID: 8121630
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>
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
What You Need to Know when Searching for a Webhost Provider
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month11 days, 11 hours left to enroll

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question