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

All responses (15)

Answers (0)

Marcel Lathouwers 236 on at

Like (0)

Report

Re: List show records for logged in user with column

You could try to use the fetchxml from xrmtoolbox and save that as a view. That gives you more options than via the standaard UI.
Another option is using fetchxml in combination with liquid on the portal side (templates), but that's not my strongpoint
sweb 117 on at

Like (0)

Report

Re: List show records for logged in user with column

@Mglat
The image from my previous post with Edit filters is from the Dataverse, on the view I use in the Power Pages list, on the right side I have "Edit filters..."
I cannot see any option to use FetchXML code inside the view, I can just add filters like in that image. Any idea how can I do that?
Marcel Lathouwers 236 on at

Like (0)

Report

Re: List show records for logged in user with column

In my approach, you would create a list in the portal showing the records table.
H the portal list is based a view in dataverse, and you can create the filters within that view.
sweb 117 on at

Like (0)

Report

Re: List show records for logged in user with column

@Mglat

Thank you, but how do you use the FetchXML inside a Power Pages table? I can only add filters on the table form with FetchXML, not to filter data by default.

I have only 2 tables, the Contacts table and another table called Records. The Identifiers is what I thought will work to tie them together for the it to work in the Edit filters.

I can add filters only like this, from the Edit filters under view.
Marcel Lathouwers 236 on at

Like (0)

Report

Re: List show records for logged in user with column

@sweb
I don't have your tables at hand, so I'll illustrate with the same relations
Contact has 1 account (Identifier)
Cases (record table) has 1 account
Account (identifier tabel)
sweb 117 on at

Like (0)

Report

Re: List show records for logged in user with column

@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?
Marcel Lathouwers 236 on at

Like (0)

Report

Re: List show records for logged in user with column

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 (0)

Report

Re: List show records for logged in user with column

@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 236 on at

Like (0)

Report

Re: List show records for logged in user with column

@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 (0)

Report

Re: List show records for logged in user with column

@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.