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

[FuadJabbr]

Hi

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

[victorcp · Accepted solution]

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]

Thank you very very very much Victor. 

Apart from the delegation issue, it works like a charm.

[FuadJabbr]

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]

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]

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]

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

[victorcp · Accepted solution]

you have to change the display fields here

[FuadJabbr]

Thanks a ton Victor.

[FuadJabbr]

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.