I have 2 SharePoint lists. SP1 and SP2
SP1 looks like:
ID | Type | Details |
1 | A | detail 1 |
2 | B | detail 2 |
3 | C | detail 3 |
SP2 looks like:
ID | Parent_Type (this is a SharePoint lookup column from SP1) | Details |
1 | A | foo1 |
2 | A | foo2 |
3 | B | foo3 |
I am trying to load a Gallery control with the items in SP1 that are selected by a user && are contained in SP2.Parent_Type.
So if a user picks SP1.Type = A and SP1.Type = C, I want my Galley to only display SP1.Type = A (because SP2.Parent_Type does not contain C).
I know that PowerApps sees SP2.Parent_Type as a record because I can load a table with Items=SP2.Parent_Type and there are two avalible fields, ID and Value - the Value field is a text field and the one I want in this case.
So the ITEM formula that I have tried in my gallery is: "Filter(SP1, Type in SP2.Parent_Type.Value)" but I get an error saying "name is not valid. identifier not recognized" on the "value" part. BUT if i leave the value off I get "can't convert this data type. powerapps cannot convert text to record".
How to I format the filter correctly so that I can get the selected items in SP1 that also overlap with the items in SP2?
Solved! Go to Solution.
Hi @Anonymous :
Could you tell me :
I assume Type(SP1)'s data type is "Text" and Parent_Type do not allow multiple selections.
Please try this code:
Filter(SP1,Type in ForAll(SP2,Parent_Type.Value))
Best Regards,
Bof
WOW. @v-bofeng-msft This worked Perfectly.
I would have never thought to use the ForAll because Parent_Type(SP2) is not a multiselect and because when I built a table to debug and set Table1.Items = SP2.Parent_Type and Column1.Field = ThisItem.Value it worked as I expected.
There must be some difference between the way the Table control and the Gallery control see a SharePoint Lookup column.
Anyway thanks again!
try
Filter(SP1, SP2.Parent_Type = Gallery1.Selected.Type)
Gallery1.Selected.Type -> a gallery with SP1 data
This still does not work.
I get an error on the "=" that says "invalid argument type"
I get the "name is not recognized" on the 'Parent_Type' if I use "in" rather than "="
I get the "name is invalid. Identifier not recognized" if I use Parent_Type.Value with either the "in" or "="
And if I switch the ordering to Filter(SP1, = Gallery1.Selected.Type in SP2.Parent_Type) then i get the "can't convert this data type. Powerapps can't convert this Text to a Record" error
Also, I am collecting the selected items from the gallery containing SP1 into C1 and would prefer to use the collection (C1) rather than reference the specific gallery. But this doesn't seem to change anything because it is the SP2.Parent_Type that seems to be causing all the problems.
Hi @Anonymous :
Could you tell me :
I assume Type(SP1)'s data type is "Text" and Parent_Type do not allow multiple selections.
Please try this code:
Filter(SP1,Type in ForAll(SP2,Parent_Type.Value))
Best Regards,
Bof
WOW. @v-bofeng-msft This worked Perfectly.
I would have never thought to use the ForAll because Parent_Type(SP2) is not a multiselect and because when I built a table to debug and set Table1.Items = SP2.Parent_Type and Column1.Field = ThisItem.Value it worked as I expected.
There must be some difference between the way the Table control and the Gallery control see a SharePoint Lookup column.
Anyway thanks again!
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
User | Count |
---|---|
200 | |
46 | |
45 | |
45 | |
39 |
User | Count |
---|---|
280 | |
81 | |
81 | |
80 | |
68 |