Power automate question (multi-contact list)

jtadhs
jtadhs ✭✭
edited 08/11/23 in API & Developers

I'm using power automate to wire up some info into smartsheets. I have everything working so far but multi contact list and multi pick list. The contact list error I get is this: The value "John smith" could not be saved in column "Assignment". This column is restricted to MULTI_CONTACT_LIST values only. The name is definitely there. Have tried email addresses, arrays, objects, strings, nothing works.

What format are these columns expecting data in? I can't find anything usable anywhere.

· Share on FacebookShare on Twitter

Best Answer

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @jtadhs How are you formatting your data for these complex column types? Are you including them as objectValue references or just values?

    Cell objects retrieved through the Smartsheet APIs have two main attributes representing cell values: Cell.value, and Cell.displayValue. A third attribute, Cell.objectValue is currently used only for adding and updating predecessors, or for multi-contact or multi-picklist details, such as email addresses or values in a multi-picklist. 

    https://smartsheet.redoc.ly/tag/cellsRelated#section/Cell-Reference

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • jtadhs
    jtadhs ✭✭

    for the multi-contact, I'm passing in the display name or the email address, as strings, and this fails. I'm using integrations w/ power automate, so there is no lower level option to configure cell.objectvalue or .displayValue, or however that works.

    for multi picklist, I'm passing in a string which matches exactly options on the list. no go.

    example:

    db1.png

    The Assignment column is my multi-contact list. For that column, data is coming in purely as strings from a JSON object which I am parsing.

    · Share on FacebookShare on Twitter
  • jtadhs
    jtadhs ✭✭

    Getting the same exact thing via Insomnia:

    image.png

    What type of data is it expecting? How is it to be formatted? Who knows! It's not documented anywhere.

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    https://community.smartsheet.com/discussion/comment/390588#Comment_390588

    Is your Multi contact list column restricted to only values that are in the contact list for the sheet? If so, you might want to turn that off.

    Also make sure the column isn't locked.

    Also add a "level" parameter to the API call with value of 2, that should enable multi picklist and multi-contact list functions.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter
  • jtadhs
    jtadhs ✭✭

    I got this to work, however, I had to remove the restriction on the column. It is not possible to update the column via API or integration if the multi-contact column is restricted. And even w/ level=1 (for multi-contact list) and include objectValue on the query string, it still does not update the contact properly. It'll show the name or the email in the column but not the object, until the checkbox is manually clicked. Jeff, thanks for your suggestion. Spent a lot of time on this one field issue, unfortunately.

    · Share on FacebookShare on Twitter
  • jtadhs
    jtadhs ✭✭

    To add: what I had to do to get this to work in power automate, was leave off the multi-contact/multi-pick list fields when inserting a row and update it in a different step. You can only populate those kinds of columns via HTTP trigger, because of the poor support integrations has been given. Supply these values on an HTTP trigger step:

    HTTP-trigger-power-automate.png

    Following Bearer is your API token value.

    Also, bring in a Parse JSON step to break down the body from the insert row, because you will need the row id in order to update those fields. For folks who don't want the headache of doing all of this, convert the column to a text/number and you should have no issues.

    · Share on FacebookShare on Twitter
  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @jtadhs Glad you figured it out! I will probably have to use this at some point, since my VP loves his power automate and wants us to use our Microsoft 365 for more things.

    Another thing I was going to suggest, though not ideal, is to add your contact to a text/number field, then use a formula in the multi-contact field to bring in the email value from the text/number field. This doesn't work with multiple emails in one field, though. But it is something I use quite a bit, especially when collecting responses or entries in a form where I don't know who the respondents will be, they don't sign into Smartsheet, and so I could not maintain a contact list; I prompt the user to enter their email address, use the email format validation in the form, and then just use a simple =UserEmailTextField@row to bring that email into the Contact List field.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

    · Share on FacebookShare on Twitter

Categories