Unanswered

List show records for logged in user with column

(0) Share

Report

Posted on by sweb

117

Hello everyone,

What I want to achieve is to only show records in a Power Pages list that the logged in user is related to.

I have 2 tables, one is the Contacts which is where logged users are saved and the other is any other table.

Both tables have a column for the relationship, so I know which is related to which.

Have tried to create a many-to-many relationship between the tables. I think it should be many-to-many since many users can have access to many related resources.

Contacts table (logged in users)

# First name Relation Column

1 Test1 11111111

2 Test2 11111111

3 Test3 22222222

Other table

# Some Column Relation Column

1 Test1 11111111

2 Test2 12345678

3 Test3 22222222

Users with ID 1 and 2 will see the record with ID 1 and user with ID 3 will se the record with ID 3.

Have tried to create this relation but I can not say which column the relation should look for.

I also thought that I can do it through the "expression builder", but it lets me say only "Equals" with the data type, for example "11111111", I can not use a variable, in this case being the column of the logged in user.

What would be the best way to achieve this filtering?

I have done this with Liquid, but now I lost the sorting, design and everything the default list have. Any way I can use the below fetchXML query for the default lists?

 {% fetchxml myQuery %}
 <fetch mapping='logical'>
 <entity name='cr1d3_swtest'>
 <attribute name='cr1d3_name'/>
 <attribute name='cr1d3_cvr'/>
 <filter type='and'>
 <condition attribute='cr1d3_cvr' operator='eq' value='{{ user.cr1d3_cvrnumberidentifier }}' />
 </filter>
 </entity>
 </fetch>
 {% endfetchxml %}

Categories:

General topics

All responses (15)

Answers (0)

Sort by

Lucas001 2,595 Super User 2026 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @sweb,

do you simply want to show the information or let the user edit them as well?
If you simply want to show it, you can use fetchXML on a complete blank page without header and footer.
There you will need to create the fetchXML and than create a JSON inside that page:

{% fetchxml yourData %} <fetch> <entity name="entityName"> <attribute name="tableColumnid" /> <attribute name="prefix_test1" /> <attribute name="prefix_test2" /> </entity> </fetch> {% endfetchxml %} [ {% for item in yourData.results.entities %} { "id": "{{ item.prefix_tableColumnid }}", "test1": "{{ item.prefix_test1 }}", "test2": "{{ item.prefix_test2 }}", }{% unless forloop.last %}, {% endunless %} {% endfor %} ]

After that you can use parseJSON and filter on the userID or the UserGroup etc.

const query = "/yourPageUrl"; // URL for fetching data from your table fetch(query) .then(response => response.json()) .then(data => { let searchedId = data.filter((e)=>e."AttributeName".replace(/\s/g,'') === searchString.replace(/\s/g,'')) })

You can than create a table and add the filtered JSON Items and create tablerows with the data you want to provide.

<th> </th> <td> "your Strings with <tr>Your Entry</tr>" </td>

You can than customize the table to your wishes with CSS.

--------------------------------------------------------------------------------------

Hope that helps.

If the post solves your problem, please mark it as solution. If it helps, consider a thumbs up.
sweb 117 on at

Like
a
(0)

Report
Copy link

Link copied!

@Lucas001Thank you, I can manage to do it this way but would be easier if I can use the default list Power Pages offers, this way I don't have to code each list.

Any idea if it's possible to filter the default lists using the logged in user?
Marcel Lathouwers 237 on at

Like
a
(0)

Report
Copy link

Link copied!

Create a view in d365 that is similar to your fetchxml and include a foyer to the contact. No matter what contact you use in there. In the portal it willl be dynamically substituted for the current user/contact
sweb 117 on at

Like
a
(0)

Report
Copy link

Link copied!

@MglatCould you please provide some documentation link? I just started last week with the Microsoft suite, still new to it and hard to find info.
Also, what is a "foyer", haven't seen this term in any docs before.
Marcel Lathouwers 237 on at

Like
a
(0)

Report
Copy link

Link copied!

HI @sweb , sorry for the typo, foyer was siri's translation of 'filter' 😊
Some good starting points are:
Create and modify views by using the Data workspace | Microsoft Learn
and in this, make sure to set a filter to a contact Create and modify views by using the Data workspace | Microsoft Learn

then use this view in your portal list Add lists | Microsoft Learn
sweb 117 on at

Like
a
(0)

Report
Copy link

Link copied!

@Mglat
Thank you, I have tried something like this before but when I tried to add filters I can use only values, not variable like a column from the Contact table.
Since each user can see only records that have one column equal to one of their Contact column.
Have tried even some relationships, and now when I choose as a relation the Relation table, in the values I have the "Names" list from that table.

Contact table
Name Relation Column

Records table
Name Relation column

Relation table
Name

I need a way to tie the Records table to the Contact table for the logged in user.
Marcel Lathouwers 237 on at

Like
a
(0)

Report
Copy link

Link copied!

@sweb I understand the confusion. It feels very counter intuitive, but add a filter to a fixed contact lookup value, not going into the related tabel and filter there.

It will be replace by the portal by the actual logged in contact value
sweb 117 on at

Like
a
(0)

Report
Copy link

Link copied!

@Mglat
Nice, it works, that indeed looked counter intuitive for me.
But it works if the record has a relationship to the Contacts table.
In my case I have column in the Contact table and a column in the records table, but this is not a Lookup, it's a number.

So Contacts have for example a column named `Identifier` that can be an 8 digit number. Many Contacts can have the same Identifier.
Records table has this column also, many Records can have the same Identifier and I want to show the records where this column matches, so multiple Contacts can see multiple Records.
If I do a lookup from Records to Contacts table, Identifier column does not do anything, it will show the records tied through the Contacts Lookup column, not through the Identifier column.
Have grinded on what relationships, tables and all that to create so I can make this work, but I can not make it work. Easiest way is to have the same identifier on both tables and match them, but I cannot match them, in Dataverse relationships work only between entities, can not choose a column.

Is there something I'm missing?

Lastly have tried creating a Many-to-Many relation between Contacts and Records, in the view, under Edit filters did "Add related table" and said Contact equals a Contact. But I don't know how to add a Record for the Contacts. And if I do, let's say another user registers with some existing identifier, there are no records shown for him since the relation is not made through it.
Marcel Lathouwers 237 on at

Like
a
(0)

Report
Copy link

Link copied!

Nice to have progress
the only way to get the correct filters is indeed to create lookup
create a table the have all the identifiers in there. And then create lookup to that table from both your existing tables, replacing the text field identifiers
sweb 117 on at

Like
a
(0)

Report
Copy link

Link copied!

@Mglat
Like this?

Contact Table
Name
Identifier Lookup

Record Table
Name
Identifier Lookup

Identifier Table
Name

But then how do I make the filters? I know it replaces only the Contact Lookup with the logged in user, the Identifier Lookup, how does it know which one to use?