MichaelTamoush
Qrew Captain
4 years ago
Solved

# 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
------------------------------
• 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
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------

### 9 Replies

• 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
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
• Brilliant! Worked like a charm

------------------------------
Mike Tamoush
------------------------------
• 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
------------------------------
• The post directly above explains the technique.  Did you try it?  Where did you get stuck?

------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
• 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
------------------------------
• @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@gmail.com
------------------------------