Power Platform Community Forum Thread Details

My question is whether you'd expect this to work or not...
I have done baby steps here and now I'm not getting expected results (no errors barking at me).
I have it working if I'm looking for a simple Lookup of the 'Last Name' in 'Other Occupants' but of course, it's never as simple as that...

My Other occupants value in the Hotels table is "Mouse, Mickey|Mouse, Minnie|Duck, Donald"
The Last Name value in the Attendee table is "Mouse"
The First Name value in the Attendee table is "Mickey"

I want to filter when the Last Name, First Name value which would be Mouse, Mickey is in the Other Occupants value in the Hotel table and in my example it is.

This works but it isn't specific enough...

ClearCollect(ThisAttendeeHotels,AddColumns(Filter(
 Hotels,
 Title = LookUp(
 Events,
 EventID = selectedEventID,
 PasskeyEventId
 ) && If(
 !IsBlank('Other Occupants'),
 LookUp(
 Attendee,
 ID = selectedAttendeeID,
 Trim(Upper('Last Name'))
 ) in Trim(Upper('Other Occupants'))
 )
 )
,"nts",'Occ Out Date'-'Occ In Date')
)

This doesn't work...

ClearCollect(ThisAttendeeHotels,AddColumns(Filter(
 Hotels,
 Title = LookUp(
 Events,
 EventID = selectedEventID,
 PasskeyEventId
 ) && If(
 !IsBlank('Other Occupants'),
 LookUp(
 Attendee,
 ID = selectedAttendeeID,
 Trim(Upper(Concatenate('Last Name',", ",'First Name')))
 ) in Trim(Upper('Other Occupants'))
 )
 )
,"nts",'Occ Out Date'-'Occ In Date')
)

Categories:

Building Power Apps

For the record this is working... and I'll show the code for others in case it helps them....
If anyone wants to offer any suggestions I'd appreciate it.

ClearCollect(
 ThisAttendeeHotels,
 AddColumns(
 Filter(
 Hotels,
 Title = LookUp(
 Events,
 EventID = selectedEventID,
 PasskeyEventId
 ) &&
 /* match on email address */
 (Trim(Upper(Email)) = LookUp(
 Attendee,
 ID = selectedAttendeeID,
 Trim(Upper(Email))
 ) ||
 /* match on employee # */
 If(
 !IsBlank(WorkerNo),
 Trim(Upper(WorkerNo)) = LookUp(
 Attendee,
 ID = selectedAttendeeID,
 Trim(Upper(SourceId))
 )
 ) ||
/* match on last name, first name in the other occupants field */
 If(
 !IsBlank('Other Occupants'),
 LookUp(
 Attendee,
 ID = selectedAttendeeID,
 Trim(
 Upper(
 Concatenate(
 'Last Name',
 ", ",
 'First Name'
 )
 )
 )
 ) in Trim(Upper('Other Occupants'))
 ))
 ),
 "nts",
 'Occ Out Date' - 'Occ In Date'
 )
);