cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cjanow
Level: Powered On

Filter on two fields where one of them is based on the currently logged in user

In our organization, we have a help desk.  Each case belonging to the Help Desk is owned by the Help Desk user and then based on whoever submitted the case, they are the designated contact. My goal is to show all cases in a gallery, then filter using these two criteria

1. The owner of the case is the Help Desk User

2. Based on the user using the app, only show their cases

 

When I go to compare GUIDs of the case the user is regarding I get the following error:

 

Regular expressions must be constant values.

 

Here is what the gallery is set to

 

Sort(
Filter(
Cases,
IsMatch(Upper(Owner),"12345678-1234-1234-1234-123456789012"), IsMatch(Upper('Assigned To'),userID)), 'Created On',Descending)

 

I set the userID variable upon the app starting. I understand the error I am getting, but I cannot come up with a workaround.   When I take out the second condition, the gallery populates as expected (i.e. filters all cases owned by the Help Desk, but not on the user they regard).  Has anyone been able to accomplish something similar?

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter on two fields where one of them is based on the currently logged in user

Hi @cjanow ,

Could you please share a bit more about the '12345678-1234-1234-1234-123456789012' mentioned in your formula? Does it represent the Unique Identifier of a specify user?

 

Based on the formula you provided, I think there is something wrong with it. Firstly, the Owner field is actually a Polymorphic lookup field in CDS Entity, you could compare it with a Unique Identifier (e.g. '12345678-1234-1234-1234-123456789012') directly.

The Contact field may be a LookUp field in your Case Entity, which references values from your Contact Entity. You could also not compare a Contact record with a Unique Identifier directly.

 

I have made a test on my side, please take a try with the following workaround:

1. Add "Users" Entity as data source in your app.

2. Modify your formula as below:

 

Sort(
     Filter(
           Cases,
           AsType(ThisItem.Owner, Users).User = GUID("12345678-1234-1234-1234-123456789012"), 
'Contact (primarycontactid)'.Concat = helpdeskID
),
'Created On',
Descending
)

Note: The Users represents your Users Entity. I assume that the helpdeskID variable stores the Unique Identifier of a specific contact.

 

 

More details about the Polymorphic lookups field in CDS Entity, please check the following blog or article:

https://powerapps.microsoft.com/en-us/blog/owner-customer-and-regarding-for-canvas-apps/

 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/working-with-references

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
RezaDorrani
Level 10

Re: Filter on two fields where one of them is based on the currently logged in user

Hi @cjanow 

 

becuase it is doing an AND operation

 

try this

 

Sort(
Filter(
Cases,
IsMatch(Upper(Owner),"12345678-1234-1234-1234-123456789012") || IsMatch(Upper(Owner),userID)), 'Created On',Descending)

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Highlighted
cjanow
Level: Powered On

Re: Filter on two fields where one of them is based on the currently logged in user

Hi Reza,

 

Thanks for your quick reply, however to get the filtering I need, it is supposed to be an AND operation.

 

I need the cases owned by the user HelpDesk AND the case is regarding the currently signed in user.

 

Hope that clarifys things.

RezaDorrani
Level 10

Re: Filter on two fields where one of them is based on the currently logged in user

Hi @cjanow 

 

And will work if the columns are different

Sort(
Filter(
Cases,
IsMatch(Upper(Owner),"12345678-1234-1234-1234-123456789012"), IsMatch(Upper(Owner),userID)), 'Created On',Descending)

 

In your case the same Owner field is being used to check the Help desk user and current user which when used in AND will yield no results

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

 

cjanow
Level: Powered On

Re: Filter on two fields where one of them is based on the currently logged in user

Oh I must apologize, the second Owner is a mistake.  If that's what it would have been, you would be correct.  It is a different column named 'Assigned To'.

 

The error is essentially coming from using a variable with the IsMatch() function, and I am interested in a work around there.

 

Thanks.

RezaDorrani
Level 10

Re: Filter on two fields where one of them is based on the currently logged in user

Hi @cjanow 

 

can u post a screenshot of the actual formula and the error

Also, what type of column is Owner and AssignedTo?

cjanow
Level: Powered On

Re: Filter on two fields where one of them is based on the currently logged in user

Sure.  The exact expression is a little different from what I put, let me know if you need more information.

 

1.PNG2.PNG

RezaDorrani
Level 10

Re: Filter on two fields where one of them is based on the currently logged in user

Hi @cjanow 

 

Contact(primary contactid) or helpdeskid  - looks to be a Record and not a text field. They are a record (row of data) and does not point to a specific column

 

check code again

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

 

 

Community Support Team
Community Support Team

Re: Filter on two fields where one of them is based on the currently logged in user

Hi @cjanow ,

Could you please share a bit more about the '12345678-1234-1234-1234-123456789012' mentioned in your formula? Does it represent the Unique Identifier of a specify user?

 

Based on the formula you provided, I think there is something wrong with it. Firstly, the Owner field is actually a Polymorphic lookup field in CDS Entity, you could compare it with a Unique Identifier (e.g. '12345678-1234-1234-1234-123456789012') directly.

The Contact field may be a LookUp field in your Case Entity, which references values from your Contact Entity. You could also not compare a Contact record with a Unique Identifier directly.

 

I have made a test on my side, please take a try with the following workaround:

1. Add "Users" Entity as data source in your app.

2. Modify your formula as below:

 

Sort(
     Filter(
           Cases,
           AsType(ThisItem.Owner, Users).User = GUID("12345678-1234-1234-1234-123456789012"), 
'Contact (primarycontactid)'.Concat = helpdeskID
),
'Created On',
Descending
)

Note: The Users represents your Users Entity. I assume that the helpdeskID variable stores the Unique Identifier of a specific contact.

 

 

More details about the Polymorphic lookups field in CDS Entity, please check the following blog or article:

https://powerapps.microsoft.com/en-us/blog/owner-customer-and-regarding-for-canvas-apps/

 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/working-with-references

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

SecondImage

PowerApps Monthly Community Call

Next Wednesday, September 18th at 8am PDT

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 164 members 4,796 guests
Please welcome our newest community members: