Forum Discussion

MichaelTamoush's avatar
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 th...
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    4 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's avatar
MarkShnier__You
Qrew Champion
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
------------------------------
PeterOhashi's avatar
PeterOhashi
Qrew Member
2 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's avatar
    MarkShnier__You
    Qrew Champion
    2 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
    ------------------------------