Looking up record serial based on a value list?

[jschwarz]

I had posted a similar question under relationships, but this is a broad question, so I think it's appropriate in this section of the forum.

I have a table of people, for a fundraising db, and a table of events.

Each event needs to be tied to a person, as it contains their payment info, etc. There need to be many events per person.

I want to have a layout that displays all events per person. I am planning on using a portal for this.

However, I can't create new events on that page (all events), as they need to be looked up. I did this by creating a field on the Events table which is joined to the Person serial.

On my layout for creating an Event, how can I get that Person serial? I have a choose Person field, created by a value list that looks that the People field and references a Full Name calculation.

I suppose I need to do some calculation based on the name selected, get the person's serial from that, and insert it into the events person serial. Can someone help me with that?

I can clarify if needed.

[Vaughan]

A person can attend many event, and an event can have many people... so you have a many to many relationship. A "join" file is needed; let's call it "applications". Each event record has one event, and one person on it, just like an application form.

You need three files: People; Applications; Events.

[jschwarz]

Sort of. Since this will contain details specific to each person for each event, it's really just one-to-many.

We're not concerned with details about the event, but rather, each person's details about the event - has an invite been sent, have they RSVP'd, have they paid, etc.

I have my tables all set up, so this is more of a technical issue than a design issue. I need to know how to get the serial from the person that I select and store it in the event record.

[bruceR]

Listen to Vaughan. He gave you the right answer. Put the fields you want in the join file.

[ESpringer]

Indeed. Note, you can still have all the join file data seamlessly viewable in a portal from your people page. You never actually interact with the join file directly after setting it up. Instead of a portal to the related events records, you want a portal to the related join records, but the portal just shows the event field from that file, along with facts about the *relation* of that person *to* that event (paid, when signed up, etc.). This is all *much* easier in FM7. You'll be able to use portals from the event side to see corresponding people (including their misc. personal data and data specific to this event), and vice versa, just by setting up one two-step path.

"Applications" may or may not be your best name for the join file: "Attendance" or "Connections" or "Signups" may also do it: the point is that what you're really tracking is a matrix of people-event combinations.

[Vaughan]

The only time you need to directly interact with the join file is when you create reports: these are generated in the join file, not in the people or event files. (This avoids having to print portals.)

Thanks for the suggestions for a better file name: I like Signups. I've done a lot of course enrolment dbs and the join file is usually either "enrolments" or "applications".

[jschwarz]

Thanks for all the replies so far. Just to clarify, what was initially asked of me was truly just a one-to-many relationship, as they didn't mind re-entering event info each time. This is, of course, silly, and a waste of effort.

So, sorry for being slow, but I have a few more questions.

1) Instead of joining my People table and Events table, I should create a third table, Signups, perhaps, and it's connected to both of the others? If so, I imagine it would have fields for People Serial and Event Serial... and that's it?

2) To add a new event, the layout should be based on the Events table. However, to fill in one person's details for an event, it should be based on the Signups table? Finally, a third layout based on the Person table would show all events for one person?

Thanks again for the help.

[bruceR]

Yes, you need a signups table. See attached.

It will probably be better to use different layouts. But you don't have to. You could put portals to the people file, signup file, and event file on one layout.

A list (portal) of all people on the left. A list of all events on the right. Click a person, his detail shows top/center. Click event, the event detail shows up in the middle center. Bottom center is a portal of people assigned to the event. With a little trickery you could even get the bottom portal to show all people for selected event or all events for selected person.

Events.zip

[bruceR]

Like this...