Question

Database Lookup with Null Values

Forms

Updated April 30, 2018

asked on January 12, 2017

Is it possible to perform a lookup based on if a column in the database is NULL?

1 0

Answer

SELECTED ANSWER

replied on January 12, 2017

This feature has been supported in Forms 10.2: now when configure lookup rule, you can configure the condition to "when _(DB.NULL) matches with data source column xxx"

0 0

Replies

replied on January 12, 2017 • Show version history

Hi Blake,

I don't know enough about your use case to give a specific answer but I am guessing your real issue is that you need to both return a value based on input and a different value when no input is given. If so the below should point you in the right direction. If your criteria column can be both null and empty you will want to handle that scenario.

select top 1 [rtn col]

from [table]

where (datalength(@InputParam) = 0 and ([criteria col]) is null)

or ([criteria col] = @InputParam)

- Curtis

0 0

View 1 previous reply

replied on January 12, 2017

Yes, if you have a stored procedure with similar syntax to what I put in the post and use a form field as the InputParam (and one to catch the output) this will work as I described. We've talked before so call me if you like.

2 0

replied on January 12, 2017

So without a Stored Procedure though it doesn't look like it's possible?

1 0

replied on January 12, 2017

You could do something similar with a view but I wouldn't. Tables (At least in the version of forms I have.) only work with a matching value.

2 0

replied on January 12, 2017

SELECT *
FROM employees
WHERE last_name IS NULL;

This SQL Server IS NULL example will return all records from the employees table where the last_name contains a null value.

0 0

replied on January 12, 2017

Thanks Mike. This is actually using the lookup rules in Forms.

1 0

You are not allowed to follow up in this post.

Question

Question

Database Lookup with Null Values

Answer

Replies

Sign in to reply to this post.