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

Filter gallery item with lookup.

Hi All,

 

I'm getting into a bit of a twist with this one. I have a mapping table which holds the ID's of related records. I want to list the items from the mapping table as their actual names instead of the ID, but the items also need to be filtered by the module they are listed under. I have the module name as a text box, and have a lookup bringing the correct name back. So to get the related items I need to use lookup to get the ID of the module, then filter by  items in the mapping table, and return the item name with another lookup. Here's what I have, with a picture of the mapping table and the listbox I need the item names to go into:

 

Filter(Mapping, crb20_module = LookUp(Modules, crb20_module = Label1.Text, crb20_module_id, LookUp(Items, crb20_item_id = crb20_item, crb20_item);

 

Filter.png

 

Please help, I'm going in circles!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filter gallery item with lookup.

I differentiated the IDs in this formulae: 

 

Filter(ItemList, ItemID in Filter(ItemModuleMappingList, MappingModID = LookUp(ModuleList, ModuleName = TextInput1.Text).ModuleID).MappingItemID)

 

This is assuming your:

1. mapping table (ItemModuleMappingList) has 2 columns: MappingModuleID and MappingItemID

2. module table (ModuleList) has 2 columns: ModuleID, ModuleName

3. items table (ItemList) has 2 columns: ItemID, ItemName

 

Does this help? 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

View solution in original post

7 REPLIES 7
Super User
Super User

Re: Filter gallery item with lookup.

Let's see if I got this right

1. A lookup to get module ID based on name entered in a text box
2. Filter the list of items based on the module they are mapped to
3. This will give you the list of items but in ID form
4. You need to do a lookup to get the names of all these items

Is that correct?

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)
Kathryn_Marie
Level: Powered On

Re: Filter gallery item with lookup.

Yep, that's right. I have the Module name working in the textbox, but now I need to get the list of items mapped to that module into the listbox.

 

Thanks

Super User
Super User

Re: Filter gallery item with lookup.

I have attached a screenshot that shows how to configure a combo box to display the Item names based on the filters. I have a combo box of all items and I have selected the ones that pass the filter test. 

 

The Items property of the combo box is set to ItemsList and the DefaultSelectedItems property of the combo box is set to 

Filter(ItemList, ID in Filter(ItemModuleList, ModID = LookUp(ModuleList, Name = TextInput1.Text).ID).ItemID)

 

If instead you simply want to have a combo box with the item names of the items that pass the filter test, set the Items property of the combo box to: 
Filter(ItemList, ID in Filter(ItemModuleList, ModID = LookUp(ModuleList, Name = TextInput1.Text).ID).ItemID)

 

Let me know in case of any questions. 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

Kathryn_Marie
Level: Powered On

Re: Filter gallery item with lookup.

Thanks, I think I'm getting confused with the ID's.

 

I have:

 

Filter(Items, crb20_item_id in Filter(Mapping, crb20_module = LookUp(Modules, crb20_module = TextInput1.Text).crb20_module_id).crb20_item)

 

I'm not getting an error, but I'm also not getting a result.

 

In the mapping table the column crb20_module contains the ID, and crb20_item contains that ID. The ID of the record is Map_id.

 

In the Modules table the ID is crb20_module_id, in the items table the ID is crb20_item_id. The Item title is in the field crb20_item.

 

 

Super User
Super User

Re: Filter gallery item with lookup.

I differentiated the IDs in this formulae: 

 

Filter(ItemList, ItemID in Filter(ItemModuleMappingList, MappingModID = LookUp(ModuleList, ModuleName = TextInput1.Text).ModuleID).MappingItemID)

 

This is assuming your:

1. mapping table (ItemModuleMappingList) has 2 columns: MappingModuleID and MappingItemID

2. module table (ModuleList) has 2 columns: ModuleID, ModuleName

3. items table (ItemList) has 2 columns: ItemID, ItemName

 

Does this help? 

 

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution. Thanks!

Hardit(Haman)

View solution in original post

Kathryn_Marie
Level: Powered On

Re: Filter gallery item with lookup.

That did help, thank you!

 

All working now 🙂

Super User
Super User

Re: Filter gallery item with lookup.

Glad I could help! 

 

Good luck with the rest of the app! Feel free to reach out in case of any questions!

 

Thanks,

Hardit (Haman)

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (4,677)