7 Replies Latest reply on Jul 8, 2012 3:12 PM by dcutlan

    Relating various tables to use in portals

    DavidCutlan

      Title

      Relating various tables to use in portals

      Post

      I have a database containing six tables. 1. Suppliers 2. Order; 3. Products; 4. Assortments; 5. ProductLineItems; 6. AssortmentLineItems.

      I have created the following key fields,

      Suppliers Table:- __pkSupplierID;

      Orders Table:- __pkOrderID, _fkSupplierID, _fkProductID, _fkAssortmentID,

      Products Table:- __pkProductID and _fkSupplierID;

      Assortments Table:- __pkAssortmentID and _fkProductID;

      ProductLineItems Table:- _fkOrderID, _fkProductID and _fkAssortmentID

      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.

      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.

      How do I create the field options, table relationships and properties of each relationship so that I don’t capture Products from all
      Suppliers or Assortments from all Products?

      How do I use the above so that the Portals for Products and Assortments work correctly?

      I do hope someone can help me with this. I’ve been spending weeks searching for the solution without success.

      David Cutlan

        • 1. Re: Relating various tables to use in portals
          philmodjunk

          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.

          • 2. Re: Relating various tables to use in portals

            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.

            Kindest regards

            David

            • 3. Re: Relating various tables to use in portals
              philmodjunk

              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.

              • 4. Re: Relating various tables to use in portals

                 

                Hi Phil

                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.

                Kindest regards

                David

                 

                 

                • 5. Re: Relating various tables to use in portals

                  Hi Phil

                  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.

                  Kindest regards

                  David

                  • 6. Re: Relating various tables to use in portals
                    philmodjunk

                    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

                    Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                    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.

                    • 7. Re: Relating various tables to use in portals

                      Hi Phil

                      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.

                      Kindest regards

                      David