cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
HarishaAkkil
Helper II
Helper II

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

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.

 

Sign in page.jpg

 

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

1 ACCEPTED SOLUTION

Accepted Solutions

What is SPCOLUMN and why is it listed twice, both for username/ID and password? Are you trying to generalize the column names here?

Anyways, how about you fetch the password and store it in a variable with something like this:
Set(userPassword, LookUp(SPLIST, INPUTEMPID = SPCOLUMN).PASSWORDCOLUMN

Then do,
If(userPassword = INPUTPWD.Text, Navigate(Menu), Notify("Error Message"))

Let me know if this works.

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

Thanks!
Hardit Bhatia
https://thepoweraddict.com

View solution in original post

20 REPLIES 20
PowerAddict
Super User II
Super User II

One way you can do that since string comparison in LookUp and Filter is case insensitive, you can compare one value to lower case and the other value to upper value. If they are the same case, the two will match. If not, they wont.

Does that help? If so, I can help you write the formula for it.

Let me know.

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

Thanks!
Hardit Bhatia
https://thepoweraddict.com

Try something like: 

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

 

Let me know if this helps. 

 

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

 

Thanks!
Hardit Bhatia
https://thepoweraddict.com

 

Hi,

 

Yes and thanks for the idea. Does this also works when used symbols in the password column (which means the column is alpha-numeric)?

 

Thank You,

Harisha

Hi,

 

I have applied the formula as suggested, but even if I give the correct password, it is not allowing me to sign in. It says Incorrect login details.

 

Thanks,

Harisha

Yes it will. Here are a couple of examples: 

Lower("4$#XDFD") = 4$#xdfd
Upper("4$#xdfd") = 4$#XDFD

 

Let me know if this helps. 

 

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

 

Thanks!
Hardit Bhatia
https://thepoweraddict.com

 

But comparing these,

Lower("4$#XDFD") = 4$#xdfd
Upper("4$#xdfd") = 4$#XDFD

 results not equal right, so how does this allow to log in? It fails to log in right?

But I want to log in the user what the user gives as input in SharePoint list.

For example, if the password column contains "ABcd" when the user enters only "ABcd" it should allow to log in. But here it is allowing to log in, if the user either enters "ABCD" or "abCD" or "abcd" . But this does not make sense right.

 

Thanks for your help!

Harisha

My bad, string comparison is case sensitive by default. For example, 

If("4$#xdfd" = "4$#XDFD, true, false)

returns false. 

 

So if you can fetch the password from the database and compare it with what the user has entered, you should be good. Does that not work for you?

 

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

 

Thanks!
Hardit Bhatia
https://thepoweraddict.com

 No, it is not working as expected. As I explained previously it is comparing on case-insensitively.

 

When I am using the formula below,

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

 

It compares case-insensitively.

 

As suggested by you, 

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

when i use the above one also it does not work properly.

 

Thanks!

Harisha

What is SPCOLUMN and why is it listed twice, both for username/ID and password? Are you trying to generalize the column names here?

Anyways, how about you fetch the password and store it in a variable with something like this:
Set(userPassword, LookUp(SPLIST, INPUTEMPID = SPCOLUMN).PASSWORDCOLUMN

Then do,
If(userPassword = INPUTPWD.Text, Navigate(Menu), Notify("Error Message"))

Let me know if this works.

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

Thanks!
Hardit Bhatia
https://thepoweraddict.com

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (20,759)