Can you post the FMP file, so we can look deeper into it?
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 Order Lines
Sales Order 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.
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.
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)
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.
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
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
Will try tomorrow, I'm in the east so my day is about done.
Sent from my iPhone
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).
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:
When the reply box opens at upper right click on "Use advanced editor" then the lower right has the Attach button.
Look for new discussion with file under community feedback
under community feedback