Transition from multiple choice text to multiple choice user efficiently

  • 0
  • 1
  • Question
  • Updated 5 months ago
  • Answered
I am currently making our teams app more efficient. I have a Work Requests table and an Employee table. In the Work Requests table, there are multiple "person" fields currently set up as multiple choice text fields:Field Tech, Designer, Reviewer, and Approved by. I recently made the Employee table to list all sorts of necessary information regarding employees and linked their name to their "user." 

My question is - how might I efficiently change these multiple choice text fields to be linked to a specific user without deleting said person fields and completely redoing them because there are countless reports and rules and formulas that use these fields, and we have over 3500 Work Request records which is why I am asking for some advice. My overall goal is to be able to have those person fields be linked to a user, so I can, for instance, have an email notification be sent to the "Reviewer" of a Work Request when it has been approved.

The other issue I am running into is making a relationship between the Employee table and the Work Request table. Each Work Request can have multiple employees assigned to it and each employee can have multiple work requests. Is a double relationship recommended for this?

Thanks for any help!
Photo of Jordan McAlister

Jordan McAlister

  • 1,460 Points 1k badge 2x thumb

Posted 5 months ago

  • 0
  • 1
My question is - how might I efficiently change these multiple choice text fields to be linked to a specific user without deleting said person fields

I don't really understand exactly what you're asking, you want to have a QB user named Field Tech? But it seems clear that part of your solution is a helper field that uses ToUser() function https://login.quickbase.com/db/6ewwzuuj?a=dr&rid=178&rl=jk

Is a double relationship recommended for this?

This is called a "many to many relationship". The foundational idea is that the 'relationship' is a third table - call it Actual Requests, let's say. So say you have PersonA, PersonB and PersonC and you have WR1, WR2 and WR3. If PersonA is assigned to WR2, you create a new Actual Request with those characteristics, and Actual Requests is a child to both parent tables.

There's TONS of education out there on this. You shouldn't have much trouble finding "Many to Many" tutorials that make this seem a lot more intuitive than it sounds like here.
Photo of Jordan McAlister

Jordan McAlister

  • 1,360 Points 1k badge 2x thumb
So, for example, I have the users:
John Doe1
John Doe2
John Doe3

My "Reviewer" field(and this is the same format for my other person fields) has the current multiple-choice text options:
J. Doe1
J. Doe2
J. Doe3

I need to either efficiently change the J. Doe# to -> John Doe# (User) or somehow let quickbase know that the text J. Doe1 is the user John Doe1. 
Right. Gotcha. If the "J. Doe1" multiple choice choices are sufficiently restricted, this is pretty easy. Add a field to that table for each field you need to change.

So, you have a [Reviewer] field with values like "J. Doe1", create [Reviewer (User)]. Make it a Formula - User field type. Write a long IF or CASE formula** to map your bad data onto good data, such as:

Case([Reviewer],
  "J. Doe1","John Doe1",
  "J. Doe2","John Doe2",
  etc)
Where the first value in each pair is the exact multiple choice text string and the second value is the exact user name you want to change it to.

And then anywhere that you need QB to operate on this value as a user value, have it use the [Reviewer (User)] field instead of [Reviewer] .

** Or create a lookup table to map these values (this is probably your preferred approach if you have a lot of these paired fields to which the same mapping should be applied, or if you're going to continue to use [Reviewer] as your data-entry field going forward). Or, if the multiple choices were created with a single consistent pattern, this could be single line of formula if you're lucky.
(Edited)
Photo of Jordan McAlister

Jordan McAlister

  • 1,360 Points 1k badge 2x thumb
Thanks, I'll give this a go!
Photo of Jordan McAlister

Jordan McAlister

  • 1,360 Points 1k badge 2x thumb
The Case function with the formula - User field worked great! I'll probably make a new post regarding the many to many relationship that I need at a later date. For now, the User field accomplishes what I need which was simply to send a notification to the viewer but I'll eventually set it up where my employees table is linked to my Work Requests table.
Thumbs up.

Many to many feels daunting at first but once you grok it you'll wonder that you ever saw things differently.