Need help with using a related table in a calculated field

[nutso]

You have to forgive me, but I'm not very good with filemaker. I have 2 tables in my database. One has customer information, and the other has phone numbers. I have it set up so that the company table is related to the phone table by a unique customer ID. It's set up that way so companies can have multiple phone numbers and not have just 2 static phone/fax fields in the customer table. Here's what I mean:

Customer table:

custno custname

1 "Customer 1"

2 "Customer 2"

Phone Table:

PhNo custno number type

1 1 555-1212 "phone"

2 1 555-2121 "fax"

3 1 555-3232 "pager"

4 2 555-2323 "cell"

My question is, how can I create a calculated field in the company table that can merge the first 2 phone records into 1 field. Somthing like this:

Modified Customer table:

custno custname combined

1 "Customer 1" "555-1212, 555-2121"

2 "Customer 2" "555-3232, (blank)"

What functions if any can pull the two related records into one field? And what would the final calculation look like?

I hope that makes sense, Thanks for your help.

[Genx]

Hmmm, enter GetNthRecord function

The calculation would reside in your Customer Table. We'll call our field cTwoNumbers.

Now, the GetNthRecord function is a function that is new to filemaker 8. Normally when you call a related field through a calculation, you will retrieve only the first record. GetNthRecord(RelatedField;RelatedRecordNumber) however can retrieve the field contents of any particular related record number.

Assuming your table accurance names are the same as your table names, your calculation could take the form of the following:

Case(

Count(Phone Table::custNo)= 0 ; "(blank), (blank))";

Count(Phone Table::custNo)= 1 ; Phone Table::Number & ", " & "(blank)" ;

Phone Table::Number & ", " & GetNthRecord(Phone Table::Number ; 2)

)

you could also use ifempty checks instead of counts but ... meh.

~Genx

Edited March 30, 2006 by Guest
syntax

[nutso]

Thanks Genx!!

Just tried it. It works great. It's fortunate for me that this database is starting it's life in FM8. All of our old databases are still in FM6.