Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Unanswered

Complex XDS policy - multiple conditions on one table

(0) ShareShare
ReportReport
Posted on by 10

Hi All,

I have a requirement to apply record level security on a PriceDiscTable with multiple conditions: e.g:

1) Show Sales price records where item group of an item is X OR Y OR Z OR....

 OR

2) Show Sales price records where relation is 'Group' and sales price group is X OR Y Or Z OR....

OR

3) Show sales price records where price type is 'All' and item group of an item is X or Y or Z or...

OR

4) Show purchase price records where item group of an item is x or y or z or....

OR

... I have few more conditions to add similar to those above..

basically user needs to see all records which falls into one of the above criteria, and I need those criteria applied as OR not as AND.

By default having multiple XDS policies puts AND between the policies, so I was trying to build one complex query (to apply to one policy) with all my conditions. This becomes too complex and I failed on building it. 

I wonder if you can think of any solutions to such a scenario?

I'll appreciate any ideas. 

Thanks.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,979 Super User 2025 Season 1 on at
    RE: Complex XDS policy - multiple conditions on one table

    HI Valdek,

    This weekend, I tried to check for some options here. I'm a bit concerned about the possible performance degradation. Maybe you can add a MyConstruct table with the possible combinations of records which should be visible. An XDS() method on this table can loop your relation tables and add records with the visible combinations. The MyConstruct table can be named something like MyTradeAgreementRelations with the next fields: Relation, Product code type and Item. With correct x++ statements, the insert of the records in the TempDB can be quite fast. You can then create a query for XDS by linking the PriceDisc table with the new MyConstruct table.

    For sure do performance tests with expected number of records in the released products and trade agreements.

  • Valdek Profile Picture
    Valdek 10 on at
    RE: Complex XDS policy - multiple conditions on one table

    Many

    I'm not exactly sure of the number of users who'll have the policy applied but surely it will be 20-30+ maybe much more.

    Item groups are 160 currently, and in the worst case scenario a user may have all itemGroups setup for Sales prices and only a few for purchase prices. I was considering various policies for such scenario - one policy if user is restricted by sales prices and purchase prices, and another policy if user has only purchase price restrictions and full rights to sales prices. But the number of combinations of what is fully allowed and what is restricted is so big, that I couldn't think of a good solution to apply various policy based on the setup. So everything is 'restricted' and if user have full rigts to sales prices, then all item groups for sales prices for a user will be listed. Not too smart, but I couldn't find smarter solution for this either.

    I do have a number of tables with price settings to users.

    For example:

    SalesPrices by itemGroup: UserID - ItemGroup - Allow edit (used for policy with edit rights)

    SalesPrices by priceGroup: UserID - PriceGroup - Allow edit

    SalesPrices by 'All' price group: User Id - Item Group - Allow Edit

    Purch Prices by Item group: UserID - ItemGroup - Allow Edit

    ... and so on.

    Kind regards

    Waldek

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,979 Super User 2025 Season 1 on at
    RE: Complex XDS policy - multiple conditions on one table

    Thanks for the extensive reply. This is clear. Before thinking of a solution direction, can you indicate how many products, groups and users you have or expect? Performance should be considered as well.

    Do you already have a table which will be used to map the price settings to users?

  • Valdek Profile Picture
    Valdek 10 on at
    RE: Complex XDS policy - multiple conditions on one table

    Hello Andre,

    thank you for coming back to me. I have read your various posts - very useful! Thanks for sharing all the knowledge.

    yes, x, y, z ... and so on will be different per user. The number of x, y z conditions is not determined and it'll usually be much longer than 3. For example you may give access a sales prices for a number of item groups (10-20 item groups can easily be possible, in some cases more), or for a number of sales price groups. Some user may even need access for all item groups or when viewing sales prices and just a couple or zero item groups for purchase prices.

    It is hard to say with what condition it should be excluded, because all access granting is planned for what should be visible to the users and not what should be hidden.

    Actually the above conditions is not the end, as second stage of the project are additional conditions based on the customer specific prices, and financial and commercial prices (new internal price types).

    On top of that the same restrictions needs to be applied to PriceDiscAdmTrans table so nobody can see trade agreement journal lines.

    to complicate things a bit further (but this I have solved) I need two policies: one to view records, and one to create/edit/delete - but that's simply sorted with two separate policies with similar but different queries - second query has additional condition/filter to filter only those records where 'allow edit' is true in the setup table.

    If you can think of any solutions it would be helpful.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 291,979 Super User 2025 Season 1 on at
    RE: Complex XDS policy - multiple conditions on one table

    Hi Valdek,

    I'm happy to assist you with this question. XDS is one of my favorite topics. Can you tell if X or Y or Z can be different per user? Is it about three or possible different number of conditions? Can you also tell if it would be easier to think vice-versa? With what conditions should be price be excluded?

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,979 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,848 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans