Posted in: Dynamics AX

Dynamics AX X++ function to get today's date

Posted on October 27, 2016 by Michael Roma

Use the follow function:

systemDateGet();

How to return a blank or empty date in X++ Dynamics AX 2009

Posted on July 28, 2015 by Michael Roma

The following function returns an empty or blank date in X++.

return datenull();

Commonly used tables in Dynamics AX

Posted on June 30, 2015 by Michael Roma

Administrative Tables

  • USERGROUPLIST
    Table that holds the association between Users and User Groups. It has the detail of what groups a user is in.

  • USERGROUPINFO
    Definition of an AX User Group.

  • SYSUSERINFO
    Definition of an AX User. Has a lot of the UI features like theme, timeout. Also defines email.

How to display the x++ stack trace using the InfoLog in Dynamics AX

Posted on March 16, 2013 by Michael Roma

Use the following code to display the x++ stack track through the InfoLog in Dynamics AX:

info(con2str(xSession::xppCallStack()));

BOM Where Used (Exploded) Stored Procedure for Dynamics AX

Posted on December 29, 2011 by Michael Roma

Below is a stored procedure that will find all BOM parents where the given item is used. The item filter will search for all parts with a wildcard.

create procedure sp_IV_BOMWhereUsed
(
  @itemFilter nvarchar(30)
)
as
  set nocount off;

  -- define globals
  declare @dataAreaId nvarchar(5); set @dataAreaid = 'dat'

  -- define temp table for gathering parents
  declare @temp table
  (
    bomId     nvarchar(30),
    itemId    nvarchar(30),  
    bomlevel  int,
    seen      bit,    
    rowgroup  nvarchar(100),
    recType   int,
    recId     int identity  
  )

  -- declare variables to track state
  declare @recId  int; set @recId = 0;
  declare @itemid nvarchar(30);
  declare @bomlevel int; set @bomlevel = 0  
  declare @pass int; set @pass = 0;

  -- add first level items to check
  insert into @temp 
    select '', itemId, 1, 0, '', 1
    from inventTable 
    where 
      dataAreaId = @dataAreaId and 
      itemId like replace(@itemFilter, '*', '%')

  -- set first level row group string
  update @temp set rowGroup = cast(power(100,bomLevel) + recId as nvarchar(100)) where rowGroup=''

  -- loop while records
  while exists(select top 1 recId from @temp where seen = 0)
  begin

    -- get the next record to process
    set @itemId = null
    select top 1 
      @itemId = itemId,
      @bomLevel = bomLevel,
      @recId = recId
    from @temp 
    where seen = 0

    -- check if no more records
    if @itemId is null break

    
    -- add the next level of records
    insert into @temp
      select distinct bv.bomId, bv.itemId, @bomLevel + 1, 0, '', 2
      from bomVersion bv inner join bom b on bv.dataAreaId = b.dataAreaid and bv.bomId = b.bomId
      where
        bv.dataAreaId = @dataAreaId and
        b.itemId = @itemId and
        not exists( select recId from @temp where bomId = bv.bomId and itemId = bv.itemId)  

    -- set the row group for these new records
    update @temp set 
      rowGroup = (select top 1 rowGroup from @temp where recid=@recid) + cast(100 + recId as nvarchar(100)) 
    where 
      rowGroup=''

    -- increate the pass
    set @pass = @pass + 1

    -- remember this record was seen
    update @temp set seen = 1 where recid = @recId
    
  end

  -- return the records
  select t.*, i.itemName,
    replicate('.', 4 * (t.bomlevel-1)) as indent
  from 
    @temp t
    inner join
    inventTable i
    on i.dataAreaId = @dataAreaid and
       t.itemId = i.itemId

  order by 
    rowgroup