User Fields Best Practice

Question

I have users (of course) and then a 'Team Members' Table. The reason for that table is I have other info (phone numbers, employee IDs, etc) that I want associated with each user.

I then have a table of Service Orders. Multiple people get assigned to each SO (some project managers, some estimators, scope writers, etc). About 6 people are assigned. It seems there are a number of ways to go about this but the two I am looking at are:

6 user fields: Make custom lists for each user field.
or
6 related fields where the dropdown is a filtered report to limit the list.

Is one a better practice? Will I regret one down the line? Is there a different option that is even more suited for this use (I thought about a many to many relationship with a 3rd table, but I want all 6 people to be in an approval workflow so I'm not sure I like that option for that).

Secondary Question: If I have a group 'Estimators' and I want to just assign them to a role, can I do that somehow? So that any notification or trigger uses all group emails?
------------------------------
Mike Tamoush
------------------------------

markshnier__you · Answer

It's an insightful quesiton.I did a major app for a client and we went with the "six fields" based on their respective functions on that project.&nbsp; While a many to many Resource assignments table might sound more elegant, in fact, you then will need to float up who the Project Manager is and who the Scope Writer is anyways, so that is a lot of formuals.Also note that you cannot control Role permissions access to parents based the Role access to child records.&nbsp; So for example may want to say limit access to view to only where the current user is a child team member on that project. That will not work due to how Quick Base calculates. It starts with the parent and then decides there is no access, therefore it cannot calculate any thing&nbsp; about the children for summary fields, so no access to Parents.So just&nbsp;&nbsp;KISS&nbsp;and slot in 6 fields for 6 user field titles on the Project.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

michaeltamoush · Answer

What about the third option where I actually make 6 of the same relationship, so I actually have relationship drop downs instead of just user fields? The reason for this is then I can bring over lookup fields of phone numbers and other employee info.

Or is there another way to do this if I just use the user field option?

------------------------------
Mike Tamoush
------------------------------

markshnier__you · Answer

Yes, definitely. Six &nbsp;relationships and then you can filter the drop downs for each field perhaps according to the title of the users. Is your employee table going to have the key field as the user ID? That always proves a very useful technique.------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

michaeltamoush · Answer

Well,I'm thinking about making it their Employee ID number for the following reason (unless you have thoughts otherwise):I have a second table of all employees. This table does not have a user field because not all of these employees are QB users. This is mostly used for importing labor hours from time cards. The key field on that table is the Employee ID. I thought maybe somehow in the future it could help to have all the same key field?But perhaps not - I don't have a specific use case yet.....------------------------------Mike Tamoush------------------------------

markshnier__you · Answer

Can be extremely useful and apps to have a relationship to an employee table where the key feel this user ID because the reference field on the truck outside can be a formula field called current user. &nbsp;
The formula will be User()That allows you to look up any information from that table automatically based on who the current user is and you can control permissions and report filters that way.

that allows you to look up any information from that table automatically based on who the current user is and you can control permissions and report filters that way.

Therefore I suggest that you have the user ID be the key field and you can look up any information you want from the full employee table by Making a relationship to the full employee table and of course that will bring down the employee number along with any other information you need from the master employee table------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

User Fields Best Practice

Related Content

Get the role of a user in a user field

Field to "assign" a user group

Snapshot Fields

Fire changes in a user field -- use a surrogate field?

How to stop a user from adding into a field