Autopopulate Contact Field with "Created by"

cantpickname
cantpickname ✭✭✭✭
edited 12/09/19 in Smartsheet Basics

Now that formulas are allowed in contact fields, I can't quite figure out the right formula to use:

 

I have a contact field with a pre-populated list of users, I have a webform that forces the user to sign in so therefore have a nice system "Created by" column.  I would like to write a formula in that contact column to be the formatted contact version of the username in from Created By.

 

Any help is appreciated!

Karen

Comments

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    The Created By column can be used in Automations to send alerts, reminders, etc to people. Is there a specific reason you need to move this information to a Contact type column?

  • cantpickname
    cantpickname ✭✭✭✭

    First is to use in a "Current User" view within a report.  

    Second is both a formatting preference, as well as being able to update the specific field to another contact if needed (e.g. I submit the form but on behalf of someone else, since you can't edit a system column I could then update that contact column to the correct person).

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ah. Ok. That makes sense.

     

    You would need to create a directory somewhere either on the same sheet or a different sheet. Then you would use an INDEX/MATCH to pull the contact info along the lines of...

     

    =INDEX([Directory Contact Column]:[Directory Contact Column], MATCH([Created by]@row, [Directory Email Column]:[Directory Email Column], 0))

    .

    The catch is... Once you manually update this cell, it overrides the formula and deletes it from the cell. That means it could potentially impact the auto-fill feature for new forms that are submitted.

     

    If that is a concern, then you could build in a workaround. Give them a field to add in a user if different from themselves.

     

    Then adjust the formula to look at either the Created By column or this new field if they don't match.

  • cantpickname
    cantpickname ✭✭✭✭

    Thanks all.  For now the user group is fairly small and I think I will just add that contact column to the form and force the person filling out to select from the drop-down.  It's manual, but gives the option of picking themselves or another name if they need to fill out for someone else.

     

     

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! yes

     

    Feel free to revisit this thread if the user group expands to an unmanageable level, and we will see if we can help set something up for you.