Since an order can list many different product IDs, I don't see a use for _fkProductID in the products table.
The Products table needs to be related to the Suppliers Table so as to capture only the products from a particular Supplier and show
them in a Portal.
Supplier::__pkSupplierID = Products::_fkSupplierID
and this assumes that a given product has only one supplier. If its possible for a product to have more than one supplier, you'll need an additional "join" table to put between them in order to link the records correctly.
The Assortments Table needs to be related to the Products Table so as to capture only the Assortments relating to a particular product and show them in a Portal.
You'll need to explain what an "assortment" is and how it would be used. Is an "assortment" a group of products sold as a single item? If so, this is also referred to as a "boxed set" or "kit" in some sales environments. And if this is the case, you normally would have a Record in Products for each assortment that you would link to a table that lists the individual ProductID's that make up that assortment. It would be related like this:
Products::__pkProductID = Assortments::_fkAssortmentProductID
Assortments::_fkItemProductID = AssortmentProducts::__pkProductID
AssortmentProducts is a second occurrence of Products that you'd create by selecting Products in Manage | Database | Relationships and clicking the duplicate button (two green plus signs).
Often, this setup requires additional scripting to keep all items in proper order.
Thanks, Phil for your response and offering to help me with this.
Re your comment, "Since an order can list many different product IDs, I don't see a use for _fkProductID in the products table", I don't have this foreign field in the Products Table. It is a Primary key in this table.
Each product comes from only one supplier. and each assortment is related to only one product.
The Assortments are an assortment of styles or colours available in a fabric. E.g.
Product: Silk Chiffon available in assortments: Mirage, Kaleidoscope, Leopard Print.
Product: HW-33643 available in Tangarine, Lemon , Hot Pink
Each product (fabric) is available in different assortments particular to that product.
Looking forward to your next assistance.
I don't have this foreign field in the Products Table. It is a Primary key in this table.
Sorry for the typo. I meant I don't see a need for this field in the Orders table.
With regards to "assortments", many systems assign a unique ProductID to each assortment to use for selection in the LineItems table for a given Order. (Not sure if this is the intended use for ProductLineItems or not.)
Then a second field is used to identify products that are members of the same assortment.
HW-33643, by the way, is not a value I'd use as a primary key for the products table. I'm guessing that this Identifier is supplied by an external source and if so, this should be stored in a field in Products, but an auto-entered serial number field should be defined and used as the primary key.
Thanks for the extra help.
Sorry for confusing you by using a product name instead a product code. Below is a revised version using generic Product Codes
Product: ProductCode 1 Material: Silk Chiffon available in assortments: Mirage, Kaleidoscope, Leopard Print.
Product: ProductCode 2 Material: HW-33643 available in Tangarine, Lemon , Hot Pink.
I've uploaded a captured image of the Layout used to enter data in an order (Indent is what the client calls it)
You will see that an Order (Indent) has two tabs. One for entering the product info and, if the order has an assortment there is then only one product on the order on the Product tab and the assortment showing on the other tab.
I have created portals for entering both Product and Assortment data. The problem I'm having is that the Product Portal pop-up Value List is populated by all products regardless of the supplier. (there is only one supplier for each product. And the Assortment Portal pop-up Value List is populated by all assortment data regardless of what Supplier or product they are related to.
I want to be able to do the following:
1. Enter data into the Products Portal from the Products Table where the pop-up Value List is populated with products only from the Supplier on that particular Order.
2. Enter data into the Assortments Portal from the Assortments Table where the pop-up Value List is populated with Assortments only from the Products from the particular Supplier on that particular Order.
I'll upload a PDF of the Database Design Report on a new post in case it will be of extra help.
I hope the above clarifies my issue and look forward to your help.
Here’s the Filemaker Summary Report In PDF. I'm sot sure if it uploaded or not.
I have a link to my Filemaker Database in Dropbox. Please let me know if it would help you to download it and I'll let you have the link.
If you check the info next to the Upload an Image controls, you'll see that only GIF, JPG and PNG file typs may be uploaded. Other file types appear to upload, but do not appear in the forum. I'd guess that maybe you've tried to upload some PDF's here and that won't work.
From your written description, my initial response is that this is an odd way to structure your data. It definitely seems to complicate both data entry and reporting tasks.
However, what you describe is the need for conditional value lists and such can be setup so that lists of values specific to the selected supplier are all that appear in the list of values.
Here are some links on the subject. They look a bit overwhelming, but there's a lot of overlap in the first two links so you can read one and skim the other for differences.
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
Many thanks for the links. I'll study these and hopefully, will find the answers I'm looking for.
I appreciate the time you've given to helping me.