Self Relating table -lookup and report

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
So I have tried to create a table related to itself. I am maintaining a staff table which join itselfs to maintain its manager hierarchy information. Now I want to create a report based on the current user's manager. I want to show only the staff that have the manager same as the currently logged in user.


Demonstrating the ask with an example:

 
Now let's say the current user who is logged in is aaa@abc.com whose related manager is 2. the report should show all staff who have related manager as 2. 

Does this sound like something that can be achieved? Any help is appreciated. Thanks,
Photo of Angel

Angel

  • 784 Points 500 badge 2x thumb

Posted 4 months ago

  • 0
  • 1
easy, just filter the report where the manager userid "is the current user".  That should be offered as a choice in the report filter.

If it's is not offered up as choice you can use the somewhat obscure legacy notation of _curuser_ to compare the userid field to (yes, with those leading and trailing underscores).  Or if necessary create a field called Current User of type formula user with a formula of
User()
Photo of Angel

Angel

  • 784 Points 500 badge 2x thumb
Well I should re frame, the current user need not be a manager. So referencing from my earlier example. If the current user is rid 1- the report outcome should have staff rid 1,2,4. 
The logic you suggest above will work only when a manager logs in and will fail for any other user.
OK, I now get the question clearly.

Here is a thought that is a bit converted but will probably work.

Create Sync table of your employee file in the same app and set the userid to be the Key field.  The userid field will come across in the sync probably in the form of an email address in text format. Sync across the userid and the manager's userid.

Create a formula text field for the userid in text format on the employee file 

ToText([Userid of Employee])

 and then use that in a relationship to pull down the managers userid and call in [Current Users Manager userid in text format].  Once again, it will be in text format, so make a formula user field called 
[Current Users Manager userid]
with a formula of 

ToUser([Current Users Manager userid in text format])

Now you can filter your report with

[manager userid]
is equal to the value in the field [Current Users Manager userid]

Photo of Angel

Angel

  • 784 Points 500 badge 2x thumb
Let me try this approach and get back. Thank you for your response. appreciate the help