web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
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

I have the same question (0)
  • Ron Draganowski Profile Picture
    Ron Draganowski 1,575 on at

    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!

  • MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    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.

  • K Day Profile Picture
    K Day 7,365 on at

     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.

  • MG-16101311-0 Profile Picture
    MG-16101311-0 26,225 on at

    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

     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."

     

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
Community Member Profile Picture

Community Member 2

#2
mtabor Profile Picture

mtabor 1

#2
Victoria Yudin Profile Picture

Victoria Yudin 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans