Forum Discussion

MICHAELSARGENT's avatar
MICHAELSARGENT
Qrew Cadet
2 years ago

Compare record owner (as user list field) to text field

Okay, this might be a bit complex, so let me lay out the issue. 

I have a text field called SME that is in the format of some guy (sg1234), another guy (ag2345).

First, I need to create a user list field from that SME text field.  I figure it's grabbing that sg1234 and appending the email suffix of @somecompany.com. 

Next, i want to see if the record owner for the record is in that user list field.  I know I'd  have to make the record owner (user) field into a user list field.  How would i accomplish the converting of the text field into a user list field?



------------------------------
MICHAEL SARGENT
------------------------------

10 Replies

Sort By
  • katlyncowley's avatar
    katlyncowley
    Qrew Assistant Captain
    2 years ago

    If you want to do this with only one field, create a formula checkbox field: 

    var userlist users = 
    ToUserList(
    ToUser(ToText([SME] & "@somecompany.com")),
    [Record Owner]
    );

    Contains($users, [Record Owner])

    If you want to do this with 2 separate fields: 

    For the user list: 

    Create a Formula - List - User field with the formula like this:

    ToUserList(

    //get user from [SME] field by appending with the company domain

    ToUser(ToText([SME] & "@somecompany.com")),

    //add record owner to user list

    [Record Owner])

    For checking to see if the record owner is present: 

    Create a formula checkbox with the formula: 

    Contains([User List Field], [Record Owner])



    ------------------------------
    Katlyn Allen
    kallen@eatatjacks.com
    ------------------------------

    • MICHAELSARGENT's avatar
      MICHAELSARGENT
      Qrew Cadet
      2 years ago

      Kaitlyn,

      Thank you for your reply.  The first problem i have is how do i extrapolate the data so i can append the email suffix?  When i pull the data over from another table, the SME field will look something like this.  michael sargent (ms1234), jason vorhees (jv2345).  So, i need to split the value, then isolate the information in parenthesis, and then append it with the email suffix.  Only then would i be able to create the user list field.  Any idea how to write the script so it does that?  Or, since the table that has this concatenation of different names is sourced from an excel file, do i need to do more work up front so it's cleaner in Quickbase?



      ------------------------------
      MICHAEL SARGENT
      ------------------------------

      • katlyncowley's avatar
        katlyncowley
        Qrew Assistant Captain
        2 years ago

        so the part in parentheses is the username of your email addresses?

        the formula for one field would look like this:

        var userlist users = 
        ToUserList(

        // extracts the text between parentheses in [SME] and appends the email domain
        ToText(Part(Part([SME], 2, "("), 1, ")") & "@somecompany.com"),

        //record owner
        [Record Owner]
        );

        Contains($users, [Record Owner])



        ------------------------------
        Katlyn Allen
        kallen@eatatjacks.com
        ------------------------------