Forums - Thread Details
  • State Suggested Answer
  • +1 person also asked this people also asked this
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 16 subscribers
  • Views 9662 views
  • Users 0 members are here
Related

Adding iSheet entry via POST with populated lookup column

Peter Darling

Hi, I'd like to know if it's possible to use the API t to add a row with a lookup column populated. I understand there are some limits to where and how you can use lookup columns generally (i.e. not in an Excel import template) so it might just not be possible, but on the other hand, the isheet GET endpoint does return what I assume is all the data needed to actually find the desired item.

As an example, I copied the below from the output of the GET endpoint on an item I manually populated the lookup column on; I then set it as the body of the POST call and it successfully created a row and populated the text field, but not the lookup column. I've tried several different variants of the body with no luck. Would be great to learn if it is indeed a known limitation, or if anyone's figured out any workaround.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<isheet recordcount="1">
<metadata>
<sitename siteid="4466"><![CDATA[Testing]]></sitename>
<sheetname sheetid="1622"><![CDATA[Checklist Complete]]></sheetname>
<viewname viewid="8523"><![CDATA[Default]]></viewname>
</metadata>
<head>
<headcolumn sequence="1" columntypealias="SHEET_COLUMN_TYPE_SINGLE_LINE_TEXT" columntypeid="1" columnid="24841" parentcolumnid="24840">
<columnvalue><![CDATA[Company]]></columnvalue>
</headcolumn>
<headcolumn sequence="2" columntypealias="SHEET_COLUMN_TYPE_SINGLE_LINE_TEXT" columntypeid="1" columnid="24842">
<columnvalue><![CDATA[Test]]></columnvalue>
</headcolumn>
</head>
<data>
<item externalid="" itemsequence="1" itemid="663010">
<column sequence="1" attributecolumnid="24841" parentcolumnid="24840">
<rawdata>
<isheetitems>
<isheetitem>
<recordid>546868</recordid>
<linkname><![CDATA[Test lookup value]]></linkname>
<apilink>https://[our instance]/api/3/isheet/1466/items/546868</apilink>
</isheetitem>
</isheetitems>
</rawdata>
<displaydata>
<isheetitems>
<isheetitem>
<recordid>546868</recordid>
<linkname><![CDATA[Test lookup value]]></linkname>
<apilink>https://[our instance]/api/3/isheet/1466/items/546868</apilink>
</isheetitem>
</isheetitems>
</displaydata>
</column>
<column sequence="2" attributecolumnid="24842">
<rawdata>
<value><![CDATA[Testing]]></value>
</rawdata>
<displaydata>
<value><![CDATA[Testing]]></value>
</displaydata>
</column>
</item>
</data>
</isheet>

  • 0
    Thomson Reuters Thomson Reuters staff member

    Hi  ,


    We will look into it.

    Thank you.

  • 0
    Thomson Reuters Thomson Reuters staff member

      

    Could you please provide the specific endpoint you are referring to? Additionally, if possible, share a supporting screenshot that explains the scenario you are trying to achieve. This will help us better understand the issue. 

  • 0

    Peter,

    You've probably found the answer by now, but for the purposes of others having similar questions later down the track...

    If you are talking about an iSheet Link column(?) then the following payload is required (using JSON format):

    {
    "data": {
        "item": [
            {
                "itemsequence": 1,
                "externalid": "",
                "alerteroption": 1,
                "column": [
                    {
                        "attributecolumnid": 1111,
                        "rawdata": {
                            "isheetitems": {
                                "isheetitem": [
                                    {
                                        "recordid": 9999,
                                        "linkname": "Community Test"
                                    }
                                ]
                            }
                        }
                    }
                ]
            }
        ]
    }
}

    Where 1111 is the iSheet Item Link column in the source iSheet, and 9999 is the iSheet Item record id in the target iSheet.
    You can also optionally supply linkname to set the text for the link - as long as the column has Allow users to rename links enabled.

    My JSON above was taken from working code, but I'd imagine the XML would look something along the lines of:

    <data>
    <item itemsequence="1" externalid="" alerteroption="1">
        <column attributecolumnid="1111">
            <rawdata>
                <isheetitems>
                    <isheetitem>
                        <recordid>9999</recordid>
                        <linkname>Community Test</linkname>
                    </isheetitem>
                </isheetitems>
            </rawdata>
        </column>
    </item>
</data>

    Note: This applies to the following HighQ Sheet API's:

    • POST   /{version}/isheet/{isheetid}/items
    • PUT     /{version}/isheet/{isheetid}/items/{itemid}

    developers.thomsonreuters.com/.../bf5a65d2-7bbd-4164-9596-eb6591c4cb1b
    developers.thomsonreuters.com/.../bf5a65d2-7bbd-4164-9596-eb6591c4cb1b

    Hope this helps,

    Andrew

  • 0 in reply to Andrew Quinn

    Thanks, Andrew, this got me most of the way there (though I'm working with lookup columns rather than link columns). This might have been what you were saying, but what what was tripping me up was that I was building the POST payload from json returned by the GET endpoint (to create a copy with some other values changed), and it turns out the value for the POST's, 'attributecolumnid' should actually match the parentcolumnid value of the GET, not the attributecolumnid. That is, if GET returns my lookup column with these values:

    "attributecolumnid": "1112",
    "parentcolumnid": "1111",
    "displaydata": {
     "isheetitems": {
      "isheetitem": {
       "recordid": "5555",
       "apilink": "">ourinstance.com/.../5555",
       "linkname": "Yahoo"
        }
      }
    },

    Then in my POST payload, for that column, I need to set "attributecolumnid" to 1111, not 1112. So if I wanted to create a new iSheet entry with the lookup field pre-populated with the same value as above, my POST payload would look like this:

    {
    "data": {
        "item": [
            {
                "itemsequence": 1,
                "externalid": "",
                "alerteroption": 1,
                "column": [
                    {
                        "attributecolumnid": 1111,

                        "rawdata": {
                            "isheetitems": {
                                "isheetitem": [
                                    {
                                        "recordid": 5555,
                                        "linkname": "Test"
                                    }
                                ]
                            }
                        }
                    }
                ]
            }
        ]
    }
}

    That everything in the POST payload matches what's returned by GET except attributecolumnid seemed counterintuitive to me. In any case, the above accomplished what I needed it to (otherwise populating lookup columns in bulk is impossible I think), thanks again and hopefully this helps anyone else stuck trying to do the same.