Author Archives: admin

Calculated Column: Calculate the difference between two times

You can calculate the difference between the time an item is created and modified via the following formulas:

=TEXT([Modified]-[Created],”h”) Displays the hours between the two times;

=TEXT([Modified]-[Created],”h:mm”) Displays the hours and minutes between the two times;

=TEXT([Modified]-[Created],”h:mm:ss”) Displays the hours, minutes and seconds between two times.

For the methods to work, hours must not exceed 24, and minutes and seconds must not exceed 60.

Calculated columns: Display items created this month

To view the SharePoint list items that are created this month you need to create two calculated columns and a custom view.

Add a new calculated column StartMonth and at the following formula:

=DATE(YEAR([Created]), MONTH([Created]), 1)

For the second calculated column EndMonth use the following formula:

=DATE(YEAR([Due Date]), MONTH([Due Date])+1,1)-1

Now create a new view and add the following filters:

StartMonth is less than or equal to [Today]

and

EndMonth is greater than or equal to [Today]

now you have a view for all calls created this month.

Calculated Column: Items created this week

An short post about how to create a view displaying the  list items that are created this week:

Create a calculated column named WeekStart and add the following formula =[Created]-(WEEKDAY([Created])-1)

Create a calculated column named WeekEnd and add the following formula =[Created]+(7-WEEKDAY([Created]))

Now create a view where there’s a filter that [Today] is bigger than or equal to WeekStart and a filter that [Today ] is less than or equal to WeekEnd

Add each item from InfoPath repeating section to SharePoint field with Nintex Workflow

If you want to add all items from a repeating section in InfoPath to a SharePoint field via Nintex workflow you can.

Create three variables:
Two text: “SoftwareToRetract” and “SoftwareRetracted “and one collection: “software collection”

  • Use an XML Query and select XML source current item;
  • Process using XPath;
  • Add your repeating field *
  • store the result in the Collection variable “software collection”

Add an For Each

  • Target Collection is “Software Collection”;
  • Store result in “SoftwareToRetract”;

Buid dynamic string

  • insert reference “SoftwareToRetract”;
  • add another one to insert reference to “SoftwareRetracted”
  • Store result in “SoftwareRetracted”

After the For Each add an Update item

  • select the field that stores the results and as workflow variable “SoftwareRetracted”

*You can get your XPATH code by opening your InfoPath form in InfoPath 2007. Now go to the field in the data source
task pane and right-click, copy XPath to get a field’s xpath code. Past it in the process XPATH field of your Query XML.

Update InfoPath field in a Repeating Table with Nintex Workflow

How to update a column in a InfoPath repeating table based on the selected user in a other column?
I wanted to add several users to a repeating table and after submitting the form the computer, location etc. from the selected users needs to be added via a workflow in the form.

The structure of the form is as follows: we need the field AccountId that is a child from Person, which is a child from gpContactSelector, that is a child is from group4, which is a child from group 3 and finaly the parent is myFields.
The XPath for this field is:

/my:myFields/my:group3/my:group4/my:gpContactSelector/my:Person/my:AccountId

updaterepeatingtable2

 

The workflow has the following actions:

updaterepeatingtable3

 

Add a Query XML on the Current item and add the XPath path, store the results in a collection variable

updaterepeatingtable4

 

Add an for each action that process the collection and a text variable.

An Update XML
XML source is current item

Create the following Xpath query:

  • /my:myFields/my:group3/my:group4/my:gpContactSelector/my:Person/my:AccountId.  Go to the parent of the field AccountId 
  • [my:AccountId={Workflowvariable:Created Text variable}]
  • /parent::my:gpContactSelector/parent::my:group4/my:FromLocation. This tells the query to go up two levels to come to group 4 and select there FromLocation

Select Update node value
Add the variable you want to set in the FromLocation

updaterepeatingtable6

Just a last few point:

  • This workflow doens’t do anything more than retrieving and updating, other options you have to add yourself
  • For more XPath see http://www.w3schools.com/xpath/default.asp
  • To get the Query I used Altova XMLSpy

Nintex Web service gives a HTTP status 400: Bad Request error

A workflow crashes with a HTTP Status 400: Bad Request error, strange that it only happened on one list item. The workflow needs to copy an item in another site collection via a Nintex web service action.

As it turned out the reason is that the title contained an ampersand (&) character and the web service crashes on special characters. So we have to excluded the parsing of the title, this can be done with the term CDATA.

Add an build dynamic string action and wrap the Title around “<![CDATA[” and end with “]]>”.
Store the result in a variable and replace the title with the newly created variable.

cdata1

Screenshot in Dutch.

How to get the ID of item which is just created with Nintex call web service

When you use the standard create item in Nintex you have the option to store the new item ID in a variable, this option isn’t available when you create an item via web service.

The solution is to use the Store result in option in the Call web service action.

webserviceid1

 

Now add the Query XML action and query the result from the web service with XPath (//@ows_ID)

webserviceid2

 

This result will be your newly created item ID

Retrieve user properties from SharePoint person field with Nintex Workflow

When working with a personfield in SharePoint you need to remember that the field doesn’t save the username from the user but his SharePoint userID. If you want to use this field to get the emailaddress, firstname or other userproperties from the user you can use Nintex workflow to retrieve these items.

Create two text variables, one for web service result and one for Query XML result.

Add an Call web service action

  • Url is the current url via Web Url and add the following to call the userprofile webservice /_vti_bin/userprofileservices.asmx
  • Web method is GetUserProfileByName
  • AccountName is the SharePoint personfield
  • Save to the variable webservice result 

Add an Query XML

  • XML is the variable webservice result
  • Process using XSLTretrieveuserpropertiesfromsharepointpersonfield
  • Store result in variable Query XML result
    retrieveuserpropertiesfromsharepointpersonfield2