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

Question

Question

Lookup with 3 matching fields - but one of them has some NULL values

Version 10 Forms
Updated January 12, 2021
asked on January 11, 2021 • Show version history

Hello: 

I have a SQL DB table with the following columns:

Unit - Data is either text, or NULL

House - Text field

Street - Text Field

PropertyID - Text Field

 

 

On the Form I have 4 fields:

Unit  - sometimes blank when submitted

House - Required field

Street - required field

PropertyID - Looked up and Filled in when Unit, House and Street are filled in

 

The goal is to spit out he property ID on a field in the form when Unit House and Street are filled in.

The problem is the Unit field. Despite my efforts to create the right combination of Lookup Rules, I can only get a PropertyID if all 3 are filled in with a value. Leaving Unit blank when the form is filled in results in no PropertyID value being set on the 4th field.

 

I suspect that when the SQL server is polled by Laserfiche Forms and matched to what's filled in the form, it's not receiving the Unit value from the form as NULL, thus no matches are found.

How do I set up the lookup rules so that if Unit on the Form is blank, it does the lookup as a NULL value in the unit field? This should resolve the issue.

 

Thanks!

Herb Cruz

 

0 0

Answer

SELECTED ANSWER
replied on January 12, 2021

I've done very similar. When you have multiple equally viable options like this is usually just boils down to personal preference. Your example with the OR statements might make more sense to someone than mine would, and mine would need adjusting if you needed to check for columns with empty values too.

Now that you got me thinking, here's a third option that might work

SELECT prop.propertyID
FROM property_id_lookup_table AS prop
WHERE ISNULL(prop.unit,'') = @unit
AND prop.house = @house
AND prop.street = @street

Instead of replacing the empty string, you just do an ISNULL on the column when you match it; that way blank will still match blank and so will null.

1 0

Replies

replied on January 12, 2021 • Show version history

A major problem is that the value being looked-up from LFForms is not going to be NULL if it it blank, it's going to be an empty string.

Best way I've found to deal with situations like this is creating a Stored Procedure on your SQL database.

Here's an example of a script to create a Stored Procedure in MSSQL v.18:

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Matthew Tingey
-- Create date:   12 January 2021
-- Description:   Example of Stored Procedure for Laserfiche Answers
-- =============================================
CREATE PROCEDURE ExampleStoredProcedure 
        -- Add the parameters for the stored procedure here
        @unit varchar(50) = '', 
        @house varchar(50) = '',
        @street varchar(50) = ''
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        -- Insert statements for procedure here
        SELECT prop.propertyID
        FROM property_id_lookup_table AS prop
        WHERE 
        (
          prop.unit = @unit
          OR
          @unit IS NULL
          OR
          @unit = ''
          OR
          prop.unit IS NULL
        )
        AND
        prop.house = @house
        AND
        prop.street = @street
END
GO

This is expecting 3 inputs: unit, house, and street, and returns 1 output: propertyID.  It uses a table named property_id_lookup_table and assumes that the inputs house and street will always match the house and street fields in the table.  It assumes the unit input may be blank or NULL or may match the unit value in the table, or the unit value in the table may be NULL.

1 0
View 2 previous replies
replied on January 12, 2021

I was just writing the same response when yours popped up lol.

One thing I might do differently, but both should work, is that instead of doing the OR in the lookup, I'd do an IF before the select to replace empty with null.

For example,

IF @unit= '' SET @unit= null

SELECT prop.propertyID
FROM property_id_lookup_table AS prop
WHERE prop.unit = @unit
AND prop.house = @house
AND prop.street = @street

 

1 0
replied on January 12, 2021

Sorry to beat you to it @████████.

I like your idea with the IF statement.  I was coming from an example where there are a few options that I want to be able to search as a certain way (things like blank values, or the word "All", etc.) so the ...OR... worked best for me - but for @████████'s specific situation, the IF statement is probably a better fit,

 

0 0
SELECTED ANSWER
replied on January 12, 2021

I've done very similar. When you have multiple equally viable options like this is usually just boils down to personal preference. Your example with the OR statements might make more sense to someone than mine would, and mine would need adjusting if you needed to check for columns with empty values too.

Now that you got me thinking, here's a third option that might work

SELECT prop.propertyID
FROM property_id_lookup_table AS prop
WHERE ISNULL(prop.unit,'') = @unit
AND prop.house = @house
AND prop.street = @street

Instead of replacing the empty string, you just do an ISNULL on the column when you match it; that way blank will still match blank and so will null.

1 0
replied on January 12, 2021

Oh!  That's really clean!

0 0
replied on January 12, 2021

Maybe if he didn't want to deal with a Stored Procedure, he could just do a view using that approach to allow the lookup to work.

CREATE VIEW [dbo].[v_property_id_without_null] AS (
    SELECT prop.propertyID
        prop.house,
        prop.street,
        ISNULL(prop.unit,'') AS unit
    FROM property_id_table AS prop
)

That way he could just point the form lookup at that view instead of the table.

1 0
replied on January 12, 2021

Thank you so much for your assistance with this - and for the quick responses - a wonderful learning process.

All of these variations worked. In the end, I went with Jason's suggestion. 

 

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

Sign in to reply to this post.

Details
Related Posts
Loading ...