Richard

I feel your pain.

You might be able to fake an export into flat file by doing something like this, where "this" is not = "flee".

#if you had a maximum related records value for each table you could write dummy data into variables to ensure that you would get the data aligned (Amount15 always after the 234th ∞ sign) for later use - $Table01Max, Table02Max, Table03Max, Table04Max... 
#locate a start table - eg, contacts - 
#go to first record
#Loop
	#define collector variables - perhaps one for each related table - $Table01, $Table02, $Table03 etc.
	#query each related table for data - get a related records count to use as a LoopStopCondition
	#write into appropriate variable and delimit it with some "never to occur character" - something from the high ASCII perhaps - I'll use ™ and ∞ and others in my example
	#You might even write the field name as well - eg.  ∞id#™08080∞Date™2/2/2010∞Amount™1125∞Notes™Never do this again∞id#™08080∞Date™3/28/2010∞Amount™555∞Notes™Send Cheese∞
	#repeat for each related table
	#join these collector variables into a large variable ($Table01&"∑"&$Table02&"œ"&$Table03)and then set that variable into a field in the "AaarghFlatFile" table (link by record id or something
	#reset all your variables = $$Null (which you never define)
	#GoToNextRecord (exit after last
#EndLoop

now you'll end up with a "flat table" of one field, many records filled with unusual delimiters.

Then perhaps some clever Substitute() to put in Char(9)s (append to your ∞ delimiters - not first and last) and then export the single field/many records as TAB separated text.
Then you convert this to an FMP database.  If your data has been suitably aligned you'll get the data in consistent fields - easier to validate.
Now you'll have clues as to how to rename F1, f2, F3...F∞ into useful Names -Amount1, Date1, Notes1, Amount2, Date2, Notes2...
Then you do another Substitute() frenzy to remove your ASCII markers, field names etc.
Then export as Excel (you will need to monitor total expected fields to the max in Excel - 16384 in '07 and '08) with first row field names, or as Merge CSV to get the field names.

I will have almost certainly made some errors in thinking this out but I may have done something approximately similar, or heard about someone doing it, or maybe just dreamed it.
It is clearly easier to visualize then to do, but often more helpful as well...
But the Char(9) export as Tab and convert back to FMPro I have done.

Present results to client and flee the country if need be...

Stephen

----------

"No sensible decision can be made any longer without taking into account not only the world as it is, but the world as it will be. " --- Isaac Asimov

On Apr 19, 2011, at 2:23 PM, Richard S. Russell wrote:

> A client asked me for advice on how to export data from a modest-sized file of <20,000 people. As we discussed what he wanted and why, he said he was looking for something he could upload to an on-line contact management system (CMS) that would replace his current FMP system. I thot this was kind of sad but doable. Then he got to the ugly bit. He's looking for flat-file output, because the people he'll have working on data entry in the future are too unsophisticated and ephemeral to deal with the complexities of relationality.
> 
> Let me spare you the effort you're about to invest in telling me what a terrible, terrible idea this is. I doubt that you could come up with anything I haven't already tried on him, to no avail. It's going to happen, with or without my help.
> 
> I explained that FileMaker Pro was kind of like a lobster trap. It's easy to get in (IE, move from a flat-file system to a relational one), because FMP has all sorts of tools to make it happen; but it's hard to get out (IE, move from relational to flat-file). I was kind of stumped right off the top of my head trying to figure out how I'd convert a portal of, say, 20 financial contributions, each with 8 fields (like amount, date, purpose, etc.) to 160 spreadsheet-style columns.
> 
> Has anyone else faced this kind of situation? Any advice (beyond the obvious "Don't do it!")?
> 
> 
> = = = = = =
> Richard S. Russell, a Bright (http://the-brights.net)
> 2642 Kendall Av. #2, Madison  WI  53705-3736
> 608+233-5640 • [log in to unmask]
> http://richardsrussell.livejournal.com/
> 
> = = = = = =
> No sense in being pessimistic. It won't work anyway.