cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Lookup/Nest/Search/??

Hello,

I have 3 SQL tables, Items, Facilities and ItemFacilityLinks. I can have multiple facilities associated with an Item record, hence the link table. I am trying to display a grid of facility names in the Item record's Gallery of a DetailsScreen. How can I reach the Facilities table to get the name (as opposed to the ID). I can do it with a SQL statement, but can't figure out how to accomplish this in PowerApps. Thanks for any guidance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Lookup/Nest/Search/??

Hi @shlittle
Old but good . . . not really necessary in PowerApps as it complicates the filters a bit. I do not have anything to test this one, but have a go at the below. You will get the idea from the structure what has to be done. Also it is not a good idea to have matching fields with the same name - can lead to ambiguity in queries.

Filter(
   Facilities,
   FacilityID =
   Lookup(
      ItemFacilityLinks,
      ComplianceItems.ItemID = ItemID
   )
)
 

An easier solution would be to have the FacilityID in ComplianceItems, <

 

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.

View solution in original post

4 REPLIES 4
Highlighted
Super User
Super User

Re: Lookup/Nest/Search/??

Hi @shlittle ,

Scenario - you have the Item name in a field in the Facility record and you select the item in a drop-down (called ddItem) from a choice column

Your gallery would by

Filter(
   Facilities,
   Item = ddItem.Selected.Value
)

In the gallery, you simply display ThisItem.Facility to show the name

If this is not the case, can you please explain a bit more how ItemFacilityLinks works?

 

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.

 

Highlighted
Frequent Visitor

Re: Lookup/Nest/Search/??

Thank you for your reply, Warren. Hopefully the attached database diagram will explain my thought process. And I'll preface this by saying I have not messed with databases in years, so this may be old design thinking. The Links table allows for multiple facilities to be linked to an Item, and multiple Items to be linked to a Facility. Thanks.

Highlighted
Super User
Super User

Re: Lookup/Nest/Search/??

Hi @shlittle
Old but good . . . not really necessary in PowerApps as it complicates the filters a bit. I do not have anything to test this one, but have a go at the below. You will get the idea from the structure what has to be done. Also it is not a good idea to have matching fields with the same name - can lead to ambiguity in queries.

Filter(
   Facilities,
   FacilityID =
   Lookup(
      ItemFacilityLinks,
      ComplianceItems.ItemID = ItemID
   )
)
 

An easier solution would be to have the FacilityID in ComplianceItems, <

 

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.

View solution in original post

Frequent Visitor

Re: Lookup/Nest/Search/??

So this finally worked:

 

Filter('[dbo].[Facilities]', FacilityID in Filter('[dbo].[ItemFacilityLinks]', ItemID=Value(ItemID_DataCard1.Default)).FacilityID)

 

Thank you for leading me towards the solution!

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,736)