Automotive Example

The documentation refers to an automotive example. I am in fact working on a real world automotive menu and need some advice.

This will ultimately be a part search by year, make, and finally model. For now, I have three tables populated with data. They are as follows:

Year
wich contains one column, YearID. The YearID is actually the year (ie, 1996, 1999, etc.)

Table two is BaseVehicle. The columns in it are:
BaseVehicleID
YearID
MakeID
ModelID

Table three is Make. It contains:
MakeID
MakeName

What I need to be able to do is select the year from the YearID table / list and then filter the results in the Make list, so that the user can continue to narrow their criteria. Where this becomes confusing to me is how exactly to configure the drop downs knowing that the second, "BaseVehicleID" contains the relational keys between the other two tables.

The Idea is to concatenate the MakeID and YearID for matching using the MySQL CONCAT Function.


the recordset for the make dropdown list will need to be a joined query to pull the makes for the selected year:


SELECT Make.MakeID, Make.MakeName, BaseVehicle.YearID, CONCAT(BaseVehicle.MakeID, BaseVehicle.YearID) AS yearMake
FROM Make
INNER JOIN BaseVehicle
ON Make.MakeID = BaseVehicle.MakeID

in the Create Dynamic Array behavior:

Parent ID = YearID
Child ID = yearMake
Child Text = MakeName


And the recordset for the BaseVehicle Dropdown list will be:


SELECT *, CONCAT(MakeID, YearID) AS yearMake FROM BaseVehicle


and in the create Dynamic array:
Parent ID = yearMake
Child ID = BaseVehicleID
child Text = BaseVehicalName

Thanks for your help. I think I have it working based on your statements above. However, the "Make" list is showing duplicates. If there are 5 instances of Kia, I am seeing all 5 in the second (make) dropdown menu. Is there a way to filter the results to eliminate the duplicates?

I'm presuming the duplicates are showing up because there may be 5 different Kia 1997 models in the database (if I select 1997 from the first list). The third list (model) will ultimately let you pick which one of the 5 Kia models you have, but I don't need to see duplicates in the Make menu.

In your query add a GROUP BY "Make" at the end of the query.

That throws a parse error. Can you be more specific as to where to put the 'group by make'? There are 2 queries. Make needs to be filtered BEFORE the make menu selection can me made / right after the year is chosen.

It should actually be 'GROUP BY yearMake' and that fixed it.

To get the third dropdown (model) I belive I need to include a fourth table. It is called "Model" and is populated with data in the following columns:
ModelID
ModelName
VehicleTypeID

What the viewer needs to see presented to them is the 'ModelName' after selecting the year and make successively. Those are already working dynamically.

I believe this will change the last concat statement and dynamic array. Suggestions on what those statements should be?

What is the VehicleTypeID column for? is it a relation to the BaseVehicle.BaseVehicleID

Jason,
VehicleTypeID is a related key to another table, called 'Vehicle Type' which will not be used for this form. It defines type of vehicle for certain applications. Vehicle Types can be passenger car, passenger truck, motorcycle, etc.

MakeID is the related key between the 'Make' Table and 'BaseVehicle.' It's in both tables.

OK, so its a standard SELECT * FROM Make recordset.

In the Create Dynamic array behavior, instead of setting Child ID = BaseVehicleID set it to Child ID = MakeID