cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JeTmAn
Level: Powered On

Filtering a data source by membership in a list subproperty

I have a form where an Office365 user is selected, and then I want to fill a gallery with a list of committees of which that user is a member.  I've got user selection working fine, and I can grab any piece of info from the user object that I need.  At the moment, I'm setting a variable called Username with the user's DisplayName.

 

My issue is I don't know how to filter the committee list to only include those which have that user as a member.  Each committee has a Members list object, and those Members do have a DisplayName, but doing something like Filter(Committees, Members.DisplayName = Username) doesn't provide me with any items.  How do I do this filter correctly?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filtering a data source by membership in a list subproperty

Hi @JeTmAn,

Could you please share a bit more about the data structure of your Committees SP list?

Is the Members a Person type column in your Committees SP list? Do you enable "Allow multiple selections" option for it?

I suppose that the Members is a Person type column in your Committees SP list, which has been enabled "Allow multiple selections" option, is it true?

If the Members is a Person type column in your Committees SP list, which has been enabled "Allow multiple selections" option, I think there is something wrong with the formula that you provided.

I have made a test on my side, please take a try with the following workaround:7.JPG

Set the Items property of the Data Table to follwoing:

Filter('20190126_case3', "Test1" in Members.DisplayName)  /* <-- "Test1" represents the display name of a user */

On your side, you should type:

Filter(Committees, Username in Members.DisplayName) 

 

Above solution may cause a Delegation warning issue, in order to get rid of this Delegation warning, please take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following:

ClearCollect(RecordsCollection, Committees)

Set the Items property of the Gallery to following:

Filter(RecordsCollection, Username in Members.DisplayName) 

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User
Super User

Re: Filtering a data source by membership in a list subproperty

Hi @JeTmAn,

I think some of the reason you are having trouble is because you are dealing with a Many to many relationship.  Members can belong to more than one committee and each committee has more than one member.  Best practice for modeling such relationships require three lists; Members,, Committes and a Junction table that includes the Primary key from the other two tables. Members_Committees.PNG

 Once you have the three lists, You can use dropdown controls to filter the Enrollments table to see the committees that each member belongs to or to list the members of each committee.  To show the usefulness of such a design, I addeded a Status field. You would use the Lookup function in the Gallery to Identify the committe or member by name from thier ID.   In this way you could put Chair, ViceChair, Member, etc.  to identify the role of the member in a particular committee.  Something you could not do with your current design.  Please see my posts in the community blog regarding Database Design in Powerapps for some examples. 

Community Support Team
Community Support Team

Re: Filtering a data source by membership in a list subproperty

Hi @JeTmAn,

Could you please share a bit more about the data structure of your Committees SP list?

Is the Members a Person type column in your Committees SP list? Do you enable "Allow multiple selections" option for it?

I suppose that the Members is a Person type column in your Committees SP list, which has been enabled "Allow multiple selections" option, is it true?

If the Members is a Person type column in your Committees SP list, which has been enabled "Allow multiple selections" option, I think there is something wrong with the formula that you provided.

I have made a test on my side, please take a try with the following workaround:7.JPG

Set the Items property of the Data Table to follwoing:

Filter('20190126_case3', "Test1" in Members.DisplayName)  /* <-- "Test1" represents the display name of a user */

On your side, you should type:

Filter(Committees, Username in Members.DisplayName) 

 

Above solution may cause a Delegation warning issue, in order to get rid of this Delegation warning, please take a try with the following workaround:

Set the OnVisible property of the first screen of your app to following:

ClearCollect(RecordsCollection, Committees)

Set the Items property of the Gallery to following:

Filter(RecordsCollection, Username in Members.DisplayName) 

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JeTmAn
Level: Powered On

Re: Filtering a data source by membership in a list subproperty

Thanks all, Kris's suggestion of simply filtering by Username in Members.DisplayName (I used DisplayName to assign to the Username variable so this works) seems to have worked!