300
I have 10's of thousands of items that I want to set the type to "Discontinued" in GP2010. Obviously, I don't want to do this one-by-one by hand!? Can I simply use SQL to update a table/field and be done with it?
*This post is locked for comments
Is that triggered off the change? So, if I were to update the field via SQL, would all this still happen based off a trigger?
Here is profiler trace when an item is discontinued from GP
declare @p1 int
set @p1=10
exec sp_prepexec @p1 output,N'@P1 smallint,@P2 datetime2,@P3 smallint,@P4 datetime2,@P5 char(32)',N'BEGIN UPDATE TWO.dbo.IV00101 SET ITEMTYPE = @P1, MODIFDT = @P2 WHERE ITEMTYPE = @P3 AND MODIFDT = @P4 AND ITEMNMBR = @P5 SELECT @@rowcount END ',2,'2013-11-15 00:00:00',1,'2017-01-15 00:00:00','1-A3261A '
select @p1
exec TWO.dbo.zDP_IV00400SS_1 '1-A3261A'
exec TWO.dbo.zDP_IV00400SS_1 '1-A3261A'
BEGIN DECLARE @num int EXEC TWO.dbo.zDP_IV00400SI '1-A3261A', 1, 1, 1, 1, @num OUT SELECT @num END
BEGIN DECLARE @num int EXEC TWO.dbo.zDP_IV00400SI '1-A3261A', 1, 1, 1, 1, @num OUT SELECT @num END
exec TWO.dbo.zDP_IV00401L_2 '1-A3261A',-2147483648,'1-A3261A',2147483647
exec TWO.dbo.zDP_IV00401L_2 '1-A3261A',-2147483648,'1-A3261A',2147483647
exec ##zDP_1972403F_2 '1-A3261A',-2147483648,'1-A3261A',2147483647
Opps - lot more is going on than update to one field on IV00101
Cheers!
Sanjay
Galina,
You are missing the point, by the way I will be having a beer!!!
Cheers!
Sanjay
I totally agree that it would be nice to do it through "recommended practices". But, if I can be assured that doing this update via SQL is "good enough", that would save me a LOT of time. I'm leaning towards doing it the SQL way...I guess I'll be prepared to do an undo if things get out of sync... Thanks everyone for your insight!
Well, Sanjay, it is true, you need to do things proper way.
If it wasn't for a fact that Jason needs to update 10's of thousands of items, I wouldn't recommend direct SQL update. I would expect the macro to run for a day ot two, if not three. IM will take several hours.
It could be fun watching, though, you right there!
My take on updating with direct TSQL statements
1) Are you sure it the only table updated ?
2) When there are relatively simple options to do it right way, why do it the wrong way
3) Always ask the question - is it the supported method
Finally - What is the fun in just executing a TSQL :).
Any way TGIF , Happy Friday!
Cheers!
Sanjay
Mr. Jason
I have personally changed the Item Type on the database level several times with no negative ramifications at all. Especially that the case is just to turn the flag of Item Type field as previously mentioned by Galina.
Although, the point that is made is not to go on SQL and work around with the tables unless you are quite aware of what's being done.
The bottom line is, you got many options to take depending on your experience. So as Galina said above, if you are comfortable with SQL, go ahead with the update statement. Be aware of the "WHERE" in your update, since you said thousands of items, then you shall be quite cautious not to update any other records by mistake, you could use the Dex_Row_Id in order to be quite precise.
Galina, that's exactly what my original question was...can I just change the item type? I was concerned that there might be other things that needed to be changed as well. By using the GP interface, econnect or integration service, I was assuming that any "other stuff" would be taken care of. Can you confirm that nothing else occurs? If so, then I can easily just change the itemtype...
Changing item type to Discontinued is literally just changing a ITEMTYPE field named in IV00101 table. If I was tasked with the update, I'd use SSIS to upload the item list from Excel or a text file in a table and run the statement:
UPDATE IV00101 SET ITEMTYPE = 2 FROM IV00101 I INNER JOIN T ON I.ITEMNMBR = T.ITEM
Done!
I"m assuming, you comfortable with SQL...
When it comes to knowledge workers who are not quite accustomed to using SQL, any update statement on key tables such as (IV00101) might be risky. In this essence, I would highly recommend using the Macro.
To use a Macro, you consider one of the following methods;
- Under the First Column (Item Number), list down you Item list to be discontinued.
- Under Macro Header, copy the following;
="# DEXVERSION=12.00.0270.000 2 2
CheckActiveWin dictionary 'default' form 'IV_Item_Maintenance' window 'IV_Item_Maintenance' "
- Under Macro Detail, copy the following:
="MoveTo field 'Item Number'
TypeTo field 'Item Number' , '"&TRIM(A2)&"'
MoveTo field Inactive # 'FALSE'
MoveTo field 'Item Type' item 1 # 'Sales Inventory'
ClickHit field 'Item Type' item 2 # 'Discontinued'
MoveTo field 'Save Button'
ClickHit field 'Save Button'
"
- Drag and drop the Macro Detail cell to include all the item list.
- Copy the whole column (Macro Header and Detail), into Word document, to paste the format.
- Finally, copy the the Code from the word document into text notepad, the extension of the text file is (.txt)
- Change the extension to be (.mac)
- Now you have the file ready to be played from the Screen.
Helping note:
If you are not quite accustomed with the steps above, I would recommend applying either scenarios (Mail merge or Excel) on a test environment.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (