How do I combine records in Filemaker?
December 19, 2007 9:20 AM Subscribe
Filemaker question: How do I combine two records into one?
I’m pretty new to database construction, and I need some help with Filemaker 9.0. I’m reading in data from a text file full of test scores (FYI, a fixed-width file, nothing fancy). Each kid has taken two tests (with multiple battery scores) and has two rows of data, one for each test. I'm using the Middle() command to extract the columns I want for each test. When I read in the data, I end up with two records per kid. How do I collapse them into one? (Bonus if I can somehow read them in on one line in the first place.)
Data set I have:
ID_ A1 A2 B1 B2
Julie .. .. 87 89
Julie 92 95 .. ..
John .. .. 97 82
John 82 75 .. ..
Data set I want:
ID_ A1 A2 B1 B2
Julie 92 95 87 89
John 82 75 97 82
If this is a trivial question, sorry. I did try my google-fu and the help files before posting here.
I’m pretty new to database construction, and I need some help with Filemaker 9.0. I’m reading in data from a text file full of test scores (FYI, a fixed-width file, nothing fancy). Each kid has taken two tests (with multiple battery scores) and has two rows of data, one for each test. I'm using the Middle() command to extract the columns I want for each test. When I read in the data, I end up with two records per kid. How do I collapse them into one? (Bonus if I can somehow read them in on one line in the first place.)
Data set I have:
ID_ A1 A2 B1 B2
Julie .. .. 87 89
Julie 92 95 .. ..
John .. .. 97 82
John 82 75 .. ..
Data set I want:
ID_ A1 A2 B1 B2
Julie 92 95 87 89
John 82 75 97 82
If this is a trivial question, sorry. I did try my google-fu and the help files before posting here.
Change my second paragraph to read:
"Sorry this is a bit wordy, but I hope you can get the gist of what we're trying to do: export half the data, delete half the records, then import that exported data back into the remaining unique records."
posted by mosk at 10:45 AM on December 19, 2007
"Sorry this is a bit wordy, but I hope you can get the gist of what we're trying to do: export half the data, delete half the records, then import that exported data back into the remaining unique records."
posted by mosk at 10:45 AM on December 19, 2007
Response by poster: mosk-- thanks for your help. I understand your logic, the problem with it is something I forgot to mention earlier. The database is going on the web so other people can upload their own files. Can I automate the process you're talking about so that when someone else imports the datafile, it will automatically be converted to one row?
posted by parkerjackson at 1:02 PM on December 19, 2007
posted by parkerjackson at 1:02 PM on December 19, 2007
Yes, with enough scripting. You probably want to have two ready-made temp tables to handle all of the above (one for full file import, the other to hold the half-file export). When the data set is final, import it into a third "repository" table, wipe the other two clean, and you're ready for the next user. But the scripting will be somewhat involved, and beyond the scope of an AskMe reply.
Again, there may be other ways of doing this, but any method is going to require sifting through your data and reducing it by half. For example, you could also write a looping script that stepped through a sorted list of these names, moving data from populated fields in the first record to unpopulated fields in the second record. When finished stepping through the records, do a Find for the records with unpopulated fields and delete them. This might be easier/less complicated if this is something that needs to be in place as a go-forward solution. I would still recommend working in a temp table and moving records into a permanent table when you are done.
posted by mosk at 2:01 PM on December 19, 2007
Again, there may be other ways of doing this, but any method is going to require sifting through your data and reducing it by half. For example, you could also write a looping script that stepped through a sorted list of these names, moving data from populated fields in the first record to unpopulated fields in the second record. When finished stepping through the records, do a Find for the records with unpopulated fields and delete them. This might be easier/less complicated if this is something that needs to be in place as a go-forward solution. I would still recommend working in a temp table and moving records into a permanent table when you are done.
posted by mosk at 2:01 PM on December 19, 2007
I love these kind of puzzles, and Filemaker lets you skin this beast a number of ways. Having massaged my share of data before importing into a database, I think I would probably open your list up in WORD first, do the 'reveal' command that lets you see all the characters like TABs and Paragraph Returns.
Next do a "Find and Replace" in your list with Word, everytime it sees the carriage return, replace it with something you can easily identify in your Filemaker Calc - like: Replace Carriage Return with XXX and CR.
Whereas Filemaker can do this by iteself, I find that using it conjunction with a word processing program cleans it up a little quicker.
posted by bytemover at 9:14 PM on December 19, 2007
Next do a "Find and Replace" in your list with Word, everytime it sees the carriage return, replace it with something you can easily identify in your Filemaker Calc - like: Replace Carriage Return with XXX and CR.
Whereas Filemaker can do this by iteself, I find that using it conjunction with a word processing program cleans it up a little quicker.
posted by bytemover at 9:14 PM on December 19, 2007
This thread is closed to new comments.
Sorry this is a bit wordy, but I hope you can get the gist of what we're trying to do: export half the data, delete half the records, then import that data back into the unique records.
How to do this:
If you are comfortable using the middle function, isolate each separate test score into it's own calculated field. You will have four fields:
A1_calc
A2_calc
B1_calc
B2_calc
For half the records, half of these fields will be blank, which is fine.
Next, create four non-calculated storage fields:
A1_Num
A2_Num
B1_Num
B2_Num
Next, do a Find for records that have blank/null values in A1_calc and A2_calc.
(Enter Find mode and put an = sign in these fields. Click Find.)
You now have a found set of half your database, and this found set has B1 and B2 values.
Export these records into a temp table - test_scores_temp.fp7. Export just the Name field (I am assuming unique names!) and the B1_calc and B2_calc fields.
After export, delete this found set from your database, then choose Reords > Show All Records
Next, import the temp table data back into the remaining records using Import Records (select the temp table as the table to import) and select the option to Update Matching Records in Found Set. IMPORTANT: YOU MUST SET THE NAME FIELDS AS THE KEY FIELDS TO USE FOR THIS UPDATE (in the Import Field Mapping dialog, align these fields and make sure the symbol joining them is an = sign). Next, map the B1_calc field to the B1_num field, and B2_calc to B2_num. These will use the arrow symbol, to indicate that data from the B1_calc field in the temp file is going into the B1_num field in the main file. Do the import.
Now, the only remaining thing to do is move the A1_calc and A2_calc data into the respective A1_num and A2_num fields, which can be done with separate Replace Field Contents commands. (See FMP Help for more info on that if you aren't familiar with it.)
posted by mosk at 10:16 AM on December 19, 2007