Convert combined text email list to list user field

I am trying to take a summary field of users (which in reality is a text field of email addresses), and convert it to a user list field.

My attempt did not work and I am not sure why. I created this formula in a field we will call [Converted To Correct Format Formula]:

If(ToText([Combined Text Field])<>"","Touser(\"")
& SearchAndReplace(ToText([Combined Text Field]),";", "\"),touser(\"")
& If(ToText([Combined Text Field])<>"", "\")")

That yields a result such as:
Touser("email1@email.com), touser("email2@email.com")

I made a user list field and if I do the following, it works:

touserlist(Touser("email1@email.com), touser("email2@email.com"))

But if I try this, it fails
touserlist([Converted to Correct Format Formula])

It tells me it is expecting a userlist but it is text, which I guess makes sense as it doesnt know what my formula field yields. Is there an easier way and I am just over complicating?

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

Formulas and functions

MarkShnier__You
5 years ago
I tested this and it works. make a field on the child table wth a formula of

ToText([My List-User field]) and then roll it up with a Combined Text summary field to the Parent.

var text value = ToText([Combined Text List-User(text format)]);

ToUserList(
ToUser(Trim(Part($value,1,";"))),
ToUser(Trim(Part($value,2,";"))),
ToUser(Trim(Part($value,3,";"))),
ToUser(Trim(Part($value,4,";"))),
ToUser(Trim(Part($value,5,";"))),
ToUser(Trim(Part($value,6,";"))),
ToUser(Trim(Part($value,7,";"))),
ToUser(Trim(Part($value,8,";"))),
ToUser(Trim(Part($value,9,";"))),
ToUser(Trim(Part($value,10,";"))),
ToUser(Trim(Part($value,11,";"))),
ToUser(Trim(Part($value,12,";"))),
ToUser(Trim(Part($value,13,";"))),
ToUser(Trim(Part($value,14,";"))),
ToUser(Trim(Part($value,15,";"))),
ToUser(Trim(Part($value,16,";"))),
ToUser(Trim(Part($value,17,";"))),
ToUser(Trim(Part($value,18,";"))),
ToUser(Trim(Part($value,19,";"))),
ToUser(Trim(Part($value,20,";"))))

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

MarkShnier__You
Qrew Legend
5 years ago
I tested this and it works. make a field on the child table wth a formula of

ToText([My List-User field]) and then roll it up with a Combined Text summary field to the Parent.

var text value = ToText([Combined Text List-User(text format)]);

ToUserList(
ToUser(Trim(Part($value,1,";"))),
ToUser(Trim(Part($value,2,";"))),
ToUser(Trim(Part($value,3,";"))),
ToUser(Trim(Part($value,4,";"))),
ToUser(Trim(Part($value,5,";"))),
ToUser(Trim(Part($value,6,";"))),
ToUser(Trim(Part($value,7,";"))),
ToUser(Trim(Part($value,8,";"))),
ToUser(Trim(Part($value,9,";"))),
ToUser(Trim(Part($value,10,";"))),
ToUser(Trim(Part($value,11,";"))),
ToUser(Trim(Part($value,12,";"))),
ToUser(Trim(Part($value,13,";"))),
ToUser(Trim(Part($value,14,";"))),
ToUser(Trim(Part($value,15,";"))),
ToUser(Trim(Part($value,16,";"))),
ToUser(Trim(Part($value,17,";"))),
ToUser(Trim(Part($value,18,";"))),
ToUser(Trim(Part($value,19,";"))),
ToUser(Trim(Part($value,20,";"))))

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
- MichaelTamoush
  Qrew Captain
  5 years ago
  Brilliant! Worked like a charm
  
  ------------------------------
  Mike Tamoush
  ------------------------------
- MICHAELSARGENT
  Qrew Cadet
  4 years ago
  Mark, how can I make this work in a 1:M relationship? I have an app in which I have a project table and then a related Assigned PMs table in which multiple PMs can be assigned to one project. They are individual users versus a List-User field so each PM has autonomy to do their own updates for their child record. Similar situation as above whereby I want to create a notification at the parent (project) table and sent it to all users related to that project. As such, I need a user-list value at the parent level.
  
  ------------------------------
  Michael Sargent
  ------------------------------
  - MarkShnier__You
    Qrew Legend
    4 years ago
    The post directly above explains the technique. Did you try it? Where did you get stuck?
    
    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
- PeterOhashi
  Qrew Member
  3 years ago
  Hi Mark,
  
  My organization currently assigns employees to projects using a default user set (all employees on our QB environment). We were looking to have each of these fields be filtered down to only the relevant role. When selecting a user as the Project Manager, we'd like a filtered list to appear. We have achieved this by manually adding names to the custom list, but would like to automate the process so we do not have to continuously edit this list.
  
  We have a table of master users with assigned roles. I am able to create a combined text field with only the users in a specific department, but am struggling to convert this to a user list. I tried your method and found that it will only work on up to 20 users. Do you have any idea how to work around this?
  
  ------------------------------
  Peter Ohashi
  ------------------------------
  - MarkShnier__You
    Qrew Legend
    3 years ago
    @Peter Ohashi
    This is the problem.
    "My organization currently assigns employees to projects using a default user set (all employees on our QB environment). "
    
    That is an expedient method which gives you no control other than manual updates.
    
    You say that you have a master table is users and their Roles. I would suggested using that as a source table for a one to many relationship with the projects table. Then you can control the drop down report used for picking a Project Manager to be a filtered list of active employees who's role incudes Project Manager.
    
    Depending on how many projects you have there are various way to convert to using the new field. One would simply be Grid Edit - Line up the old column and new column and just use Grid Edit to update.
    
    Another way would be to change the Key field of the Master Users table to have the Userid be the Key field and then make the relationship to the table. If that master table is already in relationships you may want to try this in a Copy of the app first.
    
    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------

Forum Discussion

Convert combined text email list to list user field

Related Content

Converting delimited text into checkboxes

Combining AND with OR in Formula Checkbox Field

Convert timestamp to Date/Time

Convert a 'text' email address field to 'Email' format

Creating formula to parse combined text field then create a userlist for notification email