Combine User Fields for Reports

  • 0
  • 1
  • Question
  • Updated 2 months ago
  • In Progress
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,
Photo of cowannbell

cowannbell

  • 1,230 Points 1k badge 2x thumb

Posted 2 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
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.


Photo of cowannbell

cowannbell

  • 1,230 Points 1k badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
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.

Photo of cowannbell

cowannbell

  • 1,230 Points 1k badge 2x thumb
Okay, that is what I was thinking but wasn't sure.  I'll try it out.
Photo of cowannbell

cowannbell

  • 1,230 Points 1k badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
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? 
Photo of cowannbell

cowannbell

  • 1,230 Points 1k badge 2x thumb
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.

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. 
Photo of cowannbell

cowannbell

  • 1,230 Points 1k badge 2x thumb
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.
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. 
Photo of Michelle Chapman

Michelle Chapman

  • 400 Points 250 badge 2x thumb
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.