Page 1 of 1

Household your file

Posted: Thu May 05, 2011 7:41 am
by Dave
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.

Code: Select all

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")