1 2 Previous Next 26 Replies Latest reply on Jul 26, 2016 5:22 AM by joeredc

    Relationship issue


      I am a total rookie at relationships but for the most part my database works well. Only issue is have is that on my shipping layout I have to put both the product ID and product lot number in my record before it will populate all fields. The only way I could get the shipping layout to work at all was by creating a cartesian relationship and a copy of the products table. Can anyone show me the error of my ways? See attached pictureUntitled.bmp

        • 1. Re: Relationship issue

          Can you post the FMP file, so we can look deeper into it?

          • 2. Re: Relationship issue

            Since your table occurrences show Customers, Products, Categories, and Shipping, I'm going to presume you are a distributor and the database is an inventory database.  If so, you'll need more tables:




            Purchase Orders

            Purchase Order Lines


            Receipt Lines


            Putaway Lines

            Sales Order

            Sales Order Lines


            Pick Lines


            Pack Lines


            Shipping Lines


            You may also need Invoice and Invoice Lines and Payments, but you might also use your accounting system for this.


            You certainly don't need all of the tables mentioned above - some can pull double duty, as it were.  For example, Receipt and Putaway can be combined, Sales Order and Pick can be combined and if you only ship your contents in one package (in other words, you don't ship 1 of 3, 2 of 3, 3 of 3) then you probably don't need the Pack/Pack Lines.


            Again, this is all presuming you are a distributor and have an inventory db.



            • 3. Re: Relationship issue

              If you hover the mouse over the upper left corner of Products and Products 2, do you see the same name listed?

              If so, you have two table occurrences of the same table. Can you confirm that this is the case here?


              The rest of my reply assumes two table occurrences of the same table.


              A cartesian join matches any record in one table to all records in the other table so I don't really see what you managed to fix using that relationship. Using just the = operator will allow you to match to records with specific values in the Product ID and Product Lot number fields. And yes, this will require that you enter matching values into both fields in a given Shipping record in order to link that Shipping record to a record in Products. That would seem both expected and desirable behavior so perhaps you can describe where this is a problem.


              And I agree that you will likely need a number of additional tables. My answer simply focuses on why your current relationship isn't working.

              • 4. Re: Relationship issue

                bgustin I have a copy of my file that I would like to upload to this reply but it appears to do that I'm starting a new discussion. How to do upload to the reply

                • 5. Re: Relationship issue


                  It looks like Products and Products2 are the same table (according to column names).  The relationship above does not require Products 2.


                  In a layout that uses Products as it's main data source fields with shipping info should populate for the current product and product lot.  I base this on the current relationship schema (minus Products2)



                  • 6. Re: Relationship issue

                    joeredc wrote:


                    bgustin I have a copy of my file that I would like to upload to this reply but it appears to do that I'm starting a new discussion. How to do upload to the reply

                    I think you can rely to a message and click on insert image to attach your file. I've never done it either but I think this is how people do it.

                    • 7. Re: Relationship issue


                      Yes products and products2 are identical. But the only way I could get my shipping layout to work was to create the duplicate table and the Cartesian join with shipping. I'm sure every thing I've done is amateurish but it works fairly well. I use a portal in my shipping layout to obtain the product ID and product lot number to populate the fields there but it would be a lot simpler for the end user if all they needed was the product lot number.


                      Sent from my iPhone

                      • 8. Re: Relationship issue


                        We are a cold storage warehouse and really don't need all the options you suggest. Products are shipped complete as they were received. I do use a separate accounting program.  I'm self taught in using FM and only have a limited knowledge of relationships.



                        Sent from my iPhone

                        • 9. Re: Relationship issue

                          Will try tomorrow, I'm in the east so my day is about done.



                          Sent from my iPhone

                          • 10. Re: Relationship issue


                            I'm new to FMP and may not have the right answers; we're all learning brother, however, a Cartesian result is NEVER the answer.  The relationship without the second product table will work.


                            Question, are you wanting to display multiple records in one layout?


                            You are missing an Orders table too.


                            The relationship, normally, would be a customer has many orders, orders have with many products, each product has a category, orders have (normally?) one shipping record (could be many shipping records if there are split ships).



                            • 11. Re: Relationship issue

                              Joe, real quick...in your shipping layout, use Products as your main data source and bring in shipping data via the relationship.


                              In Manage Layouts, double click the layout name and select products in the Show Records Form:



                              • 12. Re: Relationship issue

                                When the reply box opens at upper right click on "Use advanced editor" then the lower right has the Attach button.

                                • 13. Re: Relationship issue

                                  Look for new discussion with file under community feedback

                                  • 14. Re: Relationship issue

                                    under community feedback

                                    1 2 Previous Next