cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tjalui
Resolver I
Resolver I

Filter for self referential lookup not working

I have 2 Custom Entities:

1. Question

2. Group

 

Question Entity: Has a Lookup to Group Entity

Below is the fields for Question Entity

Field NameDataType
GroupLookup to Group

Group Entity: Has 2 Lookup to Group Entity

Below is the fields for Group Entity

Field NameDatatype
Next GroupLookup to Group
Previous GroupLookup to Group

Now I am filtering the gallery based on Collection. Below is the code of the Collection:

 

ClearCollect(
    NextQuestion,
    Filter(
        Questions,
        'App selector'.'App selector' = appSelector.'App selector' && IsBlank(Group.'Previous Group')
    )
)

 

There is a button called "Next" which will load next set of Questions for the same collection.

I want to filter the collection based on "Next Group" field in the Group. Below is the Code.

 

ClearCollect(
        NextQuestion,
        Filter(
            Questions,
            'App selector'.'App selector' = appSelector.'App selector' && Group = QuestionList.Selected.Group.'Next Group'
        )
    )

 

"QuestionList" is the name of the gallery. This is giving me the following error "multiple levels of many-to-one relationship expansion aren't supported".

 

Is there a way I can achieve this?

 

Any help is appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-bofeng-msft
Community Support
Community Support

Hi @tjalui :

Could you tell me:

  • What is Groups's Primary Name field?I assume it is "Name"(Because I need a unique identifier to locate the specified record)

1.jpg

Firstly,let me explian why you encounted this problem.

The reason for the error is as you said "multiple levels of many-to-one relationship expansion aren't supported".

Secondly,You can eliminate "multiple levels of many-to-one relationship expansion" by nesting another LookUp.

For example:

 

 

Filter(
    Questions,
    Group.Name = LookUp(
        Group,
        Name = QuestionList.Selected.Group.Name
    ).'Next Group'.Name
)

 

 

  • Use LookUp to find the eligible record in the entity Group and get its associated'Next Group'
  • Filter Question records through the records obtained in the first step

Best Regards,

Bof

View solution in original post

1 REPLY 1
v-bofeng-msft
Community Support
Community Support

Hi @tjalui :

Could you tell me:

  • What is Groups's Primary Name field?I assume it is "Name"(Because I need a unique identifier to locate the specified record)

1.jpg

Firstly,let me explian why you encounted this problem.

The reason for the error is as you said "multiple levels of many-to-one relationship expansion aren't supported".

Secondly,You can eliminate "multiple levels of many-to-one relationship expansion" by nesting another LookUp.

For example:

 

 

Filter(
    Questions,
    Group.Name = LookUp(
        Group,
        Name = QuestionList.Selected.Group.Name
    ).'Next Group'.Name
)

 

 

  • Use LookUp to find the eligible record in the entity Group and get its associated'Next Group'
  • Filter Question records through the records obtained in the first step

Best Regards,

Bof

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,553)