Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Limit Query Range Look...
Microsoft Dynamics AX (Archived)

Limit Query Range Lookup based on Legal Entity (DataAreaId), when using 'RetailStoreId' Field

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi,

We have recently added a new Legal Entity to AX, and now we are facing a problem with some of our old reports.

Whenever we're using a field of type 'RetailStoreId' in a query range, the query criteria lookup shows all the stores from both legal entities, regardless of the user's company.

I believe the reason for this is, because the 'RetailStoreId' lookup is based on the table 'RetailStoreTable', which doesn't have a 'DataAreaID' field (which AX would use to filter the stores) . Instead it has the field 'InventLocationDataAreaID'.

If that's the case, is there any way I can make AX use the 'InventLocationDataAreaID' Field to filter for this table?

Otherwise is there any way to customize the lookup of the query?, or any other workaround?

I'm aware that I can create a 'DialogField' and customize it's lookup, but we're facing this issue in many reports, and it would take time to update them one by one.

Thanks

*This post is locked for comments

  • Suggested answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at
    RE: Limit Query Range Lookup based on Legal Entity (DataAreaId), when using 'RetailStoreId' Field

    No problem. And yes, System administrator rights ignore security policies and you should not have those privileged users around your system. You need a test account or disable the admin privilege with a job for a new workspace temporarily to test the policy, with SecurityUtil::sysAdminMode(false); infolog.createWorkspaceWindow();

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Limit Query Range Lookup based on Legal Entity (DataAreaId), when using 'RetailStoreId' Field

    Hi Vilmos Kintera,

    That's exactly what I was looking for.

    I'm not too experienced with AX security module, so it didn't occur to me that it could help :)

    For anyone who is facing the same problem, here's what I did:-

    * Created an AOT query, called it 'RetailStoreXDS', added 'RetailStoreTable' as DataSource.

    * Added a range for the field 'InventLocationDataAreaId' with the value set to "(currentCompany())",without the quotes.

    (or you could use the "temp table with an "xds()" method" approach mentioned above, if you have more complex conditions)

    * Created a security policy, set the Query to 'RetailStoreXDS', Primary Table to 'RetailStoreTable', Enabled to 'Yes'

    * Take note that security policies doesn't work for users with '-SysAdmin-' role, before you waste hours trying to debug it :'(

    Thanks a lot for the help.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Limit Query Range Lookup based on Legal Entity (DataAreaId), when using 'RetailStoreId' Field

    Hi Rawna,

    I am not using AX as multi company, so I'm not able to completely replicate your situation, sorry.

    I did notice on forms there's a property on the datasources called 'CrossCompanyAutoQuery' and you should check to see if that is set to 'No'.  

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Limit Query Range Lookup based on Legal Entity (DataAreaId), when using 'RetailStoreId' Field

    Hi Rawna

    I've never had to do that before what you're asking, but let me see if I can get this to work in code and I'll let you know when I have some time either today or tomorrow.

  • Verified answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at
    RE: Limit Query Range Lookup based on Legal Entity (DataAreaId), when using 'RetailStoreId' Field

    You could implement an XDS security policy, where you may always limit the records for the user accounts who have/do not have a specific security role for example. It will hide and show values based on whatever range do you implement for the query of the security policy, or whatever do you put in a table's xds() method if you go down that route.

    That is most likely still a less painful way than trying to figure out how the query selection engine is rendering the lookups and only apply filtering in case of specific source tables and fields.

    https://blogs.msdn.microsoft.com/daxserver/2013/06/26/best-practices-tips-and-tricks-for-implementing-xds-extensible-data-security-policies/

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Limit Query Range Lookup based on Legal Entity (DataAreaId), when using 'RetailStoreId' Field

    Hi Beau S,

    Thanks for the quick reply.

    I think that you misunderstood my question, what I'm trying to do is filter the dropdown options, in the default AX query window, based on the user company, not filter the results of the query.

    For example, in the following screenshot, the user should not see the stores numbers ('EGA', 'FEH', 'BAS') because they are not stores in his current company, instead he should only see ('MAS', 'NMJ')

    Store-number-lookup-problem.png

    Sorry If I wasn't clear, and thank you for your time.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Limit Query Range Lookup based on Legal Entity (DataAreaId), when using 'RetailStoreId' Field

    you could override the run method and add your range there to use curExt() to get that (returns a string).

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

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,684 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,414 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans