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.
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.
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
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?
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.
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?
André Arnaud de Cal...
291,979
Super User 2025 Season 1
Martin Dráb
230,848
Most Valuable Professional
nmaenpaa
101,156