On 2008 Feb 17, at 15:27, Chuck Pelto wrote:
Greetings,
I'm working on a system to rapidly enter events that attendees at a
convention want to participate in.
These various EVENTS are in a table, separate from the table of
ATTENDEES.
There is a field in the ATTENDEES that is established as a CheckBox
field in which all the possible events from the EVENTS table are
listed.
The idea is that the person entering data on which events an
attendee is registering for will be checked in that CheckBox field.
I'd prefer that the events are listed in that field in
chronological order. The EVENTS table has a field for that, with
each record of each event.
The only way I seem to be able to get the events to sort out
properly, i.e., in chronological order, is to include the DateTime
field for events in the Value List; as the second field.
I'd rather not show the DateTime data in the CheckBox field. But if
that data is not selected in the ValueList set up, the events will
not sort properly. This, despite setting the Table for this
relationship, between EVENTS and ATTENDEES to sort based on (1)
DateTime and (2) Title of event.
How do I accomplish this?
Chuck, I hope you're not up against a tight timeline, because I'm
going to suggest something that will require a redesign of your
system but which will, I think, serve you better over the long haul.
To ease the sting a bit, I'll also volunteer to send you a sample
file showing how it works (and from which you may copy), if you
contact me off-line at [EMAIL PROTECTED]
The idea is to take advantage of FMP's relational capabilities by
introducing a 3rd table -- known variously as a merge or join or link
table -- between "Events" and "Attendees". I'll call this 3rd table
"AELinx", short for "Attendee-Event Links". Its purpose is to hold
every registration record -- each combination of a person and the
events he or she has signed up for.
The way you populate the table is to go to the "Attendees" record for
a particular person (say Joe Blow) and click on a button in a tell-
all portal that lists the available events. Let's say the event Joe
is trying to register for is "Snow: The Original 4-Letter Word for
Awful Stuff". The button is attached to a script that sends Joe's
record ID (from "Attendees") and "Snow"'s record ID (from "Events")
to a couple of global fields. Then it zips over to the data-entry
screen for "AELinx", issues a "Create New Record" command (which
pulls the values of the respective ID codes from the global fields),
and finally returns to Joe's record, where you now see "Snow" entered
as an item in his "AELinx" portal.
To keep the tell-all portal from getting unduly long (which can
happen if you're running a conference with a couple of hundred
events), you can set up a global field to filter it by, say, day or
track or initial. Whatever course you choose, the tell-all portal is
sorted the way YOU specify when you set up the relationship, so if
you want to see things listed by time slot, then by golly that's
exactly what you'll get.