Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Jeff Roe]

We are going through a cleanup of inventory items and we have thousands of items where the GP Item number should be something other than what was originally created.

I know that the PSTL tool has a function to modify a a GP Item Number to something else and does it against IV, SOP, POP etc. While this process may be sufficient for one off’s, it would likely take a great deal of time for thousands of parts.

Has anyone created a SQL process to do this instead?

I have identified what tables I think would need to be changed and have run against a single part and updated in IV, SOP, POP tables active and history and appears to have not hurt anything but am cautious to say it will work in a bigger scale.

If I were to chose a SQL solution what else would I need to consider related to creating orphaned items or breaking GP business rules.

BTW, the assumption would be that the new item number does not currently exist so there should not be any primary key or line item sequence related issues.

Any help or thoughts is greatly appreciated.

——————————
Jeff Roe
Kele Inc
Bartlett TN
——————————

[Kristen Hosman]

Hi

You can actually create a txt file to do mass changes.  See instructions below.  Let me know if you have any questions.

To modify a range of Item Numbers via a spreadsheet:

Create a spreadsheet
Enter existing Item Number in first column
Enter the new Item Number in the second column
Save as a text file
Start the PSTL function in Dynamics GP
Select the Item Number Modifier option
Click Next
Select the Import option
Select the Validate button
Select the spreadsheet text file
Select Open
If no errors, Select the Modify button
?

——————————
Kristen Hosman
Dynamics ERP Consultant
Mount Evans Consulting
——————————
——————————————-

[Jeff Roe]

Sorry, I do know that I can do en mass with the PSTL tool but my concern is that to do thousands it would be a long process and much faster if I could use basic SQL.

——————————
Jeff Roe
Kele Inc
Bartlett TN
——————————
——————————————-

[Andrew Kennedy]

Jeff,
I would stick with using the PSTL tool. 
I have a script that loops through information_schema table and finds all tables that have ITEMNMBR in it.  But then realized that not all the tables with the item number are called ITEMNMBR.  Such as the BOM component table CPN_I and PPN_I.  
I believe the PSTL modifier is actually running SQL scripts, which is why you need to be sa or db_admin to run it.  If you have a GP client on the server run it there off hours and it shouldn’t take much longer than running direct SQL.

——————————
Andrew Kennedy
Principal Consultant
Velosio
Seven Hills OH
——————————
——————————————-

[Kristen Hosman]

Hi

You should break down the data into smaller chucks to see how long it takes.  Also taking a backup before doing this is a great idea.

I’ve done this before and it’s not as slow as you would think.  As someone else noted using the PSTL tool will ensure that everything is updated correctly.

?

——————————
Kristen Hosman
Dynamics ERP Consultant
Mount Evans Consulting

——————————
——————————————-

[Blair Christensen]

Knew about the PSTL tool but didn’t know it could handle an entire list.  I’m in this same boat and this would be awesome!  Thanks!

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-

[Aleiha Hanson]

you are a life saver! I never asked much about doing a bulk number of changes…thought about maybe doing it as a macro to save my typing and clicking. NO CLUE PSTL could handle an “import” to do a big list! I’m going to try it this weekend! ?

——————————
Aleiha Hanson
Royal Basket Trucks, Inc – IT Manager
GPUG Milwaukee WI Chapter Leader
ahanson@royal-basket.com
——————————
——————————————-

[Keith Koopmans]

Good morning – 

You’ve received some great advice thus far but I’d just like to throw in another round of support for the PSTL tool.  I’ve used the similar .txt method for an extreme amount of GL code combinations (thousands and thousands) and was pleasantly surprised regarding the speed.  Some of these codes had thousands and thousands of prior uses.  The inventory item numbers may exist in more tables, but again the PSTL tool was bulletproof.

——————————
KEITH KOOPMANS
Controller
GURTLER INDUSTRIES INC
SOUTH HOLLAND IL
——————————
——————————————-

[Bruce Strom]

You should be able to record a macro that uses the PSTL tool to update one inventory item,
then use that to create a macro that updates all the inventory items.

https://blogs.msdn.microsoft.com/developingfordynamicsgp/2008/10/29/how-to-use-word-mail-merge-and-macros-to-import-data/

——————————
Bruce Strom
Programmer Analyst
Associated Grocers of Florida / Supervalu
Sunrise FL
——————————
——————————————-