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.