Two related popup menus, second one shows record ID after changing first popup
We have three tables. 'Category', 'Subcategory' and 'Product', all records have a primary key (auto number). Subcategory has a foreign key to the primary key of Category. Product has foreign keys to both Category and Subcategory (we need only the foreign key to Subcategory but need the Category for the value list).
We want to index products by Category and Subcategory. So, when adding a product to the Product table, you first select from a popup menu the right category and then from a second popup menu the subcategory. We solved this with setting the relations and creating two conditional value lists. We use the primary keys of the Category and Subcategory and display the names (use as second field) in the popup menu's. Subcategory is filtered by the selected Category via the Product layout.
Problem: after changing a category, the displayed name of the previous subcategory is changed by its primary key value, after clicking the popup menu of subcategories, the right subcategories are displayed and after selecting the new subcategory, the name is correctly displayed in the popup menu.
Question: is there a way (by scripting) to clear text in the popup menu (when not clicked) of the subcategory after changing the category? Or a solution to show the first item of the new filtered subcategories?
Erik de Vries