Forum Discussion

AnthonyGuillen1's avatar
AnthonyGuillen1
Qrew Cadet
2 years ago

Fuzzy Match / Approximate Match in Pipelines?

Is there any functionality in Pipelines or the QB API that allows for fuzzy matching? 

I've been reading that there some APIs that have built-in fuzzy matching (Reltio, Search - Get Search Fuzzy - REST API (Azure Maps), Fuzzy matching | Dialogflow ES | Google Cloud), but could not find any such mention in the QB API documentation.

I have a table of Vendors (about 4,000 records) that is being updated from our ERP using the key field, Internal ID. We often have to manually add "prospective" vendors to the QB application that haven't yet been added to the ERP. For those, an Internal ID well beyond the range of actual Internal IDs is used. 

A fraction of those "prospective" vendors get added as actual vendors to the ERP, which end up being duplicates in QB since their Internal IDs won't match.

Yes, one option is to create the prospective vendors to the ERP first, but that's just not an option with our company's established controls.

So, I was hoping I could build a pipeline that retrieve the list of vendors from QB vendor table, that are in that special Internal ID range, and compare their "Name" to the "Name" of vendors under the Internal ID range, and generate an exception report of potential duplicates that would go to and admin, to then be manually merged if appropriate.

But of course the match shouldn't be exact, because it's not likely we'd have the vendor's name during the "prospect" phase. E.g:

  • 3 Margaritas Restaurant
  • Three Margaritas Restaurant
  • Three Margaritas Restaurant, LLC
  • Margarita's Restaurant

Absent a built in fuzzy match operator in the QB API, anyone know of any other ways of doing this? Apparently there's code code scripts that can do it, but not sure if pipelines has the ability to run code.

-Anthony



------------------------------
Anthony Guillen
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    8 months ago

    My suggestion is to see if you can get part way there by trying a match in say the first 8 characters in the company name. I seem to recall that 8 is decent number to start with.  Also is there any chance you have access to a phone number?  That is also a great way to match, or a partial address like city name.  So if you get a match in first 8 of the name and the same city or zip , then more likely the proposed match is valid.