Can Case sensitive look up can be done in PowerApps using SharePoint list as a data source?

[HarishaAkkil]

Hi Team,

I have made an app in PowerApps which uses the SharePoint list as a data source. From which it look-up for the "Password" column value to sign in to the app. The below screenshot shows the sign-in page of my application.

In the OnSelect function, I wrote the following formula to sign in to the app.

If(CountRows(Filter('SPLIST',And(INPUTEMPID.Text=SPCOLUMN,INPUTPWD.Text=SPCOLUMN)))=1,Navigate('Menu Page',ScreenTransition.UnCover),Notify("Incorrect Login Details",NotificationType.Error));

where SPCOLUMN of Employee Id is a number so no need to worry in comparison. Whereas the Password column is a "Single line of text" comprises string. It when compared, compares regardless of case sensitivity.

For example: If I have the record in Password as "ABCD" it allows me to log in to the app even if I type "abcd".

So could you please give me a suggestion on this to work the app with case-sensitively?

And for your note, if I use the function LookUp instead of Filter also results in the same output.

Thanks in advance for your help!

Thank You,

Harisha

[HarishaAkkil]

I will explain clearly what it specifies,

If(CountRows(Filter('SPLIST',And(INPUTEMPID.Text=SPCOLUMN,INPUTPWD.Text=SPCOLUMN)))=1,Navigate('Menu Page',ScreenTransition.UnCover),Notify("Incorrect Login Details",NotificationType.Error));

SPLIST is a SharePoint list where the details of the user are stored.

INPUTEMPID is a power apps control where the user enters his/her employee id.

INPUTEMPID.Text=SPCOLUMN  >>> here the SPCOLUMN specifies the column (Column name =Employee Id) in the SharePoint list.

INPUTPWD is a power apps control where the user enters the password.

INPUTPWD.Text=SPCOLUMN >>> here the SPCOLUMN specifies another column (Column name = Password) in SharePoint list.

When using LookUp function again it compares case-insensitively.

And here is what my problem is,

when I am using this formula for logging in, the employee id doesn't have any problem since it is a number field declared in SharePoint as well as in Powerapps.

But when comes to the Password field, it is defined as "Single line of text" in SP means it allows alpha-numeric. So when a user enters the password regardless of the case it allows the user to log in.

You can ask me if you have any doubts are queries regarding this problem.

Thanks for your patience!

Harisha

[PowerAddict]

These details are in line with what I had assumed for my previous post. Did you give that a try?

[v-xida-msft]

Hi @HarishaAkkil ,

Based on the needs that you mentioned, I think the Lower function could achieve your needs.

Please consider modify your formula within the OnSelect property of the "Sign in" button to following:

If(
   Lower(LookUp('SPLIST', EmployeeId = Value(INPUTEMPID.Text), Password)) = Lower(INPUTPWD.Text),
   Navigate('Menu Page',ScreenTransition.UnCover),
   Notify("Incorrect Login Details", NotificationType.Error)
)

Note: The EmployeId and Password represent columns in your SP List, you should replace them with actual column name from your SP list within above formula. And the EmployeeId column is a Number type column in your SP list

Best regards,

[HarishaAkkil]

Hi @v-xida-msft ,

I tried the formula as you suggested. But it did not work properly. If the user enters the right password also, it fails to log in to the app.

Thanks!

Harisha

[v-xida-msft]

Hi @HarishaAkkil ,

Please add a Label in your screen, set the Text property to following:

LookUp('SPLIST', EmployeeId = Value(INPUTEMPID.Text), Password)

then check if the corresponding Password value would be displayed based on the typed EmployeeId value. Is there something error message with this function?

Also please make sure you do not type additional spaces within the EmployeeId box and Password Text Box. Please modify your formula as below:

If(
   Lower(
          Trim(LookUp('SPLIST', EmployeeId = Value(INPUTEMPID.Text), Password))
   ) = Lower(Trim(INPUTPWD.Text)),
   Navigate('Menu Page',ScreenTransition.UnCover),
   Notify("Incorrect Login Details", NotificationType.Error)
)

or

If(
   Lower(
          Trim(LookUp('SPLIST', EmployeeId = Value(INPUTEMPID.Text)).Password)
   ) = Lower(Trim(INPUTPWD.Text)),
   Navigate('Menu Page',ScreenTransition.UnCover),
   Notify("Incorrect Login Details", NotificationType.Error)
)

Please make sure you have replaced the EmployeeId and Password with actual column name from your SP list.

Best regards,

[HarishaAkkil]

Hi @v-xida-msft ,

None of the formula works. The same problem persists even if I give the correct login details also it is not allowing the user to successfully log in.

And as for the text box property, it is not displaying the password of the corresponding employee id.

Thanks!

Harisha

[v-xida-msft]

Hi @HarishaAkkil ,

Could you please share a bit more about the data structure of your SP List?

Is the 'EmployeeId' a Number type column or a Text type column in your SP List?

Could you please share a screenshot about the formula you used within your app?

Based on the needs that you mentioned, I think the solution I provided above could achieve your needs. Firstly, please make sure the EmployeeId value you typed within the EmployeeId Text Box is already existed in your SP List. If not, the LookUp formula I provided above would return Blank value.

Please modify your formula as below:

If(
   Not(Trim(INPUTEMPID.Text) in 'SPLIST'.EmployeeId),  // Check if the entered EmployeeId is existed in data source already
   Notify("The EmployeeId you entered is not existd in your data source, please consider create a new one", NotificationType.Error),
   Lower(
          LookUp('SPLIST', EmployeeId = Trim(INPUTEMPID.Text)).Password
   ) = Lower(Trim(INPUTPWD.Text)),
   Navigate('Menu Page', ScreenTransition.UnCover),
   Notify("Incorrect Login Details", NotificationType.Error)
)

Note: I assume that the EmployeeId is a Text type column in your SP List.

or

If(
   Not(Trim(INPUTEMPID.Text) in 'SPLIST'.EmployeeId),  // Check if the entered EmployeeId is existed in data source already
   Notify("The EmployeeId you entered is not existd in your data source, please consider create a new one", NotificationType.Error),
   Lower(
          First(Filter('SPLIST', EmployeeId = Trim(INPUTEMPID.Text))).Password
   ) = Lower(Trim(INPUTPWD.Text)),
   Navigate('Menu Page', ScreenTransition.UnCover),
   Notify("Incorrect Login Details", NotificationType.Error)
)

Please make sure the EmployeeId you typed within your canvas app is already existed in your SP List data source already. And you do not type additional space within the EmployeeId Text Box. If there is some error message with the formula, please share screenshot about it, so we can help you better in your scenario.

In addition, you could consider type proper Password value (from your SP List) within the Password Text box, then try your "Sign in" button, check if the issue still exists. If the issue still exists, I think there is something wrong with the formula to retrieve data from your SP list.

Best regards,

[HarishaAkkil]

Hi @v-xida-msft ,

My SharePoint list consist of columns: EmployeeID (which is a number field allows only numbers) and another one Password (which is a single line of text, allows alpha-numeric text).

This is the sign in page created in Power Apps,

Firstly I used the below formula in the Sign in button-

If(CountRows(Filter('SPLIST',And(inputEmpId.Text=EmployeeID,inputPwd.Text=Password)))=1,Navigate('Menu Page',ScreenTransition.UnCover),Notify("Incorrect Login Details",NotificationType.Error)

It is working fine. It is allowing the user to login if the user entered his correct employee id and password.

Later on, I found that, it has some errors,

For example: The EmployeeID  is 001 and the corresponding Password is TeSt is stored in the list.

In the Power Apps, if the user enters either of the combination of choice below it is allowing the user to login.

1. EmployeeID = 001 and Passoword = test

2. EmployeeID = 001 and Passoword = TEST

3. EmployeeID = 001 and Passoword = tEsT

So my question is, Is there a way such that the Power Apps should allow the user to log in only if the user enters the credentials as EmployeeID = 001 and Passoword = TeSt ?

And as mentioned in your reply I tried the formulas and again it is not working as expected.

Thanks!

Harisha

[v-xida-msft]

HI @HarishaAkkil ,

Is the EmployeeId a unique column value within your SP list?

Could you please share a screenshot about the EmployeeId field in your SP List?

I assume that the EmployeeID column is a unique column within your SP List, and only single one Password matches this EmployeeID value.

Please modify your formula as below:

If(
    Lower(First(Filter('SPLIST', EmployeeID = inputEmpId.Text)).Password) = Lower(inputPwd.Text),
    Navigate('Menu Page',ScreenTransition.UnCover),
    Notify("Incorrect Login Details",NotificationType.Error)
)

Based on the needs that you mentioned, I think above formula could achieve your needs. You could consider add a Label in your app, set the Text property to following:

First(Filter('SPLIST', EmployeeID = inputEmpId.Text)).Password

then check if the Label could display proper Password value from your SP list.

In addition, I think there is something wrong with your EmployeeID column. As you mentioned, if the EmployeeID is number type field in your SP list, when you store 001 into this field within your SP List, it would store it as 1 rather than 001. Please consider change the EmployeeID field in your SP list into a Single line of Text type, then refresh the data source in your app, try above formula, check if the issue is solved.

If the EmployeeID field is a Number type column, when comparing with inputEmpId Text value, it would show up a type incompatible issue within your Filter function. As an fixed solution, you should modify above formula as below:

If(
    Lower(First(Filter('SPLIST', EmployeeID = Value(inputEmpId.Text))).Password) = Lower(inputPwd.Text),
    Navigate('Menu Page',ScreenTransition.UnCover),
    Notify("Incorrect Login Details",NotificationType.Error)
)

Best regards, 

[PowerAddict]

Hi @HarishaAkkil

Did you try what was posted in message #10 in this post? It works perfectly fine for me.

---
If you like this reply, please give kudos. And if this solves your problem, please accept this reply as the solution.

Thanks!
Hardit Bhatia
The Power Addict
https://thepoweraddict.com