Jump to content

Need help with using a related table in a calculated field


This topic is 5720 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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 by Guest
syntax
Link to comment
Share on other sites

This topic is 5720 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.