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

Hide Gallery columns based on User().FullName

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. 

Timesheet.PNG

 

 

 

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:

John Timesheet.PNG

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

@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."

View solution in original post

Community Support
Community Support

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:

v-bofeng-msft_0-1604644931969.png

 

v-bofeng-msft_1-1604644931971.png

Best Regards,

Bof

 

View solution in original post

6 REPLIES 6
Post Prodigy
Post Prodigy

@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 

Super User III
Super User III

@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."

View solution in original post

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

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)

 

@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."

Community Support
Community Support

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:

v-bofeng-msft_0-1604644931969.png

 

v-bofeng-msft_1-1604644931971.png

Best Regards,

Bof

 

View solution in original post

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

Top Solution Authors
Top Kudoed Authors
Users online (24,389)