Dynamic Data for Menu

Hi
Just want to say that the software really does make if wonderful for a novice like me. Could you help me with the following:-

1) My Menu is Pulling from a Field but It is pulling multiple copies of the same item( how do I make it call one - i think the word "distinct" has something to do with it but I'm still learning. Example Customers ay have a Ford, Vauxhal, Peugeot, Honda Ford, Mazda - but i want the list to only show 1 Ford.

2) Once the Make is Selected, I want my next field to filter a populated list of the models specific to ford - ie instead of listing all models it needs to automatically filter to ford models example - Mondeo, Astra, 406, civic, Kuga, 626

If the Customer chooses the Ford (Make) from the 1) List, then the next field (models) from the 2 list will filter Mondeo, Kuga.

I hope I make sense with this - I spent a whole day yesterday trying to make it work. Please help!

Regards

Tony

to create dynamic dependent drop down list that you are describing, you will need to use our Dynamic Dropdowns extension.

your data base should have a make table, and a model table using a foreign key in the model table to relate them:

make:
makeID - Primary Key
makeName - text


model:
modelID - Primary Key
modelMakeID - Foreign Key relation to make.makeID
makeName - text

example data:
make:
makeID | makeName
------------------------
1 | Ford
2 | Honda


model:
modelID | modelMakeID | makeName
------------------------------------------
1 | 1 | Mondeo
2 | 1 | Kuga
3 | 3 | Civic

the recordset for the first list will return all columns from the make table:
select * from make


in the list, use the makeID for the value, and the makeName for the label

the recordset for the Dynamic array behavior, will return the three columns from the model table.

In the Dynamic array, set the parent ID to the modelMakeID column, use the modelID for the child ID and the makeName column for the Child lable.

Thanks Jason

Will have a play with this! Thnaks for your help!

Regards

Tony

Hi Jason,

I am trying to do something similar, but all I need is to remove multiple entries from the list and just display one of each. Do I need the Dynamic Dropdowns extension or can it be done from within Data Assist?

I'm a complete beginner at this stuff, so any help appreciated. I'm amazed I've been able to do what I've done so far with no knowledge of PHP or SQL :)

you can modify the recordset used to populate the menu.


Send a copy of your page and I can help you out with this.

Hi Jason,

Thanks for your offer of help. I have attached the search file. Is that all you need?

I did try using SELECT DISTINCT, which only seemed to work when I had one column in the recordset, although I Googled it and, from my limited understanding, it should have worked. As soon as I put the other columns in it stopped working.

Just to give a bit more info: my database has a list of publications that are classified into different study types, for example. Many of them fall into the same study type, but I want the user to be able to select from a list of the study types, not every single entry in the column which has many multiples of the same one.

I don't know if this makes it more complicated, but there are more menus on the page that need filtering in the same way.

Thanks for any help you can give. I'm sure its a common problem, but as a beginner at this a bit of guidance would be wonderful.

the DISTINCT key returns a unique row set.

It does not apply to the first column in your select statement, it is applied to all of the columns in your select statement. It will return data where the entire row in the recordset is distinct.

Instead of using distinct, you can use GROUP BY

SELECT column1, column2 FROM tblname GROUp BY column1


but actualy looking at your page, the real problem is that you are using 1 recordset for the 4 lists.

you should be using 4 recordsets, one for each list.

Using the same recordset for each list will not give the result you are looking for

Creaete a record set for each list. return only the column needed for that list and use DISTINCT

Thanks Jason, as I was typing my question that was beginning to dawn on me! As I said, I am a complete novice, so I'm only just learning how recordsets work. I didn't even realise you could have more than one on a page.

I must say that the code in that page was pretty much what the wizard produced, so it can be misleading for beginners. Maybe there needs to be more prompting as you go through the wizard. It would be good to have the DISTINCT option available through the wizard too. Just a suggestion from a beginner's point of view.

Hi Jason

Well Like Kevin Im still learning also-I tried to do the Manufaturer and Model Filtering fields with the dynamic list option and the initial instructions give. But I failed miserably. Im trying to get the model list ( car model) to filter according to the make. Ie if the make is Ford, then only ford models will display in models field. Would it be possible to give me some extra idiot friendly step by step instructions to work this?

can you post a link to your page so I can take a look at what you have so far to see what may be causing the problem.