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

Filtering based on 2 tables

I am building an app with PowerApps and struggling to come up with a formula to filter 2 tables.

 

The app is a booking system for loaned laptops and for the most part is working nicely.

 

I have 2 SQL tables dbo.BookingData and dbo.Laptops

 

dbo.BookingData has several fields for the booking data and dbo.Laptops has a Laptop field containing a complete list laptop names.

 

What i want is a gallery that lists all available laptops i.e. not booked out already so that i can use this in a dropdown box on the booking form, currently i just use the formula for Items '[dbo].[Laptops]' and the title as ThisItem.Laptop

 

On my booking form the Items formula is LaptopList.AllItems (Gallery called LaptopList)

 

dbo.BookingData has a LaptopID field and a Returned field, the returned field would be a 1 or a null, 1 being it has been booked out and then returned and is available or null being its been booked out and is not available.

 

I need a formula for the gallery LaptopList that will list all laptops that are in dbo.Laptops and arent in dbo.BookingData.LaptopID with a null value for dbo.BookingData.Returned.

 

I am a bit of a noob with this and i have spent a couple of hours trying to find examples online but not been able to find anything that works so any help would be greatly appreciated.

 

1 ACCEPTED SOLUTION

Accepted Solutions
ccoombes
Level: Powered On

Re: Filtering based on 2 tables

I came up with another solution to this in the end.

 

I added an extra field to dbo.Laptops called Available, when booking out it sets a value to 0 with Patch function and when checking back in it sets value to 1 which makes it much simpler.

4 REPLIES 4
Mike8
Level 10

Re: Filtering based on 2 tables

Hello,

Does this work for you?
LaptopList.Items = Filter('[dbo].[Laptops]',(ID in Filter('[dbo].[BookingData]',Returned=1).LaptopID ))
If it works, it may need some time to retrieve the data.

ccoombes
Level: Powered On

Re: Filtering based on 2 tables

Thanks, it does work i just had to change to

 

Filter('[dbo].[Laptops]',(Laptop in Filter('[dbo].[BookingData]',Returned=1).LaptopID ))

 

The only downside is it relies on the dbo.BookingData.LaptopID field to be fully populated with the list of laptops to work so if we were to add a new laptop to stock then we would need someone to have booked it before for it to populate the gallery.

ccoombes
Level: Powered On

Re: Filtering based on 2 tables

Unfortunately it won't work as there will be more than 1 record that has the same laptop with the LaptopID field set. 

 

i.e. after it has been booked once and has a 1 in the returned field the formula will return the value even if another record exists with a null value.

ccoombes
Level: Powered On

Re: Filtering based on 2 tables

I came up with another solution to this in the end.

 

I added an extra field to dbo.Laptops called Available, when booking out it sets a value to 0 with Patch function and when checking back in it sets value to 1 which makes it much simpler.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, August 21st at 8am PDT

Top Community Contributors for July 2019

Top Community Contributors for July 2019

Let's thank our top community contributors

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

FifthImage

Dynamics 365 and Power Platform April 2019 Release notes

Features releasing from April 2019 through September 2019!

SixthImage

Power Summit Australia 2019

August 20-23rd 2019

Users Online
Currently online: 182 members 4,065 guests
Please welcome our newest community members: