Summary for user-related fields

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • In Progress
  • (Edited)
I have an operation table with List-User field and Performance table with 1 record for each user. The operations can be active and expired. I want to see how many active operations are assigned to the specific user. Please advise what is the best way to update records in the performance table and populate them with the number of active operations assigned to the specific user?
Photo of InnaStarikova

InnaStarikova

  • 70 Points

Posted 3 months ago

  • 0
  • 1
Photo of Danie Grabe

Danie Grabe

  • 172 Points 100 badge 2x thumb
Hi Inna,

If you add a summary field in your Performance table that totals the # of Operations for that user, you can include this in your user record picker on Operations table. So it could show you Username - 6 for someone with 6 Operations. You can even go further and show active and expired totals in the same record picker for users.
Photo of InnaStarikova

InnaStarikova

  • 70 Points
Hi Danie,

Thanks for your reply. For users in list-user field, you cannot add a master-details relationship and as a result, you cannot add a summary field. Do you mean there's a workaround for that?
Photo of Danie Grabe

Danie Grabe

  • 172 Points 100 badge 2x thumb
Yes. You said there is only one record per user in the Performance table, so you can select the user from that table. The Operations table is a child to the Performance table correct? So your Related Performance record picker can be customised to show Username - # of Operations.
Photo of InnaStarikova

InnaStarikova

  • 70 Points
For now, there is no relation between the tables. In the Performance table, I have 1 record per user. In the Operations table, I have an Assigned To field that is a List-User field type. This is a specific type of user-related fields where you can select several users simultaneously (not one user per operation based on the users in the performance table). The List-User fields do not have record picker and display only the user names (comparing to the situation where you can use some report in record picker to display more information). The users are actual users who use the system.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,328 Points 50k badge 2x thumb
The design of you app makes it difficult to make a relationship.

A better design would be to have a many to many relationship where are you have a middle join table.

One team member has many operations assignments and one operation has many operation assignments members.

But based on the structure you currently have what you would have to do is create 5 different fields on the operations table to split out the list user component say up to the first five of them into five separate User fields. Then you would need to make five different relationships and roll up your totals up to the performance table. And then finally you would have to add a number of active assignments together where the team member was respectively the first second third or fourth team member in the operation.

The result of going through all that will get you the answer you’re looking for but it will be a very untidy solution.

In the long run you would be better served by reworking your app to have a mini to mini relationship.