6 Replies Latest reply on Jan 10, 2011 7:04 AM by MiloMV

    Adding event tracking to an existing database



      Adding event tracking to an existing database


      Please forgive me once again for asking for help on what is probably a basic relationship setup, but even trying to extrapolate how this is done using Filemaker’s “Registrations” template, I cannot make it work on my database. I have just ordered the “Missing Manual” book for Filemaker 11 so will not bother you kind people again once it arrives. I only ask now because I am under a deadline to make this database work for a presentation next week. I will totally understand if no one wants to bother answering. But if there is one kind soul with a bit of time who could tell me  how to do this I would be extremely grateful.

      As part of our foundation database which I am creating which already keeps track of a large list of potential donors, their donations, pledges and other information, we will be inviting certain of these people to various fundraising parties and want to keep track of who was invited to which event.

      For the sake of simplicity I present the relationship I would like to make using only two tables with only a few fields:

      Table one: DONORS containing contact information for all possible donors with the fields: idnumber, firstname, lastname, etc.

      Table two: PARTIES to hold the list of each event with the fields: idnumber, partydate, partyname, partyidnumber

      I would like to have a portal (or whatever you use with this type of relationship) on the DONORS form from which you could select the party or parties that this person has attended or to which you would like to invite them in the future. It would be nice if the party could be selected from a drop-down list.  I would also like this to be done in such a way that I could easily make a list report for each party to see who was invited to it and print out mailing labels for the invitations. I would also like to be able to do limit finds and sorts to make reports showing who was invited to not just one party but to a group of selected parties (and by omission, who has not been invited to any parties as yet).

      Right now I have a test database set up using the pared down tables and fields which I have given you above and have linked the tables with the donor idnumber which is an autofill serial number. It is on this practice database that I have futilely been attempting to figure this out.

      If with your help I can make this work on this pared down database, I should be able to incorporate it into my much larger Foundation database that already keeps track of donations and pledges using many tables and table instances in relationships that are working just fine. Since I already have numerous links to the DONORS table idnumber in my larger database, I may need to create another instance of this table (DONORS2) to match up with the PARTIES table. In attempting to figure this out by dissecting the “Registrations” template, it looked like there were small intervening tables to make this particular kind of relationship work that I did not understand. One person can be invited to many events, but also each event can have many persons invited to it.

      Thank you in advance for you help and patience. Milo

        • 1. Re: Adding event tracking to an existing database

          Your "Parties" is actually a "join" table; should really be named "DonorParties" (to denote that it contains a combo of a Donor ID and a Party ID). You need another table, for plain old Parties, which creates an auto-entered (or UUID) PartyID, has the name, date, etc.; but NO Donor fields. 

          The Party table holds "party" info. It also serves for a Value List (based on its fields, PartyID, show also "Name"), as well as the anchor of the other main entity (parties). 

          So, a layout on the Party table, based on a relationship to the join table (current "Parties") will see all people at its party. A Donor will see all his/her parties.

          So, 3 tables: Donor <1-many-->DonorParties (current "Parties)<--many-1>Parties

          • 2. Re: Adding event tracking to an existing database

            Dear Fenton Jones,

            Thank you very much for your thoughtful answer. It helped a lot and you are very kind to take time to help a beginner. I am new to filemaker and am flying by the seat of my pants until the third-party manual comes to the rescue. I now understant the concept of a join table for many-to-many relationships, but still need a bit more clarification to make it behave.

            I now have three tables:

            Donors: idnumber; firstname; lastname

            Parties: partyidnumber; partyname; partydate

            DonorParties: idnumber; partyidnumber; firstname; lastname; partyname; partydate

            I have linked the tables thus: Donors <1-many key:idnumber> DonorParties <many-1 key:partynumber> Parties

            In both links the little box shows an "=" and in both relationships I have allowed record creation in DonorParties

            In my Donors layout I created a portal called in from the DonorParties table and asked it to display the fields: partynumber; partyname; partydate. I formated the partynumber field in the portal to be a dropdown list based on a value list of all the entries in the field donorparties::partynumber.

            In my Parties layout I created a portal called in from the DonorParties  table and asked it to display the fields: idnumber; firstname; lastname.

            In testing this new arrangement, I went to the donors layout and for one of the donors selected the first box in the "parties selection" portal and, sure enough, a dropdown menu of all the party numbers appeared and I selecte party#1. But after tabbing through the other two boxes in the portal and even clicking outside to commit the recored, no other information (name and date) of the party automatically appeared in the portal.

            Likewise, I went to the donorparty layout and indeed a new record had been created, but it only filled in the idnumber of the donor and the partynumber he selected. The firstname, lastname, partyname, partydate fields were all blank. When I went to the Parties layout and went to the record for party1, in that portal the idnumber for the donor was entered but not his firsname or lastname.

            Firstly, could you tell me if I understood your instructions and have set this up properly. Secondly, how do I get the other information to automatically appear in the firstname; lastname; partyname; partydate fields when a new record is created in the join field and hence will also hopefully display in the portals.

            Any instructions you could give to help me fine tune this relationship would again be immensely appreciated. Milo

            • 3. Re: Adding event tracking to an existing database

              You're doing great so far. That sounds all correct for the relationships. You may want to also turn on [x] Delete related records, also on the DonorParties join table side (important). So if you delete a Donor or a Party, all its join records will be deleted also. Though likely you won't be deleting either anyway.

              Your next step, showing OR looking up the name, etc., data enters a gray area of database design. The simplest method is to NOT lookup the data. Because you do not need to bring it into the join table. It can be easily seen simply by pointing the fields in the ORIGINAL table from the join table. You have the relationship and you have the ID; that's all you need.

              So, rather than defining those other fields in the join table, just point back to the Party table for party name, and to the Donor table for the donor name. 

              However, there are sometimes reasons to actually bring the data into the join table; where it is known as "redundant" data. If for example you want to Sort the portals alphabetically by name (donors in a party for example; not so much parting names tho, as you'd likely just leave them in creation order), then you may want First Name and Last Name in the join table. (It is possible to sort by name even if they're not in the join table, but slower. People's names are one thing you just tend to Sort often.)

              So, to actually get the names IN the join table, the easiest method is to have those fields in the join table, and change their Field definition. Use the Options, Auto-enter, [x] Looked-up value. Chose the appropriate relationship to the original table, and the field. A Lookup will occur when you set or change the ID.

              You can Relookup all values depending on a key field in existing data in the found set by putting your cursor in the ID field, and choosing the Records/Relookup command. It will overwrite existing values, but is fairly safe (if you've never changed any, which you wouldn't in this case).

              One downside of "redundant" data is that may require "maintenance". If someone changes their name, for example (call me "Richard", not "Dick"; or gets married/divorced), you might have to go to their records in the join table, and Relookup their name (after changing it in Donors first). But that's likely rare and not terribly important for parties :-]

              • 4. Re: Adding event tracking to an existing database

                Dear Fenton Jones,

                Thank you again. You have totally answered my questions and have my utmost thanks. I have used the method of pointing to the fields in the original tables to fill in the other data fields in both portals and it calls in the appropriate firstname; lastname; partyname and partydate. I have called these into the DonorParties layout as well just to have a visual clue there when looking at it. I am able to make reports with just the information I want by pointing to the proper fields.

                My original database already had the Donors table (along with many other tables and relationships for such things as donations, pledges, non-cash donations, etc.). I added a Parties table and a DonorParties table and set everything up just as in my test database.

                I then went to a donor's record and in the new portal I made for parties, I clicked the party number field, got a drop down menu for all the parties, selected one and it worked just fine.

                Howevever, entering the parties from the a drop down list of partyid numbers such as: Party-1, Party-2, Party-3 is confusing unless you have a list of all 50 or so parties to know which is which. So I deleted the orginal portal and broght in a new one without the partyid number, instead having only the partyname and partydate field. I made the partyname field a drop-down list. However, I found that if you do not use the party idnumber (the key link field) to select the party in the portal, it does not make the proper link to the party table. After some experimenting, my work around was to make the "partyname" field the key field and link the DonorParties table to the Parties table using that "natural" field. It worked just fine. For what I am doing here, I think using a name as a key field will be ok even though it is not recommended.

                Let me know if  I have assessed the situation right or if I can actually link the two tables with the partyid field but use the name field as my drop-down selection. Thanks. Milo

                • 5. Re: Adding event tracking to an existing database

                  That is precisely what the "show values from a 2nd field" option is for, in a Value List definition. Use the Party::Party ID field as the 1st field, then use the further option, "Show only values from the 2nd field," Parties::Name field. Best to put on the layout as a Popup menu rather than a Drop-down. That way only the Party names show, but the field is the local DonorParties::PartyID (foreign key).

                  P.S. When you use the above method, "Show only values from the 2nd field," you CANNOT have exact duplicates in that 2nd field. Because only 1 will ever show/work. Best to Validate the Party Name field to be [x] Unique; force people to put something/anything to tell them apart.

                  • 6. Re: Adding event tracking to an existing database

                    Dear Fenton Jones,

                    That's great. You have provided a complete solution and have my heartfelt thanks. Milo