Merging Rows? FileMaker Import

[Ajani]

I’m having some trouble migrating from FileMaker Pro to Airtable…

I am able to export and import 99% of my data/contacts with out issue – mapping, organizing, etc. The only issue I’m running into is having the multiple mailing lists these contacts are attached to map to a single row.

For example: John Doe (on FMP) is on mailing lists A, B, and C. When I import to Airtable, it lists John Doe as being on mailing list A, but then lists an “unnamed record” as being on list B, and another one on list C. It ends up being that all of the proper contacts are followed by unnamed rows with the remainder of their lists attached. I hope this is making sense…

Is there an automation or formula I can set up to merge these into the above (correct) record? Or any tips for making my FMP output group these together, instead of having them as separate records?

[ScottWorld]

Welcome to the community, @Ajani!

As far as I know, I am the world’s only expert at both FileMaker and Airtable. You can learn more about me at my website: ScottWorld — Airtable & FileMaker Consultant

I don’t know what type of field your mailing list field was back in FileMaker, but on the Airtable side, it sounds to me like you are importing the mailing list field into a linked record field in Airtable?

That’s actually a great way to do it, but your mailing list field will need to be separated by commas ahead of time, not carriage returns. Depending on the field type where it originated in FileMaker, FileMaker separates values by carriage returns instead of commas.

So you will need to fix the data ahead of time to be commas instead of carriage returns. You can create a calculation field in FileMaker to substitute the carriage returns with commas. Or you can do a find & replace in a spreadsheet app like Excel.

[Ajani]

Glad I’m on the right track! Is there an easy calculation to replace a return with a comma?

[ScottWorld]

Yes, you would just use a normal substitute calculation in FileMaker:

Substitute ( Field Name; "¶"; "," )