Answered

Asset availability

(0) Share

Report

Posted on by DavidRyan

I'm creating a power app to enable users to find available assets and book these for specific days. I've created two SharePoint lists:

'Asset List' :This is a list with all the specifications (Name, type, colour etc.) of the asset including the mandatory Title column which has a unique serial number for the Asset.

'Asset Reservation List': This lists the Title (same unique serial number of the asset), who has booked the asset (based on O365 users within my organisation) and booked from and booked to columns (which are date fields) for all the assets that have already been booked.

Within in my App I have created a screen where users can select their dates for booking (from and to), when they submit this the dates are used to filter the 'Asset Reservation List' for all bookings made on those dates and then using the information returned to filter the 'Asset List' by the Title field to return Assets that are not in the first filter and then list these in a gallery on the next screen where users can select which asset they want to book.

This is the formula I've used but it is not working

Filter('Asset List', Not(Title in Filter('Asset Reservations List', 'Check Out from'>=DateFrom && 'Check Out to'<= DateTo).Title)

Is this the right solution and if so why is the formula wrong or is there a simpler way to do this.

thx

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

Ismael_Novo 308 on at

Like (0)

Report

Hi David.

Do you know the list item version history? Is enought for you?

https://support.microsoft.com/en-us/office/view-the-version-history-of-an-item-or-file-in-a-list-or-library-53262060-5092-424d-a50b-c798b0ec32b1

Kind regards

Was this reply helpful? Yes No
WarrenBelz 153,073 Most Valuable Professional on at

Like (0)

Report
Hi @DavidRyan ,

I cannot see your model, but normally you would need to check if the booking falls anywhere in the period (so neither the to or from dates can be within it)

Filter( 'Asset List', !( Title in Filter( 'Asset Reservations List', ('Check Out from' >=DateFrom && 'Check Out from' <= DateTo) || ('Check Out to' >=DateFrom && 'Check Out to' <= DateTo) ).Title ) )

Also if you have the possibility of take/return times being on the same day, you can remove the = where appropriate.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
DavidRyan 21 on at

Like (0)

Report

Dear @WarrenBelz

Thank you for your reply. Forgive my total ignorance but what do you mean by 'Model'.

I've tried your solution and the app is now showing a list of assets (thanks!), but regrettably it is not filtering out assets that have already been booked in between the dates I've entered.

Was this reply helpful? Yes No
DavidRyan 21 on at

Like (0)

Report

Dear @Ismael_Novo

Thank you for your reply.

Do you mean I need more than 50 versions of an item?

I assumed that each booking would be an individual record

regards

David

Was this reply helpful? Yes No
WarrenBelz 153,073 Most Valuable Professional on at

Like (0)

Report

@DavidRyan ,

Model is simply referring to the fact that I cannot see your app or data (so have to rely on what you have posted). I am a bit lost as the code I supplied (providing your values are correct) should exclude any records where either the 'Check Out From' or the 'Check Out To' are between the DateFrom and the DateTo. Please look at the logic yourself and you will see this. This is of course assuming the Title field in 'Asset Reservations List' has the same (corresponding) value as the Title field in 'Asset List' - as this is what is being compared for a potential match.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
DavidRyan 21 on at

Like (0)

Report

Dear @WarrenBelz

Thanks for the reply

Here are screen shots of the two lists and the screen that should list the available vehicles.

Does it matter that 'Title' is the column name in both lists?

Asset List

Asset Reservation List

Am I using the date picker tool correctly?

regards

David

Was this reply helpful? Yes No
WarrenBelz 153,073 Most Valuable Professional on at

Like (0)

Report
@DavidRyan ,

Probably last throw of the dice here - are DateFrom and DateTo Variables or those Date Pickers on the screen ?
Also unless you have a Delegation issue with a large list, it should simply work, so try this

With( { _Reserve: Sort( 'Asset Reservations List', ID, Descending ), _Asset: Sort( 'Asset List', ID, Descending ), _From: DatePickerFromName.SelectedDate, _To: DatePickerToName.SelectedDate }, Filter( _Asset, !( Title in Filter( RenameColumns( wReserve, "Title", "RegNo" ), ('Check Out from' >= _From && 'Check Out from' <= _To) || ('Check Out to' >= _From && 'Check Out to' <= _To) || ('Check Out to' < _From && 'Check Out to' > _To) ).RegNo ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
DavidRyan 21 on at

Like (0)

Report

Dear @WarrenBelz

Thanks for the reply

I have a screen (below) before the one shown in the previous post , this is where users select their dates, then on pressing confirm it sets the dates and shows the gallery on the next page (the page from my previous post) with the available assets. The date boxes on this second screen are just labels with Text = DateFrom and DateTo

regards

David

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,073 Most Valuable Professional on at

Like (0)

Report
@DavidRyan ,

You need to set the Variables to

DatePicker_From.SelectedDate

and

DatePicker_To.SelectedDate

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
DavidRyan 21 on at

Like (0)

Report

Dear @WarrenBelz

Thanks, yes it is now working

regards

David

Was this reply helpful? Yes No