Here’s a follow-up to a previous post Programatically Construct a Dynamics GP Macro to Disassociate Items from an Inventory Site.

This time we assign a new inventory site to selected items within GP. Once again we use a SQL query to generate a GP macro that does all the tedious repetitive work.

First I did one assignment while recording it as a macro. The macro looks like this.


# DEXVERSION=12.00.0270.000 2 2
CommandExec dictionary 'default' form 'Command_Inventory' command 'IV_Item_Qtys_Locs'
NewActiveWin dictionary 'default' form 'IV_Item_Qtys_Locs' window 'IV_Item_Qtys_Locs'
TypeTo field 'Item Number' , '6-037-31050SLT-EA'
MoveTo field '(L) Display Options' item 0 # 'All'
ClickHit field '(L) Display Options' item 1 # 'Site ID:'
TypeTo field 'Location Code' , 'MAIN'
MoveTo field 'Save Button'
ClickHit field 'Save Button'
CloseWindow dictionary 'default' form 'IV_Item_Qtys_Locs' window 'IV_Item_Qtys_Locs'
NewActiveWin dictionary 'default' form sheLL window sheLL

Then I constructed a GP query to locate all the items I wished to assign to MAIN. In my case the query looked like this:

select itemnmbr
from iv00101
where itemtype=1
and itemnmbr not in (
select itemnmbr from iv00102
where locncode='MAIN')
and itemnmbr like '6-%'

6-% is a particular class of items for us. ITEMTYPE=1 limits the results to active inventory items. The other clause in the WHERE part of the query finds items that are not assigned to the MAIN inventory site.

Then, I combined my query with the macro as so:

with VariableData as (
select itemnmbr
from iv00101
where itemtype=1
and itemnmbr not in (
select itemnmbr from iv00102
where locncode='MAIN')
and itemnmbr like '6-%'
)

select 
  '# DEXVERSION=12.00.0270.000 2 2' as MacroLine,
  -1 as MacroLineNumber,
  '' as itemnmbr

union all

select
  '  CommandExec dictionary ''default''  form ''Command_Inventory'' command ''IV_Item_Qtys_Locs''' as MacroLine,
  0 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  'NewActiveWin dictionary ''default''  form ''IV_Item_Qtys_Locs'' window ''IV_Item_Qtys_Locs''' as MacroLine,
  1 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  '  TypeTo field ''Item Number'' , ''' + rtrim(itemnmbr) + '''' as MacroLine,
  2 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  '  MoveTo field ''(L) Display Options'' item 0  # ''All'' ' as MacroLine,
  3 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  '  ClickHit field ''(L) Display Options'' item 1  # ''Site ID:'' ' as MacroLine,
  4 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  '  TypeTo field ''Location Code'' , ''MAIN''' as MacroLine,
  5 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  '  MoveTo field ''Save Button'' ' as MacroLine,
  6 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  '  ClickHit field ''Save Button'' ' as MacroLine,
  7 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  'CloseWindow dictionary ''default''  form ''IV_Item_Qtys_Locs'' window ''IV_Item_Qtys_Locs'' ' as MacroLine,
  8 as MacroLineNumber,
  itemnmbr
from VariableData

union all

select
  'NewActiveWin dictionary ''default''  form sheLL window sheLL ' as MacroLine,
  9 as MacroLineNumber,
  itemnmbr
from VariableData

order by itemnmbr,macrolinenumber

As you see, each line of the macro is mapped to a UNION clause in the query. The result set looks like this (I’ll only include 2 item numbers to keep it brief):

Result Set

Result Set

Now save only the first column in a new .mac file:


# DEXVERSION=12.00.0270.000 2 2
CommandExec dictionary 'default' form 'Command_Inventory' command 'IV_Item_Qtys_Locs'
NewActiveWin dictionary 'default' form 'IV_Item_Qtys_Locs' window 'IV_Item_Qtys_Locs'
TypeTo field 'Item Number' , '6-037-31050THR-EA'
MoveTo field '(L) Display Options' item 0 # 'All'
ClickHit field '(L) Display Options' item 1 # 'Site ID:'
TypeTo field 'Location Code' , 'MAIN'
MoveTo field 'Save Button'
ClickHit field 'Save Button'
CloseWindow dictionary 'default' form 'IV_Item_Qtys_Locs' window 'IV_Item_Qtys_Locs'
NewActiveWin dictionary 'default' form sheLL window sheLL
CommandExec dictionary 'default' form 'Command_Inventory' command 'IV_Item_Qtys_Locs'
NewActiveWin dictionary 'default' form 'IV_Item_Qtys_Locs' window 'IV_Item_Qtys_Locs'
TypeTo field 'Item Number' , '6-037-31075R-EA'
MoveTo field '(L) Display Options' item 0 # 'All'
ClickHit field '(L) Display Options' item 1 # 'Site ID:'
TypeTo field 'Location Code' , 'MAIN'
MoveTo field 'Save Button'
ClickHit field 'Save Button'
CloseWindow dictionary 'default' form 'IV_Item_Qtys_Locs' window 'IV_Item_Qtys_Locs'

Run that .mac file as a GP macro, and watch as a lot of tedious work gets done in a few seconds.

Action

Hope this is helpful!

Leave a Reply

Your email address will not be published. Required fields are marked *

 characters available