cweipipf

How to set list element metadata with Nintex and REST

Blog Post created by cweipipf on Nov 13, 2018

1. Web service requests as preparation 
   1.1. Website context information
   1.2. Authentication – Form Digest
   1.3. List property ListItemEntityTypeFullName
2. List element creation or modification
   2.1. Set text or number columns
   2.2. Managed Metadata Columns
   2.3. Setting People Picker columns within a site
   2.4. Setting one or more people picker values across site or site collection boundaries
3. Sources 

 

It happens repeatedly in projects that during a Nintex process, values of Managed Metadata columns must be set.

Example: the user fills out an application form. After the user completes and submits the form, existing items in a list must be updated or new items must be created in a list – whether in the same site, another site, across site, across site collection, or across web application. If now Managed Metadata must be set, then the question arises very fast, with which Nintex action can one do this? After several tests the result was: with “standard” Nintex actions like “Copy Item” or “Create Item in another Site” it is not possible. There is no action in order to realize this in an easy way. The solution: you have to build a web service with REST (Representational State Transfer) that sets these field values.

This article wants to show how it works for Managed Metadata columns, text and number columns as well as people-picker columns.

1. Web service requests as preparation

1.1. Website context information

In order to perform actions with REST web service requests, a so-called form digest must always be determined for authentication. A Form Digest is a client-side ticket (token) for authentication in order to perform certain operations in SharePoint sites.

To get such a Form Digest value, the context information of a SharePoint website must be requested. The following configurations in the Nintex action “Web Request” are necessary for this.

  • "_api/contextinfo" is appended to the URL of the target website.
  • The content type is "application/x-www-form-urlencoded".
  • The result is stored in a text variable (single line of text).

Query Context Info

1.2. Authentication – Form Digest

In the next step, the information of the Form Digest must be determined from the return value of the context info query.

This is done using the Nintex „Query XML“ action by reading the value from the result variable with the XPath command „(//*[local-name()=‘FormDigestValue‘]“ and storing it in a text variable, see the figure below.

1.3. List property ListItemEntityTypeFullName

The next step ist o determine the ListItemEntityTypeFullName list property. This property is important for creating or updating list items. The property is determined with a GET request in the Nintex action “Web Request” as follows:

  • Insert the URL of the target website as URL, plus "_api/web/lists/getbytitle('NAME OF LIST')/ListItemEntityTypeFullName"
  • A header is not necessary.
  • The result must be stored as usual in a text variable (e.g. var_ListItemEntity).

Web Request List Item Entity

The result of the GET request must again be read in a Nintex action „Query XML“ to extract the property „ListItemEntityTypeFullName“ with the XPath command „//*[local-name]()=‘ ListItemEntityTypeFullName‘]“. See the figure below.

Query XML List Item Entity

2. List element creation or modification

Finally, a POST request and the Nintex „Web Request“ action can be used to create a new list element or modify an existing element.

  • The following must be appended to the URL of the target website: "_api/web/lists/getbytitle('LIST NAME')/items"
  • The content type is "application/json;odata=verbose".
  • When creating a list element, the header "X-RequestDigest" must be passed. Now the Form Digest Value must be passed as variable (e.g. see in the figure with the variable var_FormDigestValue).
  • The header and content type therefore look like the picture below.

  • Important! If an element ist o be modified, the request is slightly different. The following must be appended to the URL of the target website "_api/web/lists/getbytitle('LIST NAME')/items(ELEMENT ID)". Of course a workflow variable can be used as ID.
  • When modifying an element, the header must look like this. See the figure below.

 

POST Request Modify Item

2.1. Set text or number columns

In any case, the metadata columns must be set in the POST request to create or modify a list item in the target web site. In addition, the ListItemEntityTypeFullName list property must always be specified as the type property in the text body of the request.

For simple text columns or number columns, the following syntax applies (The values or workflow variables used are examples, of course):

{
'__metadata' : { 'type' :
'{WorkflowVariable:Var_ListItemEntityTypeFullName}' },
'<LABEL OF COLUMN>':'{<VALUE OF COLUMN>}',
}

Example:
{ '__metadata' : { 'type' :
'{WorkflowVariable:Var_ListItemEntityTypeFullName}' },
'Status':'{ItemProperty:Status}',
'Title':'{ItemProperty:Title}',
}

2.2. Managed Metadata Columns

2.2.1. Managed Metadata columns with single value

 

For Managed Metadata columns with single value the following syntax applies:

{
'__metadata' : { 'type' :
'{WorkflowVariable:Var_ListItemEntityTypeFullName}' },
'<LABEL OF COLUMN>':
{
'__metadata':{'type':'SP.Taxonomy.TaxonomyFieldValue'},
'Label':'{VALUE OF COLUMN}',
'TermGuid':'{TERM GUID}',
'WssId':'-1'
}

}
 

In order to find out the label and TermGuid of the Managed Metadata set, the Taxonomy Hidden List must first be queried. In this hidden list, which exists once per site collection, all managed metadata set anywhere in the site collection is stored. You can find the Taxonomy Hidden List at the following URL:

http://<servername>/site/<sitecollection>/Lists/TaxonomyHiddenList/

The list ID must be inserted in the “Lists” area of the query. You can find this out by calling the list settings of the Taxonomy Hidden List and copying the list ID from the URL.

In the „ViewFields“ area, enter all fields that you want to query. Note that the internal field names are used. These can in turn be copied from the URL when calling the respective column settings.

In the „Eq“ area, the values must be specified according to which the list is to be filtered in order to find the term in question. The example below refers to the current field “Company” in the example list.

CAML Query TaxonomyHiddenList

In the following example the two Managed Metadata „Process“ and „Company“ are set in addition to the simple text columns „Status“ and „Title“. The syntax for this is as follows:

{
'__metadata' : { 'type' : '{WorkflowVariable:Var_ListItemEntityTypeFullName}' },
'Status':'{ItemProperty:Status}',
'Title':'{ItemProperty:Title}',
'Process':
{
'__metadata':{'type':'SP.Taxonomy.TaxonomyFieldValue'},
'Label':'{WorkflowVariable:Var_Term1033_Process}',
'TermGuid':'{WorkflowVariable:Var_IdForTerm_Process}',
'WssId':'-1'
},
'Company':
{
'__metadata':{'type':'SP.Taxonomy.TaxonomyFieldValue'},
'Label':'{WorkflowVariable:Var_Term1033_Company}',
'TermGuid':'{WorkflowVariable:Var_IdForTerm_Company}',
'WssId':'-1'
}
}

2.2.2. Managed Metadata columns with multiple values

For Managed Metadata columns with multiple values, the syntax is more complicated and the procedure is as follows.

a) To do this, the values of the respective columns must first be stored in a collection variable. It’s done with the Nintex action “Regular Expression”.

b) The collection variable is used as the basis for a For Each loop to perform the check against the Taxonomy Hidden List with the action „Query List“.

c) Still in the For-Each loop, the action „Collection Operation“ is used to add the title and the term ID of each term to a new collection. This is required to arrange the values for the respective column in a specific syntax. See figure below.

d) After this For-Each loop follows another For-Each loop, which iterates over the variable „varCompanyGUIDCollection“. See the picture below.

e) In this For-Each loop, the title of the term is first retrieved from the previously created collection variable „varCompanyTitleCollection“ using the index of the collection variable „varCompanyGUIDCollection“ and stored in a new text variable (varCompanyTitleCollectionCurrent).

f) Next, the „Build String“ action is used to create the string to achieve the syntax for setting a Managed Metadata column with multiple values. This looks as follows.

The syntax must be:

-1;#TITEL TERM1|GUID TERM1; -1;#TITEL TERM2|GUID TERM2; -1;#TITEL TERM3|GUID TERM3;

The result is stored in a new text variable (varBuildStringCompanyCurrent).

g) The next step is to add this text variable to a new collection variable (varMetadataCompanyCollection) with the action “Collection Operation”. For each iteration of the For-Each loop, the syntax for each term is assembled and finally stored in a collection variable.

h) This variable is now used as a value in the POST request described in point 2-2.2. The header of the request must be defined as described under number 2. The syntax for setting the metadata is as follows:

{
'__metadata' : {'type':'{WorkflowVariable:varListItemEntityTypeFullName}'},
'dcadc47186584071958174492f8fa6d6':'{WorkflowVariable:varMetadataCountryCollection}',
'ipiCompanyTaxHTField0':'{WorkflowVariable:varMetadataCompanyCollection}'
}

In the example, the Managed Metadata fields „Country“ and „Company“ are set. The values that are set are the collection variables with the term titles and term IDs that were previously queried using two For-Each loops and are combined as character strings.

The field name „ipiCompanyTaxHTField0“ or „dcadc47186584071958174492f8fa6d6“ is exceptional. For each Managed Metadata field, an additional field is created in the background with the name „<name of the field>_0“. The internal name of this field must be researched. This is only possible with PowerShell or with the SharePoint Manager tool.
The following article describes this very well:
http://www.aerieconsulting.com/blog/update-using-rest-to-update-a-multi-value-taxonomy-field-in-sharepoint

If the Managed Metadata field was created manually, then the internal name of the field corresponds to an ID such as „dcadc47186584071958174492f8fa6d6“. If the field was created programmatically, the internal name will look more like „ipiCompanyTaxHTField0“ depending on the naming convention.

2.3. Setting People Picker columns within a site

If the people picker column is to be set within a site, the following syntax can be used for the setting of the value:

{
'__metadata' : { 'type' :
'{WorkflowVariable:Var_ListItemEntityTypeFullName}' }, 'UserId':'{WorkflowVariable:Var_UserID_Integer}'
}

IMPORTANT! The word „Id“ must be added to the internal label of the column. The user ID of the respective user must be passed as value of the type Integer.

Attention! The user ID is only valid within one site. This means that the user ID cannot be set across site collection boundaries. This is because the user ID 123 in a site or site collection is not the same for the same user in another site or site collection. Therefore, it can only be used within one site.

2.4. Setting one or more people picker values across site or site collection boundaries

The example described here assumes that the people picker values in one column are read from an existing list and added to an element in another list (cross-site or cross site collection).

The procedure is as follows:

a) The requests described in chapters 1.1.-1.3. shall be performed.

b) The people picker column of the existing list must be queried for the (single) value(s) to be set.

c) If multiple values are involved, the values of the column must be split and stored in a collection variable with the help of the with the action “Regular expression”. This is shown in the picture as an example.

d) The collection variable is used as the basis for the For-Each Loop.

 

The following actions are performed in the For-Each loop.

The action „Regular expression“. So that the name of the user can be transmitted with the help of the web service, the character „\“ must be trunkated with another backslash (\). The action is configured as follows.

The result is restored to the same text variable (var_ContactCollectionCurrent) and then added to a collection variable. In this way, all people picker values are stored in a collection variable (var_ContactCollectionChanged).

 

e) This collection variable (var_ContactCollectionChanged) is in turn used as the basis for a For-Each loop. The current value of the collection iteration is stored in the variable „var_ContactCollectionChangedCurrent“.

 

2.4.1.      Ensureuser method

The ensureuser method can be used to check whether the specified logon name belongs to a valid user of the web site. If the user does not exist, the user is added to the web site.

For this reason, a POST request (Nintex action „Web Request“) with the ensureuser method is first executed for each iteration in the For-Each loop. To do this, the addidtion „_api/web/ensureuser“ is appended to the URL of the target site collection. With this query, the respective user is added to the target site collection, so to speak, if the user does not exist. The return value of the query is e.g. a user ID.

The content type of the query is: application/json;odata=verbose

The header of the query looks like this. See the figure below.

The text of the query is rather simple.

The label must always be „logonName“ and the current value of the For-Each loop is passed, which is stored in the text variable „var_ContactCollectionChangedCurrent“.

The result of the query is stored in the variable var_ensureuser (type: Multiple lines).

 c) The return value of this query is as follows.

{"d":{"__metadata":{"id":"Web/GetUserById(11)","uri":" http://<servername>/site/<sitecollection>/_api/Web/GetUserById(11)","type":"SP.User"},"Groups":{"__deferred":{"uri":" http://<servername>/site/<sitecollection>/_api/Web/GetUserById(11)/Groups"}},"Id":11,"IsHiddenInUI":false,"LoginName":"i:0#.w|DOMAIN\\USERNAME","Title":"USER FIRSTNAME, SURNAME","PrincipalType":1,"Email":"USERNAME@DOMAIN.loc","IsSiteAdmin":false,"UserId":{"__metadata":{"type":"SP.UserIdInfo"},"NameId":"s-1-5-21-2322584218-2848037957-3530394418-2234","NameIdIssuer":"urn:office:idp:activedirectory"}}}

It is marked in bold and contains the user ID.

2.4.2. Extraction user ID

Therefore in the next step the actual user ID with the Nintex action „Regular expression“ must be extracted from the return value. See the figure below.

The result of this action is the value: "Id":11,;

Since there are multiple people picker values and we are in a For-Each loop, the result is again stored in a collection variable (var_EnsureUserIDcol).

a) However, the comma at the end of the user ID is annoying, which is why the individual values of the collection variable are separated without a comma in another action “Regular expression” and stored again in the same collection variable (var_EnsureUserIDcol). This looks as follows.

b) Now, the stored result of the user ID is the value: "Id":11;

To get rid of the text “Id:”, the action “Set variable” with the following inline function is used in order to remove it: fn-Entfernen(var_EnsureUserIDtxt,0,5)

c) The result is the „pure“ user ID (in the example 11) as value. This value is stored in a new collection variable (var_EnsureUserIDtxtcol). Because we are still in the For-Each loop, which iterates through all user IDs.

d) Based on this last collection variable created (var_EnsureUserIDtxtcol), another For-Each loop is created directly after this For-Each Loop.

In this For-Each loop, the list element is modified in the target site collection using a POST Web Request action. It looks the same in URL, content type, and header as the action described in chapter 2.

The query text of the action is as follows:

{
'__metadata' : { 'type' : '{WorkflowVariable:Var_ListItemEntityTypeFullName}' },
'ContactId':'{WorkflowVariable:var_EnsureUserIDtxtcolCurrent}'
}

Important! The addition “Id” must be added to the name of the people picker field. The respective value (user ID) of the collection variable per iteration is passed as the value.

Since the For-Each loop changes the same list element multiple times in succession, a pause of one minute is inserted at the end of the loop. Otherwise, the changes to the field would be made too quickly one after the other.

3. Sources

The following sources were used for this article:

Outcomes