Unanswered

Collection with filter from other collection

(0) Share

Report

Posted on by Djack

I have a issue

I have a collection that gives me a list of CustomerID's depending on a selection.
I want to create a second collection with al records where the CustomerID = CustomerID of Collection1

Collect1
ClearCollect(colCustomerID,Distinct(Filter(Contracts,TypeID=MS_DD_Type.Selected.ID),CustomerID));

--> this collection works

Collection2
ClearCollect(colCustomers,Filter(CustomerList,CustomerID=colCustomerID.Result));

I have a list with customers in sharepoint

Holds only customer data like name email etc...

I have a contract list with hold the contracts

This list has a contractid, customerid, and producttypeid

I want to filter a customer list on producttypes (from a pulldown)

Categories:

Building Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

Hi @Djack

What you are describing is a Many to Many relationship and you have set up your lists perfectly to achieve your aim. However, I think you are making it more complicated than it needs to be by creating collections. What you need is a dropdown or Combobox control with the Items being producttype. If you like, you can make the Items property of the dropdown, Sort(producttype, Ascending). FYI a dropdown is limited to 500 items, for more than that you will need a combobox. Below the dropdown, place a gallery with the items property Contracts. Put a Label in the gallery with the Text property Lookup(Customers, ID = ThisItem.CustomerID, CustomerName). You could also add lookups for all of the details in the Customer list by replacing CustomerName with Address, etc. in the Lookup formula. The beauty of your design is that you can work it from the other side, looking up producttypes in the Contracts by Customer: having the dropdown items Customers, the gallery as Contracts and the Lookup in the gallery Lookup(Producttype,ID=ThisItem.ProductTypeID, Producttype1), etc

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like (0)

Report

@Djack

A Many to Many relationship is really two One to Many relationships involving a junction table. To see how to implement the edit form for your lists, check out my post here https://powerusers.microsoft.com/t5/News-Announcements/Relational-Database-Design-fundamentals-Implementing-a-One-to/ba-p/200521

Was this reply helpful? Yes No
Djack 44 on at

Like (0)

Report

Thanks I did it with collections and the issues is solved.
SPLists have limitations and i keep thinking in real database model what it is not.

Was this reply helpful? Yes No