Security policy for the special field value and empty value

Question

I have created a query in AOT. This query is used for security policy. the security policy is about restricting each user in the purchase order form to see just purchase pools which are defined for him. He should see purchase records which purchase pools are empty as well. Assume I have a table (mapping) which maps the users to the purchase pools. Mapping table have two fields UserId and PurchpoolId.

As you know Security policy object accept query. My query applies inner join on PurchPool table and mapping table with the range of the current user. the purch table is added under constraint tables under the security policy. The first part of security policy i.e., restricting user from accessing purchase orders which Pools are not defined for him is performed correctly. But the second part i.e., user can access purchase order which any Pool is not defined is remaining unsolved.

How can I create security policy with a query considering records with user defined Pools and also empty Purchase Pools ?

Answer 1

This sounds like something I've worked on before. It requires some twisted thinking. You'll need to use two not-exists joins.

Both your query and your policy will need to be set to use not-exist joins. That way you are returning invalid values from your query and restricting those invalid values via your policy. This allows for blanks/default values to be included in your valid data set.

Here's a sample query we used for Business unit:

And here's the policy:

Answer 2

Recently, we have found the better solution which I think it is more efficient and simpler than using not exist join. Here I have applied security policy on fixed asset table based on the location. So I create a query which joins the Assetlocation to my mapping table for current user. The query can be the simple inner join or exist join. In the security policy we should use constrained expression instead of constrained table. So in the value property we can OR two conditions as below:

(AssetTable.Location == "") || (AssetTable.Location == AssetLocation.Location))