Filtering Many to Many relationships

[emballantine]

Okay, I am working on an Inventory Solution. Right now I am just focusing on the Data Entry end.

I have the following tables and relationships:

Assets Table

AssetID_Pk

ItemID_Fk

ManufacturerID_Fk

CategoryID_FK

Item Table

ItemID_Pk

ManufacturerID_Fk

CategoryID_Fk

Manufacturer Table

ManufactuereID_Pk

Category Table

CategoryID_Pk

Each AssetID can only relate to one ItemID, but one ItemID can relate to multiple AssetIDs

Each ItemID can only relate to one ManufacturerID, but one ManufacturerID can relate to multiple ItemIDs

Each ItemID can only relate to one CategoryID, but one CategoryID can relate to multiple ItemIDs

Each Category can relate to many Manufacturers (through the Item Table), and each Manufacturer can relate to many Categories (through the Item Table)

I would like to have a data entry layout (based on the Assets Table) where first you choose a Category from a dropdown list of categories

Next you choose a Manufacturer from a dropdown list that has been filtered to only show Manufacturers who manufacturer items in the category chosen in step 1. Is this possible given the many to many relationship of Manufacturer and Category through the Item table.?

Next you choose an Item from a dropdown that has been filtered by both Manufacturer and Category. I think I know how to do this, I have an instance of the Item Table with a relationship between it and the Asset Table that relates both the CategoryID_kf and the ManufacturerID_kf in each table that I use to create a Value List that is filtered via this relationship.

Is there a way to do this?

Thanks!

[comment]

Something in your description does not add up. It seems you want to have something like:

Manufacturers -< Items >- Categories
                   |
                   ^
                 Assets

But then, if each Asset belongs to one Item, and each Item belongs to one Category, then the Category of an Asset is given by its parent Item - and there is no room for a CategoryID_FK  field in the Assets table. Likewise for Manufacturers, as related to Assets.

 

 

 

[emballantine]

Yes, the CategoryID_fk and ManufacturerID_fk fields in the assets table are redundant. I am using them to create the relationship between assets and items that allows me to filter an ItemID dropdown by manufacturer and category on the assets layout. If there's some better way to do that, I would love to learn. Really, though....my question is about the many to many portion of the problem. How to filter a dropdown list of manufacturers by categories that they are related to through the items table?

 

Thanks.

[comment]

I am afraid you got me confused. First you agree that it makes no sense to have the CategoryID_fk and ManufacturerID_fk fields in the Assets table, then you ask how to populate them.

Let us have a simple example: suppose you have 5 laptop computers, 2 made by Toshiba and 3 made by Apple. How would this be represented in your data model?

 

 

Edited June 2, 2016 by comment

[emballantine]

I'm sorry if I've been unclear. Maybe my terminology is wrong. I'm not asking how to populate those two fields (Assets::CategoryID_fk and Assets::ManufacturerID_fk). Pretend those fields don't exist in the assets table. I get what your saying on that.

I want to be able to populate the following fields from a layout based on the assets table. Assets::AssetID_pk, Items::CategoryID_fk, Items::ManufacturerID_fk, and Assets::ItemID_fk.

1. Assets::AssetID_pk is an autopopulated serial number.

2. I have a dropdown list of Categories to enter the Items::CategoryID_fk field.

3. After choosing a category to populate the Items::CategoryID_fk field, I want the drop down list for entering the Items::ManufacturerID_fk field to be constrained to only manufacturers who make items in the category chosen previously.

4. Then after choosing both a category and a manufacturer, I want the drop down list for entering the Assets::ItemID_fk field to be constrained to only items in the chosen category made by the chosen manufacturer.

Is this scenario possible?

Using your example...I have created a small example file with the Manufacturer, Category, and Item Tables populated. I have set up the Asset Layout, but do not how to do the filtering necessary to achieve the effect described above. So, at the moment each drop down for data entry from the Asset Layout just shows all of the Categories and all of the Manufacturers in the database. I have not populated the Asset table with any data yet.

Example1.fmp12

[comment]

Sorry, I am fraid that makes no sense to me.

[emballantine]

1 minute ago, comment said:

Sorry, I am fraid that makes no sense to me.

Which part doesn't make sense?

 

[comment]

No, we're not going to do it that way - me explaining what I don't understand in your explanation.

I am going to take a wild guess here and suggest you look at the attached file. It does not fit your description, but I suspect it might help.

 

CompaniesinCategoryVL.fp7

[emballantine]

It is very helpful in explaining what you were talking about earlier with the separate join table.

But....my problem is that I have an Asset Table. I have three different assets that are all Apple Alphas and two different assets that are Bravos. 

Is there a way, through relationships, additional fields, or something else entirely that I am not aware of, to create a layout based on the Assets Table that allows a user entering inventory to choose a Product from a dropdown list that has been filtered by category and manufacturer? Ie. The asset layout allows me to select a category and then a manufacturer (preferably only manufacturers that produce products in the previously selected category (like your example), and then a product that is made by said manufacturer in said category. (In my database the product list will be quite extensive (we have a ton of thing-a-ma-bobs, whozits, and whatzits) and I would like to be able to whittle that dropdown list by category and then manufacturer - manufacturer list will, also, be quite extensive.)

Even more awesome would be if there was a way to populate all these fields if necessary. Ie. I have a new asset which is a new item in a new category made by a new manufacturer and want to be able to input all the information from the same layout and have it propagate all the appropriate fields in all the tables and relate them accordingly.

I'm sorry if this explanation is confusing. I am trying to simplify it as much as possible, but I imagine I am asking to do a complicated thing.

[comment]

20 minutes ago, emballantine said:

Is there a way, through relationships, additional fields, or something else entirely that I am not aware of, to create a layout based on the Assets Table that allows a user entering inventory to choose a Product from a dropdown list that has been filtered by category and manufacturer?

Yes. For this, you would need your Assets table to have both a CategoryID and a ManufacturerID field. You would use these fields to define a relationship to a dedicated occurrence of the Products table (let us call it Products 2), matching on both these fields.

With this in place, you can define a value list using values from the Products 2::ProductID field, show only related values, starting from Assets.

Use this value list to populate a ProductID field in the Assets table - and use this field to link the Asset to its selected Product parent (using the original occurrence of the Products table).