Filter Records by logged in user not working

[RMeena_9]

Hello,

In my Datasource excel I have a column with mail id's, In app, I am trying to write a code where logged in user should only see his/her allocated records. But the below code is not working.

Any help is appreciated.

I have set one variable for User().Email when MyRecords button is selected:

OnSelect = Set(varLoggedInUser,User().Email)

and

Inside Gallery window I am adding below filter - (Mail Id is my column name in datasource)

Items = Filter(SampleRecord,'Mail Id'.Email=varLoggedInUser)

Do I need to do any changes ?? or missed any part ?

[kameshm · Accepted solution]

Hi @RMeena_9 ,
  Is the column name for mail correct?
  What is your column type in excel?
 1.Can you please check lowercase in both sides of condition?
 2.Please check with hardcoded mail
 3. Finally try to reset gallery when clicking button
Thanks

[RandyHayes]

@RMeena_9 

If this is Excel, then you will not have a complex record column 'Mail Id' with an Email column in it.

So, change your formula to:

Filter(SampleRecord, 'Mail Id' = varLoggedInUser)

If letter case is an issue, then use the following:

Filter(SampleRecord, varLoggedInUser in 'Mail Id')

I am assuming that SampleRecord is the name of your Excel table as a datasource in your app.

I hope this is helpful for you.

[RMeena_9]

Hi,

My column name is 'Mail Id' and it contains mail id's of different users in hyperlink format.

I tried to remove the hyperlinks and kept their Id's only as text and checked.

In both ways with or without hyperlinks it is not working

the variable which I set (varLoggedInUser) is showing correct mail id in both excel and Global variables list, I tried to reset and also restart by applying lowercase, hardcoded email nothing worked.

[RMeena_9]

Hi @RandyHayes 

Yes, SampleRecord is my excel datasource.

I tried above two statements it is not working. 

Is there anything else I need to check or make sure ?? Any Action should I have to fill in - like Default, DefaultMode, visible ....etc ??

[kameshm]

@RMeena_9  Could you please share the screenshot of your formula and excel screenshot?
Are you using excel online business connector or static excel?

[RandyHayes]

@RMeena_9 

No, you do not need to worry about those properties.  Stay focused on just this formula for the Items of your gallery.

Can you send a screenshot of the formula?  Click IN the formula so that red underlines are showing, then hover over the red underlines (specifically any parts that have a thicker red underline) and send a screenshot of that.

[kameshm]

Hi @RMeena_9 ,
Could please try like below.

Filter(SampleRecord,Lower('Mail Id')=Lower(varLoggedInUser))

Thanks 

[RMeena_9]

Its Working Now !!😀

I had to remove spaces between column names Instead of Mail Id, I renamed as Mail_Id in excel. Below simple code worked for me since I have text inside my column with lowercase I didn't use Lower() function.

Filter(SampleRecord, Mail_Id = varLoggedInUser)

For sharepoint and excel there shouldn't be any spaces in table column names. If you want to keep spaces or '.' make sure you add either _x002e_ or _x0020_ in between them.

Mail Id -> Mail_x002e_Id 

S.No. -> S_x002e_No_x002e_

(Refer Microsoft docs filter function).

Thank you guys for helping out. 

[kameshm]

Hi @RMeena_9 ,
Good to hear 😀!,
I thought also the problem with column name. yesterday I tested with same column as yours, and I faced the same issue of yours. Finally, I add Lower on both sides of condition, that solution solved that issue. so I pasted same code in my last thread. Anyway Thanks.