Relationships, Portals & Printing

[LDLayton]

No excuses for my ignorance... unless you count simply being brain dead.

(Under the advice of one of our local heroes) I have 3 related DBs. DB1 = Attendees, DB2 = Customized Data (DB1 is disposable data after an event is concluded), and DB3 = Meetings.

DB3 contains various meetings (most, not all) attendees in DB1 are assigned to attend with a field for Topic, Speaker, Location, Group and Time. Each attendee in DB1 is assigned a Group Code (there are more attendees in a group than each meeting has room for so each meeting re-occurs). Currently I have a relationship set up to display each attendee's meeting schedule in DB1 using a Portal with the relationship by Group. Works beautifully. In DB3, I can also see the attendees from DB1 attending each session.

(I know you all know what's coming next...) I need to print a report for each Speaker that displays attendees for his/her sessions, sorted by Time (which essentially separates each group). I can do one Time per page if necessary, but there could be as many as 60 people in a Group. I understand from reading previous posts (I think?) that I can't print the report from DB3 (portal size required is longer than the page) but for the love of weekends lost to database issues, I can't make it work in DB1. HELP!

And a tedious afterthought/question. If I put all related databases together in a folder and email them, why does the user on the other end have to locate each of the related files when they essentially haven't moved out of their original folder... is there a way to resolve this?

[Ender]

Portals are best for viewing and editing data, but they are not so good for printing reports: You can't easily group portal rows together and they don't always slide properly, especially over multiple pages. It's better to make this type of report in the related file with sub-summary parts.

It's not clear what DB2, "Customized Data" is for or how it relates. Is that the join file? If so, make a sub-summary report in there. This report will need a Sub-Summary by Speaker part for the Speaker's name, a Sub-Summary by Time part for the Time, and a Body part for the Attendee. The sub-summary parts only show up in Preview Mode or on Printouts, and only when you sort by the break fields. So in this case sort by Speaker, Time, and Attendee Name to get a attendee list for each Time for each Speaker.

I couldn't tell if you needed your attendee list broken down by Group too, but that could be accomplished by adding a Sub-Summary by Attendee::Group part and sorting by that as well.

[LDLayton]

I apologize for not giving you more information about DB2. The main reason I didn't is because it's almost sole purpose is to customize the data in the main DB (DB1). (Versions of) DB1 have been in use for several years. I created DB2 as an easy way to customize data that otherwise had to change everytime a new DB1 was created. (It's an event management database; once an event is concluded, it goes into the archives.) Now, with DB2, you simply complete the "form" and DB1 is ready to roll. It's also a security measure. DB1 is PW protected and doesn't allow editing of field definitions. DB2 contains Event name, contacts, cut-off dates, all Value List definitions, etc. and is mostly editable by the user. (It's of no value without DB1.)

In response to your solution, I completely understand the sub-summary function and how that works. (I know it doesn't show here but I've been successfully using FM for years. Sadly, I haven't done alot with relational databases. I know just enough to be dangerous (and to get DB1 & DB2 to function together nicely. Here's my problem. The relevant Speaker and Topic information are in DB3. The attendees are in DB1. They are related by GroupID (DB1::Breakout Code = DB3::Group). I managed to produce an a report by attendee easily using the Portal to DB3 since each attendee/group attends less than 10 sessions each.

For the Speaker Roster (the current problem child), I want to print a report which would include a Sub-Summary by Time. Let's say there are 3 meetings, each re-occurring 3 times to accomodate 3 different groups (so 3 different time slots). What I'd like is a report showing the Topic/Speaker and then 3 sets of information on the page(s) which is a list showing each attendee (sorted by Group/LastName/FirstName) under their appropriate time slot. The problem is getting the right attendees to show up to start with. For example: Group B attendees attend Topic1 at 8:00; Group A attends Topic1 at 10:00 and Group C attends at 2:00. I want it to sort by Time with 8:00 am being 1st on my report. Because the Time field is in DB3 and I'm trying to run the report out of DB1, I can't manage to make it sort properly. The only connection between a Topic and an Attendee is the Group Code. I was under the impression I had to produce the report in DB1 since that's where my attendees are. What am I missing???

I found a work around but it's only good for this one event. I'd like a solution that's long-term and allows generics... i.e., writing the script, etc. without knowing the Topic name or Group assignment (sometimes they're ABC, sometimes 123, sometimes A1B2C3, etc.)

I know the solution is staring me in the face... but I can't seem to find my FM glasses.

[Ender]

You should be able to put the sub-summary parts on a layout in your Attendee file. Just use related sub-summary break fields:

Header

------------

Sub-Summary by DB3::Speaker

------------

Sub-Summary by DB::Time

------------

Body

------------

Footer

BTW: I know you have this system pretty much working for you, but you might find going to a normalized design helpful. If you had a structure similar to what I have below, you could keep data in there from year-to-year, and just add Events and their corresponding Workshops and Sections over time. The benefit is less data entry and the ability to quickly see the Attendee history over time. Something to think about.

[LDLayton]

I have the exact set up you suggested in place currently. Part of the work around I found was taking the Portal off my report -- causing uncontrollable sorting issues (how stupid was that! Had my head on portals and couldn't get it back to just related fields). The problem is I can only do one time slot at a time. Assuming that's what I have to live with, my issue now is with the finding of each of the subsequent groups without knowing Code assignments ahead of time. I think the solution is with an ID field. I'm working on it and will come back if that's not the ticket.

Regarding your suggestion, that would work nicely if there weren't a magnitude of other varying factors. I only told you about the "Breakout Sessions" we're running on one day of a 4-day event. We do special events for a Fortune 50 company and rarely are 2 events ever remotely the same. Subsequently and because we do work for a number of different divisions, the attendees are rarely the same so while we could purge multiple attendee lists, it would serve little if no purpose to the client. (It's happened but it's rare.) Many times it's a "Customer" event to introduce a new product line or an appreciatory schmooze-job. I'm lucky if I get to import email addresses from a group of 15 previous client attendees. Client addresses commonly change with titles as they're moving up the ladder. By the same token, in the last 7 years, I've probably seen the same customer (maybe) twice. We literally manage everything from invitation distribution to hotel selection/management, flight schedules, ground transportation, who's staying for dinner and who wants cookies before bedtime. Basically we do it all (with the help of many various suppliers). The client just tells us who to invite and signs the check. My objective is to get DB1 to the point that I can pass it off without the standard edits resulting from yet another customized event. I'm almost there if I get this and one other issue resolved.

Thanks so much for your help and I promise to beckon if I need more guidance.

Linda

[D-fendr]

If I'm understanding correctly, I don't see why you can't print this out of DB1 since "DB1 are assigned to attend with a field for Topic, Speaker, Location, Group and Time."

If you create a columnal layout report with Grouped data; specify the fields: Speaker, Attendee, Time. Select "Speaker' field as a group and also sort by time. Specify your page break after each Speaker in the Part Setup for the Speaker subsummary.

Hope this helps.

[LDLayton]

D-fender: No, attendees are only assigned a Group Code in DB1. All Speaker, Topic, Time, etc. data is in DB3. There is also a matching Group field in DB3 which is what links the Session to the Attendee in DB1. The reason I created DB3 was so there wasn't unnecessary duplication of data for all attendees in DB1. It's already very size intensive and it would require at least 10 additional fields for each session they're scheduled to attend. We've done events where there were as many as 15 breakout sessions attended (or available) for each attendee so that didn't seem the most feasible avenue.

Thanks anyway.

[D-fendr]

Yeah, I should have known that was too easy.

Sounds like you have something quite similar to a classic edu course registration application where you have Student, Course, Section, Classroom, Teacher and you need to report student schedule, teacher schedule (and in edu, room schedule.) You may have seen this used before as the example for teaching about designing relationships.

Offhand, I think you're going to end up with a join file with one record for each attendee/session occurance.

[LDLayton]

D-fendr... Do you have a link to a sample of that? Sounds like there may be some valuable info there. Thanks so much for your input

Linda

[D-fendr]

I've seen this used as an example in teaching basic relational database design. Do you mean a sample database? Or the file/table design - tables/relationships?

Here's a tutorial using students, etc. to teach design/normalization: http://www.geekgirls.com/database_dictionary.htm

Scroll down to see the tables, fields, keys, etc. The Student/Courses table is the join I referred to earlier.

I also found a Filemaker 7 template, "Registration" that may provide some information. I downloaded it and was able to list attendees by event with event contact (substitute speaker). You could vary the layouts and get what you want. You could look at the relationships/tables they used to get a better idea.

I haven't studied all your posts in detail so I hope it's not a waste of time, it may be too simplified. But here's the link: http://collection.filemaker.com/collection/search_details.jsp?serial=2182

What I was really thinking of is an article that used this as an example to develop Entity-Relationships. It may have been in an old issue of FileMaker Advisor, but I'm unable to find it in the time I have to look right now.

As you know, FileMaker is big in edu; it would surprise me if there weren't more templates for student/course/registration that might be of use.

Hope this helps. Let me know if it's not what you're asking for, I'll try to find more..

John