Table merge

[cglchristian]

Please excuse what may be a very stupid question. I have created a db with 2 tables of respectively 198 and 150 different fields. After doing all this work, I realize that it would be better to have everything in a single table. Is there any way to merge the two tables, or transfer all the fields from one table into the second?

Thank you for your help.

Christian

[David Jondreau]

1) It's easy to do with the Advanced version of Filemaker. I don't think you can do it with the vanilla version.

2) It's likely your table design is pretty inefficient. 150 fields is a lot of fields to have in one table. Not that it isn't justifiable in certain cases, but 350 fields in one table is almost certainly not the right way to go.

Edited November 30, 2007 by Guest

[Matthew F]

I realize that it would be better to have everything in a single table

A single flat file? Maybe you need Excel, not a relational database.

To answer your question, Copy and Paste functions in the Define Database window are available in the Advanced version of Filemaker Pro and they come in handy for his sort of thing. Otherwise I'm afraid you're stuck having to create them one by one.

[cglchristian]

The database I am building is to compile clinical results for a future study. my current plan is 4 tables:

Investigators with contact info, contract info etc

Patient database with personal info

clinical data trial 1

clinical data trial 2

Clinical data for trial 1 has approximately 300 data points that will be captured. Originally I had broken it down into two by theme, thus my question.

I guess that the "advanced" version is the way to go. Funny, I do not remember the "normal" and "advanced" segragation on my old version of Filemaker... Arghh.

Thanks for the quick answers.

Christian

[David Jondreau]

I would handle this differently. Instead of using a table with a field for each of the datapoints, I would use a table with a record for each of the datapoints.

I'd start with one table that would be TrialRef which would contain a record for each of datapoints. This is a reference table.

I'd make another table, Trials which only contains the general information about a trial, not sure what you're capturing, but things like Start Date and End Date and PatientID.

And finally, I'd have a DataPoints table where each record is related to a trial. Script the creation of a new Trial which includes creating 300 related DataPoints records (looked up from the Ref table).

[bruceR]

A count of 198 fields strongly suggests a design problem. Your description of field clinical data trial 1, clinical data trial 2 etc. Confirms it.

David J. is on the right track with his suggestion.

[cglchristian]

Ok. Thanks for the suggestion, I will try to follow your advice.

One problem that I see is that the datapoints have many different formats. Out of the 198 data points, some are times, some are counts of cells, some are dates and some are comments. Thus explaining why I first set out to make a field per datapoint.

Christian