Lookup from multiple sources

[williamhchang]

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" 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

[comment]

Wouldn't it be simpler to put all your contacts in a single table?

Is this behavior possible to code? I can't use a Lookup

It's possible, if you use an auto-entered calculation instead of lookup proper. But why would you want to copy the address, when you can calculate it? With a lookup, if a parent or a family change their address, you will have to do the update twice.

[williamhchang]

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

[comment]

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.

I'm not sure what leads you to this conclusion. As I said, you can perform a pseudo-lookup by auto-entering a calculated value. The calculation would be something like:

Case (

not IsEmpty ( ParentID ) ; Parents::Address ;

not IsEmpty ( FamilyID ) ; Families::Address

)

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 don't see why. The address is available at all times from the related record.

P.S. After reading your explanation, I still think you should have a single table for all your contacts. Families could be an exception - but this raises the question: is it possible for a family to have an address that is NOT the address of one of the family members? If not, there shouldn't be a stored address at the family record either.