Question

convert text field to date field

Workflow

Updated February 6, 2019

asked on August 27, 2015

We have templates set up with a date range field that is currently text for various reasons. It can either contain a single date or a date range. It reads either:

Date Range:

02/18/13 - 05/04/15

Single Date:

06/17/2013

We want to convert these into 2 separate date fields - start date and end date, that are actually date fields and format as such.

I created the 2 date fields and added them to the template. I have a workflow that finds a series of files, and for each determines if the date is a single date or a range and then breaks up the date text using regular expressions.

All goes well until I get to assigning the date field and I get an error every time that the value I am trying to assign to the new field is invalid. I tried omitting the "/" and just inserting the numbers, inserting it into a token and tagging the token as a date prior to assigning to the date field. Nothing is working, I keep getting either a 9013 or a 9017 error.

Does anyone have suggestions or can point me in the right direction? Thanks!

0 0

Replies

replied on August 27, 2015

9013 is "access denied", so it sounds like your Workflow connection profile user may not have rights to write to the fields or modify the document?

9017 is "bad data", which indicates that the field value was not a proper date. What do the values look like when you're getting the error?

2 0

replied on August 28, 2015

I get this:

I was thinking that access was denied because the value it is trying to write to a date formatted field is incorrect. Like it is a text date that I am trying to cram into a field that is supposed to be formatted as a date. But I put this into the token to format as a date before I try to assign to the template and I still get this error.

Using track tokens I get:

But the single date of 02/21/2013 cannot be written to the new field.

This is what the create single date token looks like:

I am just practicing with the single dates right now and can't get past writing to the new field. Thanks for the help.

0 0

replied on August 28, 2015

No, "access denied" is a permission issue. I guess the message is a bit misleading.

0 0

replied on August 28, 2015

When I have to convert text fields to date fields I use the Date Token Calculator. I choose Specific Date as my Date type, they use expressions to fill in each specific part of the date.

My example is built for a field that contains something like 08282015. So when you pull your parts you would want it to look something like \d{2}.\d{2}.\d{4} as the base and then put the parentheses around the \d{#} that you want to pull. So month would be (\d{2}).\d{2}.\d{4} day \d{2}.(\d{2}).\d{4} year \d{2}.\d{2}.(\d{4})

2 0

replied on August 28, 2015

Thank you, this was helpful!

0 0

replied on February 6, 2019

This was helpful for me on an unrelated issue. Thanks for posting the screen captures. I was having an issue formatting extracted text into a date.

0 0

replied on August 28, 2015

It's difficult to comment without seeing the content of the Assign Token Values and the Assign Start Date tools, but have you tried escaping the / by putting a \ before it? Also, if you use a track tokens tool, you can see what the actual values are...sometimes they are not what you expecting, but it provides a place to start troubleshooting...

0 0

replied on August 28, 2015

After a bit of trial and error, if I format the date to omit the "/" (02192013) I get a 9017, bad field value.

If I leave the "/" in (02/19/2013) I get a 9013 - the data assigned to the field is not valid, access denied.

Stumped as to how to format this. The field I am trying to write to is formatted:

0 0

replied on August 28, 2015

Does the user specified in the connection profile have rights to edit the field? 9017 is the correct error for "02192013" since it's not a valid date. 9013 is not an issue with the data.

1 0

replied on August 28, 2015 • Show version history

The user is workflow and yes, I specifically added Workflow to the field to see if that was the cause. It isn't resolving the issue.

Oops, retract that! I just tried again with the / in and it RAN! FINALLY! I guess I needed to give workflow specific rights to the field! Sorry, I just keep modifying so many different things I thought I had tried that, but apparently I had not tried 02/19/2013 WITH Workflow being specifically granted access to that field! IT WORKS! Woo Hoo! Thank you, Miruna.

On a side note - this is the first time I've ever had to specifically grant workflow access to a field for it to be able to write to it. We have plenty of other workflows that operate just fine and write to fields all the time without specific access to those fields. I am curious as to why it needs specific permission this time around.

1 0

replied on August 28, 2015

It would depend on how your security is set up for those fields. If you go to the Effective Rights tab, it should show you where the user would be inheriting rights from if any.

1 0

You are not allowed to follow up in this post.

Question

Question

convert text field to date field

Replies

Sign in to reply to this post.