Task-Filter not taking Info from Esritask_assignee

Question

Hello everyone,

We have a project where we would like to filter tasks based on different criteria. In addition to time and two other criteria, one of them is the logged-in user. However, our tests show that this is not working: regardless of which account I log in with, I can still see tasks from other users. For time and the other criteria, the filter works as expected.

Do you have any idea what we might be doing wrong, or how we could adjust the filter so that it correctly applies to the logged-in user?

Thank you in advance!

Logic of filter:

(esritask_assignee = 'mihaly.e' OR esritask_assignee = 'tibor.g' OR esritask_assignee = 'daniel.f'
  AND OMC_ID = 1
  AND Group_ID = 1
  AND esritask_duedate BETWEEN CURRENT_DATE + 0.1667 AND CURRENT_DATE + 1.1667
)
OR
(
  esritask_assignee IN ('zoltan.p', 'zoltan.z', 'tamas.f')
  AND OMC_ID = 2
  AND Group_ID = 1
  AND esritask_duedate BETWEEN CURRENT_DATE + 0.1667 AND CURRENT_DATE + 1.1667
)

troyf_PTC · Answer

Yeah... I wasn't COMPLETELY clear on exactly which tasks OP was trying to see for tasks if they already had a user filled into the [esritask_asignee] field, so I left his code part in as-is. I was more focused on getting the CURRENT_USER in there. (And I do have workflows in my case for a supervisor where they might want to have a task filter to see tasks assigned to others so that he can re-assign them in the field).

And I agree the UPPER() and LOWER() were probably redundant. I wrote it pretty quick and couldn't remember off the top of my head if the SQL statements are always case-insensitive in ArcGIS and Arcade evaluations (which I know this isn't arcade) like they usually are through SSMS, so I just threw it in to be safe.

But it looks like OP got it figured out despite my nonsense and poor or rushed practices, and that's what matters I think.

ZentraleTechnik · Answer

Thank you for the advices and your help @ChristopherCounsell and @troyf_PTC.

we did some further testing and figured it out how we can filter with all the necessary criteria within all three groups (not using the license though):

Group_ID = 2 AND esritask_duedate BETWEEN CURRENT_DATE + 0.1667 AND CURRENT_DATE + 1.1667 AND
(OMC_ID = 1 AND CURRENT_USER IN ('mihaly.e', 'tibor.g', 'daniel.f'))
OR
Group_ID = 2 AND esritask_duedate BETWEEN CURRENT_DATE + 0.1667 AND CURRENT_DATE + 1.1667 AND
(OMC_ID = 2 AND CURRENT_USER IN ('zoltan.', 'zoltan.z', 'tamas.f'))
OR
Group_ID = 2 AND esritask_duedate BETWEEN CURRENT_DATE + 0.1667 AND CURRENT_DATE + 1.1667 AND
(OMC_ID = 3 AND CURRENT_USER IN ('peter.l', 'peter.g', 'laszlo.r'))

Thanks!
We really appreciate it!

ChristopherCounsell · Answer

regardless of which account I log in with, I can still see tasks from other users

I'm overall confused a bit by the SQL expression. To me it reads as though:

*

esritask_assignee = 'mihaly.e'
* sritask_assignee = 'tibor.g'
* 
(
esritask_assignee = 'daniel.f'
AND OMC_ID = 1
AND Group_ID = 1
AND esritask_duedate BETWEEN CURRENT_DATE + 0.1667 AND CURRENT_DATE + 1.1667
AND LOWER('CURRENT_USER') IN ('mihaly.e','tibor.g','daniel.f')
)

This means other users are likely to see users assigned to others. Need to review the () and order of operations.

The current_user is designed to be a filter between the field (assignee) and the current username. You shouldn't need to manipulate it with upper and lower. the usernames in the drop-down list should match the username. This can also apply to the filter itself if for whatever reason you need to hardcode it. Just apply the correct case there?