3 Replies Latest reply on Jul 14, 2014 9:47 AM by philmodjunk

    Newbie Question: Easiest way to do Bento collections in FMP13?

    sritcp

      Title

      Newbie Question: Easiest way to do Bento collections in FMP13?

      Post

           We run some online workshops. I have been using Bento for a number of years to manage our workshop registrants (and alumni).  With the demise of Bento (and more importantly, its crashing in Mavericks), we went ahead and purchased FMP13 yesterday. I converted the Bento-exported CSV file and got it into FMP. I need a quick fix solution to organize my data (next workshop season is approaching; I don't have the time to master FMP!!!).

           I have a master data table of about 2000 attendees (records or rows) and about 50 fields (or columns). We run 8 different workshops. People register for 1 or more workshops. In Bento, I used to make a collection (not a "smart" collection) for each workshop, and drag the attendees (records) registered for that workshop. In each collection, I used fields that were relevant to that workshop. Each of these collections, therefore, had not only a subset of records, but a subset of fields.

           What is the simplest way to achieve this in FMP? I understand that this is called a "view"? 

           I thought I could use FIND to filter records, but I guess this won't filter the fields. Portals (which I haven't yet studied) seems like using a cannon to swat a fly; I just need to be able to look at the registrants for each workshop and selected fields. Of course, I should be able to add new registrant without having to go back to the master table or view.

           I am still doing the tutorials. Any help would be greatly appreciated.

           Thanks,

           Sri.

        • 1. Re: Newbie Question: Easiest way to do Bento collections in FMP13?
          philmodjunk

               For those of us that never used Bento, your description of a "collection" lacks sufficient detail to be sure of being able to effectively answer your question.

               While there's always a chance that someone else will step in that does understand sufficiently, I suggest that you back up and describe what you want to do without referring to collections so that there are more people who might read this that will be able to understand your problem sufficiently enough to offer suggested solutions.

               

                    I have a master data table of about 2000 attendees with about 50 fields.

               That's a start. What does one record in this table represent? I can read that it's an "attendee", but if the same person were to attend 3 different workshops on three different dates, would that result in one record in this table or 3 records?

               Have you, at this point, defined any other tables in your database? FileMaker is a relational database so you won't get very far without setting up multiple tables linked in relationships.

               

                    I thought I could use FIND to filter records, but I guess this won't filter the fields

               A find is the most common way to take the records in a table, such as your 2000 attendees and pull up a sub set of those records (called a "found set") that all satisfy search criteria that you specify in one or more fields. The design of the layout will then determine what fields are visible on your layout. My best guess is that your 2000 record table with 50 fields probably needs to be redesigned into 2 or more related tables linked in relationships, but I have no way to be sure of that from what you have thus far posted.

               

                    I don't have the time to master FMP!!!

               Well, that will be a problem for you. Bento is NOT FileMaker. Filemaker is a much more powerful system, but one that is not as simple to use. Bento is like snapping together duplo blocks while FileMaker is more like building with Lego blocks. Not only are there more and smaller moving parts, you need to have at least a basic understanding of Relational Database Design in order to create an effective solution.

               Here's a "rough" cut at how you might set out to manage the first parts of scheduling people for workshop attendance. This is far from a complete solution, it's just offered as a way to get you thinking:

               Contacts-----<Contact_Session>-----Sessions>------WorkShops

               Contacts::__pkContactID = Contact_Session::_fkContactID
               Sessions::__pkSessionID = Contact_Session::_fkSessionID
               WorkShops::__pkWorkShopID = Sessions::_fkWorkshopID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               Contacts, as suggested by the name lists each person that registers for a workshop a single time. Fields here record contact info such as name, phone, email address...

               Workshops form a "course catalog" of workshops offered now, in the past or sometime in the future. If you plan to offer a workshop on "the joy of basket weaving" in January, May and September, you have just one record in workshops, but three records in Sessions.

               Sessions as I just alluded to, records each date/time/location that a given workshop is offered.

               Contact_Session, which might be renamed "reservations" or "registration" is the interesting table here. This is a "join" table linking contacts to sessions is a many to many relationship. Each time that a contact signs up for a specific workshop session, a new record is created in this table. It's a many to many relationship because a given contact can sign up for multiple workshop sessions (one for Joy of Basketweaving, one for Zen of Soul food, one for...) and a given session will link to multiple contacts--all those that signed up for that session of its linked workshop.

               

                    Portals (which I haven't yet studied) seems like using a cannon to swat a fly;

               Portals should be high on your list of things about FileMaker to master. It's very rare that I create a data entry layout that does not have at least one portal to a related table. In this example, a portal to Contact_Session can be placed on a contacts layout as a way to select workshop sessions for that contact and a portal on a Sessions layout could list all contacts signed up for that session. A portal on a Workshops layout could list all contacts that ever, for any session, signed up for that workshop...

               And that's just a starting point. Additional tables and relationships will likely be needed to manage the whole process.

          • 2. Re: Newbie Question: Easiest way to do Bento collections in FMP13?
            sritcp

                 Hi PhilModJunk:

                  

                 First, I’d like to thank you for taking the time and effort to write a detailed comment. I am continuing to process it, as I am studying the training materials simultaneously.

                  

                 1. To get the Bento collection out of the way, here’s what it is. I have a master table of 2000 attendees (records) and 50 fields (columns). I create a ‘collection’ called “Workshop#1”. This collection name is displayed just under the Master Table, in the left hand side column. Into this collection, I drag the records of attendees who have registered for the first workshop. In this collection, I keep the needed fields (“Whether they have paid of the first workshop” ) and turn off fields not needed (“Whether they have paid of the second workshop”). So, I am left with, say 50 records and 25 fields. Of course, I can add a new registrant to this collection and the master table will update to include the new entry. I can delete a record from the collection without deleting it from the master data.

                  

                 2. To answer some of your questions,

                 a. It is one record per attendee, even if she registers for more than one workshop. Many attendees, do several of our workshops over a period of years. This information is stored in a text field named “History” (not the best way to do it, I know; but that’s how it has been done).

                 b. No, I haven’t defined any other tables in the database. (As I mentioned, I purchased FMP yesterday, and I imported the Bento data in as a master table, and I was looking for a simple way to track the registrants for each (upcoming) workshop.

                 c. It is not clear to me how the data naturally splits into two tables. I have fields representing “Whether a purchase order is needed,” “If the P.O. has been sent”, “Has the payment been received”, etc., but these really relate to the attendee, though they refer to the workshop. I don’t know what fields will go with the “Sessions” table and what would constitute records in that table. I need to study your example before I can comment intelligently. It seems to provide a much better power over the data.

                  

                 3. I do see that FileMaker is more complex and more powerful and I intend to get into it. But I need to solve the immediate problem. You have said I could use “found set” and a different layout to filter the variables. If I haven’t misunderstood this point, this should fulfill my limited objective (outlined above). I am going to try that first.

                  

                 Thanks,

                 Sri.

            • 3. Re: Newbie Question: Easiest way to do Bento collections in FMP13?
              philmodjunk
                   

                        c. It is not clear to me how the data naturally splits into two tables. I have fields representing “Whether a purchase order is needed,” “If the P.O. has been sent”, “Has the payment been received”, etc., but these really relate to the attendee, though they refer to the workshop. I don’t know what fields will go with the “Sessions” table and what would constitute records in that table. I need to study your example before I can comment intelligently. It seems to provide a much better power over the data.

                   This is a crucial aspect of relational database design and not unique to FileMaker. The basic rule of thumb is to avoid having to record or store the same information more than once. So if a person registers to attend several different workshops, you would not record their name and contact info once for each workshop. Not only is that more work, but if, say do to a data entry error, you need to correct some of their contact information, you do not want to have to track down and identically update every copy of the same information.

                   So you'd record the person's name and contact info once in a table of contacts, but information such as "a purchase order is needed" would be recorded in a related table where you register the contact for a specific workshop if that data is only specific to registering for that one workshop. (but if "Purchase Order is needed" applies to any and all workshop registrations, you'd record that data in Contacts.)

                   

                        You have said I could use “found set” and a different layout to filter the variables.

                   That's not really what I said. You commented on performing finds and I tried to clarify how they work. Think of each record in a table as a 3x5 note card. The complete stack of Note Cards in a file box would be your table. If your note cards record contact info and you perform a find for all contacts whose last name starts with "A", that is the equivalent of sorting through your note cards and making a stack of all Note Cards where the last name starts with "A". That "stack of cards" would represent your "Found Set". But any given layout you design offers you options for selecting which items of data recorded in a given record are visible on the screen so just because you have 20 different fields in a contact record does not mean that you have to put all 20 fields on one layout for contacts. You can design several different layouts all based on contacts but designed for different tasks and showing only the data you need for that specific task.