Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Integration Manager -V...
Microsoft Dynamics GP (Archived)

Integration Manager -VBScript - Get the Value of an Auto Generated Number

(0) ShareShare
ReportReport
Posted on by K Day 7,365

I am working on some VB Script that calls a stored Procedure to insert records into Extender Tables for a Purchase Order.  I am using the After Document script.  It works fine when I explicitly call out a PO Number in my Source File, because one of the Parameters of the procedure id PONumber, so I can use the SourceFields("PONumber") and get that value.  But what if I wanted to not map that PO number and just have GP generate my next PO Number based on the system?  How can I fetch that number that it creates for example "PO00008" because that is the next PO number based on setup. 

*This post is locked for comments

  • K Day Profile Picture
    K Day 7,365 on at
    Re: Re: Re: Re: Integration Manager -VBScript - Get the Value of an Auto Generated Number

     I'm still using a field called PONumber, but just giving them a sequential number just as an identifier to link the 2.  What I was thinking you were saying was to query the database for the transaction that I just entered and then return the PO Number.  What I was trying to do was dynamically build a select statement something like

    SELECT TOP 1 PONUMBER FROM POP10110 WHERE ITEMDESC = '"&SourceFields("PO Line - Test.ITEMDESC")&"' AND VENDORID = '"&SourceFields("PO Line - Test.VENDORID")&"' AND ITEMNMBR = '"&SourceFields("PO Line - Test.ITEMNMBR")&"'"

    ...which would return my PO Number, and then plug that into my Stored procedure as parameter.  But, now I can't quite figure out how to return that PO Number to my script (set a variable to that PO Number).  I got it to execute the Procedure, so I'm almost there.

    However, I looked in the documentation and it says "The SQL statement you execute should not produce a result set."

     

  • Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    Re: Re: Re: Integration Manager -VBScript - Get the Value of an Auto Generated Number

    I guess it's more along the lines of, what field links your PO header and your PO detail in your source queries? That's the field that should be passed to GP, preferably at the header level of the PO.

  • K Day Profile Picture
    K Day 7,365 on at
    Re: Re: Integration Manager -VBScript - Get the Value of an Auto Generated Number

     Thanks, good advice.  I will take your method Mariano and start figuring out what I need to do.  The nice thing is that the item description will be unique (non-PO items) with a combination of a name, an ID, and some dates, so I can focus on that.

  • Mariano Gomez Profile Picture
    Mariano Gomez 26,225 on at
    Re: Integration Manager -VBScript - Get the Value of an Auto Generated Number

    There is always a way if you understand what needs to be done. Your source data must be related by some common key -- this is always the case. You can map that key to some destination field in your purchasing integration, for example a description field and use VBScript to open a connection to the database to look for this key in your after script. If the key is found, you can retrieve the associated PO number.

    You can then pass the retrieved PO to your stored procedure for it to do all the other stuff it needs to do. I have done this hundreds of times with IM. I can't say I am the author of the method, but sure pioneered the technique.

  • Ron Draganowski Profile Picture
    Ron Draganowski 1,575 on at
    Re: Integration Manager -VBScript - Get the Value of an Auto Generated Number

    You're not going to like my response :)

    I ran into this a few years ago, and I came to the conclusion at the time that there was no way to get the IM auto-generated number.  Yes, there are some risky tricks, like get the TOP 1 POP10100.PONUMBER FROM POP10100 (NOLOCK) ORDER BY DEX_ROW_ID DESC.  I bet in almost every case that would work, but someday if someone is creating a PO at the same time you run your integration, you might get the wrong one.  In my opinion, not worth the risk.

    So, unless there's a new feature in IM that I don't know about (please let me know), I believe the best practice is to get the next PO number yourself in the Before Document script, store it in a global variable, and use it the PO number field script and in the after document script.  There is a SQL stored procedure "taGetPONextNumber" that will simplify that for you.

     Good luck!

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans