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

Discussion

Discussion

Lookup with Stored Procedure Auto Populating data before Auto Fill button used

posted on November 16, 2017 • Show version history

I have a stored procedure that started with:

ALTER procedure [dbo].[sp_GetInfo]
( 
 @last4ssn nvarchar(4),
 @DOB date
)
as

SELECT        U.ID, U.FirstName AS FirstName, U.LastName, RIGHT(U.SSN,4) as Last4SSN, T.Code AS Code, T3.Area_Name AS Area, E.UserEmail as Email
FROM            U INNER JOIN
                        Code AS T ON U.Code = T.ID INNER JOIN
			Area AS T3 ON U.Area = T3.AreaID INNER JOIN
			UEmail As E ON U.ID = E.Users_ID and E.UserEmail like '%officedomain.com%' 
WHERE        U.DOB=@DOB and RIGHT(U.SSN,4)=@last4ssn

I have to modify the names as to not overlap our real data.  Running the Stored Procedure in MSSQL results in the following row if passed the two variables needed:

@last4ssn = 9999,  @DOB = 11-11-2017

ID    |  FirstName  |  LastName  |  Last4SSN  |  Code  |  Area           |  Email

222  |  Chase        |  Ayers          |  9999         |  555     |  California  |  chase@officedomain.com

It needs to pass the (Last 4 Social Security Number) and (Date of Birth) before running the select statement.

The Form Lookup Looks as follows:

The behavior should be that the submitter first name will not be populated until I enter in the first two pieces of information then hit the Auto Fill button.  But instead when I open the form:

I haven't touched anything yet and it has already Auto filled a name which is most likely the first returned first name from my U table.

Any ideas as to why it's bypassing the Auto fill?

0 0
replied on December 8, 2017

Hi Chase,

On form load the lookup rule will be triggered with @last4ssn=empty and @DOB=empty, which I guess would return that user in your stored procedure.

Can you try updating the stored procedure and not returning anything if input is empty?

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

Sign in to reply to this post.