3 Replies Latest reply on Aug 5, 2014 6:59 PM by AjEGfmTech

    Selecting records from one table to create a relationship with different table/layout

    ShaneB

      Title

      Selecting records from one table to create a relationship with different table/layout

      Post

           I am working on a new project where my users need to create observations based on project participants. On a layout based on the observations table, I would like users to select from a pick list of project participants. I would like 3 other fields to be auto-populated after this selection takes place. The observations table is related to the participants table through a join table. I am unable to figure out how to set it up so users would pick a participant, the participant id and observation id then joined on the join table, and the remaining fields from the participant table populated on the observations layout.

           I know this is probably Filemaker 101, but I have never had to do a project like this and it's surprisingly challenging to me.

        • 1. Re: Selecting records from one table to create a relationship with different table/layout
          philmodjunk

               I would guess that you have these relationships--though the names may differ a bit:

               Observations-----<observation_participant>-----Locations

               Observations::__pkObservationID = observation_participant::_fkObservationID
               Locations::__pkParticipantID = observation_participant::_fkParticipantID

               You can place a portal to observation_participant on the Observations layout to list and select a Locations record for each given observations record. Fields from Locations can be included in the Portal to show additional info about each selected Locations record and the _fkParticipantID field can be set up with a value list for selecting Locations records by their ID field.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               And you may find this demo file to be a useful source of ideas: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

               FileMaker 12 users can launch the application, then use Open from the File menu to get a copy of this file converted to the .fmp12 file format.

          • 2. Re: Selecting records from one table to create a relationship with different table/layout
            ShaneB

                 That file helped me see what I was doing wrong: attempting to use the participant pop-up to select the participant name from the PARTICIPANT table vs. populating the field in the JOIN table with the user ID. I just have it show the user name field but populate with the user id instead using the option in the pop-up menu data.

            • 3. Re: Selecting records from one table to create a relationship with different table/layout
              AjEGfmTech

                   Thanks for this example and the Demo file. That was EXTREMELY helpful in understanding how this works.

                   Followup question, if I may.

                   I have created a similar database where Locations = Ingredients and Events = Sandwiches. (My database is for a nutritional menu.)

                   For every Ingredient record I have several nutritional fields (calories, fat, sugar, iron, etc.).

                   The goal is to allow a user to select different Ingredients for their Sandwich and see the impact on the overall nutritional values. Basically, a nutritional calculator.

                   My question is how to get the Sandwiches layout to display the summary of the nutritional fields of the Ingredients records that are selected/deselected using the technique described in this thread?

                   My initial thought was to create a summary field in the Ingredients table for every nutritional field, THEN create a GetSummary calculation field (Sum_NutriCalc) in the Sandwiches table for every summary field in the Ingredients table. To do this required a break field in the GetSummary calculation. I just chose the Ingredient name.

                   On the Sandwiches layout, I have a portal where the Ingredients are selected from. I sorted the portal by Ingredient name. I then added the Sandwiches::Sum_NutriCalc fields. Needless to say, no values appeared in those fields. I'm sure I have the plumbing wrong somewhere, but I'm not sure where or if the GetSummary calc field is a good idea in the first place?

                   Any help would be greatly appreciated. Thanks.