Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Ronald L McVicar Jr]

Up

0

Down

::

I think the USPS has a validation API portal: Web Tools APIs | USPS

Also internally using SQL and Management Studio or Power BI Reports with dataset similar to the following you can do some of this validation and evaluate some of the efforts.

————————————————-

/****** Script for Contact: estimate effort for data validation for E-mail Addresses ******/

SELECT COUNT(*) AS [TotalContacts], COUNT(CASE WHEN [E-Mail] IS NULL THEN 1 END) AS [NullEmailCount], COUNT(CASE WHEN [E-Mail] NOT LIKE ‘%@%._%’ THEN 1 END) AS [InvalidEmailCount], COUNT(CASE WHEN [E-Mail] IS NOT NULL AND [E-Mail] LIKE ‘%@%._%’ THEN 1 END) AS [ValidEmailCount], COUNT(CASE WHEN [E-Mail 2] IS NULL THEN 1 END) AS [NullEmail2Count],

COUNT(CASE WHEN [E-Mail 2] NOT LIKE ‘%@%._%’ THEN 1 END) AS [InvalidEmail2Count],

COUNT(CASE WHEN [E-Mail 2] IS NOT NULL AND [E-Mail 2] LIKE ‘%@%._%’ THEN 1 END) AS [ValidEmail2Count]

FROM [NAV16].[dbo].[nvdbcompany$Contact]

WHERE ([E-Mail] IS NOT NULL OR [E-Mail 2] IS NOT NULL)

AND ([E-Mail] NOT LIKE ‘%@%._%’ OR [E-Mail 2] NOT LIKE ‘%@%._%’)

————————————————-

/****** Script for Contact: estimate effort for data validation other than e-mail addresses ******/

SELECT COUNT(*) AS [TotalContacts], COUNT(CASE WHEN [Address] = ” THEN 1 END) AS [BlankAddressCount], COUNT(CASE WHEN [City] = ” THEN 1 END) AS [BlankCityCount],

COUNT(CASE WHEN [Country_Region Code] = ” THEN 1 END) AS [BlankCountryCodeCount],

COUNT(CASE WHEN [Post Code] = ” THEN 1 END) AS [BlankPostalCodeCount],

COUNT(CASE WHEN [Address] IS NULL THEN 1 END) AS [NullAddressCount],

COUNT(CASE WHEN [City] IS NULL THEN 1 END) AS [NullCityCount],

COUNT(CASE WHEN [Country_Region Code] IS NULL THEN 1 END) AS [NullCountryCodeCount],

COUNT(CASE WHEN [Post Code] IS NULL THEN 1 END) AS [NullPostalCodeCount]

FROM [NAV16].[dbo].[nvdbcompany$Contact]

————————————————-

/****** Script for Contact: names, addresses, and phone/mobile numbers ******/

SELECT [Name], [Address], [Phone No_], [Mobile Phone No_], COUNT(*) AS [DuplicateCount]

FROM [NAV16].[dbo].[nvdbcompany$Contact]

WHERE [Name] IS NOT NULL

AND [Address] IS NOT NULL

AND ([Phone No_] IS NOT NULL OR [Mobile Phone No_] IS NOT NULL)

GROUP BY [Name], [Address], [Phone No_], [Mobile Phone No_]

HAVING COUNT(*) > 1

ORDER BY [DuplicateCount] DESC

————————————————-

usps.com

Web Tools APIs | USPS

USPS Web Tools APIs let ecommerce website and shipping software developers access USPS data and features free of charge. Check shipping rates, verify addresses, track packages, schedule package pickups, and more through USPS Web Tools.