I have yet another question about m

[genericjanedoe]

I have yet another question about my database, and it is linked to my previous problem of drop down lists triggering sub-lists.

First my thanks once again to Paul Bent for his original solution; but now I need to take this a step further. I created the filtered lists in a separate small database and they worked fine. However, I need to show these results in a main database, and whilst I made exact copies of the filtering rules, the sub-category list in the main database keeps pulling up all subjects instead of the ones allocated to each category.

I don't know what it is that is making the difference between the two databases...help!

 

[paulbent]

The principle is the same whatever the database. If you got it working in one apr file then it should work fine in another one. You'll just have to recheck those settings again.

If the subcategory is filtered on the combination of two previous choices, make sure the modification formula for the combined fields is correct and the field has been updated in existing records.

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk

 

[genericjanedoe]

OK, I discovered that the problem with the unfiltered lists was because the field boxes on the form were pointing to the main database instead of the one in which I created the original lists.

Now however, although I can click on one category and get a corresponding list appear in the sub category, I cannot change the record permanently because a pop up message informs me that the ID number in the original categories database must be filled in. I have looked at the field definition in the ID number field, but there are no validation boxes checked. I even gone into the join options between databases and tried various option combinations, but nothing works.

It seems that the only way to avoid this message appearing is by pointing the field boxes back to the main database again, which leaves me with unfiltered lists and means I am right back where I started!

 

[paulbent]

The form has a main table and you can only enter data in fields from that table. Other fields from joined tables on the same form are just to display data and should be read-only The drop down lists can be bound to fields in the main table but the lists can be created from fields in another table and filtered by the value entered in the previous main table field.

Suppose you have a "Products" table containing all possible products broken down into categories:

IDNum   Group     Category      Subcat
1       Fruit     Apple         Cox
2       Fruit     Apple         Bramley
3       Fruit     Orange        Satsuma
4       Fruit     Orange        Mandarin
5       Vegetable Cabbage       Savoy
6       Vegetable Cabbage       Spring
7       Vegetable Onion         Red
8       Vegetable Onion         Spanish

The main table contains the IDNum field, the two table are joined and all join options are deselected. The reason for the id # join is to achieve a many-to-one relationship between main and products. If we joined on the Group field alone it would be an (illegal) many-to-many relationship.

In order to achieve the second level of filtering, a text field named GroupCat is added to both tables, long enough to hold the combined contents of Group and Category. Both fields must have a default modification formula:

Main.GroupCat
Combine(Main.Group, Main.Category)

Products.GroupCat
Combine(Products.Group, Products.Category)

Then on a form based on the main table you set up the drop down lists like this:

1. Main.ID. List created from Products.ID. Description field from Products.Group. You would also put the Products.Group field on the form and make it read-only to display the Group description when the id # is selected.

2. Main.Category. List created from Products.Category. Filtered by Products.Group using the current value in Main.Group

3. Main.Subcat. List created from Products.Subcat. Filtered by Products.GroupCat using the current value in Main.GroupCat

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk