Forum Discussion

KorrieClark's avatar
KorrieClark
Qrew Trainee
4 months ago

Custom report based on current user and list of locations

I'm trying to customize a report where the results are based on teh current user and the locations in the current user's region. We have the following infrastructure already:

table of employees > within the table of employees are the current user's and they do have a user account field that links them to their profile with a "user" type field although i'm not sure that is relevant for solving this. 

On each employee profile is their Location (Multiple Choice)

On each manager's profile is a "Region" field (Multi-Select text field) inclusive of the same options in the location field- caveat being a manager can be responsible for multiple locations hence the region.

Now- we're struggling with how to lookup the options in teh Region field to call up all teh records for the employees in their region. 

We also have a projects table where multiple manager resources are linked and based on the project those assigned managers on each employee record through a table to table relationship where we're filling in related program and it pulls the assigned managers; so it works when we say "if the assigned manager field contains teh current user- show" but we also want it to limit just to their Region. 

What am I missing to make it go one level further on a report that is customized per user so they only see stuff relevant to them through "current user" options 

reports
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 months ago

    You seem to be asking two questions.

    For this question here:

    Now- we're struggling with how to lookup the options in the Region field to call up all the records for the employees in their region. 

    If you just want to show a block of text which lists the employees in their Regions, then this can be done with a Formula Query. The result could be also done as a hyperlink to the employee records, but it would not be a normal embedded table on the Manager Record.  If you did want to have an embedded table, then there is a User Focus Technique which I could implement with you which would take about 1 hour of consulting time.  You would go to the Manager record and push a button to put that Manager "in Focus" and the screen would refresh with a typical embedded report of the employees in that Manger's Regions.

    Then if I understand the second question, you seem to be saying that

     you have a Project which is in a Region and you want to assign managers to that Project, but only mangers who have that Project's Region in their Multi-select Regions field. 

    That can also be accomplished though the User Focus method where the user would click a button to put the Project in Focus, and then the choices offered could be limited to just the Managers responsible for that Region. 

    You can contact me at mark.shnier@gmail.com to work with you to set up that User Focus method.  I;m not sure if you have worked with Formula Queries before, but we can either work though that method slowly on this Forum here or else more quickly as a one-on-one Zoom call session.

     

     

  • DonLarson's avatar
    DonLarson
    Qrew Elite
    4 months ago

    For your two questions I would solve it this way

    For Q1  the Employee Report Link in the Regions table would be looking for the Employees where the Related Locations are the same.   It is not a direct relationship between the data but each record in the Region Table will have a report of all the Employees at that location

    For Q2

    You can get both User fields into the Project Employees table.   Your reports from there can use an "Any" filter to let either the Employee or the Manager see the records based upon being the Current User.

    • KorrieClark's avatar
      KorrieClark
      Qrew Trainee
      4 months ago

      Yeah we don't have a regions table because the combinations could be infinite so we're doing it on a manager level 

      ie. Manager A on Project A could be responsible for a region that is Spain and France

      but Manager B on Project A could be responsible for a region that is Spain, France and England, so neither the manager or the proejct dictate the region exclusively there is overlap where things are inconsistent. 

      Ideally i'm looking for a way to either utilize a checkbox formula or a formulaquery that will say if an Employee's location (Multiple Choice) is included in a Manager's region (Multi-Select) then show that employee on the report without having to have the manager select a filter. 

      Setting the report filter as Employee's Assigned manager contains <<Current User>> AND Manager's Region includes employee's location.