Skip to content

SalesforceSFDC/Fuzzy-Matching-for-Duplicate-Address-Checks

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

5 Commits
 
 
 
 

Repository files navigation

Fuzzy-Matching-for-Duplicate-Address-Checks

Helper Functions

// Convert nulls to empty strings
private static string ops(string s) {
    return s == null?'':s;
}

Find Accounts Matching Address

/**
  * Return all accounts matching this address
  *  The field AnnualRevenue is misused to return the probability of match
  */
public static List<Account> AccountsByAddress(String Name1, String Street, String Postcode, String City, String Country) {

    List<Account> result = new List<Account>();

    List<Account> accs;
    Integer MAX = 50;

    // Find accounts with same postcode OR city
    accs = [SELECT Id, Name, BillingStreet, BillingPostalCode, BillingCity, BillingCountry
            FROM Account
            WHERE BillingCountry = :Country AND (BillingPostalCode = :Postcode OR BillingCity = :City)
            LIMIT :MAX];
    if (accs.size() == MAX) {
        // Too many, enforce same postcode
        accs = [SELECT Id, Name, BillingStreet, BillingPostalCode, BillingCity, BillingCountry
                FROM Account
                WHERE BillingCountry = :Country AND BillingPostalCode = :Postcode
                LIMIT :MAX];
    }
    if (accs.size() == 0) {
        // None, try same city
        system.debug('Fuzzy pre-selected none!');
        accs = [SELECT Id, Name, BillingStreet, BillingPostalCode, BillingCity, BillingCountry
            FROM Account
            WHERE BillingCountry = :Country AND BillingCity = :City
            LIMIT :MAX];
    } else if (accs.size() == MAX) {
        // Too many, enforce city
        system.debug('Fuzzy postcode pre-selected too many!');
        accs = [SELECT Id, Name, BillingStreet, BillingPostalCode, BillingCity, BillingCountry
                          FROM Account
                          WHERE BillingCountry = :Country AND BillingPostalCode = :Postcode AND BillingCity = :City
                          LIMIT :MAX];
    }
    system.debug('Fuzzy accounts Pre-selected ' accs.size());

    if (accs.size() == MAX) {
        // Too many, enforce name
        system.debug('Fuzzy postcode and city pre-selected too many(2)!');
        string Name4 = Name1.substring(0, 4) '%';
        accs = [SELECT Id, Name, BillingStreet, BillingPostalCode, BillingCity, BillingCountry
                          FROM Account
                          WHERE BillingCountry = :Country AND BillingPostalCode = :Postcode AND BillingCity = :City
                            AND Name LIKE :Name4
                          LIMIT :MAX];
    }
    system.debug('Fuzzy accounts pre-selected ' accs.size());

    String Name10   = phonetic(Name1, 10);
    String Street10 = phonetic(Street, 10);
    String City10   = phonetic(City, 10);
    system.debug('Name10=' Name10 ', Street10=' Street10 ', City10=' City10);

    String Name4   = phonetic(Name1, 4);
    String Street4 = phonetic(Street, 4);
    String City4   = phonetic(City, 4);
    system.debug('Name4=' Name4 ', Street4=' Street4 ', City4=' City4);

    for (Account acc: accs) {
        system.debug(acc);
        Boolean SameName     = (acc.Name == Name1);
        // SF Street is multi-line, if SAP Street is in there somewhere it's a match
        Boolean SameStreet   = (acc.BillingStreet == Street || ops(acc.BillingStreet).indexOf(Street)>=0);
        Boolean SameCity     = (acc.BillingCity == City);
        Boolean SamePostcode = (acc.BillingPostalCode == Postcode);
        system.debug('SameName=' SameName ', SameStreet=' SameStreet ', SameCity=' SameCity ', SamePostcode=' SamePostcode);

        Boolean Like10Name   = (phonetic(acc.Name, 10) == Name10);
        Boolean Like10Street = (phonetic(acc.BillingStreet, 10) == Street10);
        Boolean Like10City   = (phonetic(acc.BillingCity, 10) == City10);
        system.debug('Like10Name=' Like10Name ', Like10Street=' Like10Street ', Like10City=' Like10City);

        Boolean Like4Name   = (phonetic(acc.Name, 4) == Name4);
        Boolean Like4Street = (phonetic(acc.BillingStreet, 4) == Street4);
        Boolean Like4City   = (phonetic(acc.BillingCity, 4) == City4);
        system.debug('Like4Name=' Like4Name ', Like4Street=' Like4Street ', Like4City=' Like4City);

        if (SameName && SameStreet && SameCity && SamePostcode)
          acc.AnnualRevenue = 99;
        else if (SameName && SameStreet && SameCity) acc.AnnualRevenue = 98;
        else if (SameName && SameStreet && ( SameCity || SamePostcode )) acc.AnnualRevenue = 97;
        else if (SameName && Like10Street && SameCity && SamePostcode)   acc.AnnualRevenue = 96;
        else if (Like10Name && SameStreet && SameCity)            acc.AnnualRevenue = 95;
        else if (SameName && SameCity && SamePostcode)            acc.AnnualRevenue = 94;
        else if (SameName && SameStreet && Like10City)            acc.AnnualRevenue = 94;
        else if (SameName && Like10Street && Like10City)          acc.AnnualRevenue = 93;
        else if (Like10Name && SameStreet && Like10City)          acc.AnnualRevenue = 92;
        else if (Like10Name && Like10Street && SameCity)          acc.AnnualRevenue = 91;
        else if (Like10Name && Like10Street && Like10City)        acc.AnnualRevenue = 90;
        else if (SameName && Like4Street && SameCity)             acc.AnnualRevenue = 86;
        else if (Like4Name && SameStreet && SameCity)             acc.AnnualRevenue = 85;
        else if (SameName && SameStreet && Like4City)             acc.AnnualRevenue = 84;
        else if (SameName && Like4Street && Like4City)            acc.AnnualRevenue = 83;
        else if (Like4Name && SameStreet && Like4City)            acc.AnnualRevenue = 82;
        else if (Like4Name && Like4Street && SameCity)            acc.AnnualRevenue = 81;
        else if (Like4Name && Like4Street && Like4City)           acc.AnnualRevenue = 80;
        else if (SamePostcode && Like4City && Like10Name && country != 'China' && country != 'Korea' && country != 'Japan')
          // Same postcode, similar city(4), similar name(10)
          acc.AnnualRevenue = 80;
        else if (SameName && SameCity)
          // Different postcode, same name, same city (postcode misspelled)
          acc.AnnualRevenue = 80;
        else if (SameCity && SameStreet)
          // Same postcode, city && street - name might be misspelled
          acc.AnnualRevenue = 79;
        else if (SameName && SameStreet)              acc.AnnualRevenue = 74;
        else if (Like4Name && Like4Street)            acc.AnnualRevenue = 73;
        else if (Like4Street && SameCity)             acc.AnnualRevenue = 72;
        else if (Like4Name && Like4City)              acc.AnnualRevenue = 71;
        else {
          // Probably not a duplicate
          system.debug('PROB=0');
          continue;
        }
        system.debug('PROB=' acc.AnnualRevenue);

        result.add(acc);
    }

    return result;
}

Use Case

// Check account newAcc against all Accounts
Account[] accs = Fuzzy.AccountsByAddress(
                   newAcc.Name,
                   ,newAcc.BillingStreet
                   ,newAcc.BillingPostalCode
                   ,newAcc.BillingCity
                   ,newAcc.BillingCountry
                 );

About

No description, website, or topics provided.

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published

Languages