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.

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.

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.

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?