Is it possible to perform a DLOOKUP in Dataverse i.e. look up the value of a field in another table?

(0) Share

Report

Posted on by MJWhite

338

Here is my issue, as described in the screenshot below:

I want to control which categories of data users can access (using the standard options of assigning permissions based on Account or Contact record doesn't meet the requirement)
I can easily define the Categories which each user can access with a table 'User Categories', which captures combinations of User and Category
I can filter a View of 'User Categories' to show the Categories for the current user
But what I'm struggling to do is to filter a View of Categories to show the Categories (and all the associated fields) for the current user
In other databases, I would use an IF and DLOOKUP function in a calculated field to identify whether a user should have visibility of a Category and I could then filter on that calculated field (See screenshot. Syntax would vary according to the database)
But in Dataverse, it looks like a calculated field (now Power Fx field?):
- In general, can't make any reference to fields in other tables
- Specifically, doesn't offer the use of a DLOOKUP function or equivalent
Is that the case?

Calculated field.jpg

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (1)

Sort by

fm_skeller 277 Moderator on at

Like (0)

Report
Copy link

Link copied!

In power pages you could assign table permissions, this will restrict the view based on the logged in portal user (contact):
1) on portal user (i.e. contact table) -> own contact record
2) on user categories -> parent right based on the relation between contact and usercategories
3) on categories -> parent right based on the relation between usercategories and categories.

You could achieve the same by using a FetchXML query - or showing a subgrid for usercategories where you add a field from categories to the view.

Was this reply helpful? Yes No
MJWhite 338 on at

Like (0)

Report
Copy link

Link copied!

Thanks for the response
The requirement isn't to restrict the view based on the logged in portal user using the 'own contact record' option.
The requirement is to be able to specify which users can access which records on Categories and then apply that to a View of Categories for the current user.
Hence, I can't see how the three steps above meets the requirement.

Was this reply helpful? Yes No
fm_skeller 277 Moderator on at

Like (0)

Report
Copy link

Link copied!

I don't appreciate the negative tone, just trying to help here.
However, I do think that my solution does exactly what you are describing. Using entity permissions in this way restricts access to records - not to the view itself. So in effect it will filter the view based on if a user has access to the specific record.

Was this reply helpful? Yes No
MJWhite 338 on at

Like (0)

Report
Copy link

Link copied!

I could have been positive and said 'I can see how the three steps above meet the requirement', but that wouldn't have been truthful or helpful to anyone. Also, just because someone says 'they're not getting it yet' doesn't mean they won't get it in the future.
Oliver has gone into a bit more detail on separate but related post: Is it possible to configure permissions at record ... - Power Platform Community (microsoft.com)
I'll study Oliver's response, revisit your response and let you know how I get on.

Was this reply helpful? Yes No
Verified answer

MJWhite 338 on at

Like (0)

Report
Copy link

Link copied!

Hi there

I had other stuff to do, so it took me a while to test Oliver's solution in Solved: Re: Is it possible to configure permissions at rec... - Power Platform Community (microsoft.com) but I've done it now and marked Oliver's reply as the solution. I've added a few comments against your suggestions in red below for other readers, which with the above link will hopefully be helpful.

In power pages you could assign table permissions, this will restrict the view based on the logged in portal user (contact):
1) on portal user (i.e. contact table) -> own contact record I don't think it is necessary to apply a permission to the portal user (i.e. contact table)
2) on user categories -> parent right based on the relation between contact and user categories I think a Contact Access permission is applied to the User Categories table, which points to the lookup to the portal user (i.e. contact table)
3) on categories -> parent right based on the relation between usercategories and categories. Agree and this can't be done in the Design Studio and has to be done in Portal Management

You could achieve the same by using a FetchXML query - or showing a subgrid for usercategories where you add a field from categories to the view. The use of table permissions above seems to be the right solution for my current requirement, but appreciate your suggestion about the subgrid as I can see there may be scenarios where this could be useful

Thanks

Was this reply helpful? Yes No