Drop Down Menu selection query for recordset.

[cmswinnjr]

I am looking to utilize a dynamic drop down menu to query a recordset...I am using Colfusion to import an MS Access database that contains the following fields: "Model Date", "Name", "Points" and "Target".  Each time the database updates, the "Model Date" field contains the date and time the model was run.  I have figured out how to create a dynamic drop down using SELECT DISTINCT "model date" and have also figured out how to create the dynamic recordset to be displayed showing the "Name", "Points" and "Target" data. 

I want the user to select the "Model Date" from the drop down, hit a submit button and then have the appropriate "Name", "Points" and "Target" data queried and shown below.

The query should take the selection from the "model date" dropdown and then query the "name", "points" and "target" fields for that particular "model date"

I admit my knowledge of SQL and Coldfusion is not the best, but it seems like this is a somewhat simple task and I am just missing one or two pieces of the cog to put it all together.

The database basically looks like this:

Model Date...........Name..........Points........Target

8/1/2010 08:00......John Doe.....1,250.........5.55%

8/1/2010 08:00......Jane Doe.....850............2.35%

8/1/2010 08:00......Bill Smith....11,832........-123.23%

8/2/2010 09:02......John Doe.....1,323.........6.67%

8/2/2010 09:02......Jane Doe.....1,001.........3.21%

8/2/2010 09:02......Bill Smith....10,235........-110.26%

The dropdown will only show the "model dates"

8/1/2010 08:00

8/2/2010 09:02

For example, if 8/1/2010 08:00 was selected from the dropdown, I want the following displayed:

Name..................Points...................Target

John Doe.............1,250....................5.55%

Jane Doe.............850.......................2.35%

Bill Smith............11,832...................-123.23%

Any help or suggestions would be greatly appreciated!!!

Thanks,

Mike

[bregent]

There are several ways to go about this. Search the web for 'dependent drop down' or 'cascading drop down' for some ideas.

[BenPleysier]

Also have a look at Spry http://labs.adobe.com/technologies/spry/samples/data_region/DataSetMasterDetailSample.html

[Lon_Winters]

I don't know if I interpret your question the same as bregent - do you actually want the resulting data returned from the Model Data query to populate subsequent drop downs, or do you just want to display the resulting data on a results page?

If you just want to display the data, then you create a results page with a recordset and base that recordset on the model data field, bind the data to the regions on the page where you want it to display, then create a repeat region server behavior so it shows more than just the first record. 

If you do want to populate subsequent drops downs there is an easier way to do it, but much less elegant, or do it the better way which requires some javascript or the purchase of one of the extensions that is made for this.

The less elegant way is to create recordsets for the other other drop downs and bind that data the same as you did for the Model Data menu. The page would have to re-load each time to populate the menu. 

The better way involves as I said, javascript, but creates an array based on a link table in the database which you would have to create, and would not require the page to refresh itself each time. 

[cmswinnjr]

Lon,

You hit it in your second paragraph...That is where I am stuck...I cannot figure out how to get the query to "see" for lack of a better word the selection chosen from the Dropdown menu...

[BenPleysier]

Create an HTML-table filled with data from your database, create a SpryHTMLDataSet, create two dropdown lists, fill the first list with unique dates and filter the second list on those unique dates.

Alternatively, create an XML-file from your database, create a SpryXMLDataSet and follow the same procedure as above with the exception that the datset will be filtered using XPath.

Either way gives you an easy to incorporate JavaScript solution where there is no page refresh.

Sorry, I now realise that you only want the one dropdown which then displays the relevant data. I shall return with a working copy.

Message was edited by: vw2ureg

[cmswinnjr]

I have thought about trying that...I have tried having my datapump export an html file, but the formatting that is applied to it doesnt play nice with Spry...

If i create an html file with the data, would I have to re-create that file each time the database is updated (mostly daily, but sometimes multiple times per day)?

I would go the XML route, but to be honest, my knowledge of actually how to create an xml file from my data is extremely limited...

The current report that this is supposed to replace is in Excel and uses PivotTables to filter the data (by the Model Date).  As the month goes on, the database gets bigger and since Excel imports the entire database, the file is getting upwards of 10megs which is causing problems with end users being able to open it...Hence the need for a web-driven application.

[BenPleysier]

If i create an html file with the data, would I have to re-create that file each time the database is updated (mostly daily, but sometimes multiple times per day)?

No, not if you create the table dynamically from your database. The format would have to be as follows

<table id="myData">
  <tr>
    <th scope="col">Model Date</th>
    <th scope="col">Name</th>
    <th scope="col">Points</th>
    <th scope="col">Target</th>
  </tr>
  <tr repeat until EOF>
    <td>$row['modeldate']</td>
    <td>$row['modeldate']</td>
    <td>$row['modeldate']</td>
    <td>$row['modeldate']</td>
  </tr>
</table>

The table gets an ID for the SpryHTMLDataSet to be able to recognise it. The rows are then populated with the data from your database. The syntax is dependant on the serverside code that you use.

If you want, the above table resides in the same document as your SpryDataSets and the output page or can be placed in a separate document.

[cmswinnjr]

Thank you both for the insight...I am going to try both of your suggestions and see what I come up with...At least i will have some code to share.

[BenPleysier]

I have uploaded a quick demo of what I mean here http://pleysier.com.au/cmswinnjr/

What remains to be done is to replace the left hand side to a dropdown with distinct dates.

[cmswinnjr]

That is awesome...Pretty much exactly what I am looking for...Let me get into DW and see what I can come up with.

[Lon_Winters · Accepted solution]

My second paragraph talks about just displaying the filtered data, so I'm assuming that's what you're looking for, but still not quite sure based on what the other responses are. 

But I head on anyway -

On your first page, make note of the instance name of your drop down menu, such as "ModelDate".  Make sure it's in a Form and set the form action to the page where you want to display your data, set the form action to POST.

On the results page, create a table with cells for each of the data elements you want to display. Create a recordset which you can do in Simple mode. Leave it at select all, and set the filter drop down to the database field which contains your Model Date. In the box to the right, select "=". the next dropdown selct "Form Variable" and the variable name type in the instance name of the drop down on the first page. 

I may not have the terminology right, doing it from memory.

From the data bindings tab, expand your recordset and locate each of the database fields you want to display.  Drag each one to the table cell on the page where you want it displayed.  The table cells have to be in a linear row.

Now, select the table row buy selecting the TR tag in the tags just above the properties panel.  In the server behaviors tab, select repeat region and "All Records"

Publish your pages and test!

[cmswinnjr]

Here is what I have got...When I test, no results are displayed.

<body>
<form id="form1" name="form1" method="post" action="">
  <label for="ModelDateSelect">ModelDateSelect</label>
  <select name="ModelDateSelect" size="1" id="ModelDateSelect">
    <cfoutput query="rs_ModelDate">
      <option value="#rs_ModelDate.Model_Date#">#rs_ModelDate.Model_Date#</option>
    </cfoutput>
  </select>
</form>
<p> </p>
<p> </p>
<table width="200" border="1">
  <tr>
    <th scope="col">Model Date</th>
    <th scope="col">Agent Name</th>
    <th scope="col">Total Points</th>
  </tr>
  <cfoutput query="rs_Dataresults">
    <tr>
      <td>#rs_Dataresults.Model_Date#</td>
      <td>#rs_Dataresults.Agent_Name#</td>
      <td>#rs_Dataresults.Total_Points#</td>
    </tr>
  </cfoutput>
</table>

[cmswinnjr]

I appreciate all your help with this...I have something that does work now.

I am going to play around a bit with what I have and throw some live data at it to see how it handles...I will post my results and any other questions I may have...Again, thanks for your help!

[bregent]

>I don't know if I interpret your question the same as bregent

Ooops. I was thinking about another thread when I replied to this one. Sorry folks.