Hello guys I need help, this is driving me insane.
I have 2 galleries that are 2 entities that comes from Common Data Service current environment connector.
In gallery A, I have an entity called "Catalog Groups catalogs".
In gallery B, I have an entity called "Matrix Profile Catalog".
Gallery B entity has a lookup field to Gallery A entity called "Catalog Group", and also a field CODE which is unique.
Now I want to create a dropdown which displays all items from gallery A which CODE is being pointed from any record from gallery B.
I tried thousands of combinations, and thought this had to work but it is not
Filter(GalleryA.AllItems,Code in GalleryB.AllItems.'Catalog group'.Code)
'Catalog group' is the lookup field that goes from B to A, so B.'Catalog group'.Code is the same as A.Code.
What is wrong with all this?
Your syntax is not correct. I am assuming that you have a one to many relationship between the two tables and tableA (Parent) is the One field and TableB (Child) is the Many field. Also, I am assuming that the "Code" field in TableA is a primary key. To avoid ambiguity, I would rename the field in the Child table to Acode, indicating that this field is a foreign key from TableA. In your example, you are using a strange way of describing a table (GalleryA.AllItems,Code), instead I would simply use the Items property in GalleryA exactly as it is written as the filter for your dropdown control.
Filter(TableB, Acode= TableA.Selected.Code)
Make sure that the Window of your dropdown shows the field that you want. In the screenshot below TableA would be States and is shown in the datatable on the left. TableB is a contact list of fictitious companies (FakeNames).. The dropdown shows the companies in the selected state. Since the Items property of a dropdown is a filtered table, the same filtered table can be displayed in a gallery like the one on the right.
Hello @Drrickryp ,
thanks for your help.
Issue here is that I don't have any selected item, following your example, you would have in each state from TableA a main contact from TableB. This table B is prefiltered, so it does not have all contacts from your database.
Then I would like to see on my dropdown only the states from TableA which main contact is on this filtered contact list from TableB.
Hope this helps with the issue.
Just so that I understand the problem, Are you saying that you want to create a list of unique Codes from TableB to filter TableA?
OK let's start over so it is 100% clear with your analogy.
I have TableA, with states.
I have TableB, with contacts, which are already prefiltered like type=X.
States have a unique code field.
Contacts have a lookup field pointing to the state they belong.
I have a dropdown on my app which should display only the states that have any contacts pointing to them.
I have always worked with Model Driven and i would easily do this with queries using odata or fetch accesing the guids from the lookups, but those are not accesible from my app, that is why I am trying like "bring me all the states which code is one of the codes being pointed by the related state of the contact list".
Thank you for your help.
Would something like this work for you as the formula for your dropdown control. The With statement sets the filter on the contacts list creating a temporary table called "cit" and the Distinct() produces a unique result based on the filtered list. In the example, I filtered the original contact table on cities starting with "g" but it could have been an entry into a textinput box, giving users a dynamic way of filtering the results in the dropdown. Please check out the Distinct() function if you are not familiar with it. It will produce a single column table with unique values called "Result". You can use that value to further filter another table as in
Check out new user group experience and if you are a leader please create your group
Did you know that you can visit the Power Query Forum in Power BI and now Power Apps
Participate in the Power Virtual Agents Community Challenge
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.