Share
ReportHow 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
You’re offline. This is a read only version of the page.
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
Please tell me you're NOT doing this in a production environment.
Test on one item first.
UPDATE IV00101 SET DPSHPIDX=1325 WHERE ITEMNMBR='XXXX'
Replace XXXX with an item number. Make sure the system replies with "(1 row(s) affected.)"
Check to make sure it was updated.
SELECT ITEMNMBR,DPSHPIDX WHERE ITEMNMBR='XXXX'
Check in GP.
If you can't get this working, please use the front-end (yes, I know it's tedious).
Tim
I just ran this:
UPDATE IV00101 SET DPSHPIDX=1325
It changed ALL the data in the Drop Ship row to NULL - How do I re word it to change date to 1325
there is no way to change it from this query!
You said, "...plug in the account number myself." I thought you meant Dynamics GP. SQL Server Management Studio doesn't do that.
Tim
This pulled up the info I need, BUT, it will not let me change anything !
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.
Tim
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.
One learns something new every day!!!!
The [Accounts] view in a DynamicsGP company has that already. Script the view. I'm trying to keep it simple.
Tim
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
#1
André Arnaud de Cal...
292,494
Super User 2025 Season 1
#2
Martin Dráb
231,305
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (
