List show records for logged in user with column

[sweb]

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 %}

[sweb]

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

[Mglat]

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

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

[Mglat]

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]

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

[Mglat]

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