Advances filtering based on two SharePoint list

[NewNW]

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. 

[Anonymous]

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]

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]

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]

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]

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

[Anonymous]

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

[NewNW]

Hi LouisC,

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

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. 

Following are the data on Booking and car details list.

list as follows