12 Replies Latest reply on Feb 28, 2014 8:18 AM by philmodjunk

    How do I plan a conference program database?

    otto_m

      Title

      How do I plan a conference program database?

      Post

           Hi,

           I am planning a database for an upcoming teacher conference that should be able to output all documents necessary for the organisation of the event.

           So far, I have two tables, "Presenters" and "Papers". As there are joint presentations, "Papers" can have more than one entry from "Presenters". Therefore, I have joined them by a link table using "Presenter ID" and "Paper ID".

           In a next step, I need to set up the conference schedule to cover the following events: There are twelve time slots @ 40 minutes each, and in each time slot, there are four events in four different rooms.

           The output should be like:

           Friday 23 May 2014 — 13:00—13:40 — Room 4.01 — Presenter A and Presenter B — Title of Paper 1
                                                                           Room 4.02 — Presenter C                            — Title of Paper 2
                                                                           Room 4.03 — Presenter D and Presenter E — Title of Paper 3
                                                                           Room 4.04 — Presenter F                            — Title of Paper 4

           Friday 23 May 2014 — 13:50—14:30 — Room 4.01 — Presenter G and Presenter H — Title of Paper 6
                                                                           Room 4.02 — Presenter I                               — Title of Paper 7
                                                                           …………………

           I would be extremely grateful if you could answer these questions:

      • How many tables do I need for the time slots and rooms?

      • How do I join the new table(s) to include the presenters and papers?

           Thank you very much in advance,

           Otto

        • 1. Re: How do I plan a conference program database?
          philmodjunk

               Assuming that each paper is only presented once....

               You need one table for rooms and one for time slots. Each record in Time slot needs to specify three details: The Room ID, the Start time for that slot, the ID of the paper being presented. The time slot table will serve as another "link" table but this time between rooms and Papers. Once you have linked a slot to a Paper, your existing relationships enable you to list all of the presenters for that paper.

          • 2. Re: How do I plan a conference program database?
            otto_m

                 Dear PhilModJunk,

                 Thanks a million for your help!!! It works like a charm, and you have saved me an enormous amount of trial and error time:-)

                 The list looks great, based on the time slot table and with the following parts:

            Sub-summary when sorted by "Start Time"
                 Fields: "Start Time" - "End Time" - "Event Type" (talk, workshop, etc.) from the time slot table.
                  
            Body:
                 Fields: "Name of Presenter" from the Presenter table, "Title of Paper" from the Paper table, "Room Number" from the Room table.

                 However, there is one last thing I could not work out:

            • In the case of two speakers, how do I get the second speaker's name on the list without repeating the title of the paper?

            • Or can I create a field that merges the names of the two speakers into one field?
                  
                 Thank you very much in advance,
                  
                 Otto
                  
            • 3. Re: How do I plan a conference program database?
              philmodjunk

              In the case of two speakers,...

                   Use a portal to list all speakers. (Could be more than two in some cases). The portal can be set to slide up/resize enclosing part and then, when printed, extra unused portal rows will not show in the printed/PDF'd/Previewed report.

              Or can I create a field that merges...

                   That is also possible. Substitute ( List ( RelatedTable::Field ) ; ¶ ; ", " ) is one way to get a list of speakers delimited with commas so that you can list them in a horizontal row.

              • 4. Re: How do I plan a conference program database?
                otto_m

                     Thank you for your advice.

                     However, I am not able to define the portal setup so that it displays any data. Can you please explain how to set up the portal correctly.

                     Sorry to be such a bother.

                     Otto

                • 5. Re: How do I plan a conference program database?
                  philmodjunk

                       What are your current tables and relationships? On which of those is your layout based? I need those details before I can specify.

                  • 6. Re: How do I plan a conference program database?
                    otto_m

                         Thank you for your swift reply.

                         Attached are the relationships as seen in FileMaker and the layout of the parts in question.

                         To recap, the layout "Programme" looks great when there is only one speaker:

                    Sub-summary when sorted by "Start Time"
                    Fields: "Start Time" - "End Time" - "Event Type" (talk, workshop, etc.) from the time slot table.

                    Body:
                         Fields: "Presenter" is a calculation concatenating "Given name" and "Family name" from the Participants table, "Title" is from the Proposals table, "Room Number" from the Room table.
                          
                         However I tried to set up the portal for more than one speaker, it either showed one speaker or nothing.
                          
                         Thanks again for your help.
                          
                         Otto
                    • 7. Re: How do I plan a conference program database?
                      otto_m

                           Only the second image seems to have been attached, therefore I am sending the Relationships again.

                      • 8. Re: How do I plan a conference program database?
                        otto_m

                             … and the layout is based on ESP_2014_timeslots

                        • 9. Re: How do I plan a conference program database?
                          philmodjunk

                               I'm having trouble matching up the table described in your original post to these:

                               So far, I have two tables, "Presenters" and "Papers". As there are joint presentations, "Papers" can have more than one entry from "Presenters". Therefore, I have joined them by a link table using "Presenter ID" and "Paper ID".

                               That describes this set of tables/relationships:

                               Presenters------<LinkTable>-------Papers

                               Presenters::__pkPresenterID = LinkTable::_fkPresenterID
                               Papers::__pkPaperID = LinkTable::_fkPaperID

                               If a paper has two presenters, you create two records in LinkTable with the same PaperID, but different PresnterIDs. I don't see that set up in your screen shot of tables/relationships. Instead I see a record with two "Speaker" fields--which does not match to the above data model where multiple speakers means multiple records in LinkTable, not multiple fields in the same record.

                               My best guess is that Events is your "link table" but then the Speakers, Speaker 1 and Speaker 2 fields should be removed from that field as you won't need them here. Instead, you'd use a portal to Participants to list names from the Participants table and this should list all participants linked to that Event record and which is linked in turn to your time slot record.

                                

                          • 10. Re: How do I plan a conference program database?
                            otto_m

                                 Dear PhilModJunk,

                                 Thank you for being so supportive. I am sorry I have caused some confusion by renaming the tables. I have done this because there are not only presenters in the table but also attendees, publisher reps etc. And the Papers table actually contains all speaker proposals, even the ones we had reject.

                                 I have cleaned up the link table, inserted a portal into the layout, and yippie, it works!!!

                            Thank you ever so much,

                                 Otto

                            • 11. Re: How do I plan a conference program database?
                              otto_m

                                   Dear PhilModJunk,

                                   I think I have figured out almost everything for the conference database, but there are two little things that have drawn criticism from colleagues:

                                   I created a field that merges the names of the two speakers into one field using Substitute ( List ( Participants::Family Name ) ; ¶ ; ", " )This works well, but some would like to see their name first, because they are the main contributors. I have set up the Participant ID to reflect the order and tried sorting the list, but FM ignores the order.

                              Is there a way to sort List entries?

                              The Conference Programme layout looks great, but the page breaks are random. E.g. there are fewer events on Friday than would fit on the first page, so Saturday events are listed under the Friday heading. I have tried to avoid that by inserting blank records for Friday events so that the first Saturday event is pushed to the new page. 

                              (My layout uses a subsummary sorted by Time ID, the body is a portal containing the names of the presenters, the title of their presentations and the room number.)

                              • Can I set user-defined page breaks without using dummy records?

                              Thanks again for your help.

                                             Otto
                                              
                                    
                              • 12. Re: How do I plan a conference program database?
                                philmodjunk

                                Is there a way to sort List entries?

                                Sorting the portal or sorting a found set won't change the order of names returned by List, but if you double click the relationship line in Manage | Database | relationships, you'll find a button for sorting the related records. By sorting the records at the relationship level, List will return your names in that same sorted order.

                                ExecuteSQL can also be used in place of List and then there's an ORDER BY clause that you can use to get the names into the desired order.

                                Can I set user-defined page breaks without using dummy records?

                                     Do you want to see each Sub Summary layout part start a new page? If so, double click the part label while in layout mode to open Part setup and you'll find an option to specify a "page break before every occurrence".