Forum Discussion

JordanMcAlister's avatar
JordanMcAlister
Qrew Captain
7 years ago

Transition from multiple choice text to multiple choice user efficiently

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!
Data import and export
Forms
Relationships
  • AlexCertificati's avatar
    AlexCertificati
    Qrew Cadet
    7 years ago
    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.
    • JordanMcAlister's avatar
      JordanMcAlister
      Qrew Captain
      7 years ago
      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. 
    • AlexCertificati's avatar
      AlexCertificati
      Qrew Cadet
      7 years ago
      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.