williamhchang

hi, I apologize if this has been asked and answered before, I haven't been able to find a solution. I need to concatenated multiple related records into a single text calculation field on the parent table/layout seperated with some delimiter I have a FAMILY table with a primary key FamilyID that joins to the CHILDREN via FamilyID Given a record in the FAMILY table and 3 related records in the CHILDREN table,the calculation field should have the value: Thomas;Matthew;Kathryn Is there a function that I can call to lookup all related records? thanks in advance for your assistance. Sincerely, William

you were right. reversing the orders of the functions didn't work. what did work was to create another occurrence of the Donations table and join that back to the Donors table with both DonorID and DonationSchoolYear. with that in place I can just use a straight Sum() function on the DonationAmount in the new occurrence of the Donations table.

what consultant stated above about the the behavior of the If () function made sense so I tried reversing the functions to: Sum(If ( Donations::Donation School Year = Current School Year - 1; Donations::Amount;0)) now the behavior is that if there is only one record, the Previous School Year Sum field displays the correct value, but when there is more than on related donation records, the field displays a "?" thank you in advance for your assistance. Sincerely, William

I'm working on a donor/donation tracking application for a K-5 school PTA. I have a donor table and a donation table with a primary key DonorID that is a foreign key to the Donations table (one to many) In the Donations table, I have the fields DonationDate, DonationAmount, and a calculation filed DonationSchoolYear that is defined as DonationSchoolYear = Year ( Donation Date )-(Month ( Donation Date ) <7) In the Donors layout, I have a portal field that displays the related Donations records for the current Donor record. Below the Donations portal, I have 3 calculation fields, CurrentSchoolYearSum, PreviousSchoolYearSum, and TotalDonationSum. The TotalDonationSum is a straight Sum of Donations:DonationAmount and appears to work fine. The PreviousSchoolYearSum is supposed to be a sum of all donation amounts from the previous school year (August to July) PreviousSchoolYearSum=If ( Donations::Donation School Year = (Current School Year - 1); Sum ( Donations::Amount ) ; 0 ) where CurrentSchoolYear=Year ( Get ( CurrentDate ) )-(Month ( Get ( CurrentDate ) ) <7) The CurrentSchoolYearSum=If ( Donations::Donation School Year = Current School Year; Sum ( Donations::Amount ) ; 0 ) The problem that I'm encountering is that is there is more than one related Donations record for a given Donor record, the sums are returning 0 (zero). see first screenshot. Where are is only one related Donations record the calculations fields work correctly. Am I using IF() incorrectly? Thanks in advance for your assistance. William

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

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