field limit on layout

[ted choi]

edited to fix some errors in my description

i'm using filemaker 8.0 on PC and 8.5 on mac os x. i imported a tab delimited file with 1300 rows and 12500 tabs. each field contains a 10 digit decimal number. i want to use the db to find subsets of the 1300 records from queries and then export the found set of records. "define db" shows 12500 fields as expected but the default layout only shows the first ~460 fields. if i build a layout from scratch, the 'move fields' dialog shows all 12500 fields and moves them but the layout still only shows ~460 fields, and only those ~460 fields will export. how do i get all the fields to display on a layout?

also, if i export to excel i can get the field names as the first row (as a header row) but i don't get this dialog for tab delimited or csv text output. i need to export as text file due to excel dimension limitations. how can i get field names exported as a row?

finally, when i export as tab or csv text file, the records are rows and the fields are columns. is there a way to transpose the export so that the fields are rows and records are columns?

thanks very much.

ted

Edited August 23, 2007 by Guest

[bcooney]

how do i get all the fields to display on a layout?

Are you in table view? I believe it has an upper limit. Try list view or form view.

i need to export as text file due to excel dimension limitations. how can i get field names exported as a row?

Merge file on export.

transpose the export

Try excel's transpose in the Paste Special dialog.

[ted choi]

thanks for the reply.

i'm in listview. i've attached a test file so you can try it yourself. it's a tab delimited text file of 1500 columns and 3 rows. just sequential numbers. when i import into fm 8.5, 'define database' shows 1500 fields imported but only 442 of the 1500 fields shows up on a layout. if i make new layout, the fields chooser shows 1500 fields, and 'move all' shows 1500 fields but only 442 ends up on a layout.

i'm stumped. i've tried it on both mac and pc platforms and had same results.

ted

testfile.txt

[bcooney]

Could you post again as a zip file?

[ted choi]

will do. i need a minute to find winzip or something to zip with. if you have the new excel you could easily make a table yourself. that's how i did it. be back in a flash.

[ted choi]

does this work?

testfile.zip

[Ender]

Here's a dumb question from the peanut gallery: Why do you want to work with 1500 fields (or 12500 fields, depending on how you tell it) in a single FileMaker table? And why, oh why, on a single FileMaker layout??

If you like the spreadsheet look and feel, why not stick with Excel?

[bcooney]

True, why not Excel and I'm finding it difficult to believe that a relational database would have as many as 12,500 fields in ONE table.

However, I wanted to experiment. I have FM5, not FM6. But I think you're running into the same limitation. Layouts can only be so tall. So FM adds as many fields vertically as it can, and then stops. You can manually add more, but it certainly would be tedious!

Rethink the goal here.

[Vaughan]

Open the data file in Word and replace the Tabs with carriage returns, so that each column becomes a row. Then when it's opened in FMP you'll have one field but thousands of records.

[ted choi]

the 1300 records are a collection of microarray experiments and the 12500 fields are the data for each expt. the 12500 datapoints never change within record, so it could in principle be one field with 12500 values in it.

the reason i want to use filemaker is that there are a half dozen other attribute fields for each record, and i need to assemble different subsets of the 1300 record dataset reflecting different combinations of attribute values. there may be hundreds of different combinations. i was intending to use filemaker as a subset assembly engine, using the query functions on the six attribute fields. doing this in excel could be done, and in fact this is how i was doing it until it got to be so tedious to do manual sorts and rearranging columns that i thought i'd try filemaker.

the reason for importing as 12500 fields is... well, i didn't know any better. this was my first attempt at making this work and this was the way filemaker imported a tab delimited table of 1300 rows and 12500 columns. i think the page length limitation of a layout is what's causing the problem here and it's looking like i'm gonna have to learn how to handle arrays of data within a field such that i can easily export the field to the tab delimited format i need.

for the reasons above, i can't transpose the table in filemaker (to give 12500 records of 1300 fields), since the 12500 datapoints comprise a single record. in fact, the tab delimited file format i need is 1300 columns and 12500 rows, but since filemaker would import that as 12500 records of 1300 fields, i needed to transpose the excel file before import. hence my question about whether i could directly transpose the export from filemaker, rather than having to transpose back (to 12500 rows) in excel.

finally, the 1500 datapoint file was a small test file to demonstrate the layout limitation i was seeing. you probably wouldn't want to download the 400MB excel table with the full dataset on it!

thanks for all the comments and advice. if anyone has simple instructions for handling 12500 datapoints within a single field, please let me know!

ted

Edited August 24, 2007 by Guest

[Vaughan]

OK, so why not create a layout that only displays the 6 attribute fields. Forget the rest. (They'll be in the database but not visible on the layout.

[David Jondreau]

You could do a table where each record is a single datapoint for a single experiment. It will have about 16 million records, but that might be easier to deal with than 12,500 fields.

Are you on a Mac? It has zipping ability built in.

[The Shadow]

I agree with everyone else, change this over to small related records, it will make it much easier to work with.

Here's a tiny perl script that will process the file into the form:

tab tab

splitIt.pl.zip