Validate and Insert/Update Vendor Emails in Microsoft Dynamics GP from a Text File

Microsoft Dynamics GPI’ve previously posted a script which could be used to update emails in Microsoft Dynamics GP from a tab delimited text file. I’ve recently been working on a project with a client and created a variation on that script which works only for vendor emails, but validates the data before inserting or updating to ensure that the data is valid.

The original script took it on trust that the data was correct, but this tie we had some concerns about the quality of data being provided so I added validation to ensure the vendor and vendor address both existed before any data was inserted or updated; if there were any errors found, the errors were presented to the user to fix and then rerun the script.

The highlighted section is the path to the text file being uploaded; regardless of where you are running SSMS, this path and file must exist on the SQL Server itself.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk) This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int). */
/* CREATE TEMP ERROR TABLE */ CREATE TABLE #Errors ( Error VARCHAR(1000) ,ROW_ID INT IDENTITY ) GO /* CREATE TEMP TABLE FOR DATA */ CREATE TABLE #SY01200_IMPORT ( VENDORID VARCHAR(100) ,ADRSCODE VARCHAR(100) ,EmailToAddress VARCHAR(1000) ,EmailCcAddress VARCHAR(1000) ,EmailBccAddress VARCHAR(1000) ) GO /* BULK INSERT */ BULK INSERT #SY01200_IMPORT FROM 'c:\temp\email.txt' WITH ( FIELDTERMINATOR = '\t' ,ROWTERMINATOR = '\n' ,FIRSTROW = 2 ) GO /* VALIDATE DATA */ -- validate creditors INSERT INTO #Errors (Error) ( SELECT DISTINCT 'Vendor does not exist: ' + CAST(SYI.VENDORID AS VARCHAR(100)) FROM #SY01200_IMPORT AS SYI LEFT JOIN PM00200 AS ['PM Creditor Master File'] ON ['PM Creditor Master File'].VENDORID = SYI.VENDORID WHERE ['PM Creditor Master File'].VENDORID IS NULL ) GO --validate creditor addresses INSERT INTO #Errors (Error) ( SELECT DISTINCT 'Vendor address does not exist: ' + CAST(SYI.VENDORID AS VARCHAR(100)) + ' / ' + CAST(SYI.ADRSCODE AS VARCHAR(100)) FROM #SY01200_IMPORT AS SYI LEFT JOIN PM00300 AS ['PM Creditor Addresses'] ON ['PM Creditor Addresses'].VENDORID = SYI.VENDORID AND ['PM Creditor Addresses'].ADRSCODE = SYI.ADRSCODE WHERE ['PM Creditor Addresses'].VENDORID IS NULL ) GO /* UPDATE EXISTING DATA IF NO ERRORS */ IF (SELECT COUNT(*) FROM #Errors) = 0 UPDATE SY SET SY.EmailToAddress = ISNULL(SY_I.EmailToAddress, '') ,SY.EmailCcAddress = ISNULL(SY_I.EmailCcAddress, '') ,SY.EmailBccAddress = ISNULL(SY_I.EmailBccAddress, '') FROM SY01200 SY INNER JOIN #SY01200_IMPORT AS SY_I ON SY_I.VENDORID = SY.Master_ID AND SY.Master_Type = 'VEN' AND SY.ADRSCODE = SY_I.ADRSCODE GO /* INSERT NEW DATA IF NO ERRORS */ IF (SELECT COUNT(*) FROM #Errors) = 0 INSERT INTO SY01200 ( Master_Type ,Master_ID ,ADRSCODE ,EmailToAddress ,EmailCcAddress ,EmailBccAddress ,INETINFO ) ( SELECT 'VEN' ,VENDORID ,ADRSCODE ,ISNULL(EmailToAddress, '') ,ISNULL(EmailCcAddress, '') ,ISNULL(EmailBccAddress, '') ,'' FROM #SY01200_IMPORT WHERE (SELECT COUNT(Master_ID) FROM SY01200 WHERE Master_Type = 'VEN' AND Master_ID = #SY01200_IMPORT.VENDORID AND ADRSCODE = #SY01200_IMPORT.ADRSCODE) = 0 ) GO /* OUTPUT ERRORS */ IF (SELECT COUNT(*) FROM #Errors) > 0 SELECT Error FROM #Errors ORDER BY ROW_ID GO /* DROP TEMP TABLES */ DROP TABLE #SY01200_IMPORT GO DROP TABLE #Errors GO