Filter gallery using a separate related list

(0) Share

Report

Posted on by annamarieke

I wondered if anyone can help me with the following query.

I have 2 SharePoint lists:

BoatsAndPackages
ProductionSchedule

In my PowerApp I bring these lists into the App as a Collection/Local lists during the OnStart:

ClearCollect(

BoatsAndPackagesLocal,

BoatsAndPackages

);

ClearCollect(

ProductionScheduleLocal,

ProductionSchedule

);

I also create a collection to filter the ProductionScheduleLocal to only show Boats that are available for ordering and only boats that are assigned to the logged in user.

ClearCollect(ColFilteredToDealerAndAvailable,Filter(ProductionScheduleLocal,AvailableforOrdering.Value="Available", AssignedDealerEmail.DisplayName=Office365Users.MyProfile().DisplayName))

I then want to display in a gallery the Boats and Packages that match the items from the filtered ProductionScheduleLocal (the ColFilteredToDealerAndAvailable collection).

I can only get this to work when I use ModelCode_ProdSch which is a Single line of Text field.

However I need this to work with the a lookup field for the Boat model (called “Model_x003a__x0020_Model_x0020_C). But it errors when I use this field:

I tried reducing the formula to just the Filter and it still doesn’t work. I’ve tried it with .Value & .Id but can’t get it to work. It works fine if I only need to filter in the one list, but errors when I try to get it to filter based on a list of values from a separate (related) list.

But not

I have tried a few different ways to get this to work but have not yet been successful. Does anyone have any ideas?

Thanking you in advance for your help,

Annamarieke

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

Sort by

WarrenBelz 156,382 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @annamarieke ,

Two things - can you please post all code in Text - much easier to copy and you will always need Model_x003a__x0020_Model_x0020_C.Value if it is a Lookup field, however is it a Multi-select or Single Select? If (as I suspect it is multi-select, then prepare for the many-to-many battle below . . .

With( { wData: Ungroup( ForAll( ProductionSchedule.Model_x003a__x0020_Model_x0020_C As aLU, Filter( BoatsAndPackagesLocal, aLU.Value in 'Model code' ) ), "Value" ) }, Sort( Search( Filter( wData, VisibleinApp.Value = "Yes" ), search.Text, "Title", "Model code" ), If( IsBlank(Index.Value) ||Index.Value = "Not Selected", 10000, Value(Index.Value) ) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
annamarieke 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz ,
Thank you for responding so quickly.
The model codes in the Boats and Packages are Single line of text. So no option for multi-select. The Lookup for model Codes in the Production schedule comes from the Title (Boat name) in the Boats & Packages list. This is not allowing multi-select:
Here is my code pasted as requested:
Sort(
Search(
Filter(BoatsAndPackagesLocal,VisibleinApp.Value = "Yes",Model_code in ColFilteredToDealerAndAvailable.ModelCode_ProdSch),
search.Text,
"Title",
"Model_code"
),
If(
IsBlank(Index.Value) || Index.Value = "Not Selected",
10000,
Value(Index.Value)
)
)

And with the Lookup value:
Sort(
Search(
Filter(BoatsAndPackagesLocal,VisibleinApp.Value = "Yes",Model_code in ColFilteredToDealerAndAvailable.Model_x003a__x0020_Model_x0020_C),
search.Text,
"Title",
"Model_code"
),
If(
IsBlank(Index.Value) || Index.Value = "Not Selected",
10000,
Value(Index.Value)
)
)

I'll have a go at your code above and see if that works.
Thanks so much,
Annamarieke

Was this reply helpful? Yes No
annamarieke 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz ,
I gave your code a go. I can see where we are heading with this, and it makes sense. Especially the ForAll.
Sadly it is still not working. It errors the .Value as per below.

Here is the actual code:

With(
{
wData:
Ungroup(
ForAll(
ProductionScheduleLocal.Model_x003a__x0020_Model_x0020_C As aLU,
Filter(
BoatsAndPackagesLocal,
aLU.Value in 'Model_code'
)
),
"Value"
)
},
Sort(
Search(
Filter(wData,VisibleinApp.Value = "Yes"),
search.Text,
"Title",
"Model_code"
),
If(
IsBlank(Index.Value) ||Index.Value = "Not Selected",
10000,
Value(Index.Value)
)
)
)

Have I missed something?
thanks,
Annamarieke

Was this reply helpful? Yes No
Verified answer

WarrenBelz 156,382 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @annamarieke ,

Coming from a collection (comparing any record in the ColFilteredToDealerAndAvailable to any record in BoatsAndPackagesLocal, you still have a many-to-many filter

With( { wData: Ungroup( ForAll( ColFilteredToDealerAndAvailable As aLU, Filter( BoatsAndPackagesLocal, aLU.Model_x003a__x0020_Model_x0020_C.Value in 'Model code' ) ), "Value" ) }, Sort( Search( Filter( wData, VisibleinApp.Value = "Yes" ), search.Text, "Title", "Model code" ), If( IsBlank(Index.Value) ||Index.Value = "Not Selected", 10000, Value(Index.Value) ) ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
annamarieke 14 on at

Like
a
(0)

Report
Copy link

Link copied!
Thank you so much, this got it working. I did tweak a couple of bits but it's now working, which is great - thank you. I have a couple of further questions for you if you are open to assisting with these:

Is it possible to get a Distinct list of the items. This formula generates multiples and I only want one entry for each model code. I've tried using the Distinct function but without success.
One of the codes is SF660, it returns these 2 codes SF660 & SF660C but I only want an exact match (SF660). Is there a way I can do this. Exact In didn't work for me.
I've seen that you have recommended that folk don't use Lookup columns as we'll encounter issues like this. How would you recommend we work instead, so that I avoid issues like this in the future?
Thank you again for your help,
Annamarieke

Was this reply helpful? Yes No
WarrenBelz 156,382 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @annamarieke ,

Answers below

Is it possible to get a Distinct list of the items. This formula generates multiples and I only want one entry for each model code. I've tried using the Distinct function but without success
The problem with Distinct() is that it returns one column only - Result. It is better to use GroupBy, where you can specify several columns, but you still need to use AddColumns to get any other fields back onto the list. With the complexity of your existing code, this would not be a simple task.

One of the codes is SF660, it returns these 2 codes SF660 & SF660C but I only want an exact match (SF660). Is there a way I can do this. Exact In didn't work for me.
If you are going to use Search, that is exactly what you are asking for (the search string is contained anywhere in the field). You can go back to equals ('Model Code' = search.Text) in your main filter instead of using Search, meaning the user would need to enter the exact code.

I've seen that you have recommended that folk don't use Lookup columns as we'll encounter issues like this. How would you recommend we work instead, so that I avoid issues like this in the future
You simply do not need them (unless you want to use "Edit in grid view in SharePoint) as all of their functions can be easily done directly in Power Apps by accessing the relevant list directly. I have a blog that may be of interest to you. Happy to elaborate further if you have a specific scenario.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
adedapo 256 on at

Like
a
(1)

Report
Copy link

Link copied!
You don't need BoatsAndPackages directly
1. Add additional fields you need to the lookup field from BoatsAndPackages (note that complex types such as Choice, People and LookUp columns are not supported)
2. Filter production schedule and group the results by model
/*You should compare by emails and not display names because display names are not guaranteed to be unique. Also save output of Office365.MyProfile ahead in a variable */ ClearCollect(colFilteredToDealerAnsAvailable, GroupBy(Filter(ProductionSchedule,AvailableforOdering.Value="Available",AssignedDealerEmail.Email=userProfile.Mail),"Model","Model_x003a__x...","Other Boat related columns")) Search(colFilteredToDealerAndAvailable,"Model","Model_x003a__x...")

Was this reply helpful? Yes No
annamarieke 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you @WarrenBelz, with your code and feedback above I have now got this working. I have redesigned the solution to eliminate the need for the lookup field.
Thank you for you help.
Annamarieke

Was this reply helpful? Yes No
annamarieke 14 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your assistance and advice @adedapo.

Was this reply helpful? Yes No