I will call the table on which you base your layout "LayoutTable" and the table from which your value list will list values filtered by category "ValueTable". And they can be two occurrences of the very same table.
First you need a relationship:
LayoutTable::Category = ValueTable::Category
Then, in Manage | Value Lists, you create a new value list with the "use values from a field" option. When you click specify to select the fields used for the value list, select ValueTable in the drop downs at the top of this dialog. Then select "Include only related values, starting from LayoutTable".
You now have a value list that will only list values from the records in Value table that match to the currently selected Category in LayoutTable::Category. Normally, LayoutTable::Category is set up with a value list of categories.
Thank you so much, I have the two fields set up and working now. There is only one problem, every time I pick a category from the first menu I have to change the subcategory in the second menu manually, it does not reset itself.
For example If I pick Artist in menu 1 and pick Singer in menu 2, Singer in menu 2 remains visible in the second field even when change the choice from menu 1. How do I get the second menu to automatically show the first item of related list?
You'd need to put a script trigger on the category field that performs a script to clear the second field. OnObjectSave (drop down list) or OnObjectEnter (popup menu) can use a single set field step to do that.
works beautifully, thank you!