Thursday, April 11, 2013

Dynamics GP - Update User Category Fields and Drop Down Menus


Create another temp table, load it with all your data and the item numbers to update.

update u
set uscatvls_1 = s.dept from iv00101 u
    inner join iv00101_load s on
        u.itemnmbr = s.itemno

Update the Custom Category Lookup Menus
Original Post here
http://www.gp-dynamics.com/dynamics-gp-command-details.asp?id=57

/*
* 1. Load all CUSTOM CATEGORY1 DATA FROM INVENTORY (IV00101)
* 2. Iterate through each CATEGORY1 and check if it exists in the table of CUSTOM CATEGORIES (Must match the category code AND category position)
* 3. If not found then create a new custom category entry in the reference table
* 4. The correct custom category1 of your items should now correctly display when viewed in the Item Maintenance window.
* Note that there are 6 custom category positions so change @ATEGORY_POSITION accordingly. The query below works for the first custom category only.
*/

declare @CATEGORY_CODE char(11)
declare @CATEGORY_POSITION int

set @CATEGORY_POSITION = 1

declare curs cursor for
select distinct v.USCATVLS_1
from IV00101 v
where len(v.USCATVLS_1) > 0

open curs

fetch next from curs into @CATEGORY_CODE

while @@fetch_status = 0
begin
  if not exists(select USCATVAL from IV40600 where  USCATVAL = @CATEGORY_CODE and USCATNUM = @CATEGORY_POSITION)
  begin
    print @CATEGORY_CODE
    insert IV40600 (USCATVAL, USCATNUM) values (@CATEGORY_CODE, @CATEGORY_POSITION)
  end
  fetch next from curs into @CATEGORY_CODE
end

close curs
deallocate curs

No comments:

Post a Comment