After giving an example of how to use the automatic role assignment by using one of the standard queries in my previous post, I will inform you how to add a new query to use within the Automatic role assignment rules. It is not always possible to add linked tables to the base query to achieve the expected result, due to several points. E.g. two fields in a table have a relation to the same table.

Position hierarchy

Imagine that we want to grant the Manager role to all people in our organization responsible for one or more employees. The line managers are visible within the position hierarchy.

When you create positions, you also provide the manager by filling the field Reports to position. This will build the position hierarchy for the line organization in Microsoft Dynamics AX2012. The question is how to represent this hierarchy in a query and link it to a user?

Manager query

So in fact we want to build a query where the automatic role assignment knows the position of a worker and if this position has some sub-positions. First we have to analyze the tables used and draw a data model:

You will notice that the HcmPositionHierarchy table has two fields with a relation to the same table. Also three tables are date effective enabled. To use only active records and the relations it is better to create a new query for the automatic role assignment. The assumption I made to recognize a “Manager” is when a person has a position where a record exists in the HcmPositionHierarchy table related on the field ParentPosition. I will also make a note later in this post on the date effectiveness as we don’t want people to be recognized as a manager if the organization has changed.

There is only one requirement for creating a new query and have it available within the selectable queries when you create a new rule. The first data source should be based on the table UserInfo.

Now we can add the tables based on the data model as shown above. In my query I skipped the HcmPosition table. Make sure you have set the Fields property Dynamic for every table. Also check if you have the correct relations for each data source.

When you have saved and compiled the query, you can use it.

To have only the role assignment look for the Line organization you can set the criteria on the Hierarchy type field from the table Position hierarchy types.

When you select the Date options tab page, you will have a choice to include only the active records from the date effective tables. This tab page is not showing up if you start with a base query Select all users and add date effective tables in the query manually.

When you close the query, you have to wait for the batch job running periodically or click the button Run automatic role assignment.

Now the users have been assigned automatically to the role Manager based on the new created query. This is really cool to use. When there are multiple people moving jobs or the organization changes and a person might become a manager due to new positions this example is a real time-saver for your security administrators.

I do hope you liked this post and will add value for you in your daily work as a professional. If you have related questions or feedback, don’t hesitate to use the Comment feature below.

That’s all for now. Till next time!

3 replies
  1. Baber
    Baber says:

    Excellent article.

    Is it possible to add the table in the query which is not directly linked to DirPerson table ? Let say I have some custom tables and I want to assign users to a role when some conditions are met for my custom tables ?


    • André Arnaud de Calavon
      André Arnaud de Calavon says:

      Hi Baber,

      Thanks for reading the blog. As long as the new custom table has a reference to the UserInfo table (User ID field used?), it is possible to use this new custom table in the automatic role assignment.


Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.