4 Replies Latest reply on Aug 20, 2015 11:14 AM by TonyArcangelini

    Tables won't relate

    TonyArcangelini

      Title

      Tables won't relate

      Post

      I am creating a database to help us with our weekly inventory and ordering. There are multiple locations that all take inventory on separate tables. On each table there is a field for cups to order. I am attempting to pool all the "Order" entries on a separate table for the purchasing manager to see without having to go to each location and see the quantity but cannot seem to figure out the best way to do this. I attempted to make a joined table but can't get the records to display from the relationship. I am fairly new to FMP and am not sure what I am missing here... I attached a picture of my relationships to clarify. 

      relations.jpg

        • 1. Re: Tables won't relate
          philmodjunk

          Separate tables for each location would appear to be the first mistake.

          Use one table for all three locations but with a field that identifies the location. You can then set up reports that show the orders broken down by location using this field rather than three separate tables. This simplifies your data model to just two related tables instead of 4.

          What you show should work, however. It just doesn't really look like an ideal design. 3 Portals on the Order_Cups layout should show all records from the related tables. Though I would use a single field in Order_Cups in all three relationships.

          In the case of any relationship, the records only match if the two fields used as match fields have the same exact values.

          • 2. Re: Tables won't relate
            TonyArcangelini

            I agree that this would be much easier if it was all on one table, however this is not my full database. Each location/table has multiple things they are inventorying (cups, sugar, sweet n low, creamers, stir sticks, equal, etc) and each table is referencing the previous record to populate a "Previous" table. If there is a better way to set this up then I am all ears because I can't seem to get this to work the way I thought it would.

            • 3. Re: Tables won't relate
              philmodjunk

              What you describe would not convince me that separate tables make sense. There are ways to get data from the "previous record of the same location" using relationships that match to a different occurrence of the same table that includes the location field as part of the match. Matching to the previous record with the same type of object as well as location is also possible.

              And in my last post, I also commented on ways to get your current design to work if you insist on keeping it.

              • 4. Re: Tables won't relate
                TonyArcangelini

                That sounds way better! I will look into that option rather than the mess I have created already. Thanks for your guidance.