3 Replies Latest reply on Feb 3, 2017 7:38 PM by philmodjunk

    Help with a relationship


      To preface, I'm a novice user. I have Filemaker Pro Advanced (14, I believe), and I'm trying to create relationships across a few tables. What I'm attempting to do is compare what was ordered from the "LB_Order_Info" table to what was produced in a table called "Alb_Incoming_Shipments_Item". For further explanation, the "LB_Order_Item" table has a portal that's related to the "Alb_LB_Order_Item" table. In LB_Order_Item, I key the week that the product was produced for, and then in the portal associated with Alb_LB_Order_Item, I type in the item number, and the amount ordered.


      On another set of tables, I have "Alb_Incoming_Shipments," where I key in the information from the delivery slips created by the production facility, into another portal related to "Alb_Incoming_Shipments_Item". This portal/table contains the item number, the quantity shipped, and the week it was produced for.


      What I'm trying to do is create a layout with LB_Order_Info as the table, with only the only field being production week, then place a portal inside of it that shows the item number, the quantity ordered (from Alb_LB_Order_Item table), and the quantity shipped (from the Alb_Incoming_Shipments_Item table).


      I've attached a screenshot of my relationships. If you see "BUSS" or "BUSS_Number," that's the item number I'm talking about above.


      Thank you in advance!

        • 1. Re: Help with a relationship

          Is the relationship between LB_Order_Info and LB_Order_Item supposed to be 1 to 1? That's what appears to be the case, yet the field naming conventions suggest that it should be one to many in a typical "one order to many listed items" relationship. (There are "crows feet" missing that suggest a unique values validation or auto-entered serial number option on fk_LB_Order_Info and this does not appear to be what should be specified here.)


          I don't have time at the moment to look at the other details, but first glance brought that issue to my attention.

          • 2. Re: Help with a relationship

            I do have an auto-enter serial function in the primary key field for LB_Order_Info. I did notice that the crows feet were missing from some of my one-to-many relationships. Should I not be using an auto enter serial to create a relation field?

            • 3. Re: Help with a relationship

              An auto enter field option should be specified for the field on the one side of the relationship but usually not on the many side and neither unique values nor prohibit modification should ever be specified for the match field on the many side.


              Auto-enter on this side: Parent -----< Children Do NOT auto-enter in the match field on this side


              pk = Primary key, fk = foreign key in many field naming conventions. A Primary key has to always have a unique value and this value should never ever be changed for a given record once it has been assigned. A foreign key (in a one to many relationship) does not store a unique value and gets a value set to match the primary key on the other side of the relationship. It's value is assigned via other means such as:

              1. Automatically copied from the primary key via a "create relationship" as happens when you enter data into the "add row" of a portal.
              2. A value list used to select the appropriate parent record
              3. Scripts
              4. And yes, there are cases where an auto-enter calculation might enter the value, but this would not be an auto-entered serial nor an auto-entered UUID. It would be an expression that copies the needed matching value from another source such as a global field or variable.