How do I write a query that will auto fill the Inventory Item Account Maint. "Drop Ship Itmes" field?
TY
Mary
*This post is locked for comments
It would be a lot safer to do this via an Item Class "roll down" change in the Dynamics GP client (front end). Go to an Item Class. Only change the Drop Ship Account. Answer yes to roll down. Repeat for other Classes. Done (safely).
If this is un-acceptable please reply and I can help.
Tim
Isn't this for initial setup of an item? Will it auto fill the current inventory? Do I have to creat a new Item Class and Description to enable this?
Classes do help a lot when setting up new items. They are also useful when needing to make mass changes to existing items. If you edit an existing class setting and answer yes to the "roll down" window - only the changed settings will be written to all of the items in that class. Other settings should not be affected. That's why you need to make sure you only change the Drop Ship account when editting the class.
This functionality exists and is "enabled" by design. Creating a new class won't help you, as the new class won't be assigned to any items.
If you have a test company give it a try.
I just looked at one of our inventory items nad Class ID is blank
Well that's not encouraging...
You could do these changes throught the front end via a Macro. See David Musgrave's blog post:
http://blogs.msdn.com/b/developingfordynamicsgp/archive/2008/10/30/how-to-use-word-mail-merge-and-macros-to-import-data.aspx
If you are still wanting SQL....
Step by Step:
1) Make a backup of the database
2) Read step 1 again. Maybe make a second backup.
3) With no one else logged in, from your company database, determine the account Index of the target account
SELECT [Account Number],[Account Description],[Account Index] FROM Accounts WHERE [Account Number]='XXX-XXX-XXX'
Replace XXX-XXX-XXX whith your target Account
4) Check impact of what you are going to do
SELECT ITEMNMBR,ITEMDESC,DPSHPIDX FROM IV00101 WHERE....
You will have to perfect your WHERE clause
5) Change the SELECT stuff to UPDATE
UPDATE IV00101 SET DPSHPIDX=XXX WHERE...
Replace XXX with with the Index you found in step 3. Remeber to specify the WHERE conditions!
6) You may want to change only one item to test. Check in the results in the front end.
I can't emphasize enough the risks in doing this from the back end. TEST...TEST...TEST!!!!!
I would like to make a slight change to the sql script provided above by Tim, in order to correctly get the accounts from GP you need to actually read 2 tables, here is the script:
Select B.Actnumst, A.Actdescr, B.Actindx from GL00105 B join GL00100 on A.Actindx = b.ActIndx where B.Actnumst = 'XXX-XXXX-XX'
This way you will be able to read properly the accounts.
Have a great day
The [Accounts] view in a DynamicsGP company has that already. Script the view. I'm trying to keep it simple.
One learns something new every day!!!!
I appreciate ALL your responses, but, I am leary of doing too much in SQL as I know it can be messed up easily and this method above it a bit more complicated than I want to get.
It there a script that will pull up the inventory showing this field and I can go in an plug in the account number myself. At least this would be easier than doing it item by item as I have over 1300 in inventory.
SELECT ITEMNMBR AS [Item Number],ITEMDESC AS [Item Description],
(SELECT ISNULL([Account Number],'') FROM [Accounts]
WHERE [Account Index]=IV00101.DPSHPIDX) AS [Drop Ship Account]
FROM IV00101
This will give you all Inventory Items.
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.
As AI tools become more common, we’re introducing a Responsible AI Use…
We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…
These are the community rock stars!
Stay up to date on forum activity by subscribing.
