Discussions

Expand all | Collapse all

Combine User Fields for Reports

  • 1.  Combine User Fields for Reports

    Posted 03-04-2019 13:58
    I want to see if it is possible to combine three user fields into one for reports.  So I want to run a report for user A and user A maybe in field 1, field 2 or field 3.  So when I run the report I want to select user a once but it return records if that user is in any of the user fields.

    Thanks,


  • 2.  RE: Combine User Fields for Reports

    Posted 03-04-2019 14:09
    np

    You can make a formula like this as a formula List - User list user field type

    ToUserList([user],[Last Modified By])

    then you should be able to filer the report on where that field includes the current user.

    to get the Current User you can either make a field called [Current User] with a formula of 

    User()

    or else  use the legacy and probably undocumented notation of

    _curuser_

     with leading and trailing underscores.




  • 3.  RE: Combine User Fields for Reports

    Posted 03-04-2019 15:20
    I can't do current user.  This will be used in a report that shows all users that fit in the criteria of the report but when I do the summary, it shows up for user A regardless of what user field they show in.  This would be ran by management and not the current user.


  • 4.  RE: Combine User Fields for Reports

    Posted 03-04-2019 15:33
    OK, so you want an <ask the user> report. I did not read your question carefully enough.

    Make a formula text field like this

    UserListToNames(ToUserList([User 1],[User 2],[User 3]))

    and then make and <ask the user>  contains ... report based on that field.



  • 5.  RE: Combine User Fields for Reports

    Posted 03-04-2019 15:42
    Okay, that is what I was thinking but wasn't sure.  I'll try it out.


  • 6.  RE: Combine User Fields for Reports

    Posted 03-04-2019 15:55
    Okay, this doesn't give the user the ability to select a user.  You have to key in the user and that won't work for what I need.


  • 7.  RE: Combine User Fields for Reports

    Posted 03-04-2019 16:21
    But if you use a Contains filter, then they could key in bell and get a hit on you, right?  Isn't that good enough? 


  • 8.  RE: Combine User Fields for Reports

    Posted 03-04-2019 16:05
    Maybe I should explain this more.  Management runs a production report.  The report that I have right now looks like the attached.  I would like it were it is combined or group by the user regardless of what user field they are listed in.  I hope this makes more sense.



  • 9.  RE: Combine User Fields for Reports

    Posted 03-04-2019 18:11
    The best way to account for this would be a "Many to Many" relationship where what you're reporting on is the unique assignment/types a user may have for any particular "record".

    When reporting on a single table, a column can only summarize a single record once, or not at all based on the filters you apply to that report. 

    Lets assume the table you're assigning these users as either "Assigned to", "Transferred to" and/or "Reassigned to" is called "Tickets". A single ticket record is unique, and having 3 different user fields will only allow you to summarize that ticket based on one of them.

    If however you you had a table called assignments where you connected the user, their "Assignment Type" and the Ticket, you could then run a report from the assignments table where that user/assignment is unique, and the # of "Tickets" to which they were assigned is not.

    Your reports would look like this....


    Hope this helps. 


  • 10.  RE: Combine User Fields for Reports

    Posted 03-04-2019 18:50
    Hum,  I'll have to think about this.  This would require me to keep a user table updated, not sure if I want to go that route.  Thank you for you suggestion.


  • 11.  RE: Combine User Fields for Reports

    Posted 03-04-2019 18:57
    You could forego the user table honestly and just use the native User field type.The key part is that the assignments be "Child" to the existing "Records" table. 


  • 12.  RE: Combine User Fields for Reports

    Posted 03-06-2019 20:57
    Have you gotten any further with this? I have a very similar problem I am trying to work through and I am stumped...I have built a separate Assignments table and tried to link users to the different positions they could be listed but still cannot get a single report that says total "production" per rep.