Question

Insert Null in SQL database if date field is blank?

Workflow

Updated January 19, 2015

asked on January 16, 2015

I have a daily scheduled workflow that runs a Laserfiche search and then exports those results to a SQL table that is used as a data source for automated reports. The problem I am running into is when a blank date field is returned in the LF search results. Since the SQL table has field types set as a "date" format and to allow Null values, SQL will insert 1/1/1900 in all date fields that are blank in the Laserfiche metadata.

I know I can set the fields in SQL to varchar but I would like to keep them as date fields if possible. How can I have Laserfiche insert "Null" only if the search result returns a blank date field?

I was thinking I could use an expression on those fields in the Workflow but not sure of the syntax that would replace blank with Null.

Thanks in advance!

Eric

0 0

Replies

replied on January 19, 2015 • Show version history

Here are two sample workflows that can be used to achieve the desired result. Note that the database table I'm inserting into uses an nvarchar column for the string field and a date column for the date field.

The first one just uses a separate "Update Data" activity that will update all date values that equal '1900-01-01' with NULL.

The second workflow uses a "Custom Query" activity instead of an "Insert Data" activity. In my "Custom Query" activity, the database type is just using SQL Direct Connection and the parameter types in the query are set to default. This allows me to use NULLIF when inserting the date field value.

Note that these workflows are proof of concepts so you should test to see how they work performance wise with what you're actually doing.

2 0

replied on January 16, 2015

i believe the token to use is %(DB.Null)

Use a conditional decision to test if the returned value is empty and then modify a token you use with that token above or the current value.

0 0

replied on January 16, 2015

I was really hoping to avoid a conditional decision test. That would create over 80,000 conditional checks each time the workflow runs (daily) based on the number of rows and number of date fields I would have to compare. Was hoping I could do it with an expression but that may not be an option either.

Thanks for the reply.

0 0

replied on January 16, 2015

Here are a couple options of Kenneth's suggestion takes too much processing time, however I think you will find that that all will happen very quickly.

1. Why not have these inserts happen per item when they are complete instead of one huge workflow at the end of the day?

2. You can do this minor type logic in a custom SQL query if you are familar with SQL.

3. If this is happening in off hours, how about doing the inserts as you currently have the workflow setup, then at the end of your insert loop, performing a SQL update where the date equals the 1/1/1900 and setting all those values back to null correcting all your data.

I think the first option would be the best assuming the processes allow for it.

0 0

You are not allowed to follow up in this post.

Question

Question

Insert Null in SQL database if date field is blank?

Replies

Sign in to reply to this post.