Combine multiple records into one record that have the same last name at the same address
Example Input:
John Smith 100 Main St
Jane Smith 100 Main St
Kevin Smith 100 Main St
Result you will see in the field named FIRST:
The Smith Family
INSTRUCTIONS
Make sure the name information is separated into the First and Last fields. if not you can Parse the name information from the Special>Parse> Name option.
Run the duplicate search from the Select>Show Duplicates option, with Household selected in the general tab as the search criteria.
IMPORTANT: Make sure to select the "Backup Duplicates option" at the bottom of the screen.
Update the database with the duplicate results.
Run the following command from the Command window (Edit>Command) You can copy/paste the following command into the Command statement Syntax area of the command window and click RUN.
| ON ERROR com_message=message() PUBLIC cDupFilePath,comAlias,comUnique PUBLIC ARRAY comArFiles(1,1) SET SAFETY OFF * Get the current Databases Alias Name comAlias=ALIAS() * Capture all back file names in memory =ADIR(comArFiles,ADDBS(JUSTPATH(DBF(comAlias)))+"*.rpt") * Create the variable name for the duplicate records cDupFilePath=ADDBS(JUSTPATH(DBF(comAlias)))+FORCEEXT(comArFiles(1,1),'dbf') comUnique=SYS(2015) USE (cDupFilePath) IN 0 ALIAS (comUnique) SELECT(comUnique) INDEX ON therec_xx tag therec_xx SELECT(comAlias) ALTER TABLE (comAlias) ADD COLUMN priority C(1) ALTER TABLE (comAlias) ADD COLUMN name2 C(60) replace name2 with first for "1"$(priority) replace all priority with "" REPLACE priority WITH IIF(!DELETED(),'1','2') for SEEK(RECNO(),comUnique) RECALL ALL replace first with "The "+allt(last)+" Family", last with "" for "1"$(priority) delete for "2"$(priority) pack USE IN IIF(USED(comUnique),SELECT(comUnique),0) RENAME (FORCEEXT(cDupFilePath,"rpt")) ; TO (cDupFilePath+".txt") |
