7 Replies Latest reply on Sep 4, 2014 11:50 AM by philmodjunk

    Table Relationship and autofill??? question

    gjbdjc

      Title

      Table Relationship and autofill??? question

      Post

           I have a database with an "ORDERS" table which links to both a "ORDER DETAILS" table and a "PRODUCTS" table as shown in diagram.  Within the ORDER DETAILS layout, I want to show Equipment that is specific to the PRODUCT.  Each equipment combination has a code, such as RJ 30 (as shown). I would like the equipment specifics fields to "auto-populate" when the code is selected??? 

      Equip.jpg

        • 1. Re: Table Relationship and autofill??? question
          philmodjunk

               Looks like your relationship should actually be:

               Orders----<OrderDetails>----Products-----<Equipment

               And if the same equipment record needs to be linked to more than one product, you need:

               Orders----<OrderDetails>----Products-----<Product_Equipment>-------Equipment

          • 2. Re: Table Relationship and autofill??? question
            gjbdjc

            I am hoping this is an easy fix that I just cant figure out. I've changed my relationships as suggested above.

            I want my Equipment Information to be "PRE-ATTACHED" when a new order is entered and this function currently works.

            However, if I change my Mill_ID the corresponding fields ( Mill Name, Mill Size... ) do not change????

            Thoughts

            • 3. Re: Table Relationship and autofill??? question
              philmodjunk

              Product-Mill links to Product-Mill? That's not what I recommended. Is that perhaps an error in your illustration?

              Mill_ID is a field in what table? and you are changing the value on a layout or portal based on what table?

              the corresponding fields ( Mill Name, Mill Size... ) do not change

              And in what tables are those fields defined? Are they set up to auto-enter (copy) data from a related table or are they the actual fields from the related table?

              • 4. Re: Table Relationship and autofill??? question
                gjbdjc

                Sorry, illustration error. Product-Mill links to Mills Table.

                Mill_ID is a field in both Product-Mill and the Mills Table, to set up the many-to-many

                The remaining fields are all in the Mills Table, actual fields in the Mills Table.

                • 5. Re: Table Relationship and autofill??? question
                  philmodjunk

                  Mill_ID is a field in both Product-Mill and the Mills Table, to set up the many-to-many

                  But on your layout its from only one or the other table, not both. Which is it on your layout?

                  and to repeat: and you are changing the value on a layout or portal based on what table?

                  • 6. Re: Table Relationship and autofill??? question
                    gjbdjc

                    Currently, I have a portal (Equipment Information)  in my Order Detail based layout (as shown Above).

                    The portal contains these fields:

                    Mills ID (from the Products/Mills table)

                    Mill Name (from the Mills table), Mill Size (from the Mills Table) and so on... (all from the Mills table)

                    This set up does exactly what I want if to do, if I change the Mill ID field, the others change also. 

                    however no one understands which MillID = which mill. Not very user friendly???

                     

                     

                    • 7. Re: Table Relationship and autofill??? question
                      philmodjunk

                      So you need to format the field as a value list of Mill ID's and Mill Names. A Use Values from field value list can be set up in that format. The user sees and selects the mill by name, but the value list enters the required ID number.

                      There is some information--including a layout page dedicated strictly to how to set up a "use values from field" value list in Adventures In FileMaking #1. I am then covering this in more detail in Adventures in FileMaking #2 (forthcoming) where I describe ways to improve on the basic "ID number in field 1, Name in field 2" value list.