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.