5 Replies Latest reply on Jul 23, 2015 8:20 AM by lombardi3g

    display multiple parent records?

    lombardi3g

      I know it's been only 3 days since FM intermediate training, but I can't get this to work.  A have a layout for inspections of orchards.  'Inspections' are a child table of the parent table 'Orchards' (many-to-one).  My layout has the child table fields and when I have entered the inspection record I then type in the foreign key which matches with the parent record primary key in which the inspection took place (i.e. identifies the correct orchard).

       

      That works fine.

       

      But I have a poor memory and so I need a drop-down list of Orchard Names et al so that I can enter the correct value into the foreign key field to fetch the correct Orchard record.

       

      How can I do this? - i.e., basically have a display list of all parent records on the child entry layout so the user can make the proper connection between parent and child.  (either manually or automatically)

       

      TIA,  Allan

        • 1. Re: display multiple parent records?
          PeterWindle

          You may need to look at the option to add a second field to your value list.

          You can add a second field to display in your drop down list by selecting this check box (see attachment)

           

          Also, keep in mind that attaching a parent to a child record may not be the most effective way to create the link.

           

          It may be a better option to add multiple children to a parent via a portal (depending on your process - in other words, how you perform your data entry for work flow)

           

          I hope this helps

          • 2. Re: display multiple parent records?
            erolst

            So the title should read: “display multiple potential parent records”

             

            Anyway, there should be no need to memorize any of the IDs you (the database) uses in the background. You could …

             

            In the Inspection context

             

            • create a value list “from field” with 1.field: Orchard::ID, 2. Orchard::name, and “display values from second field only”.

            Put the orchard foreign key on the layout, format it as a popup and assign it that new value list.

             

            When you select an orchard name, Filemaker actually inserts the associated ID – and still presents you the name in the popup (this works even if there is no relationship between Orchard and Inspection). Be aware that – for technical reasons – the resolution of an ID into a name does not work with a dropdown list.

             

            • create a “Cartesian” relationship (x operator) between Inspection and Orchard; i.e. create a new table occurrence of Orchard, name it, say, Orchard__all, and relate it to Inspection like so:

             

            Inspection::anyField x Orchard__all::anyField

             

            Put a portal into that relationship on your Inspection layout (you could stash it in a popover), sort by name, put in the fields you need to unambiguously identify the Orchard, and make one of the fields a button (or create a dedicated “button button”) with the single step

             

            Set Field [ target field: Inspection::id_orchard ; Calculated result: Orchard__all::id ]

             

            i.e. set the foreign key of this Inspection record to the primary key of the clicked Orchard record (portal row).

             

            Another approach is to work …

             

            In the Orchard context

             

            … and

             

            • either allow the creation of related records in the bold TO of your relationship

             

            Orchard::id = Inspection::id_orchard

             

            by checking the appropriate setting.Then put in a portal of Inspection on the Orchard layout, and create new Inspections by creating new portal rows – where the foreign key is set automatically!

             

            • or write a script to capture the current Orchard record's primary key, go to Inspections, create a new record, insert the captured value, and start working on the new Inspection record.

             

            I'd be somewhat amazed if none of these basic techniques (for a basic task) hasn't been covered in that Intermediate Training … or have they?

            • 3. Re: display multiple parent records?
              fmprojohn

              1)  Select the field in question then go to Inspector ---> Data tab ---> Control Style ---> select "Drop-down list."

               

              2)  Now you need to create the "Value List."  At the same place in Inspector click the "pencil" and choose "New" value list.  Give the value list a name and select the field name on which it is to be based.  Next you choose up to two fields to display.  Sometimes you might need to place a calculated field in your table to use as a descriptor to help the user select a name or description as well as the unique ID field.  In other words, if the user only sees

              ID01

              ID02

              ID03

              ...

              and so on, it would be difficult/impossible to make a meaningful choice.  You can choose to only display a descriptor field but use the key field upon selection.

               

              Play with (and de-bug as necessary) your end result to make sure it meets your expectations.

              • 4. Re: display multiple parent records?
                erolst

                If you have a value list from two fields, you will not want to use a dropdown list, because it always show the value from the first field, i.e. what's actually been inserted.

                • 5. Re: display multiple parent records?
                  lombardi3g

                  Yes, I should have said potential parent records.  Thank you for your extensive response - I will give it all a try.  It was a 1 day training only and this was covered but not nearly so thoroughly and I missed a few points in my notes - until I DO it myself I really haven't learned it.  Thank you again and to the other responders, Allan.