Sub categories in value list

I have a MySQL online store for antique radio parts, and an FMP file that interfaces wtih it. I have one layout in FMP where I want a drop down menu showing all the product categories. But here's the problem. In the MySQL DB each category has it's category ID, a parent category ID, and a sort order. What happens is, any top level category (Parts, Tools, Supplies) has a parent ID of 0. If you have a sub category (under Parts you would have Capacitors, Resistors, etc), then it's parent ID would be the categroy above it. So the parent ID for Capacitors would be the category ID for parts. I also have a 3rd level of categories, so Capacitors might have 2 sub categories itself.

The sort ID tells MySQL what order to sort each category and sub category (i.e. All the top level categories are in a certain order, under parts each part category is sorted amongst the other parts categories, etc.

What I want to do is have my drop down menu that uses these category names as it's values, to have the first top level category, all of it's sub categories below that (and their subs) then have the next top level, etc. I actually have a join table in FMP that has all of the categories as files, so I guess the issue would be how to sort that table the right way. But the drop down menu should look like this:

Parts

...Capacitors

.....Axial Leads

.....Radial Leads

...Resistors

.....1/4 Watt

.....1/2 Watt

Tools

...Soldering

.....Soldering Irons

.....Soldering guns

etc, etc, etc. **NOTE** the periods I have here in the list will not be in the real menu - the are only here to show the spaceing on this post - when I posted it to start with, it got rid of my spaces. So pretend the periods are actually spaces.

Edited May 14, 200916 yr by Guest