Create a report where the user can see requests that has their manager attached

[TheInquisitor13]

We have a custom table extended from Task, where I am trying to make a report viewable to the internal vendor that opened the request, based on their Manager. On the form, the vendor's manager is auto-populated on one of the custom fields, called "Sponsor". It is a reference field to sys_user table.

So, I tried setting the report filters to be: "Opened by.manager" is same as "Sponsor" - But  the only option I get is "Manager", and I see no other refence fields from the form, such as, Assigned To, Closed by, Opened by.

If I only do "Opened by", then I am able to see "Sponsor" as an option for comparison. Why is that? and how can I get around this?

[Tony Chatfield1]

Hi, you have indicated that the vendors manager is populated directly into your custom table field 'Sponsor' and if this is correct? then I would  expect for this field to be visible\directly selectable as 'Sponsor' against your table in your report  - as long as ACL's allowed read access.

Dot walking to a field IE opened_by.manager is a viable option when the field exists on the reference table, but in your description 'Sponsor' is  on your new task table not on the reference table for opened_by (sys_user); So while the 'Sponsor' content may be derived from opened_by.manager, it is on your custom task table and not a field on the reference table.

I would recommend you start by checking the Dictionary or Tables & Columns to confirm where the Sponsor field exists, then check\validate that it has a suitable read ACL(s) either specific to the field newTableName.fieldName or via newTableName.* or task.*.

The easiest way to validate ACL's is to enable security debug, reproduce the issue and review the debug logs.

ACL debugging tools (servicenow.com)

[TheInquisitor13]

@Tony Chatfield  Thank you for your response. I've been doing some digging, and found that doing the following filter:

"Sponsor" - contains - "javascript:gs.getUserName();"

I can get results for the current user, but:

"Sponsor" - contains - "javascript:gs.getUser().getManagerID();"

Gives me an "undefined" result on the report. I feel this actually gets be closer to what I want to do, but how can I handle the "undefined" value? 

[TheInquisitor13 · Accepted solution]

Disregard. I was referencing the ServiceNow guru article (https://servicenowguru.com/scripting/user-object-cheat-sheet/), but found the below and got the answer I was looking for:

https://community.servicenow.com/community?id=community_question&sys_id=55bdeaf8dbc15b442e247a9e0f961930