Merging double records

[Rzztmass]

Hi!

I've inherited a filemaker v11 database and I want to merge double records. The database consists of two files linked by a unique ID (social security number). Unfortunately layouts from one file happily use data from the other file and vice versa. This has led to the current situation where there are doubles, with one person having one record in file2 and two records in file1. Neither of the doubles in file1 is empty, they use the same table but are in essence two half-records, one with data from the layouts in file1, one with data from the layouts in file2. Additionally there's some extra overlap with non-identical values for the same field. So to get rid of doubles I need to merge these two records together. I have already made it really hard for the users to generate more of these doubles, but I still need to adress what I have. I did a search on the forum and didn't find what I was looking for.

We are talking about several thousand records, maybe 150 doubles and around 150 fields.

I was thinking of extracting all the field names in the default table in file1 with FieldNames() and looping through them, comparing values for double records that I have identified with the sort by ID and compare to next/previous record method. If only one record has a value, great, merge, if both records have values and they aren't identical, display a dialog and ask which to keep. When done, the doubles are identical and then I delete the one that isn't related to the record in file2 (sensibly, the relation is one-to-one).

Do you think this might work? Is it unneccessarily convoluted? You don't need to tell me that the current structure is suboptimal, I know that, I work with what I have.

I appreciate your advice.

Edited March 21, 2016 by Rzztmass

[bruceR]

"The database consists of two files linked by a unique ID (social security number). Unfortunately layouts from one file happily use data from the other file and vice versa."

By itself, there is nothing wrong with this. That's a basic, very practical feature of FileMaker.

You'll need to describe more detail about the tables in the two files.

Better would be clones of the files; or a PDF of the table definitions.

[Rzztmass]

I get that. One problem for example is that the main layout of file2 uses the person's name stored in file1. If a user creates a new record in file2 and enters the person's name before the SSN, a double is created in file1 and the previously existing record in file1 will not be treated as related to the new one in file2. As I said, I changed that, but I still have these doubles to deal with.

The tables work roughly like this: File1 contains patient data. File2 contains data about a certain medical procedure. I guess the sensible thing would have been to have a one-to-many relationship here, but the database works in a one-to-one fashion and repeated procedures are stored in fields like procedure2, procedure3 and so on. Anyway, some data about the procedure is stored in file1 and some data about the patient is stored in file2.

The problem is that I have these "half-records" with one record in file1 containing most of the patient's data while the other record in file1 contains information about their medical procedures. This last record is also the one liked to the corresponding record in file2. Both records in file1 might have a value for the patient's adress and their current main diagnosis, to name examples. These values might differ.

So, is the approach I outlined above a sensible way to merge and remove these doubles?

Edited March 22, 2016 by Rzztmass

[Rick Whitelaw]

It seems the database is already working as one to many. If table one is patient data and table two is medical procedures I's normal for one patient in table one to have more than one record in table two.

[comment]

27 minutes ago, Rick Whitelaw said:

I's normal for one patient in table one to have more than one record in table two.

However, it's not normal to have the same fields in both tables, apart from PatientID.

 

[Rzztmass]

5 hours ago, Rick Whitelaw said:

It seems the database is already working as one to many. If table one is patient data and table two is medical procedures I's normal for one patient in table one to have more than one record in table two.

That would have been a sensible way to setup the database. What I have, however, is doubles in file1 - patient data. Repeated medical procedures are not stored in distinct records in file2 but rather in extra fields of the same record of file2. The users have been pretty good at avoiding creating doubles in file2. But there are several instances of the following: Several procedures saved in the same record in file2 in different fields. Kind of working as intended. For that patient there can be two or more records in file1, one record containing for example the name, the adress and their diagnose while the other record contains a differently spelled name and the date of their medical procedures (dates of medical procedures described in file2 are for some reason stored in file1). Both records contain the patient's SSN if I'm lucky (As SSN is set as unique, filemaker will show a warning when you try to set a new record's SSN to a value that already exists, but it will let you do it. The users seem to hate warnings and instead of either not creating the record or ignoring the warning and creating the record, they have found another way: Slightly modifying the SSN so it doesn't count as a double and won't show a warning...).

I did by the way go ahead and use my approach above to loop through fields to merge records and it worked fine. Took ages sifting through conflicting information, but it's done now. If anyone's interested I can post the code when I'm at work.

I am no longer sure that I have made it hard enough for the users to create doubles, as they seem to cancel on warnings and then try to modify their data entry so that no warning is shown.. I think I will have to make the main layouts uneditable except for the SSN if SSN is not set. Then I'd have a script check for previous instances of that SSN, if they don't exist, check if the SSN is valid and make the rest editable. Otherwise check that the current record is empty except for the SSN, delete it and go to the already existing record. If the record isn't empty, accept defeat and let them go on with what they're doing without showing a warning about non-unique SSNs. That way, no warnings are shown except for invalid SSNs (There are patients that have no valid SSN and need a dummy number) and the users get to the record they want.

Or how would you handle users behaving that way? I work with Pro and Server, so I cannot modify the toolbar to remove "Create new record", otherwise I would do that.

Edited March 23, 2016 by Rzztmass

[comment]

2 hours ago, Rzztmass said:

Repeated medical procedures are not stored in distinct records in file2 but rather in extra fields of the same record of file2. The users have been pretty good at avoiding creating doubles in file2.

Is that a good thing?

3 hours ago, Rzztmass said:

I am no longer sure that I have made it hard enough for the users to create doubles,

The idea is to make it not hard enough, but impossible.

3 hours ago, Rzztmass said:

I work with Pro and Server, so I cannot modify the toolbar to remove "Create new record", otherwise I would do that.

The toolbar has nothing to do with this. If you don't want users to do something, then take away their privileges to do so in their privilege set. You can always allow them to do it under controlled circumstances, using a script set to run with full access privileges.

 

[ggt667]

9 hours ago, comment said:

However, it's not normal to have the same fields in both tables, apart from PatientID.

Normal and normalization is two different things, no?

It's quite normal for many solutions that are built by people who knows their trade; and have not optimized or have knowledge of database normalization to have the same fields in 2 related tables; however what @comment means is that by doing so your are breaking the normalization of the database

Edited March 23, 2016 by ggt667

[Lee Smith]

And what does this have to do with helping the OP?

[Rzztmass]

7 hours ago, comment said:

10 hours ago, Rzztmass said:

Repeated medical procedures are not stored in distinct records in file2 but rather in extra fields of the same record of file2. The users have been pretty good at avoiding creating doubles in file2.

Is that a good thing?

Given that multiple procedures are meant to go into the fields procedure2, procedure3 and so on of the same record, it is. Otherwise the layouts wouldn't really work. If I could design the database from scratch, I would have one record per procedure, but I can't.

7 hours ago, comment said:

If you don't want users to do something, then take away their privileges to do so in their privilege set. You can always allow them to do it under controlled circumstances, using a script set to run with full access privileges.

Thank you, I hadn't thought of that. Given that my users are physicians, I may have a hard time explaining why they shouldn't have full privileges, but I think I can pull it off.

[ggt667]

6 hours ago, Lee Smith said:

And what does this have to do with helping the OP?

Clarifying the terms used.