Unanswered

Issues with Filters that have Multiple Conditions

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

hi all,

Is anyone else having issues with Filters with multiple conditions not working? I have several throughout my app and they all worked flawlessly in the past, but now are either not working at all, or are returning nothing or incomplete data.

For example:

ClearCollect(colAttendees,Sort(Filter(MeetingAttendees,Title = MeetingID And Office365Users.UserProfileV2(AttendeeName.Email).accountEnabled = true),Office365Users.UserProfileV2(AttendeeName.Email).surname));

This used to successfully filter out people who have left the company, but now stops once it finds accountEnabled = false – ie. it should return 7 people but only returns the first 2

Similarly:

Patch(MeetingAttendees,First(Filter(MeetingAttendees,Title = MeetingID And Lower(EmailAddress.Address) = Lower(AttendeeName.Email))),{OutlookInviteResponse: Status.Response})

This can’t find the record, even though it is definitely there.

I’ve tried changing And to &&, separating the criteria by a comma, and changing it to Lookup(), but none seem to work.

Anyone else experiencing the same, or have an insight?

Thanks in advance,
Mik

Categories:

Building Power Apps

All responses (5)

Answers (0)

Sort by

v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
HI @Anonymous ,

Regarding the formula that you mentioned, I could not find any syntax error with it. Could you please share more details about the data structure of your MeetingAttendees data source?

For your first formula, please modify it as below:

ClearCollect( colAttendees, Filter( MeetingAttendees, Title = MeetingID, Office365Users.UserProfileV2(AttendeeName.Email).accountEnabled = true ) )

Note: Make sure the MeetingID variable value has same data type as the Title column in your MeetingAttendees data source.

For your second formula, please consider modify it as below:

Patch( MeetingAttendees, LookUp(MeetingAttendees, Title = MeetingID && Lower(EmailAddress.Address) = Lower(AttendeeName.Email)), { OutlookInviteResponse: Status.Response } )

Note: Make sure EmailAddress.Address formula returns a email address as the AttendeeName.Email formula. And the AttendeeName field is a Record type value in your MeetingAttendees data source.

Please also consider remove the connection to your MeetingAttendees data source, then re-create a new connection to it from your canvas app again, check if the issue is solved.

Regards,
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Kris, thanks for the detailed reply.

The MeetingAttendees data source is a SharePoint List. The significant columns in this case are Title, which is a single line of text holding a GUID, AttendeeName, which is a Person column, and OutlookInviteResponse which is also a single line of text. MeetingID is text (GUID).

The Patch statement from above is part of a larger code block that checks an Outlook invite for people and compares that to the list of people for the meeting. If they are not found, it adds them to the meeting (this part works fine). If they are found, it is supposed to store their response from the Outlook invite. This is the part that is failing as it isn't finding the record. EmailAddress.Address pulls from the Outlook invite and is definitely an email address.

here's the full code for reference:

ForAll(peopleResponse, If(IsEmpty(Filter(peopleCheck,Lower(EmailAddress.Address) = Lower(AttendeeName.Email))),
If(Lower(EmailAddress.Address) <> "Organizer's Email Address",
Patch(MeetingAttendees,
Defaults(MeetingAttendees),
{
Title: MeetingID,
AttendeeName: {
'@odata.type': "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser",
Claims: "i:0#.f|membership|" & EmailAddress.Address,
Department: "",
DisplayName: EmailAddress.Name,
Email: "",
JobTitle: "",
Picture: ""
},
OutlookInviteResponse: Status.Response
})),
Patch(MeetingAttendees,LookUp(MeetingAttendees, Title = MeetingID && Lower(EmailAddress.Address) = Lower(AttendeeName.Email)),{OutlookInviteResponse: Status.Response})));

I've tried your code suggestions and removing and re-adding the MeetingAttendees data source, but still have no luck in this working.

Any other ideas would be appreciated!

thanks again,
Mik
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Currently, I could not reproduce your issue. Is there some error message occurred when you execute the formula?

Could you please share a bit more about the peopleResponse data source? Is it a Collection or a SP List data source?

What about the "peopleCheck" data source? A SP list? Are the EmailAddress column and MeetingID column both columns from your peopleResponse data source?

Based on the formula that you mentioned, I could not find any syntax error with it. Please consider modify it as below:

ForAll( peopleResponse, If( IsEmpty(Filter(peopleCheck, Lower(EmailAddress.Address) = Lower(AttendeeName.Email))), If( Lower(EmailAddress.Address) <> "Organizer's Email Address", Patch( MeetingAttendees, Defaults(MeetingAttendees), { Title: MeetingID, AttendeeName: { Claims: "i:0#.f|membership|" & Lower(EmailAddress.Address), Department: "", DisplayName: EmailAddress.Name, Email: EmailAddress.Address, JobTitle: "", Picture: "" }, OutlookInviteResponse: Status.Response } ) ), Patch( MeetingAttendees, LookUp(MeetingAttendees, Title = MeetingID && Lower(EmailAddress.Address) = Lower(AttendeeName.Email)), { OutlookInviteResponse: Status.Response } ) ) )

If the EmailAddress column and MeetingID column both columns from your peopleResponse data source, please modify above formula as below:

ForAll( peopleResponse As LoopRecord, If( IsEmpty(Filter(peopleCheck, Lower(LoopRecord.EmailAddress.Address) = Lower(AttendeeName.Email))), If( Lower(LoopRecord.EmailAddress.Address) <> "Organizer's Email Address", Patch( MeetingAttendees, Defaults(MeetingAttendees), { Title: LoopRecord.MeetingID, AttendeeName: { Claims: "i:0#.f|membership|" & Lower(LoopRecord.EmailAddress.Address), Department: "", DisplayName: LoopRecord.EmailAddress.Name, Email: LoopRecord.EmailAddress.Address, JobTitle: "", Picture: "" }, OutlookInviteResponse: LoopRecord.Status.Response } ) ), Patch( MeetingAttendees, LookUp(MeetingAttendees, Title = LoopRecord.MeetingID && Lower(LoopRecord.EmailAddress.Address) = Lower(AttendeeName.Email)), { OutlookInviteResponse: LoopRecord.Status.Response } ) ) )

Please try above solution, check if the issue is solved.

Regards,
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

hi Kris,

the peopleCheck and peopleResponse are both (poorly named) Collections. peopleCheck pulls the people from the MeetingAttendees SharePoint list and peopleResponse pulls the attendees from the Outlook invite. There are no errors when this code is executed.

EmailAddress column comes from the Outlook Invite and MeetingID is a variable that holds the GUID for the meeting being edited.

I've tried your code suggestions, but unfortunately still not working. I've added a label to check that it is finding the correct record for the second Patch statement, and it locates it fine.

Any other thoughts? I'm fresh out of ideas.

thanks a million for taking the time,
Mik
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

so, somehow my Data row limit for non-delegable queries was reset to 500 (I had set it to 2000 previously), which seems to be what was causing the multi-criteria filtering issue.

Was a little puzzling that this was the issue because my MeetingAttendees SharePoint list has around 1000 rows, but was going well beyond the 500 row limit when searching (to nearly the 900th row to find data and then stopping when finding the first piece of data that didn't fit the criteria, even though the following 3 rows fit the criteria). As well, DisplayName and Email ARE delegable within a complex column.

Thanks very much for taking the time to confirm all the formulas. I'm going to look into offloading the non-delegable pieces from the server to temporary collections.

Much appreciated,
Mik