Multiple MySQL records into a single FM record

[jonesy17]

Searching fmforums for this yielded hundreds of potential posts but after the first hundred my eyes started to glaze over faster than a Krispy Kreme doughnut and I wasn't getting anywhere, so...

I have a MySQL db (that I did not setup) which contains multiple records for a single customer, with the customer's email address as the primary field. I am using ODBC to import the records from the MySQL db into FM Pro Unlimited under Win2K.

In the MySQL db, the data looks like this:

MEMBER_ID FUNCTION ACTIVE

[email protected] upload Y

[email protected] download Y

[email protected] delete N

I would like my FM db to contain a single record for [email protected] but with repeating fields for FUNCTION and ACTIVE.

Is this possible, and how the heck would I do it? It doesn't sound like it should be too hard, so something simple must be eluding me. Any help is appreciated!

[Fenton]

First, you should carefully consider whether to use repeating fields. For one thing, you have to know the maximum repeats possible. For another, it's difficult to work with data in them.

You could keep your current records as a "line items" file, with an "Email" file having just the email. In any case, you can use the method below to get the unique emails out.

Open the data file with FileMaker (hopefully it's in a format FileMaker can deal with, comma or tab delimited; I know little of SQL obviously).

Create a summary field. It can summarize any field, it doesn't matter what. But FileMaker demands at least 1 for the next step.

Sort by the email address. Export as a summarized FileMaker file.

Under the fields to export is a "Summarize by" button. That opens another window. The email address field will be there. Click just to the left of it; you will then see a checkmark. Don't miss this step!

Click "OK." It will now say, "Summarize by" and "email address"

It will export only 1 record for each unique email address, for an Email file.

I would keep the "line items" file with the Function and Active fields as it is. Link to it using the email address as the key in each file, from the Email file.

If you really must have the repeating fields, the Email file is where they'd be. You will have to create a loop in the line items file, that sorts by email, then loops through each person, setting the repeating fields in the Email file.

It's doable, but I'd rather not write it until you confirm that is what you need.

[Fenton]

Well, I found a script I already had, doing something similar. It's not really that difficult. Mine is setting multiple emails to a Contact file email repeating field. Yours is setting multiple attributes of an email to an Email file.

Similar, but not the same. You would replace ContactID with EmailAddress. And instead of "email," you'd be setting Function and Active. (Confusing, but this is what I had.)

#Transfer emails to contact db

Go to Layout [