Self Relating table -lookup and report | Discussions

Expand all | Collapse all

Self Relating table -lookup and report

  • 1.  Self Relating table -lookup and report

    Posted 02-20-2019 18:01
    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:
    ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1777824/RackMultipart20190220-50006-f1ir5a-Capture_inline.JPG?1550685505">
     
    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,


  • 2.  RE: Self Relating table -lookup and report

    Posted 02-20-2019 19:38
    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()


  • 3.  RE: Self Relating table -lookup and report

    Posted 02-20-2019 20:07
    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.


  • 4.  RE: Self Relating table -lookup and report

    Posted 02-20-2019 20:37
    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]



  • 5.  RE: Self Relating table -lookup and report

    Posted 02-21-2019 22:30
    Let me try this approach and get back. Thank you for your response. appreciate the help