Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Best place to put vali...
Microsoft Dynamics GP (Archived)

Best place to put validation to check if invoice exists in another database

(0) ShareShare
ReportReport
Posted on by bitshift 860

Im new to GP and Dynamics in general and its come up that since we have multiple databases (due to multiple companies), there needs to be something that before a new invoice is entered into the system a user is logged into, that a check is done against the other system(s) to see if its already been entered there.  Someone suggested building a web app that would do this, but it seems like this would be something that would be done as an event that is fired from the GP form in question, where the invoice was being entered.  Does it not?  So that implies making changes to the form itself - which means getting the GP SDK for Visual Studio etc.  I am a developer, so this would be something I could handle...assuming I learn the basics of GP first.  

*This post is locked for comments

  • Suggested answer
    soma Profile Picture
    soma 24,408 on at
    RE: Best place to put validation to check if invoice exists in another database

    VBA is the best choice for achieve your requirement with quicker time. But, If you using GP 2013 Web client, then VBA is no longer support. So, you can achieve the same by using .Net or Dexterity(Create Stored Procedure and call it in Dexterity to verify the invoices number with multiple databases.)

    Hope this helps!!!

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Best place to put validation to check if invoice exists in another database

    If you have the VBA registered, you can do this in a couple of minutes, add the form to the VBA, add the document number field to the VBA, and write a code on the field leave event to connect to the database and check for invoice duplication, you can search the web on how to use ADO with VBA which can help, generate a message for end user that the invoice is already exist and refocus the field back.

    Same approach could be used using VS Toolkit but it might take a bit longer, register the events for your field on the launching of GP and write your .Net code that performs this validation.

    Read about the above and start trying by your self then send your findings to help you more.

  • Heather Roggeveen Profile Picture
    Heather Roggeveen 9,144 on at
    RE: Best place to put validation to check if invoice exists in another database

    Hi

    I am not a programming expert so I can't give you much in the way of technical specifics, however, I can point you in the right direction for some toolkits that could be useful.

    In order to compare across the databases, you would need to ensure that the Vendor card set up is the same in all databases.  Using the Professional Services Toolkit is a good way to ensure this.  You can set one database to be the master and set up the triggers to ensure that when a supplier is added to the master database, it replicates across the rest.

    Then you would need to look at a trigger that compares what is being entered in a single database across all of them with the combination of VENDORID and Document Number.  You would need to ensure some business rules in place about entering invoice numbers (good practise anyway) to make sure that it is entered how it is shown on the page - even if that means something like INV00000000123 where you need to count the zeros.

    The Support Debugging Tool has functionality in it to help with developing and understanding triggers.  Aside from knowing it is there, I couldn't give you any details of how to use it.  There is a manual that goes with the Support Debugging Tool that seems to have some good details on developing triggers.

    Hopefully that gets you moving in the right direction.  

    Cheers

    Heather

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,735 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,466 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans