What are you trying to achieve with the export?
Joe Green, Coca Cola, 21 Main Street, 01234 567 89, 987 541 632, 456 789 123
Jane Doe, Google , 1 High Street, 987 654 321, 874 596 321, 874 741 741
Joe Green, Coca Cola, 21 Main Street, 01234 567 89
Joe Green, Coca Cola, 21 Main Street, 987 541 632
Joe Green, Coca Cola, 21 Main Street, 456 789 123
Jane Doe, Google , 1 High Street, 987 654 321
Jane Doe, Google , 1 High Street, 874 596 321
Jane Doe, Google , 1 High Street, 874 741 741
Option one preferred, in defined order, eg:
Jane Green, WFO, work number, mobile number, personal number, office, email, skype
option 2 also possible, but the numbers have to land in defined fields so
jane green, wfo, ,123 456 7890, , , , ,
jane green, wfo, , , , , jane@gmail, ,
I put this aside for a bit, but have time now to re-think. Is there no further response to the above?
Option 1 should be easy if you run the export from Table B, and choose the fields you want in the order you want.
Can you please expand on that? At the moment an export from Table B looks something like this:
joe green, company name, address, home, xxx-xxx-xxxx
joe green, company name, address, work, yyy-yyy-yyyy
joe green, company name, address, work, firstname.lastname@example.org
joe green, company name, address, asst, email@example.com
My goal is more like:
Using another table, I think I can get to something like this:
joe green, company name, address, home, xxx-xxx-xxxx, work, other, , work, , asst, ,
joe green, company name, address, home, , work, yyy-yyy-yyyy, other, , pers, ,asst , ,
joe green, company name, address, home, , work, , other, , work, firstname.lastname@example.org, pers, ,asst, ,
joe green, company name, address, home, , work, , other, , work, pers, ,asst, email@example.com,
but am not clear on a way to induce all into a single record for Table A item?
I apologise: what you are seeing by exporting from Table B was exactly what I expected, but I can see that it was not what you want.
You can export from Table A and so only have one record per person, and it can contain the contents of several related records. For example, if you use the List function and the Substitute function, you could generate a comma-separated list for:
Table B related Records
Joe Green Home Address
Joe Green Company Address
Joe Green e-mail work
Joe Green e-mail personals
Joe Green, Home Address, Company Address, E-mail Work, E-mail Personal
Jim Brown... etc
The related records are in no particular order (necessarily) so their field sequence would be unpredictable. But you could get around that by sorting the relationship, so they will at least always run from Address to Zipcode.
But I can't yet think of any way to get around the fact that some parent records will have 3 related records and some will have 5. You would need to create the concept of 'place-holders' for the missing two. And of course the benefit of using a related table and not fixed fields is that you have the ability to add as many contact records as you want, so it would involve a re-design when you add another category.
But then the reason you are almost removing the benefit of such flexibility is that the destination application does not work that way - you have defined them as flat files, with all that associated data in one record. In that case you could create the flat file in Filemaker. In Table A define as many fields as there are possible contact types (in your destination application). For example, if it has target fields for Address Work, Address Home, Spouse, and Birthday, then create those as distinct fields in Table A. (They will be the same as the Value List that you choose from.) Also create calculation fields with fixed text in them, equal to your Value list values.
Then make each of those fields as a related field (or a lookup) based on its own unique relationship to Table B. For example:
TableA::PersonID = TableB::PersonID
TableA::TextOfAddressWork = TableB::ContactType
...would pick up the Work Address for the person if the text field TableA::TextOfAddressWork was "Work address" and that was the contact record type.
It will only pick up one contact of each type, but then is that what your destination application expects.
It seems counter-intuitive to use FM in that way, but you are of course accommodating the limiting migration to a flat-file system. You can then export those fields from Table A and it will always be in the correct order, including spaces.
Thanks for the expanded thoughts. I have accepted that I'll have to create a related "flat" record to use for export only. I had tried calcualting fields in A2 but had not figured out how to create the unique relationships and pull in all related data from Table B. Thanks for pointing the way on that!