Newbie question re synchonizing address lists

[Michael Correll]

Panorama is great for data transformation tasks like this, and there  
are always any number of ways to approach this kind of situation.

If you are very new to Panorama, some of the primary tools for this  
situation will be the lookup( , replace( , strip( , and array 
(  functions.   Also text funnels can be very handy in this  
instance.  Check the documentation for more details.

I note you have a field named 'recid'.   Can we assume that this is a  
_unique_ numeric field for the combined dataset -- i.e., each record  
in the combined file has a unique id?   If so this may be the  
key...and the key data field.

If you don't trust the address correction from the mail house (and  
assuming 'recid' is indeed a unique number and it exists in both your  
original and returned file) you could create a new field adjacent the  
address field in the returned file, and then use the Math / Formula  
Fill  menu and choose the lookup( function from the list to fill this  
*new* field with the address values from your *original* combined  
file.  This would put both the old and new side by side for  
comparison.  The formula in the Formula Fill lookup( dialog would  
look something like this:   lookup("OldDB","recid",recid,"Address","",0)

In this case you are looking up (and filling the new field with) the  
value in the old address field where the 'recid' field in both files  
match.   With fields from both tables  side by side, you should be  
able to see what was changed in a record by record view. You could  
also do this for other fields to see what may have changed.   Of  
course, if 'recid' is not a unique number for all records, this isn't  
gonna work.   It's _always_ a good idea to have a unique identifier  
for every record -- particularly in cases where data is transferred  
and modified by a third party.

As for finding records with duplicate last name and birth dates, you  
could create a new field and using Formula Fill from the Math menu  
again, fill it with the combined values of both fields, perhaps  
separated by a space or some other delimiter.  Then sort up on that  
field and use the Search/Select Duplicates menu.  This should find  
what you want to find....or perhaps what you _don't_ want to find, to  
look at it another way ;-)

Another approach -- non Panorama -- would be to check into some of  
the available address correction software available commercially.  I  
know there are a lot of resources on the Windows side...not sure  
about the Mac side.   Google "address correction software" and see  
what happens.   This would allow you to do your Address & Zip  
correction in-house, and prep your data for cases where you may have  
to use an outside fulfillment house for actual mailing.

The ultimate (??) solution is to make sure your data entry is as  
clean and accurate and up to date as possible in the first place.   
Setting the field properties properly and using the equation field in  
the design  sheet can go a long way toward making that happen.

I suspect others here who may be able to be of more help, or maybe  
offer some other useful suggestions on this.

MC>


On Feb 2, 2008, at 4:01 PM, Ken Doucet wrote:

> I have an address file (200k) consisting of
> recid
> socin
> first
> last
> address
> add2
> city
> code1(c1)code2(n1)code3(c1)code4(n1)code5(c1)code6(n1)
> pcode(c6) field that is just a consolidation of code1-6.
>
> This single file is an aggregation of 7 separate databases and has  
> been sent
> to a mailing house which has returned a file with 2 additional fields:

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://provue.com/pipermail/qna/attachments/20080203/b839b3c9/attachment.html