Event Dates

[rdhaden]

Anybody else making databases of events? How are you handling dates, particularly multiple dates? For example, a concert may hold two weeks of dates until the tour solidifies and they settle on one particular date. Or a basketball tournament may occur across three days. Thus multiple dates per event.

In 5, I had date as a repeating field, but that has obvious limitations, first being the number of dates an event can have is limited to the number of repetitions the field has.

In 7, since I can have multiple tables in a single file, I thought I'd make Events one table and Dates another, with the Dates table having EventID as a foreign key. Sounds good. I can have unlimited number of dates per event, unlimited number of events per date. But...

I want context-sensitive calendars (i.e., a calendar that only shows events that use a particular room, or only confirmed events, not tentative, etc.) Since calendars are really portals for each day, I match left side globals set by script, right side date. But for context, the match has to contain info from both tables (space used or status in Events table, date in Dates table). But you can't index a calculation that references related fields. Any suggestions?

[comment]

You can have up to 32,767 repetitions in a field.

[Ender]

rdhaden,

In order to reference Event information in Date, add those fields to the Date table with an auto-entered Lookup from the original field in the Event table. These fields in Date can then be indexed and used as match keys in relationships.

If it's possible for those parent Event fields to change, and you need the changes to propagate to the Dates, use a script to update the Date fields by performing a Relookup on the EventID key for the related Date records.

[rdhaden]

The limitation on repeating fields is how many I can fit on a page, or how many I can reasonably expect to be used, or how many I want to have to deal with in scripts, etc., not how many Filemaker can handle. Thanks anyway.

Thanks for the re-lookup idea. There are other scripts that run when an event is changed, so that solution is perfectly acceptable. Thank you very much!