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

Advances filtering based on two SharePoint list

HI All,

Can someone please tell me how can I build the following SQL on PowerApps on a button click.

SELECT *
FROM CarDetails CD, BookingDetails BD
WHERE CD.CarRegistartionNumber != BD.CarregistrationNumber
AND @Pickupdate <= BD.Dropoff_Date AND @Pickuptime > BD.DropOffTime
AND @DropoffDate >= BD.PickupDate AND @DropOffTime < BD.PickupTime

 

Here CarDetals is a seperate SharePoint list and BookingDetails is a Seperate SharePoint list, I want to chcek all available cars for booking on the given date range, @Pickupdate ,@DropoffDate and @Pickuptime are user selection based on a drop down.

I would like to do button OnSelect= Filter('CarDetailsDS', the above query) here CarDetialDS is the data source that is bind to a data table. So based on the query output the data table fill with dynamic data.

 

Can someoen please help me on this. 

Much appareciate. 

 

 

7 REPLIES 7
Anonymous
Not applicable

Re: Advances filtering based on two SharePoint list

Hi again @NewNW,

 

Here's how I would have done.

You create a DataTable and you set Items to :

Filter(
    AddColumns(
        Test1,
        "Col1",
        LookUp(Test2, IDTest2 <> Test1[@IDTest1], Column1),
        "Col2",
        LookUp(Test2, IDTest2 <> Test1[@IDTest1], Column2),
        ...
    ),
    Dropdown1.Selected.Value <= Dropoff_Date,
    Dropdown2.Selected.Value > DropOffTime,
    Dropdown3.Selected.Value >= PickupDate,
    Dropdown4.Selected.Value < PickupTime
)

In this, my IDTest1 and IDTest2 are your CarRegistrationNumber and my Test1 and Test2 are your CarDetails and BookingDetails BD.

In the AddColumns function, you set all the columns from Test2 that you want to add to Test1 DataTable.

And in the filter you exchange my dropdowns with names you have.

 

Does that help you ?

 

Best regards,

 

Louis

NewNW
Level: Powered On

Re: Advances filtering based on two SharePoint list

Thank you very much LouisC, much appreciate your help.

Could you please expalin what is Col1 and Coulmn 1 and waht should I include under [@IDTest1].

Sorry still learning.

Please see the attached lists for your refrence.

Is the following is correct ?

Filter(
AddColumns(
CarDetails,
"CarRegistrationNumber",
LookUp(BookingDetails, CarRegistrationNumber<> CarDetails[@CarRegistrationNumber], CarType),
"CarType",
LookUp(BookingDetails, CarRegistrationNumber<> CarDetails[@CarRegistrationNumber], Transmission),
"Transmission"
),
dtpDropOffDate.Selected.Value <= DropoffDate,
dpdDropOffTime.Selected.Value > DropOffTime,
dtpPickupDate.Selected.Value >= PickupDate,
dpdPickupTime.Selected.Value < PickupTime
)

Anonymous
Not applicable

Re: Advances filtering based on two SharePoint list

No problem 🙂

 

The formula you did seems to be good!

To explain, each couple of lines like :

"CarType",
LookUp(BookingDetails, CarRegistrationNumber<> CarDetails[@CarRegistrationNumber], CarType),

is a duet 

"NameOfTheColumnAddedToCarDetails",
LookUp(SourceOfColumn, IDofSecondTable<> FirstTable[@IDofFirstTable], ColumnNameInTheSource),

where IDofSecondTable<> FirstTable[@IDofFirstTable] is the link between your 2 tables.

By this way, you can add the number of columns from an other source that you want.

Then, Col1 in my example is the name of the column that I add in the table and Column1 is the real name of my column in the source Test2...

 

I don't know if I'm clear and if my english is right, I'm trying my best 😉

 

Does your formula work for you ?

 

Louis

Anonymous
Not applicable

Re: Advances filtering based on two SharePoint list

Oooh I didn't see you have an error in your formula sorry.

 

Try with this :

 

Filter(
    AddColumns(
        CarDetails,
        "CarType",
        LookUp(BookingDetails, CarRegistrationNumber<> CarDetails[@CarRegistrationNumber], CarType),
        "Transmission",
        LookUp(BookingDetails, CarRegistrationNumber<> CarDetails[@CarRegistrationNumber], Transmission)
    ),
    dtpDropOffDate.Selected.Value <= DropoffDate,
    dpdDropOffTime.Selected.Value > DropOffTime,
    dtpPickupDate.Selected.Value >= PickupDate,
    dpdPickupTime.Selected.Value < PickupTime
)
NewNW
Level: Powered On

Re: Advances filtering based on two SharePoint list

Thank you very much, I will try tomorrow morning and let you know, really appreciate your help.
Anonymous
Not applicable

Re: Advances filtering based on two SharePoint list

It's a pleasure, do not hesitate to come back to me if you have other requests 🙂

NewNW
Level: Powered On

Re: Advances filtering based on two SharePoint list

Hi LouisC,

I tried the query as follows but I wont get any value in to Data table.

 

query.png

When a user select the pick up date, pick up time, drop off date, drop off time, I want to show the avaliable cars on data table. 

screen.png

Following are the data on Booking and car details list.

data1.png

data.png

list as follows

List.png

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,004)