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.