Answered

Display sharepoint column value which is not in another sharepoint column value

(0) Share

Report

Posted on by FuadJabbr

I have 2 sharepoint list

1) Booking list which has Title, Departure Date, Return date and a look up column name Vehicle which gets the vehicle name from Vehicle list

2) Vehicle list which has Title, Vehicle no(this column is the reference for lookup in my booking list

What I am trying to achieve is to display the vehicle that is not booked for a particular date and time.

I am able to pull all my bookings for a particular To and From date using the below formula

Filter(
'FAJ_Test_Transport booking',
(var_starttime > 'Departure Date' && var_starttime < 'Return date') || (var_endtime < 'Return date' && var_endtime > 'Departure Date') || ('Departure Date' > var_starttime && 'Departure Date' < var_endtime) || ('Return date' < var_endtime && 'Return date' > var_starttime)

Where

Set(var_starttime,'Departure Date_DataCard1'.Update); - var_startime is value when the user selects a date in forms
Set(var_endtime,'Return date_DataCard1'.Update); - var_endtime is value when the user selects a date in forms

I am stuck in getting those vehicles which are not booked for a requested date and time. Please help

Categories:

Building Power Apps

All responses (12)

Answers (2)

Sort by

Verified answer

victorcp 2,350 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hi,
I could reach that removing all the active vehicle using 'Not' function, there is a delegation warning but if you have less than 2000 vehicles it will not be a problem.
Filter( Vehicle, // Name of your Vehicle list Not( ID in ForAll( Filter( 'FAJ_Test_Transport booking', (var_starttime > 'Departure Date' && var_starttime < 'Return date') || (var_endtime < 'Return date' && var_endtime > 'Departure Date') || ('Departure Date' > var_starttime && 'Departure Date' < var_endtime) || ('Return date' < var_endtime && 'Return date' > var_starttime) ), Vehicle.Id // Check the Vehicle column name in 'FAJ_Test_Transport booking' ) ) )
For a better performance, I would save the ForAll function into a variable when you set the other variables. and the code will be like this:
Set( var, ForAll( Filter( 'FAJ_Test_Transport booking', (var_starttime > 'Departure Date' && var_starttime < 'Return date') || (var_endtime < 'Return date' && var_endtime > 'Departure Date') || ('Departure Date' > var_starttime && 'Departure Date' < var_endtime) || ('Return date' < var_endtime && 'Return date' > var_starttime) ), Vehicle.Id // Check the Vehicle column name in 'FAJ_Test_Transport booking' ) )
Filter( Vehicle, // Name of your Vehicle list Not( ID in var ) )
FuadJabbr 73 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you very very very much Victor.
Apart from the delegation issue, it works like a charm.
FuadJabbr 73 on at

Like
a
(0)

Report
Copy link

Link copied!

I tried to use the above formula in a gallery and that works fine. However I am trying to use a form with this lookup column and want to display the filter for the lookup column and also patch the the selected value to the column.
When I apply this formula, the dropdown is empty.
victorcp 2,350 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,
is it a combobox or a dropdown? if it is a dropdown please change it to a combobox, because it will be easier to update.
How is the items of your component?
FuadJabbr 73 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,
Its a combobox, and I tried with a drop down as well. Basically this is a lookup column on a list called "FAJ_Test_Transport booking" which takes the information from another list called " FAJ_Test_Transport vehicles".
In powerapps, I am using Edit form control and want this lookup column to display only the vehicles which are available for the selected date and time and when selected it should be updated on the booked item

When I use the formula you provided on the items of a gallery, it works fine, however, when I use the same formula on the combo box(datacard value) items , it shows blank. I am using the same formula. Not sure if there is a different syntax to be used on a lookup column.

Hope I am clear. If not please let me know
FuadJabbr 73 on at

Like
a
(0)

Report
Copy link

Link copied!

When I check the formula, it shows up the required items for the combo box items, however, when I click on the drop down, it shows like this
Verified answer

victorcp 2,350 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

you have to change the display fields here
FuadJabbr 73 on at

Like
a
(1)

Report
Copy link

Link copied!

Thanks a ton Victor.
FuadJabbr 73 on at

Like
a
(0)

Report
Copy link

Link copied!

The vehicle information is now populated on the lookup column datacard but when we submit form it does not update the column in the list, basically blank for this lookup field.
When I checked the update function of the data card, it is "Datacardvalue.Selected" and this displays a record like

And if type is Datacardvalue21.Selected.Vehicle_No, there is an error in the formula, which says its a text value.
victorcp 2,350 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
Hi,
when you update a lookup field you need to create a object like this:
{Id: 4, Value: ""}
in your case, I would suggest you to remove the vehicleNo here
and use this code:
{ Id: DataCardValue21.Selected.ID Value: "" }