SeedCode Support Forum

I have an Excel file with AccountID and Services. The AccountID is unique. Each AccountID has from 3 to 20 services. In the original Excel file the services are listed as rows not columns. When imported you have multiple records with the same AccountID but different Services. I need one record per AccountID with all services listed as Service1, Service2, etc.

Working in FM 8.5 or 9

thanks,

Jim

It sounds like what you've created from your import is the Child Table where each service is its own record. Creating a new table with one record for each unique AccountID would create the Parent Table. This structure is *much* more versatile than having just the parent table where services are fields like Service 1, Service 2, etc.

If you're intent on having services in their own fields, the easiest way to do this is to

a) make your patent table by sorting the one you have by AccountID and then exporting grouping by account ID. This will make a new FileMaker file with one record for each AccountID.

b) make a temporary relationship between this new file and the one you have now based on AccountID. If you want the services coming into your fields in alphabetical order, sort this relationship by the service field.

c) then create the Service 1, Service 2, etc. fields in the new file.

d) Put your cursor in Service 1 and use the "Replace Field Contents" command to make Service 1 equal to GetNthRecord ( YourTempRelationship::ServiceNameField ; 1 )

e) Continue this for each Service field in the new file. For example, the replace into Service 2 would be GetNthRecord ( YourTempRelationship::ServiceNameField ; 2 )

f) Continue through each of the 20 odd service fields you created.

Now if you just want the services listed in one text field in the new table, that text field would get replaced as List ( YourTempRelationship::ServiceNameField )

See how versatile the parent-child structure is? =)

John,

Worked great. Now using on another project.

thanks,

Jim