Getting data from a related table via a calculation field

[kcep]

Merry Christmas Everyone,

We read the Christmas story ... the kids are finished opening presents ... my wife made a wonderful Christmas dinner (Duck). So, I thought I would tackle a database problem ... Weird, I know.

I need to export records from an address book database. However, the phone numbers are on a separate table. (see my attached example). This makes exporting a challenge for me. I thought one solution could be to have four calculation fields in the main address book table. (cHome, cCell, cOffice, cFax) ... These fields would get the information from the related Phone Numbers Table.

I'm having trouble defining these potential calculation fields. So far my try at defining these calculation fields only yields the first row of the the portal as a result ... I'm doing something wrong.

AddressBook.fp7.zip

[comment]

Why don't you export from the phone numbers table?

[kcep]

I tried that and ended up with about 3 times more records than I needed.

[comment]

It depends on what you define as needed. Since the number of phones per person is theoretically unlimited, they cannot be forced into a fixed number of columns. That's the entire purpose of keeping them in a separate table - so that one person can have 5 cell phones, while another has none.

What you're trying to do contradicts the very principle of the method you have adopted. You could just as well create a field for each type in the main table and delete the related table. Perhaps you should explain what's the purpose of this export, and how do you expect it to look - esp. with people with multiple phones of the same type.

[kcep]

I'm trying to import a FileMaker database address book (that happens to have the phone numbers in a separate table) into a software application called Address Book. This application is standard on the Mac OS X operating system. This Mac OS X Address Book application synchs well with mobile devices and is very useful in other areas.

[comment]

You haven't answered my question. Suppose there are two people records: Adam has two work phones and one home phone. Betty has one work phone and two home phones. To import this information into Address Book via a text file, you will need to create 4 columns for phones (2 x "work phone" + 2 x "home phone"). This is very clunky, and as soon as you add Cecil with three work phones, your entire structure will need to be reworked.

I think a better method would be to export to a vCard format, using XML and a stylesheet.

Alternatively, you could use Applescript to transfer the records to Address Book. Both methods have been discussed numerous times - do a search for "Address Book" and/or "vCard" in the Import/Export, XML/XSL and Applescript sections.

[kcep]

Yes, you are correct ... someone could have multiple phone numbers that are the same type of phone number.

Thanks for your help. Exporting address book records when the phone numbers are in a separate table sounds like it's more challenging than I expected. I don't see how I can export from the Phone Numbers Table ... If John Doe has three phone numbers, then exporting from the Phone Numbers Table will make three records for John Doe with one phone number on each record. What I need is one record for John Doe with three phone numbers on that one record.

I guess I don't see how exporting via Apple Script to Address Book will solve my problem. I don't see how I can export via any exporting format with the phone numbers in a separate table.

Thanks again for your help

[comment]

The problem is you're trying to do this via a flat-table file. Using XML/XSL, you could massage your export into a vCard format which is much more flexible, so multiple phones could look something like:

BEGIN:VCARD

VERSION:2.1

N:Doe;John;;;

TEL;HOME:555-5555

TEL;HOME:555-5556

TEL;CELL:888-8888

END:VCARD

This is a format that Address Book understands natively, and will import correctly.

With Applescript, you wouldn't even need to export at all - it could go over your FMP records and re-create them in Address Book.

However, both methods require a lot more investment in time and effort than a simple export. A lot depends on whether this is a one-time migration or something that you intend to use periodically. If the former, you could possibly cut some corners to hack the data into shape.

[kcep]

GREAT !

Thanks for explaining !

I really appreciate it. -)