3 Replies Latest reply on Nov 14, 2014 8:23 AM by philmodjunk

    Populate Fields from separate table



      Populate Fields from separate table


      I am having a problem populating fields on a layout from a table an am hoping you can help.

      My main database the Contact Management database with a number of people (Contacts table).  Each record has a unique ID (Contact ID) as its key.  The second database is an external source (separate FMP database) that records the presence of each contact at an event (Events database).  I have created a table in the Contact Management database (Events History) using the Events database as the data source and created a relationship between the Contacts Table and the Events History Table based on the Contact ID.  I can get a history of each person's attendance at events by using a portal but here is my problem:

      I want individual fields on a layout to show me if a contact has attended a specific event.  So I have 5 fields: Event 1...5.  I made each field a calculation using an IF statement - IF(EventHistory::EventDate = "SpecificEventDate1...5"; EventName; "ERROR").

      What happens is only the first instance of the Contact ID populates the corresponding field for whatever the first event is that they went to (i.e. if they went to event 2 and 3, only the field for event 2 shows the name and all other fields show ERROR).  By way of reference, I will change the ERROR to a blank after complete.  The ERROR is used by me for troubleshooting.



        • 1. Re: Populate Fields from separate table

          Any time that you set up a calculation that refers to fields from a related record where there are more than one related record, the calculation can only refer to the first related record unless you use a specialized function that does it differently. This is why each If function only references data from the first related record.

          Why did you choose to define 5 separate fields instead of a portal? What problem does that solve for you? What happens when there turns out to be 6 or more events that the contact has attended?

          Note that there are ways to use portals to display data in a horizontal row and also ways to use a calculation field to list all data from a related table in a horizontal row.

          • 2. Re: Populate Fields from separate table

            Hi Phil,

            The reason I chose fields over a portal is for search purposes. I wanted to be able to create complex searches such as: people who attended event 1 & 3 but not 2.  As for why I used 5 fields it is just because there are a finite amount of events.  If there another event comes along I was just going to add another field. 

            After my original post I was thinking about enlarging the portal ant trying that so I am very open to suggestions. 

            • 3. Re: Populate Fields from separate table

              If there another event comes along I was just going to add another field.

              I would not recommend that approach as it is pretty inefficient.

              The type of search you describe should be possible either by using a script specifies criteria in your portal or by performing (probably also with a script) the search on a layout based on your portal's table.