Repeating Field Helps?

[Meetloaf]

I am wondering if anyone knows of a good resource, where I can figure out how to use repeating fields. I think they will help my solution, so I would like to try them out.

If this is the wrong place for this post, let me know, and I will repost it elsewhere.

Perhaps this will help you give me direction, it may be confusing, but here it goes:

I am creating a database that handles Scholarship applications, awardings, and sending of letters to recipients.

Here are my tables:

Scholarship Donors------Awards>---Applications>---Applicants

PKs

Scholarship Donors = Account #

Award Allocations = Account #/Award ID

Awards = Award ID

Application = Application ID

Applicants = Student#

Congrats Letters = Letter #

OK, I have one more table that is for Congratulations letters that relates to the Awards Table.

So, each applicant can have more than one application (from year to year). Each applications can only have one award (though, filemaker defines this as a 1:N for some reason). Each Award can have many Award Allocations (b/c each awarded can be funded by many different scholarship accounts). Each award allocation can only be for one award and one scholarship donor account.

In the case the someone's Scholarship is funded with many scholarship accounts (donors), we want to make sure the letter references each donor within the letter. (I plan to accomplish this through a merge field) So, my plan is to make a repeating field in the Awards Table that houses the Scholarship Account #'s for each Award Allocation that correspond with any given Award ID (which may be 1-4 scholarship account #'s). I would then merge the Donor Names, via the scholarship account #'s in the repeating field into my form letter.

So, if I can get the repeating field to work, I think I can accomplish this. If I am wrong, or you know of a simpler/better way to do this, I am very open to suggestions.

Thanks!

Edited November 17, 2006 by Guest

[Meetloaf]

Ok, so it appears my problem was ill-described, or it's just a good problem.

Here's an easier help.

How does a portal retrieve information? Is there a way to retrieve the text from a certain field for each record in a portal. e.g. If an invoice has many parts, how do you retrieve the "Description Field" for each item included in the order?

Thanks!

[Tim W]

Hi,

Repeating fields are problematic for storing data.

So, my plan is to make a repeating field in the Awards Table that houses the Scholarship Account #'s for each Award Allocation that correspond with any given Award ID (which may be 1-4 scholarship account #'s)

This can be pulled through the relationship Awards =>JoinTable => Donations. A portal is populated by the matching data in a relationship.

In relationship graph connect awards to donations via a join table with the fields (SerialNo, AwardID, DonationID). The sole purpose of this table is as the name suggests to Join two tables together. This allows for various combinations. The serial is simply a sequential record id.

In the relationship graph connect as follows:

AwardsTable:AwardID = JoinTable:AwardID

and

DonationTable:DonationID = JoinTable:DonationID

Base the portal on the JoinTable & place the Account# field, description field from the Donations table in the portal row.

That should do it.

Tim

[Meetloaf]

I could be wrong, but I think I have that structure built in already, where your "Donation" table is my "Scholarship Donations" table and the "Join Table" is the "Award Allocations" table. I have the portal in my Awards table that lists each allocation (Serial No., Award ID, Scholarship Account #, and Amount). What I want to be able to do is obtain the account # for from each record so that I can merge them into a form letter. Any given award may be funded by 1-5 accounts, so it need to be dynamic, and hence I need to be able to properly format it accordingly (commas, spaces, etc.)

If you know how to accomplish this, I would be elated.

Thanks!

[comment]

See if these help:

http://www.fmforums.com/forum/showtopic.php?tid/177351/

http://www.fmforums.com/forum/showtopic.php?tid/181174/

[Meetloaf]

Thanks Comment,

GetNthRecord is EXACTLY what I needed. Way to save the day!