Task-Filter not taking Info from Esritask_assignee

ZentraleTechnik

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
)

CodyPatterson

Hey @ZentraleTechnik

Just so I understand correctly, you're attempting to have other users tasks display for all users in your organization, so for example if you log in, you'll see your own tasks along with Daniel F?

If this is the case, I would try to do the filter one at a time, so in your filters section of the Field Maps tasks interface, if you are able to edit/view the filters for your current items, try to take the same exact schema of that, and paste it into a new filter, and just replace the username with the user.

I think the query may be too complex for Field Maps tasks at the moment, especially since they've changed how the system interacts with queries and filters, which causes the Field Maps to be non-editable or other errors crop up.

tldr; Try with a single user, and verify if that works, it could be a different part of your query so slowly build it back up if it works initially!

Cody

troyf_PTC

'CURRENT_USER' is the ~~local~~ (Global? In-app?) variable within Field Maps Tasks to call and filter based on the currently logged-in user within the Tasks Filter query.

So for instance, if I write a filter with

(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
AND LOWER('CURRENT_USER') in ('mihaly.e','tibor.g','daniel.f')
)
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
AND LOWER('CURRENT_USER') in ('zoltan.p', 'zoltan.z', 'tamas.f')
)

I think it should work.

When I do a simpler version without all of the conditions, if I write the filter as (I just replaced our real usernames with PERSON_1, PERSON_2, and PERSON_3 for this code snip):

UPPER('CURRENT_USER') in ('PERSON_1','PERSON_2') and UPPER(esritask_assignee) in  ('PERSON_1','PERSON_2') 
AND NOT(esritask_status = 3)

Then I have two phones, one logged in as PERSON_1, and one logged in with PERSON_3 (who shouldn't see things in this filter)

It works as expected: Logged in as Person_1, I see this task filter and tasks that are assigned to both myself (PERSON_1) as well as PERSON_2, and are currently not set to a "Completed" status.

When logged in as PERSON_3, the task list is empty.

I hope this is what you were asking.

(Note: I just used the SQL UPPER() and LOWER() values to standardize the usernames since they CAN be mixed case in our organization and depending on the platform reading the info)

Edit: and as an FYI: the esritask_asignee is an attribute within the feature layer with a domain list used to fill in an actual pre-defined user as assigned to the task, or fill it in when they claim it, which gets saved in the attribute table for the feature as a hard-coded value.

'CURRENT_USER' is a dynamic variable for the user logged into Field Maps (specifically for the SQL92 filtering, I don't think it works in form Arcade expressions or calculations or anything).

ChristopherCounsell

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?

troyf_PTC

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

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!