Multiple Legacy Records

[MacConsulting]

I inherited a Database made in the original Filemaker that's now 10 years old. I'm getting it relationalized into Clients and Transactions. Over the years there have been multiple records entered for the same client (i.e. each transaction duplicated most of the client info). When there are 5 or 6 records for the same client I want to combine them into one record (just as Address Book lets you merge records). I can easily locate all the records for a client but I'm looking for a way to put all that information in the same record. While addresses/phone numbers have changed over the years, I can find the most recent entry, but there's often data in older entries that isn't in the newest. Is there a method of keeping all the data across five or six records and merging it into one. I would hate to lose data in some of the records when they have to be deleted as duplicates.

[LiveOak]

Maybe, but first you have to create an exact definition of what you mean by merge which is simple enough to be reduced to a procedure.

What "data" is it you are trying to preserve? Old, now incorrect, phone and address information or something like notes fields?

There are lots of methods of keeping all the data, the key will be not having to wade thru all the old junk eveytime you use the file. Your current situation is a "method" of keeping the old data.

-bd

[MacConsulting]

LiveOak,

Thanks for replying. In all cases I want to preserve the newest data. What I would like to preserve is older records that have more data than the newest. For example, an old record might have a middle name which isn't included in the newest record. The newest record would be more complete if I could collect some of the data from the old record:

First Middle Last Ph. ID number Date Entered

Bill Thompson 123-45-6789 7/11/97

Bill C. Thompson 555-8348 123-45-6789 6/15/97

William Thompson 555-7843 123-45-6789 5/12/93

(Sorry couldn't figure out how to make this look like a table)

In this instance, I would like to preserve the Middle Initial and Phone number from the second record (sort of like getting the Ph. number to slide up to fill the empty spot above it) and then get rid of the second and third records. I can lose what's probably an old phone number in the oldest record.

I've been able to export just the ID numbers to a separate file and nix the duplicates. I can then relate the ID's remaining back to the original file where I can create a portal to view all the records with that ID. By sorting the relationship by most recent date I see all the multiple records with the most recent at the top. Here's where I'm stuck. I have no idea how to make the old data slide in where there's no new data (the phone number above) or how to pull off just the top record.

Thanks for taking your time to respond.

[LiveOak]

This is always a tough problem. If you have a way to identify recors belonging to the same contact, that's half the battle. It seems like you have this with ID numbers. It would be simple, but tedious, to write a script that would loop through all the records in each ID set and look for empty and full fields. If you decide to keep the newest record, you can then use globals to move data to the newest record. This would all be based upon the assumption that the "best" data is in the newest record. You could configure the script to only look for data in other records if the field is blank in the newest record.

-bd

[Ugo DI LUCA]

(this post have been edited, joining my three answers into one to avoid confusions. Sorry for the extra returns...)

Hi,

If all you want to do is merge all records into one, retrieving all last non empty fields, you could try to do the following :

Back-up your files first

1. Create a calcfied referenced to all your other fields this way, using a self relationship on the record_Id :

c_returnLastFirstNameContent = Last(SelfjoinOnId::FirstName)

c_returnLastLastNameContent = Last(SelfjoinOnId::LastName)

c_returnLastMiddletNameContent = Last(SelfjoinOnId::MiddleName)

c_returnLastPhoneContent = Last(SelfjoinOnId::Phone)

c_returnLastDateEntered =Last(SelfjoinOnId::Date entered)

2. Call a easy loop script (or go to the very last end of this post)

Show All records

Go to First record

Loop

Set field (Firstname, c_returnLastFirstNameContent )

Set field (LastName, c_returnLastLastNameContent )

Set field (MiddleName, c_returnLastMiddletNameContent )

Setfield (Phone), c_returnLastPhoneContent )

Set field (Date entered*), c_returnLastEnteredDate

(* If you want to keep the first date the record was entered, change the c_returnLastEnteredDate to Min(Seljoin::Date entered))

GotoNextRecord

End after last

End Loop

If, for some reasons you want to keep track of all datas entered into these fields (with a limit upon the size of the text entered), you could use the ValueListItem function....

1. define a related value list for each field, using the selfjoin

2. define calculations as the following :

c_relatedListName = ValueListItems(Status(CurrentFile, "your value list ")

3. Two options now depending on the fields formats.

option 1 : create a t_OldValueFirstName, t_OldValueLastName,... and call other setfield (t_OldValueFirstName, c_relatedListName) at first in the loop script given in the former answer.

option 2 : concanate all "t_OldValueFirstName,...." in another calculation

c_AllOldValues =

"FirstNames"& "PP" &

t_OldValueFirstName & "PP" &

"LastNames"& "PP" &

t_OldValueLastName& "PP" &

"MiddleNames"& "PP" &

t_OldValueMiddleName& "PP" &

"Phones"& "PP" &

t_OldValuePhones)

where "PP" stands as a carriage return.

and call other setfield (t_OldValues, c_AllOldValues) at first in the loop script.

Not sure for Option 2 would work though...:

And if finally, this is what you're looking for...

You could use a great FM feature : LOOKUP.

1. Back up your files,

2. Just keep the unsorted "Last "calcs, c_AllOldValues (or each Multikey field created) , the text field(s) to retrieve the multikey(s), and the selfjoin,

3. In define fields, set your contact fields + the text Multikey(s) as lookup from their related calculations.

4. Trigger a relookup for each field (just using the menu).

No script (at list none created) necessary !!

In all the above solutions, you'll end with a list of duplicate records.

Find dupes with c_markdupes = Case(RecordID = SelfJoin::RecordID, 1,0)

This will tag the first record with a 1, then you can search for the 0's and delete these records.

[Ugo DI LUCA]

Post deleted by Ugo DI LUCA

[Ugo DI LUCA]

Hi,

If all you want to do is merge all records into one, retrieving all last non empty fields, you could try to do the

I realize that my english may be confusing...

With this settings, all last records would be preserved, and the empty fields for this last record would be replaced by the latest non empty fields...

The only thing you need is a unique record_id for that contact