VALIDATE GL ACCOUNT PRIOR TO RUNNING MAP
Brenda Hopkins asked 4 years ago
Is there a way to validate that the GL Account in the EXCEL file is actually in GP prior to running the map?
Answers
Patrick Roth Staff answered 4 years ago
Yes, you would use the Map Pre Tasks | Map Data Checks task to write a data validation for any data you would like in your map.
Brenda replied 4 years ago This is great, is there a way to print this?
thanks
Jody answered 4 years ago
Brenda,
I hope this isn’t too elementary, but here is what I did to accomplish this task:
1) Create a SQL view to display only active accounts
CREATE VIEW ACTIVE_ACCOUNTS
SELECT A.ACTINDX, RTRIM(A.ACTNUMST) ACTNUMST, B.ACTDESCR
FROM GL00105 A
INNER JOIN GL00100 B
ON A.ACTINDX = B.ACTINDX
WHERE B.ACTIVE = 1
2) Create the validation check in your map as outlined by Patrick above (I called mine ACCOUNT_VALIDATE)
3) You’ll need to do a User Defined Query
The way I do this:
Pick a random (small) table from the list
Click on Modify
Delete the random table
Scroll down and select the view we created above
4) Valid if: Data Exists
5) Add criteria Source Field: (Your account number column) = Target Field (ACTNUMST)
6) To notify the end user, set up an email task in Map Post Tasks>Tasks that run if map fails>TaskSendMail
Check the box to Include Validation Errors
Hope this helps.
I hope this isn’t too elementary, but here is what I did to accomplish this task:
1) Create a SQL view to display only active accounts
CREATE VIEW ACTIVE_ACCOUNTS
SELECT A.ACTINDX, RTRIM(A.ACTNUMST) ACTNUMST, B.ACTDESCR
FROM GL00105 A
INNER JOIN GL00100 B
ON A.ACTINDX = B.ACTINDX
WHERE B.ACTIVE = 1
2) Create the validation check in your map as outlined by Patrick above (I called mine ACCOUNT_VALIDATE)
3) You’ll need to do a User Defined Query
The way I do this:
Pick a random (small) table from the list
Click on Modify
Delete the random table
Scroll down and select the view we created above
4) Valid if: Data Exists
5) Add criteria Source Field: (Your account number column) = Target Field (ACTNUMST)
6) To notify the end user, set up an email task in Map Post Tasks>Tasks that run if map fails>TaskSendMail
Check the box to Include Validation Errors
Hope this helps.
Jody replied 4 years ago Not sure what happened to the formatting, but you get the gist 😉