Relationships (seems easy, but I'm not seein' it)

[cj247]

I have three databases, one for locations, one for dates, one for events.

When you add an event, you pull down all the locations, some predefined dates and then you add an event. I'm trying to make it such that if you pull down a date and a location that already has a record associated with it (i.e. what would be a second record at the same location on the same date), the first record would show up.

Example: Earlier I told the database that Led Zep was playing on the 9th at the Forum. Today I create a new record and pull down the Forum (from the location list) and the 9th (from the date list).

Can anyone think of a way for filemaker to take me to that record that I had created previously? Which I could then update as I saw fit?

Thanks for any insights that you could provide.

[SoCalMacDude]

Maybe you could make a calculation field that puts event name and date together, then relste that to the other database, creating a record if there is not one present. Maybe you would have to fill in the event & date fields in the related database with a reverse calculation.

Just food for thought!

[-Queue-]

If you make the location list and date list global fields and script the entry of a new record, then this can be done. You'll need a separate layout with a global text field (for location) and a global date field, each formatted as the appropriate list. You'll also need a calculated text field of EventLocation & "_" & DateToText(EventDate) and a self-relationship from a global text field (not the one holding the location) to this calculation field. When a user clicks a 'New Record' button, a script moves them to the global field layout. They make their selections from the lists and click a 'Continue' button. The script ensures the EventLocation and EventDate fields are not empty. Then it sets the non-location global text field with gLocation & "_" & DateToText(gDate) and Exits Record/Request. It tests

If [not IsEmpty(selfrel::id)]

Go to Related Record [show only related, selfrel]

Else

New Record/Request

End If

Go to Layout [Events]

where selfrel is the global text to calculation relationship above.

Of course, you'll want to disable the menu options so that users can't create a new record using a shortcut or the menu selection.