You are viewing limited content. For full access, please sign in.

Question

Question

Using DB.Null in a Token

asked on June 16, 2016

Is this possible?  Basically the token shows a warning if I enter %(DB.NULL) in the value.  I have to type this as it is not available in the list of token choices.

We need to write NULL values to a database if certain fields on a submitted form are empty.  This means that we have to check each one and we would certainly want to stay away from lots of Update Datas or Inserts.  Basically I have a Parallel that is checking all the tokens if they are empty or not.  Below is just two:

If the field submitted is empty, we update the token with DB.NULL.  If it is not empty, we update the token to hold whatever the value submitted was.  After this parallel is when we do our Insert to the database and I want to use the token values.  However DB.NULL is not a choice and when I try and publish I get an error about "Cannot implicitly convert type 'string' to 'System.Workflow.Activities.SequenceActivity'".  I assume this is because the token is marked as a string

Anyone know how to write NULL to a db from a token?  Otherwise we are going to have two update data's for each possible field.

 

Thanks,

Chris

1 0

Answers

SELECTED ANSWER
replied on June 16, 2016

No. It's only usable in the data activities.

0 0

Replies

replied on June 16, 2016

Thanks for all the replies.  Yes, we use the NULL activity in both Insert and Update.  However we would just like to update a token with NULL and use it if needed.  Maybe it can be looked at as a feature request.  As it works now, this adds an additional 12 Update Data activities to our workflow.  Maybe writing a stored procedure in Custom Query is the way to handle it.

2 0
replied on July 9, 2018

I know this is an old question but I thought I'd post a workaround in case anyone else needed it.  I had this same issue where I have a dynamic query and it errors when trying to pass the DB.NULL via a token.  What I did was write an empty string into the token and then add NULLIF to my insert/update statement.  Thus the UPDATE was:

  • UPDATE table
    SET integer1=NULLIF(@integer1,''),decimal1=NULLIF(@decimal1,'')...

 

This inserts a NULL value if the token value is blank.  

4 0
replied on July 10, 2018

Awesome, thanks for the tip!

0 0
replied on October 28, 2020

I just want to add my support of this being a feature request. Has anyone heard if we will be able to assign %(DB.NULL) to a token in any future release of WF?

The current process I'm working on needs to insert/update a lot of data to a table but only if the field is not empty. By just updating all the columns directly from their associated field values, it creates empty columns, not NULL, if a field value is blank. Other components of the process (i.e. lookup rules in a related form, other workflows, etc.) are looking to see if a value is returned from these columns. Since empty is not the same as NULL, I either have to implement a bunch of conditionals in the original workflow that will need to be maintained or, as previously suggested, update/insert using a custom query (this is the route I will be taking) in order to insert the NULL that subsequent components rely on. In any case, being able to just modify a token with %(DB.NULL) (or something that achieves the same outcome) would sure save a lot of time.

The concept discussed here is cool but specifically doesn't accommodate inserting NULL into a database.

2 0
replied on June 16, 2016

Only way i was able to write NULL to database was by using custom query.

UPDATE [LFRefDB].[dbo].[EmployeesTest]
SET [Middle_Name]=NULL
  WHERE [ID]=8

When i try to use Token value as parameter or use update data activity(with or without token), column is updated with 'NULL' instead of actual NULL.

1 0
replied on June 16, 2016

The DB.NULL token is useful if you would like to clear a Laserfiche Forms variable in the middle of a process. 

If you would like to set a NULL value using one of the databse activities, you can use this token: 

1 0
You are not allowed to follow up in this post.

Sign in to reply to this post.