Hi Eric,

Could you provide an example of a stored procedure that will prevent this behavior? 

Thanks,

Jen

Hi Eric,

I want to second Jen's request. Please provide us with an example of a stored procedure that will prevent this behavior.

Thanks & Regards,

Stored procedures do not use the auto-fill behavior, so if you create a lookup with a stored procedure, you won't encounter this problem. Here's an example:

In this example, replace Database with the database that you'll use for the lookup. After the create statement, you declare the variables that will be your input parameters. In this example, I want to create a lookup that populates a few fields based on a case number. You can use multiple input parameters by separating them with commas.

USE [Database]
GO  
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_simpleLookup] 
       @casenumber nvarchar(50)

AS
BEGIN
         Declare @myTable table
    (
                caseNumber nvarchar(50),
                [day] nvarchar(50),
                gender nvarchar(50),
                race nvarchar(50)
    )
    INSERT INTO @myTable (caseNumber, day, gender, race)
    select CaseNumber, Gender, Day, Race from dbo.CaseFiles where CaseNumber = @casenumber
    select top 1  * from @mytable
END

You'll also need to replace dbo.CaseFiles with the appropriate table name. Once you've created the stored procedure, you can create a stored procedure lookup rule that uses it:

Additional information about using stored procedures in Forms is available on this page of the online help.

Hi Eric,

Thanks for the instructions on the stored procedure. I successfully set it up.

Once my client enters their badgenumber in this case, the rest of the fields (firstname, lastname & employee id) are not auto-populated. I am able to run the stored procedure at the SQL level and get the desired outcome but not is forms. I have tried the Tab & Enter keys but nothing. Am I missing something?

Did you update the data source in Forms after you created the stored procedure?

Yes I updated the data source be able to see stored procedure listed under table/view.

When the data is input and you can click on Tab or enter, the rest of the fields don't auto-populate.

It could be that the result set column names used in your stored procedure lookup rule aren't correct. Here's my lookup rule:

Here are my result set columns: