Question

Handling NULL date in Workflow

Workflow

Updated June 22, 2015

asked on September 15, 2014

Hello Everybody,

Working with Workflow's 9.1.1 Query Data activity. This activity request data from an SQL table which has several date fields. Some of dates fields are not populated yet and have NULL entry. When Workflow processes that NULL date field I'm getting "The token ForEachRow_DT_TO was not found [0511-WF1]" in the conditional decision or in assigning field values activities. I'm looking for the way to run one branch of the workflow if this field is NULL and another if it has date in it.

Is there any solution for that?

Thank you very much,

Vladimir

0 0

Answer

SELECTED ANSWER

replied on September 15, 2014

As Kenneth said, definitely make sure all the column definitions are correct. Another way to accomplish this would be with the SQL IsNull function that is built into MSSQL. It would require you to change it to a custom query activity though. Isnull returns a specific value in the event of a null being returned. See http://www.w3schools.com/sql/sql_isnull.asp

John

1 0

replied on September 15, 2014

Good thinking, this is actually something I do but forgot to mention. This is not the normal way people do things though so this problem is either user error or a potential bug in the WF designer that might need to be addressed

0 0

replied on September 15, 2014

Thank you John,

I'll give IsNull a try.

Best Regards,

Vladimir

0 0

Replies

replied on September 15, 2014 • Show version history

you should be able to test the token to see if it is empty. Are you not able to do that? I would add a conditional sequence that tests if that token is empty and then change it to a blank token instead so that you can go without changing all the activities to a new token yet account for this situation. I haven't run into this specifically though, I might give it a try and report back. Can you give us some more details about how you are using this token in the conditional decision?

Can you also make sure to click on the circular arrow button next the the DB table name in the activity to make sure you even have that column available. If it is not in the table and yet you are expecting it, then you might be getting this error all the time.

0 0

replied on September 15, 2014

Hi Kenneth,

Thank you for reply, that exactly what I was trying to do - to assign something (current date perhaps ) to a filed if it's NULL in DB.

I made sure the field exists in the columns list, and then tried a conditional sequence to check is field is empty - it does not work - with warning message. In the evaluation of the condition it says token value is not empty.

Attaching screen shots

Thank you,

Screen Shot 1.png (109.39 KB)

| Download

Screen Shot 2.png (74.91 KB)

| Download

Screen Shot 3.png (29.88 KB)

| Download

0 0

replied on September 15, 2014

hmm....error message when it's checking for empty, but no error when checking for if not empty? Have you tried seeing if it is equal to "%(DB.NULL)"?

0 0

replied on September 15, 2014

Hi Kenneth, it actually shows warning in both cases, there in no %(DB.NULL) selection in the condition, tried to manually put it in the Token Dialog - did not work.

Thank you,

0 0

replied on September 15, 2014

Can you try a "Track_Token" activity in a "Catch" after putting that sequence in a "Try-Catch? And put a "End Workflow" activity after as well so we can just see the last things to happen before that error.

Can you post some screenshots of that?

0 0

replied on September 16, 2014

Hi Kenneth,

Tried Try-Catch - did not work because it's only warning, so never got in to Catch branch. Track tokens was on - The token in question is there and is empty.

Thank you,

Vladimir

0 0

replied on September 16, 2014

Vladimir,

Have you considered creating 2 SQL views based on date value (NULL or not)?

Each workflow branch could then run in parallel based on its respective view.

0 0

replied on September 16, 2014 • Show version history

I tried John's suggestion and it worked as a workaround - will mark it as an answer. Propose development team to make changes in a Workflow Query Data activity - it definitely should return all columns from DB request as tokens (even if value is NULL) and then have a comparison rule %(DB.NULL).

Thank you,

Vladimir

0 0

replied on September 16, 2014

I am pretty sure you can just type in that token as the right half of the comparison and it will evaluate. might give you a warning during publishing though, but should work just fine.

The thing is, that's a hidden token usually only used in update query activities. I am surprised that you are getting an error though since you are iterating through the results and some have the token and some do not. That is quite odd, but that is definitely a great case to use a SQL View or the Custom Query activity so you have less work to do in your workflow as well.

0 0

replied on September 16, 2014

Hi Kenneth,

I tried putting %(DB.NULL) in the Condition - still does not work, attaching screen shot

Screen Shot 4.png (34.81 KB)

| Download

Screen Shot 5.png (66.14 KB)

| Download

0 0

replied on September 16, 2014

have you tried the custom query activity?

I would love to see which iteration that error comes up on. Is this maybe only happening if the first row has a NULL in that column so it doesnt make that token during runtime?

0 0

replied on September 17, 2014

That's actually a great point it - it may because in my testing I've started with NULL record. Will try to create a query where first is not NULL.

0 0

replied on June 19, 2015

Hi Vladimir,

I've run into the same issue. Checking the field value for "is empty" (the only native option that looks close to being able to handle this) returns false (as it should, as null is not the same as empty), so that doesn't work to avoid using the null.

To get around the issue I run a custom query "SELECT null AS dbNull" at the start of my workflow and then compare (using a "does not equal"; adjust to suit your logic) the field value I want to check for null to the value returned by this custom query. This seems to work reliably.

Regards,

Daryl

0 0

replied on June 22, 2015

Thank you Daryl,

Yes I used the same and run it on many entries - it does work reliably.

Best Regards,

Vladimir

0 0

You are not allowed to follow up in this post.

Question

Question

Handling NULL date in Workflow

Answer

Replies

Sign in to reply to this post.