web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / UserInfo Datatype update
Microsoft Dynamics AX (Archived)

UserInfo Datatype update

(0) ShareShare
ReportReport
Posted on by Dereck Britton 90

Hello,

A company that I am helping with a Dynamics AX implementation wants all of their user IDs (in the UserInfo table and others) to match their EmplIDs.  

The company has updated the user relations form (or will before the time comes) to provide a link to what the user ID is supposed to be changed to.  So, making the connection is easy.  

Currently, since the UserInfo Table/Form and UserID Datatype are invisible (or locked down) in the AoT, I cannot use the cross reference tool to find out all of the related tables / fields.  So, I have resorted to the information schema views in the database.  I have a query that looks at the columns view that restricts results based on the following:

  • DATA_TYPE = 'nvarchar'
  • MAXIMUM_LENGTH=5
  • TABLE_NAME NOT LIKE 'DEL%' or 'sv%'
  • COLUMN_NAME LIKE 'ID', 'USERID', '%BY'

I have ran the script and of course it brings back many tables.  Updating these tables with the new UserID and then running the other query that updates the UserInfo and SysCompanyUserInfo tables with the new UserID through MSSQL does not seem to cause any issues.  However I want to ensure I am getting all of the tables to prevent possible future issues.  

Is there any more efficient/accurate way to determine every table that has a UserID datatype field in it?  Preferably through Dynamics, so that any code that is against them is taken into account.  

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Denis Patrakov Profile Picture
    Denis Patrakov on at

    [quote user="Dereck Britton"]Currently, since the UserInfo Table/Form and UserID Datatype are invisible (or locked down) in the AoT[/quote]

    You can find the UserInfo and the UserId extended type under System Documentation AOT node.[quote user="Dereck Britton"]I cannot use the cross reference tool to find out all of the related tables / fields.[/quote]Yes, you can: either browse to System Documentation\Tables\UserInfo and open cross references from the context menu or open Tools/Development Tools/Cross References/Names form and enter the corresponding filter values manually: the latter way works for any application objects - even for those that you cannot see in the AOT, f.e. for doInsert/doUpdate table methods.

  • Dereck Britton Profile Picture
    Dereck Britton 90 on at

    I have tried using the cross reference tool before (and just tried again to make sure ;)) and no entries exist for the data type ID, UserID, or the object UserInfo.  Is there some special option to have these indexed when running the cross reference tool?  

  • Suggested answer
    Denis Patrakov Profile Picture
    Denis Patrakov on at

    Extended data types (EDTs) and enums are not quite... indexed by themselves (except for data type hierarchy), but rather table fields are indexed and thus the information about EDTs and enum used by fields is gathered. Are you sure you have completed the cross references update? In my case according to cross references there's about a hundred fields that use UserId EDT. You can roughly evaluate if you have complete cross references by the number of records in the xRefPaths table: for AX 2009 it should contain about 600-950 thousand records depending on the amount of customizations and/or "size" of a vertical solution you use.

    Anyway, if your task is to change UserId values to match EmplIds and respectively update all the tables that refer to UserIds then you can use the standard functionality to rename primary keys (it's available in the Record Info form). Any table that uses a foreign key (FK) refers to the table in which a FK value is the PK - either explicitly through a relation on the table itself or implicitly through a relation on the EDT used for a FK field, and as the kernel has access to the whole data dictionary it can easily find out which tables need to be updated when you rename a PK. The kernel doesn't need cross references for that, but you'll need them at least to see for which tables you should create additional indexes to speed up the rename process.

    You can create a simple job to automate renaming - see \Forms\SysRecordInfo\Methods\renamePrimaryKey method for the code you'll need (inside the try block).

  • Dereck Britton Profile Picture
    Dereck Britton 90 on at

    I just reran the cross references before posting my last comment.  When following the directions provided previously (System Documentation -> UserInfo -> id -> AddIns -> Cross Reference -> Used By) I get maybe 20 tables and all of the references are code only.  However, there are several tables that have CreatedBy and ModifiedBy that are referencing UserId that are not listed in Xrefs.  

    I will look into the code provided at \Forms\SysRecordInfo\Methods\renamePrimaryKey ;)

  • Denis Patrakov Profile Picture
    Denis Patrakov on at

    Look at xrefs for \System Documentation\Types\userId and filter them by something like "\\Data Dictionary\\Tables\\*\\Fields\\*" - all those fields have an implicit relation with UserInfo.userId. System table fields like modifiedBy, createdBy, dataAreaId are not indexed by xrefs.

  • Dereck Britton Profile Picture
    Dereck Britton 90 on at

    I looked into the code you requested.  This seems a ton easier and more efficient that a SQL approach.  I am sure a pure SQL approach is faster, but if DAX tracks all of the relations and updates them with the indicated value, then I should be able to rest assured that it is done correctly.  Are there any caveats to using DAX functionality over SQL updates that are apparent such as tables that are not updated?  

  • Denis Patrakov Profile Picture
    Denis Patrakov on at

    A situation when a table doesn't get updated during primary key renaming is usually caused by using a wrong EDT for a field (a one without a proper relation with the "master" table). One "good" example is LedgerJournalTrans.ApprovedBy - this field is based on EDT derived from EmplId but it's filled by a UserId value rather then EmplId - see \Classes\LedgerJournalEngine\initValue, \Classes\LedgerJournalEngine_ProjectCost\offsetAcctModified, \Classes\SMAServiceFunctionLine_Transfer\createProjJournalLine_Expense, etc. If you want this field to be updated too you'll have to write a separeate job or an SQL script.

    I think what's important is that you should (temporary) create additional indexes for "huge" tables: PK renaming runs in one transaction for all the affected tables and if you get lots of full table scans it might take a way too much time. Anyway, when you run renaming on a test database you'll find out which tables require additional attention.

  • Dereck Britton Profile Picture
    Dereck Britton 90 on at

    Thanks for your help!  I will post my findings here after we get approval from the client to execute in the testing environment.  

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.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans