SeanF
Scholar

How to change a collection of values from DateTime to Date format dd/MM/yyyy

Jump to solution

Hi All, hope all's well.  I'm in need of your amazing workflow talents again.

I have created a workflow thanks to @jesse_mchargue 's post regarding collections (Thanks Jesse).

 

However, I have reached an issue I cannot seem to solve.  My workflow works as follows:

1 - Query XML from a Multi Line repeating section column on the list and pull back the following:

   -ActionOwner

   -Action

   -ActionDate

 

2 - I then use Jesse's guidance to do a "For Each - Action" loop and then nest three collection operations to get the aforementioned data in to three collection variables (aptly named colActionOwner, colAction and, yep you guess it, colActionDate).

 

3 - I store the collection data in to Text variables (same name as above but with txt infront of their name instead of "col").

 

Now this is where the issue I'm suffering occurs.

 

The txtActionDate variables look like this: "MM/dd/yyyy 00:00:00"

I use Regex to remove the Hours, Minutes and seconds so i'm left with "MM/dd/yyyy" but further downstream, where I have a "Create Item" operation that creates new list items based on the "txtAction" and "txtActionOwner" variables, when I try to parse this date in to this new list (in to a date column called "Complete action by") it errors.

 

I've read various posts on this forum with people explaining either using a "Convert" operator to transpose the date alternatively building strings using "fn_FormatDate({variable}, "dd/mm/yyyy")" but unfortunately, nothing seems to be working for me.  Whilst I'm sure it's down to me not comprehending something, I wondered if you have any suggestions or gold nuggets on this forum that might help me? 

 

I think the issue has something to do with the format the date is in when I try to create the new item but I would welcome any advice you guys may be able to offer up?

 

Error parsing value. Value '01/24/2020 ' does not match the provided date format 'dd/MM/yyyy'. 

 

My WorkflowMy Workflow

 

Any information needed, please let me know.

Thanks

Sean

0 Kudos
Reply
4 Replies
SimonMuntz
Nintex Employee
Nintex Employee

Re: How to change a collection of values from DateTime to Date format dd/MM/yyyy

Jump to solution
Hi,

I dont think its the format that is the issue. I think its the fact that you are putting a text variable into a date column.
0 Kudos
Reply
SeanF
Scholar

Re: How to change a collection of values from DateTime to Date format dd/MM/yyyy

Jump to solution

Hi Simon, many thanks for your response. So I changed from a text variable within the collection operation to a date / time variable (see below).

 

Collection.JPG

In the "log in history" action I ask for this variable to be output but the workflow errors and says:

 

"Error operating on collection variable. Data type returned from the collection is incompatible with the variable to store the result in."

 

When I change the collection output variable back to a text variable and run the workflow, it runs okay and outputs the following in to the history log.

HistoryLog.JPG

 

Any relevant posts / guidance you think I could look at in order to try and resolve this, would be most welcome. 

Thanks again for your time. Sean. 

0 Kudos
Reply
SimonMuntz
Nintex Employee
Nintex Employee

Re: How to change a collection of values from DateTime to Date format dd/MM/yyyy

Jump to solution
Try using a text variable in your collection operation and then run that variable through a Calculate date action and saving it into a date variable.
0 Kudos
Reply
SeanF
Scholar

Re: How to change a collection of values from DateTime to Date format dd/MM/yyyy

Jump to solution

Hi Simon, Thanks for your response. So I tried to use your method but unfortunately, the workflow errored.  However, it forced me to try a few other things and thankfully, one of them worked but I'll be honest, how I have no idea? 

 

So here's what I did: 

 

1 - After performing a collection operation for "Action", "Action Owner" and "Action Date" I added a Regular expression operation and removed the time from the text variable I output the collection operation to (txtActionDate).  I did this by using the following pattern [0-9]+:[0-9]+:[0-9]+ (see pic below):

RegexExample.JPG

I output this new data to another variable called txtShortActionDate.

 

3 - I then used a "Convert Value" operation but this time, despite reading lots of forums about the "Date Format" column and defining which format I should use (like "MM/dd/yyyy" etc, I decided I would just leave it blank and see what occurred. This took the text from my "txtShortActionDate" variable and output it in to a date variable called "dateActionDate"

 

Thankfully, this worked (see below):

ConvertValueEg.JPG

Now when I run this workflow, it loops through my list item, extracts the repeating section data, creates a new list item in another list which has an "Action", "Action Owner" and a "Action due by" date.

 

I've attached the workflow I used in case it helps others with a similar problem.

 

Thanks for your collective help.  This forum really is a great place to get help from people.

Cheers, Sean.

View solution in original post

0 Kudos
Reply