3 Replies Latest reply on Aug 5, 2011 8:51 AM by philmodjunk

    lookup from multiple sources(tables)

    WilliamChang

      Title

      lookup from multiple sources(tables)

      Post

      hi,

      I'm working on a donor/donations tracking application for my son's K-5 school and could use some advice.

      I have a Donors table that holds donor records including address information (address, city/state/zip).

      There are also a Parents table that holds records of parents of students at the school including address information and a Families table that holds records of families including address info.

      The Donors table has foreign keys ParentID, and FamilyID pointing to the Parents and Families table respectively.

      In the Donors layout, there are the fields FamilyID and ParentID; and either or both can be populated.

      The behavior that I want is,

      - if the user populates the FamilyID from a drop-down menu list, the address data from the Family record will be copied over to the address fields on the Donor layout that are associated to the Donor table.

      - if the user populates the ParentID from a drop-down menu list, the address data from the Parent record will be copied over to the address fields on the Donor layout that are associated to the Donor table.

      - if the user populates the FamilyID field first, then populates the ParentID field, the address data from the Parent record will be overwrite the address fields on the Donor layout that are associated to the Donor table.

      Is this behavior possible to code? I can't use a Lookup as my understanding is that you can only associate fields/columns on a given table to corresponding fields/columns on one other table.

      I'm open to putting buttons on the layout that would effectively be "copy family address" and "copy parent address" using the FK in the FamilyID/ParentID fields but I'm not sure how to write that scripts.

      I'm new to filemaker pro, but I'm quite familiar with database applications (specifically Oracle). I would greatly appreciate any advice anyone can offer me.

      Sincerely, William

      donorapp.jpg

        • 1. Re: lookup from multiple sources(tables)
          philmodjunk

          You could use an auto-entered calculation with a case or if function to control which table serves as the source for the contact information, but it would make more sense to me to put all your contact information in a single table rather than in a parents and a families table. Then you no longer need such a complex set up nor would you have the very likely possibility of entering duplicate contact information for the same person in more than one table.

          • 2. Re: lookup from multiple sources(tables)
            WilliamChang

            When I initially posted this question, I knew I would be asked why addresses are being stored in 3 different places. Since this isn't specifically relevant to the question of how to copy data from one table to another via a foreign key I didn't include this explanation in my initial post. I see now this will be a distraction so I'll explain now.

            The workflow of this application is that first a family record is created with the address information. Then parent records are created with a foreign key to the family record. At this time the address in the family record is copied into the layout for the parent record via a lookup. However in the instance where the two parents of a given student live in different addresses, the address information that is copied over from the family record can be overwritten. This is why both the Family and the Parent table hold address information.

            In the case of Donors, this was created as a separate entity because donations can come from a family, a parent (e.g. when divorced parents of a student make donations individually), or from a non-family entity (e.g. Business, staff or faculty, related family member. Consequently I need to be able to store addresses at the donor record for the purposes of generating thank you letters at a later time.

            i'm coming to the conclusion that the best solution for this problem is a script that is launched by a button on the layout. a Copy Family Address or Copy Parent Address button. my question now is what function do I use? what is the difference between Set Field and Set Field by Name? How do I set Donor:Address = Family:Address where Donor:FamilyID=Family:FamilyID?

            Thanks for your time and assistance.
            Sincerely, William 

            • 3. Re: lookup from multiple sources(tables)
              philmodjunk

              The workflow of this application is that first a family record is  created with the address information. Then parent records are created  with a foreign key to the family record. At this time the address in the  family record is copied into the layout for the parent record via a  lookup. However in the instance where the two parents of a given student  live in different addresses, the address information that is copied  over from the family record can be overwritten. This is why both the  Family and the Parent table hold address information.

              I'm working with the same situation in my databases. In my project, if the parents have different addresses, that makes two family records linked to one child and I still have my addresses stored in a single table instead of multiple tables. A join table facilitates the potential many to many relationship. A field in the child's record identifies the custodial parent in cases where this info is relevant and useful.

              Donor contacts can also be stored in the same table. A field in this table can store data to label records that are from non-student family contacts and a donations table can track all donations with a link back to this unified "contacts" table.

              It's best not to copy data like this from one table to another unless you need to preserve a snap shot of what data was current at the time a donation was made. Copying/storing such data in multiple tables makes updating contact information a real nightmare that can be easily and fully avoided if you do not copy data into multiple locations. Instead, layouts where you need to display this related contact data can use fields directly from the related table--again something that is far easier to set up if the contact data is all stored in a unified table.