Best Practices- Compare Addresses?

Options
Carol Grant
Carol Grant
Seventh Anniversary 100 Likes 100 Comments Name Dropper
in SKY Developer General Discussions

I'm working on a file that comes from our Human Resources department that has updates. It doesn't necessarily say which field is updated (phone, email, spouse or address). For now, I need to compare the HR address to what we have in RE.

How do you accomplish this? Do you combine address lines, city, state and zip into one line and then compare the two? What if there are two address lines? What if the address lines equal but the city does not?

What if Road is spelled out in one address but it's abbreviated in the other (Rd) ? That's not an equal match but it's really the same thing.

Do you use contains when you compare them?

So many things that I wonder if this can be automated? I'm sure others have figured this out. If they don't match do you just add a new address and clean it up later?

Thanks,

Carol Grant

Tagged:

Comments

  • Ben Regier
    Ben Regier
    Sixth Anniversary 100 Likes 100 Comments Name Dropper

    @Carol Grant
    I have a couple of processes that have the potential to bring in updated addresses from outside sources. I usually compare each field individually (City to City, address lines to address lines, etc.). If any field has changed, I update the whole address. You're right that this sometimes results in unnecessary updates (for instance, changing “Road” to “Rd”, but I think trying to account for all of those edge cases would add too much complexity to the flow and would make it less reliable rather than more.

    The other thing I do is decide how much I trust the source of this information. If it's a very trustworthy source, I may simply update the address in RE and be done with it. If it's less reliable, I might notify someone of the change, or even start an approval that has to be accepted before the change goes through.

    If I send an approval, I'm always careful to highlight in the approval itself which fields have changed (for instance, the approval might say "Address lines changed from ‘102 Oak Road’ to ‘102 Oak Rd’). I found that if you don't highlight the exact changes, most people will just click Approve without really checking what changed.

  • Alex Wong
    Alex Wong Community All-Star
    Ninth Anniversary 1,500 Likes 2500 Comments Name Dropper

    @Carol Grant
    Something I looked into previously but did not implement (maybe in future): use a web based API to get standardized address. USPS, Google Map, Bing Map all have an API for this I think. Once standardized, then a direct compare can be done.

  • Carol Grant
    Carol Grant
    Seventh Anniversary 100 Likes 100 Comments Name Dropper

    @Alex Wong

    Thanks for responding Ben, great tips, this helps.

    Alex- I did research USPS APIs, you need to create a business account. I don't really want to do that before asking my main IT department if they already have one.

    I also looked at Google Maps which may be a possibility but again it wants billing information even though the Address Validation calls are free. I think I would have to still go through my main IT department. Although Google Maps Address Validation API did seem promising.

    I don't think I will pursue these right if it involves me having to go to our main college IT department. I'll figure something else out or wait for Blackbaud to make a connector that will format incoming addresses like Address Accelerator.

    Thanks,

    Carol

Categories