3 Replies Latest reply on Jan 20, 2012 9:06 AM by philmodjunk

    Relationships on reports question



      Relationships on reports question


      I have three tables: Mammals, Birds and Donors.  I have a field in each table for "donation" and an additional field in the donor table for "other donations" (non-monetary).  The relationship I have to each table is "last name" and "donation".  I want a report for Donations and have the fields Last Name and Donation in the report as well as some others.  The report is giving me some information correctly from the other tables but for other donations in those tables, it appears to be adding donations as a total for different people to one Last Name.  The other Last Names don't show up.  Still quite new to FM.  What am I doing wrong?  Any help would be greatly appreciated.  My representation is in Excel since I'm at work and can't access the file.

      Thank you!



        • 1. Re: Relationships on reports question

          Matching Records by name in relationships is almost never a good idea. Names are not unique and people change there names--both issues will cause problems for your database. Usually, you set up serial number fields to uniquely identify records in each table and you use them in your relationships. (Example: What will you do if two donors named John Smith both "adopt" the Great Horned Owl by donating $100?)

          I'm not clear in what your mammals and birds tables have to do with a given donation. Can you explain?

          From what I see and guess at this point, I'd use something like this for the tables in this system:

          Donors----<Donations>-----Animals(birds and mammals in one table)

          Donors::__pk_DonorID = Donations::_fk_DonorID
          Donations::_fk_AnimalID = Animals::__pk_AnimalID

          This allows a single donor to make multiple donation records. If a given donation should need to be linked to a given animal, it's ID is selected from a drop down to link the donation to that specific animal.

          This allows you to set up a Donor report on a layout based on Donations that can list every donation, every associated animal, all grouped by the donor making the donation.

          • 2. Re: Relationships on reports question


            The reason for the two tables, Mammals and Birds, is one of expediency.  There are numerous choices for both and the value lists for other fields I have not included in my examples here are species specific.  The Feds and the state require separate reporting too.  The Donor table is because someone may not bring in an animal or bird but may donate money or other supply items.  A field for the other supply type items is not included in the mammal or bird tables, only the Donor table.  I do have a "record" field that is autoentry in both the Mammal and Bird tables.  Would that work better for the relationship instead of the name?

            Embarrassed to ask but still too new to understand your example.  What does the _pk_ and _fk_ mean?


            • 3. Re: Relationships on reports question

              There are numerous choices for both and the value lists for other fields I have not included in my examples here are species specific.  The Feds and the state require separate reporting too.

              None of this requires separate tables and the extra table complicates your design and relationships. Value lists can be set up as conditional value lists that list different values for different animal types. Report layouts with finds to pull up records for specific animal types can give you your separate reporting, while still allowing you to also produce combined reports as well. If necessary, you can add mammal and bird tables as table related to the animal table for fields that are specific to that animal type.

              pk and fk are a naming convention you can use for your fields if you want. pk means primary key. In FileMaker, primary keys are almost always defined as auto-entered serial numbers. fk means foreign key. Primary keys uniquely identify a specific record in your table. Foreign keys identify the "parent" record to which your current record is related.

              The single underscore for fk's and double underscore for pk's is a trick that sorts these important fields to the top of lists when they are sorted by field name in ascending order. Just keep in mind that this is an option you can use or not as you choose. The name of a field does not change it's function. You control that through the field options and formats you specify for it in your design of the database.

              However you structure your animal table(s), It's important to distinguish between a record for the donor and a record for their donation--whether it be animal, supplies or dollars. A given donor many donate to your organization more than once. If that should happen, it's better not to enter the same donor information a second time--rather create a new donation record to link to the existing donor record.