--------------------------------------------------------------------------- Finding and Merging Duplicate Church Records --------------------------------------------------------------------------- Over the years the same person is sometimes entered twice. It happens when a family is registered again at a new station, when a baptism register is typed up a second time, or when two parish computers are synced and the same person arrives from both sides under different GUIDs. A duplicate is not simply an inconvenience. The person's contributions are split across two records, so neither shows what was really given. The children hang off one family file and the parents off another. Reports count the person twice. This tool finds those pairs and lets you join them back into one record without losing anything. The work is done in two stages, on two screens: THE DUPLICATES LIST finds candidate pairs and lets you judge them THE MERGE SCREEN joins one pair into a single record You reach both from the FIND DUPLICATES button on the control panel. =========================================================================== PART ONE: HOW THE APP FINDS POSSIBLE DUPLICATES =========================================================================== Why it cannot simply compare everybody with everybody ----------------------------------------------------- A parish with 20,000 church records would need 200 million comparisons to check every record against every other one. That would never finish. So the App works in three steps: it first simplifies every name into a comparable form, then uses that form to gather small groups of records that could possibly belong together, and only then does the careful comparison - on a few hundred pairs instead of 200 million. STEP 1: Making the names comparable ------------------------------------ People's names are written down differently by different clerks. Chirwa and Chilwa. Adam and Adamu. Alefa and Alepha. Cecilia and Sesilia. Before anything can be compared, each name has to be reduced to a form that ignores this. The App does that in two ways, in this order. FIRST, the table of synonymous names. This is the table you can edit yourself through the control panel (SYNONYMOUS NAMES). If it says that Chirwa and Chilwa are the same name, then the App treats them as the same name. This is the most important part of the whole search, because it carries your own knowledge of how names are spelled in your area. The better that table is, the better the duplicate search will be. The App is careful with that table in one respect. If Anna appears both under Annastasia and under Anne, then Annastasia, Anna and Anne are all joined into ONE group. Without that, Anna would belong to whichever heading came first and the other would be missed. SECOND, for names that are not in the table at all, the App falls back on a sound-alike key. It rewrites the name using rules taken from the way names actually vary in this region: ph becomes f Alepha -> Alefa th becomes t Botha -> Bota ck becomes k Enock -> Enok c becomes k or s Clara -> Klara, Cecilia -> Sesilia z becomes s Damazio -> Damasio l and r are treated the same Chilwa -> Chirwa, Elina -> Erina g and j before e/i/y are treated the same Angela -> Anjela a trailing vowel is dropped Adamu -> Adam This is only a safety net. The synonymous names table is the real instrument. The App also prepares, for every record: - the first Christian name, reduced in the same way - ALL the Christian names, reduced, sorted and de-duplicated, so that "John Peter" and "Peter John" come out identical - the father's and the mother's names, reduced the same way - the year of birth (from the real date field, or the first sensible four-figure year found in the written date) - the sex - the baptism number combined with the baptism parish STEP 2: Gathering the candidates --------------------------------- The App now runs seven separate passes over the prepared keys. Each pass gathers records that agree on one particular combination, and pairs up only the records inside each small group. A pair found by any pass becomes a candidate. PASS 1 same surname and same first name The ordinary case: one person entered twice with a spelling variant in one of the names. PASS 2 same surname, same year of birth, same sex Catches badly mistyped first names where the date is known. PASS 3 same first name, same father, same mother Finds duplicates inside one family - and also real brothers and sisters, which is why these pairs need looking at. PASS 4 same baptism number and same baptism parish The strongest of all. Two records claiming one and the same entry in the baptism register. PASS 5 same surname, same father, same mother, same year of birth Whole families entered twice. PASS 6 same family file and same first name The same person entered twice on one card. PASS 7 same father, same mother, same year of birth, same sex Catches duplicates where the surname is missing or wrong. A group that is too large is skipped. If thirty or more records share the same surname and first name, that is a common name in the parish, not thirty duplicates, and pairing them all up would produce hundreds of useless lines. STEP 3: Judging each candidate ------------------------------- Only now, on the few hundred candidates rather than on the whole table, does the App compare the records letter by letter and give each pair a score out of 100. Points are added for agreement and taken away for contradiction: surname, compared letter by letter up to +20 surname is a known variant +8 Christian names, compared name by name in any order, so word order does not matter up to +20 the names are known variants of each other +5 same sex +5 sex contradicts -40 father's name agrees up to +7 mother's name agrees up to +7 both on the same family file +8 same year of birth +15 years of birth one apart +8 years of birth more than two apart -15 exactly the same date of birth +10 THE SAME BAPTISM ENTRY +30 different baptism numbers -20 exactly the same baptism date +12 different baptism dates -12 both recorded as deceased +5 The result is capped at 100 and grouped into three bands: 80 and above almost certainly the same person 55 to 79 worth reviewing 40 to 54 weak, probably not the same person The weights are deliberately set so that NAMES ALONE CANNOT REACH THE TOP BAND. Two records that agree perfectly on every name, on sex and on both parents still only reach about 60. It takes a matching date of birth or a matching baptism entry to go higher. This is on purpose: in a parish where many people share a name, name agreement on its own is weak evidence. Notice also that a contradicting sex takes away 40 points. That is usually enough on its own to push a pair below the threshold and out of the list. The "not the same person" memory --------------------------------- Some pairs will always look alike and will never be duplicates: twins, a father and a son with the same name, two cousins with the same parents' names. When you press NOT THE SAME on such a pair, the App remembers it and no future scan will ever offer it to you again. That memory is kept in the backend, so it travels with your data and is shared by everyone using the same database. It is stored by GUID, not only by record number. Record numbers can change - a deleted record's number is given to the next person entered, and the RENUMBER function reassigns them all at once. The GUID never changes. Before every scan the App quietly re-checks the memory against the GUIDs and corrects any record numbers that have moved. Without this, a remembered decision could end up pointing at a completely different person and hide a real duplicate. What the search does NOT do ---------------------------- It does not decide anything. It produces a list of pairs to look at, ordered by how likely they are. Every merge is your decision. It does not find a duplicate whose names, parents, dates and baptism number are ALL different. If nothing at all agrees, nothing can be matched. It will offer you pairs that are not duplicates. That is intended. It is better to be shown ten pairs and reject nine than to miss the tenth. =========================================================================== PART TWO: THE DUPLICATES LIST, FEATURE BY FEATURE =========================================================================== The list shows one line per candidate pair - or rather two lines, because each pair carries a lot of information. RECORD A IS ALWAYS THE RECORD THAT WILL BE KEPT. RECORD B IS ALWAYS THE RECORD THAT WILL BE MERGED ONTO IT AND DISAPPEAR. The App decides which way round that should be, and you can always overrule it on the merge screen. How it decides is explained further below. The buttons across the top --------------------------- SCAN NOW Runs the whole search. It first asks whether you want to look at all the records or only at records created since a given year. The year option is the quick check to run after an import or a sync, when only recently arrived records can have created new duplicates. While it runs, the please-wait window reports what it is doing and how far it has come. A full scan of a large parish takes a while; the counter tells you it has not frozen. REFRESH Re-reads the list. Also quietly removes any line pointing at a record that has been deleted in the meantime. WRITE REPORT Writes the whole list to a text file in the Logs folder and opens it, so it can be printed and worked through on paper. The report groups the pairs by band and gives the full reasons for each. MERGE THE SAFE ONES Batch mode. It merges, without asking further, every pair that scores 90 or more AND in which the two records do not contradict each other in any single field. In other words: pairs where there is nothing to decide, because wherever one record is empty the other has the value. Anything requiring a judgement is left in the list for you. Make a security copy before using this. UNDO LAST MERGE Brings back the record removed by the most recent merge, with its original GUID and creation time, and takes it out of the sync deletion memory so it will not be deleted again on the next sync. Read the limits of the undo carefully in Part Three. CLOSE Closes the list. The count line --------------- Under the heading, a single line reports how many candidate pairs there are and how they divide between the three bands. The columns ------------ The list is in two columns, one for each record, and each record keeps entirely to its own column. SCORE the score out of 100, highest first RECORD A - KEPT on a pale GREEN background first line the record number, the family file number, the name second line the partner on each family file, then the children RECORD B - MERGED ONTO A on a pale GREY background the same three pieces of information for the other record The colour has no meaning beyond the column it is in: green is the record that stays, grey is the record that goes. THE PARTNER COLUMN needs a word of explanation. A person can be on more than one family file - their own, and any secondary family files recorded in their Scards. They can have a different partner on each. So this column lists them all, one after another: 1234: Maria Banda | 5678: Agnes Phiri meaning: on family file 1234 the partner is Maria Banda, and on family file 5678 the partner is Agnes Phiri. Where a family file has no second adult, it says "no spouse" rather than leaving a blank, so that you can tell the difference between "has no partner" and "not looked at". THE CHILDREN COLUMN gives the number of children followed by their names, across all the family files where the person is an adult. These two columns are usually what decides the case. Two records with the same name, the same partner and the same children are the same person. Two records with the same name but different partners and different children are two different people - or one person with two families, which the merge screen handles properly. The detail panel under the list -------------------------------- The reasons why two records look alike are far too long for a column. So the panel underneath shows everything about whichever pair the cursor is on. Move to another line - by clicking anywhere on it, or with the arrow keys, or by scrolling - and the panel follows. It shows: the score and the band the record to be kept: number, name, family file, partner, children the record to be removed: the same a note if both records are on the same family file WHY THEY LOOK ALIKE: the full list of reasons behind the score THE TWO HOUSEHOLDS: how the two families relate (see below) A IS KEPT BECAUSE: why the App put them this way round "The two households" --------------------- This line is a judgement the App makes, and it is not something the two names show by themselves. It compares the two PARTNERS with each other, using the same name comparison and the same synonymous names table, and reports one of: spouses double the two partners also look like the same person. The whole family has probably been entered twice. spouses differ the two partners are different people. This is one person appearing on two family files with two different partners. same spouse both records already point at the same partner. no spouse neither record has a partner on a family file. This matters because it decides what happens to the second family file during the merge. It is worth reading before you press COMPARE. Why record A rather than record B ---------------------------------- The App chooses which record survives, in this order: 1. THE MORE COMPLETE FAMILY FILE WINS. A family file carrying both partners beats one with a single adult. A file with both husband and wife is the real household; a file with one adult is far more often the half-finished second entry. 2. IF BOTH FILES ARE OF THE SAME KIND, THE ONE WITH MORE CHILDREN WINS. More of the family's history already hangs off that file, so it is the cheaper one to keep whole. 3. ONLY IF THAT STILL DOES NOT SEPARATE THEM does the older record number win, being the record that other tables have had longest to point at. The reason is shown in the detail panel, and the merge screen can swap the two round if you disagree. The four buttons on each line ------------------------------ COMPARE Opens the merge screen for that pair. This does not change anything yet. NOT THE SAME Records that these two are definitely different people. It asks you to confirm, then offers you a box to say why - "twins", "father and son". The note is only for your own future reference. The pair disappears from the list and no future scan will offer it again. OPEN A / OPEN B Opens the family file of that record, so you can look at the person in the ordinary way before deciding. =========================================================================== PART THREE: THE MERGE SCREEN, FEATURE BY FEATURE =========================================================================== The merge screen shows the two records side by side, field by field, and lets you build the surviving record out of both. The two lines at the top ------------------------- KEEP: 1042 John Banda (family file 1234, 2 adult(s)) REMOVE: 3871 John Bhanda (family file 5678, 1 adult(s)) The number of adults on each family file is shown because that is the main reason the App put them this way round. The two explanation lines -------------------------- The first says how the two households relate, in words, and what that means - for instance that the partners look like the same person too, and that you should merge that pair as well afterwards. The second lists, family file by family file, exactly what the merge is going to do with each one. Read this before pressing MERGE. It is produced by the same code that carries the merge out, so what you read is what will happen. The field grid --------------- One line per field of the church record, showing the value on each side. Only the fields where the two records DIFFER are shown at first. Fields where both records agree are hidden, because there is nothing to decide about them. SHOW EVERY FIELD reveals them all. Each line carries a status: auto One side is empty and the other has a value. The App has already chosen the one with the value. Nothing to do. conflict Both sides have a value and they are different. YOU MUST CHOOSE. The MERGE button will not work until every conflict is decided. system The surviving record keeps its own. These are the fields that identify the record - the record number, the GUID, the creation time, the last edit time. They cannot be taken from the other record because they are what makes the record itself, and because the database will not allow two records to share them. special The family file links (family file, parents' file, secondary files). These are not chosen field by field; the merge works them out from the family files themselves, as described below. The TAKE A and TAKE B buttons on each line choose which value the surviving record will carry. KEEP BOTH is offered on the free-text fields - other names, remarks, attention, instructions, attachments - and joins the two values together with a slash, so that nothing written by anybody is thrown away. The buttons ------------ SWAP: KEEP THE OTHER ONE Turns the merge round, so that the record that was going to be removed is kept instead. The whole grid is rebuilt and the screen tells you plainly which way round it now is. Worth using whenever you know something the App cannot know. SHOW EVERY FIELD Reveals the fields where both records agree. Press again to hide them. MERGE Carries out the merge. It asks you to confirm first, and lists everything that will happen. Refuses while any conflict is undecided. CANCEL Closes without changing anything. =========================================================================== PART FOUR: WHAT THE MERGE ACTUALLY DOES =========================================================================== Everything below happens inside a single transaction. If any part of it fails, the whole merge is undone and the data is left exactly as it was. 1. THE CHOSEN VALUES are written onto the surviving record. 2. THE CONTRIBUTIONS are settled year by year - see below. 3. THE BAPTISM REGISTER LINK of the removed record is moved to the surviving record, if the surviving record does not already have one. (The baptism book is linked to the church records by GUID, and a baptism entry can only be linked to one record.) 4. THE FAMILY FILES are settled, one at a time - see below. 5. THE REMOVED RECORD IS DELETED, through the ordinary deletion used everywhere else in the App. That takes it off every family file, removes family files that have fallen empty, and records the deletion in the sync memory so that the next sync from another computer does not bring it back. 6. THE WHOLE MERGE IS RECORDED in the merge history table, including a complete copy of both records as they were before, and a list of everything that was moved. The family files ----------------- Each family file that the removed record was an adult on is settled on its own, by one rule: NO ADULT LEFT ON THE FILE The file would be left with children and nobody to hold it, so its children move across to the surviving record's own family file and the empty file is removed. AN ADULT IS LEFT, AND THE TWO PARTNERS ARE DIFFERENT PEOPLE This is genuinely one person with two families. The surviving record takes the removed record's place on that family file as a SECONDARY family file, added to its Scards. The partner and the children stay exactly where they are. AN ADULT IS LEFT, AND THE TWO PARTNERS LOOK LIKE THE SAME PERSON The file is left completely untouched. The partner's duplicate keeps it, alone, with its children. When you then merge that partner pair in its turn, that file will have no adult left, the first rule above will apply, and its children will come across by themselves. This is why, when the App tells you the partners look like the same person too, you should merge that pair as well. The two family files then join up on their own. No arrangement of these rules can ever leave children without an adult, or put two adults of the same sex on one family file, and the outcome does not depend on which of the two pairs you merge first. The contributions, year by year -------------------------------- This needs care, because the same contributions were often entered twice - partly on one record and partly on the other. Simply moving everything across would add the two entries together and overstate what the person gave. So each contribution year is settled on its own. For a given year: IF THE SURVIVING RECORD'S TOTAL IS AT LEAST AS HIGH, it is left alone. The removed record's entries for that year are not brought across, and go when the record goes. IF THE REMOVED RECORD'S TOTAL IS HIGHER, it clearly holds the fuller account of that year. The surviving record's entries for that year are DELETED and the removed record's entries are moved across whole. Never a mixture within one year. Whichever side is taken, it is taken entire, so that the individual transactions always still add up to their own total. Each ledger is settled separately, because archiving is a deliberate act and an archived year should not be weighed against a live one. The screen shows you the whole plan, year by year, before you commit: LedgerI 2022: keeping the surviving record (500.00, the removed record had 300.00) LedgerI 2023: taking the removed record (1,500.00) in place of 1,200.00 LedgerI 2025: taking the removed record (800.00) in place of 0.00 NOTE CAREFULLY: this is the one part of a merge that deletes entries belonging to the record you are KEEPING. Every deleted entry is written into the merge history first, with its date, amount and description, so there is always a record of what was removed. Undoing a merge ---------------- UNDO LAST MERGE brings back the record that the last merge removed, exactly as it was, with its own GUID and creation time, and takes it out of the sync deletion memory. IT DOES NOT UNDO EVERYTHING: - values copied onto the surviving record stay there - contributions moved to the surviving record stay there - contributions deleted from the surviving record are NOT restored - the family file arrangements are not put back So the undo gives you the two records again, but you will have to sort out the details by hand. The merge history holds a full copy of both records as they were, and a list of everything that was moved or deleted, which tells you what to put right. The safest habit is to make a security copy before a session of merging. A note on syncing ------------------ A merge deletes a record. If another computer still holds both records, the next sync would bring the deleted one back - which is why the merge writes the removed record into the sync deletion memory. Even so, merging is best done on the main copy of the data, or straight after a full sync, so that the other computers learn about the deletion at the next sync rather than fighting it. =========================================================================== PART FIVE: A SUGGESTED WAY OF WORKING =========================================================================== 1. Before anything else, spend time on the SYNONYMOUS NAMES table. Every name variant you record there makes the duplicate search better. This is the single most useful thing you can do for it. 2. Make a security copy. 3. Run SCAN NOW over all records the first time. Afterwards, run it over records created since the last check - it is much quicker and catches what an import or a sync has just brought in. 4. Work down from the top of the list. The highest scores are the clearest cases and take the least thought. 5. For each pair, read the second line - the partners and the children. That usually settles it faster than reading the reasons. 6. Press NOT THE SAME on every pair you reject. It costs a moment now and saves you looking at the same pair every month from now on. 7. When the App says the partners look like the same person too, merge that pair as well, straight afterwards. The two family files then join on their own. 8. Use MERGE THE SAFE ONES only once you have looked at a good number of pairs by hand and trust what the scores are telling you. 9. After a session of merging, run the family file repair functions from the control panel, and take a fresh security copy.