Question

Forms 10.3 Lookup with blank field as parameter

Forms Version 10

Updated October 9, 2019

asked on October 9, 2019

I have a form with a table populated from a Lookup rule and a single field to filter the Lookup.

Here's the Lookup:

Here's the store procedure I am using for the Lookup:

@EmployeeName VARCHAR(100) = ''


SELECT *,
    (SELECT 
        CASE 
        WHEN mda.application_status = 'Active' THEN 1
        WHEN mda.application_status LIKE 'Pending%' THEN 2
        WHEN mda.application_status = 'Inactive' THEN 3
        WHEN mda.application_status = 'Declined' THEN 4
        WHEN mda.application_status = 'Canceled' THEN 5
        ELSE 3
    END) AS statusOrder
FROM uhf_mobile_device_application mda
WHERE (@EmployeeName = '' OR employee_name = @EmployeeName)
ORDER BY employee_name, statusOrder, service_start

As you can see in the SPROC if there is no value passed for the parameter @EmployeeName it will default to empty set and in the Where clause if @EmployeeName is empty then it should return all rows. However, you can see in my screenshot the table is not populating when the the Employee Name field is empty. I tested the SPROC in SSMS with nothing in the parameter and it does return all rows. My question is what does Laserfiche Forms send as a parameter if the Employee Name field is blank? If I put a default value in the Employee Name field like my name then the Lookup populates the table with all rows with my Employee Name but I want the table to populate with all rows to start then only filter for Employee Name once a name is typed into the Employee Name field.

0 0

Replies

replied on October 9, 2019 • Show version history

I believe Forms would send a 'NULL' value if that field is blank. Try adjusting your 'WHERE' statement to include results where the @EmployeeName field has a NULL value (see below):

WHERE (@EmployeeName IS NULL OR employee_name = @EmployeeName)

0 0

replied on October 9, 2019

I had actually tried this as the SPROC:

@EmployeeName VARCHAR(100)


SELECT *,
	(SELECT 
		CASE 
		WHEN mda.application_status = 'Active' THEN 1
		WHEN mda.application_status LIKE 'Pending%' THEN 2
		WHEN mda.application_status = 'Inactive' THEN 3
		WHEN mda.application_status = 'Declined' THEN 4
		WHEN mda.application_status = 'Canceled' THEN 5
		ELSE 3
	END) AS statusOrder
FROM uhf_mobile_device_application mda
WHERE (@EmployeeName IS NULL OR employee_name = @EmployeeName)
ORDER BY employee_name, statusOrder, service_start

and i had the same results.

0 0

replied on October 9, 2019

I'm not sure the lookup will fire at all on from load with a blank value in that field. If you add a value to get results, then delete it, you can see if it returns the "default" set.

If the problem is that it just isn't firing on load when blank, then you could try adding JavaScript that runs on document ready and "manually" triggers the change event if the field is empty.

0 0

You are not allowed to follow up in this post.

Question

Question

Forms 10.3 Lookup with blank field as parameter

Replies

Sign in to reply to this post.