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!
Solved! Go to 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."
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
@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
@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."
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."
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
User | Count |
---|---|
183 | |
109 | |
88 | |
44 | |
42 |
User | Count |
---|---|
226 | |
108 | |
103 | |
68 | |
68 |