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 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'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
    ------------------------------

9 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    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
    ------------------------------
    • MichaelTamoush's avatar
      MichaelTamoush
      Qrew Captain
      4 years ago
      Brilliant! Worked like a charm

      ------------------------------
      Mike Tamoush
      ------------------------------
    • MICHAELSARGENT's avatar
      MICHAELSARGENT
      Qrew Cadet
      3 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's avatar
        MarkShnier__You
        Qrew Champion
        3 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'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
        ------------------------------