Address validation in NAV/BC

  • Address validation in NAV/BC

    Posted by Ahmed Seghier on June 30, 2023 at 11:36 am

    Hi fellow NAV/BC enthusiasts,

    I hope you’re all doing well! I’m currently working on implementing address validation for order entry in NAV 2017, and I’m seeking your valuable recommendations and insights on the best approach or solution for this task.

    Here are a few specific points I’d like to address:

    1. Integration Options: I would like to explore different integration options to seamlessly incorporate address validation into the order entry process. Are there any third-party APIs or services that have proven to be effective in NAV 2017 for this purpose?
    2. Validation Accuracy: Accuracy is key when it comes to address validation. I’m interested in hearing about your experiences with different address validation solutions and their accuracy rates. Which solutions have provided the most reliable results in terms of validating addresses?
    3. Ease of Implementation: As we’re working with NAV 2017, I’m particularly interested in solutions that are compatible and relatively easy to implement within this specific version. Are there any specific considerations or challenges that I should be aware of when implementing address validation in NAV 2017?
    4. User Experience: Considering the impact on our order entry team, it would be helpful to hear about solutions that offer a seamless and intuitive user experience. How easy is it for our team members to work with the address validation solution during order entry? Any specific features or functionalities that we should look for in this regard?
    5. Cost Considerations: Of course, cost is an important factor in any implementation. I’m interested in hearing about cost-effective solutions that provide a good balance between functionality and affordability.

    I would greatly appreciate any recommendations, personal experiences, or insights you can provide regarding the best approach or solution for implementing address validation in NAV 2017 order entry. Your expertise will be invaluable in helping us make an informed decision and ensure a successful implementation.

    Thank you in advance for your time and assistance.

    Ronald L McVicar Jr replied 11 months, 1 week ago 2 Members · 1 Reply
  • 1 Reply
  • Ronald L McVicar Jr

    Member
    July 18, 2023 at 3:08 pm
    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

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

The discussion ‘Address validation in NAV/BC’ is closed to new replies.

Start of Discussion
0 of 0 replies June 2018
Now

Welcome to our new site!

Here you will find a wealth of information created for people  that are on a mission to redefine business models with cloud techinologies, AI, automation, low code / no code applications, data, security & more to compete in the Acceleration Economy!