Answered

Filtering with variable

(0) Share

Report

Posted on by JanDras

Hello,

I need help troubleshooting my issue here. I'm using some 'off the shelf' desk reservation app I found on the internet and modified it for our specific use case. The request now is to see who has the desk reserved before & after the specific user.

As a "database" of these reservations a List is used. This list is named 'Desk Reservations'.

This list has following columns: 'DeskText' which can be used as a idenfitifier for the specific desk; 'Reserved By' as to see who reserved the desk and 'Check Out From' a 'Check Out To' which mark the start & end of the reservation. The formate is 'DD/MM/YYYY HH:MM'

Variable is also used 'varSelectedReservation' which is defined as Set(varSelectedReservation;ThisItem);;Navigate(Reservation)

Now I want to create a formula that'd would show in a field an output ('Reserved By') of the closest person that has the reservation of the same spot before the ThisItem reservation.

Problems:
1. If I use varSelectedReservation.'Reserved By' it says that there's an error in this formula and outputs nothing - in the source, this column is Office365 user; If I input varSelectedReservation.'Check Out To' it works as intended

2. Now the main issue, I've tried creating a formula that makes sense in Excel but doesn't work in Power Apps and I can't tell why.

FirstN(
 SortByColumns(
 Filter(
 'Desk Reservations';
 DeskText = varSelectedReservation.DeskText &&
 'Check Out From' >= varSelectedReservation.'Check Out From'
 );
 'Check Out From';
 SortOrder.Ascending
 );
 1
).'Reserved By'

Note: Local PowerApps use ";" in arguments instead of international ",".

I'm thinking there's only something wrong with the syntax or logic I use.

I'd really appreciate if anyone could help.

Categories:

Connecting to Data

General Questions

All responses (4)

Answers (1)

Verified answer

JanDras 85 on at

Like (1)

Report
Re: Filtering with variable

Here's a solution. I created a gallery with following code
FirstN( SortByColumns( Filter( 'Desk Reservations'; DeskText = varSelectedReservation.DeskText && 'Check Out To' <= varSelectedReservation.'Check Out From' ); "CheckOutFrom"; SortOrder.Ascending ); 1 )
Works now.
JanDras 85 on at

Like (0)

Report
Re: Filtering with variable

If I use LookUp function, I'm getting back a value, if I use the same with filter() I get an error. I don't understand why.

LookUp('Desk Reservations';DeskText = varSelectedReservation.DeskText).'Reserved By'.DisplayName
JanDras 85 on at

Like (0)

Report

Re: Filtering with variable

Hello @Nogueira1306 ,

Yes, the app is from @aprildunnam but my issue isn't relatable to what you've sent as for some reason, the filter function does't work in the syntax given in the original message.
Nogueira1306 7,390 Super User 2024 Season 1 on at

Like (0)

Report

Re: Filtering with variable

I think that this might be able to help you:

https://www.youtube.com/watch?v=zroxwXpVhHI&t=624s&ab_channel=AprilDunnam

https://www.youtube.com/watch?v=n8fJXTMPYsg&ab_channel=AprilDunnam

This demos are from @aprildunnam

If you need additional help please tag me in your reply and please like my reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️!

Best regards,

Gonçalo Nogueira

Check my LinkedIn!

My website!

My blog!