Hide Gallery columns based on User().FullName

[PoojaG]

Hello,

I have a SharePoint list as a data source and the data looks like below: It is basically a timesheet for employees to track number of hours spend on each project during the month of the year. 

When a user logs in, it should have a filter to choose the month-year and only see the hours under his/her name. For example, if user name is John and he selects July 2020, he should see something like below:

To achieve this, I'm using a Gallery and making it look like a table format by using labels for each column, I could also use a datatable. Under the visible property for each label where ever there is a user name the function I wrote: 

If(Text(ThisItem.John)= User().FullName,true,false)

this hides the column even for John. Is there any way I can achieve the desired result? Thank you!

[notj]

@PoojaG Instead of hiding items that are not related to the current user, try using a filter so you only bring in the right items in the first place. 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup 

[mdevaney · Accepted solution]

@PoojaG 
I believe there is an issue with your SharePoint data structure.  There should not be a new column for each person.  If you go down this path your app will need to be re-coded each time a new person is added.

My suggestion is to make an Employee column (Person type) in SharePoint.  Then restructure your data like this:

Project	Date	Employee	Value
Project 1	Jul-20	John	100
Project 1	Jul-20	Sara	0
Project 1	Jul-20	Matt	15
Project 2	Aug-20	John	10
Project 2	Aug-20	Sara	0
Project 2	Aug-20	Matt	20

Now you can easily FILTER the gallery by using this code in the Items property.

Filter('SharePoint List Name', Employee.Email: User().Email)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[PoojaG]

thank you @mdevaney  Let me try this solution. Will update you if it works and accept your solution.

[PoojaG]

thank you @notj . Can you please elaborate it further. I checked the reference document but it's not clear how to use it. I tried the function but received incompatibility type error. 

Filter(test, ThisRecord.John = User().FullName)

[mdevaney]

@PoojaG 
This function is not valid because it looks at its contents, not the column name itself.  FILTER brings in specific rows.  You want specific columns.  Therefore, I suggest you refactor your data structure in SharePoint then use my suggested code.

Filter(test, ThisRecord.John = User().FullName)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[v-bofeng-msft · Accepted solution]

Hi @PoojaG :

Firstly, let me explain why you encountered this problem:

The meaning of ‘Text(ThisItem.John)’ is the value of ‘John’ field in the record. So your formula will no t work as you expected.

Secondly, the key to solve the problem is to use the If() function to display the employee column you want. I've made a test for your reference:

1\ Add a dropdown control ‘Dropdown2’ to screen, and set its Items property to:

Distinct(LSIT3,Date).Result /*LSIT3 is my datasource*/

2\ Add a blank vertical gallery control to screen ,and set its Items property to:

Filter(LSIT3,Date=Dropdown2.Selected.Result)

3\ Insert three label controls into the gallery control:

Label1-Text

ThisItem.Title

Label2-Text

ThisItem.Date

Label3-Text

If(User().FullName="John",ThisItem.John,User().FullName="Sara",ThisItem.Sara,User().FullName="Matt",ThisItem.Matt)

4\ Add a label control to screen ,and set its Text property to:

"Grand total"

5\ Add a label control to screen ,and set its Text property to:

Sum(Filter(LSIT3,Date=Dropdown2.Selected.Result),If(User().FullName="John",John,User().FullName="Sara",Sara,User().FullName="Matt",Matt))

6\ The result is as follows:

Best Regards,

Bof