cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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
Highlighted

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
Highlighted
Super User II
Super User II

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)
Highlighted

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

Highlighted

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)

Highlighted

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.

 

 

Highlighted

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

Highlighted

That did help, thank you!

 

All working now 🙂

Highlighted

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (9,663)