cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

Distinct Gallery with Consolidated Record

Hello Everyone,

 

I would like to ask for help with the problem I'm encountering. I am building an app for the summary of my team's workload. I created a collection from the sharepoint and  linked it to the gallery.

 

SummaryTab.png

 

 

Here I have User 1 with 4 Ongoing tasks. I want for this gallery to display only one record for User 1, then consolidate all his tasks into that one record like this:

User 1
On Going: 4

 

I would also hope that the date filter could be applied to what I want.

 

Looking forward to your help.

 

THanks,

Red

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Distinct Gallery with Consolidated Record

Hi @anabananei ,

Yes, you need to use simple data type in groupby function.

Since you have choice type and person type, you need to use AddColumns function to reshape your table.

Try this:

AddColumns(
AddColumns(
GroupBy(
AddColumns(
     Filter(tablename,(startdate>=datepicker1.SelectedDate,startdate<=datepicker2.SelectedDate)||
                      (enddate>=datepicker1.SelectedDate,enddate<=datepicker2.SelectedDate)
            ),
     "userimage",
     personfield.Picture,
     "username",
     personfield.DisplayName,
     "taskstatus",
     status.Value
            )
"username",
"userimage",
"taskstatus",
"tasks"),
"num",
CountRows(tasks)
),
"tasknum",
Concatenate(taskstatus,":",num)
)

Then the username, userimage,tasknum are the columns that you want.

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Highlighted
Super User
Super User

Re: Distinct Gallery with Consolidated Record

Hi @anabananei 

It sounds like you need to use the GroupBy() function.  Please refer to the documentation and examples here: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-groupby .  In your case, you would group your data on the User. 

Highlighted
Community Support
Community Support

Re: Distinct Gallery with Consolidated Record

Hi @anabananei ,

Do you want to merge multiple records to one based on username, status and filter based on date?

If so, you need to use GroupBy function.

Could you tell me

1)how does date stored in your data source? Do you have a start date column a end date column or only has one date column? their data type?

2)the user name field data type?  status field data type? I assume that they are both text type.

 

If you only has one date type, try to set the gallery's Items like this:

AddColumns(
     GroupBy(
        Filter(tablename,date>=DatePicker1.SelectedDate,date<=DatePicker2.SelectedDate),
//filter based on two datepickers
        "usernamefield",
        "statusfield",
        "tasks"//group based on usernamefield and status field
       ),
     "num"
     CountRows(tasks)  //add column with the value of task number
          )

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Advocate II
Advocate II

Re: Distinct Gallery with Consolidated Record

Hello @v-yutliu-msft ,

 

Thanks for the response. To answer your question:

1. I have a start date and end date field as Date Column

2. The user filed is a Person/Group type while the Status is a Choice Column.

 

I noticed that GrouBy only works on single text column. However, I want to retain the images of each user plus their current workload. Is there any way to work around on this?

 

Thanks!

Highlighted
Community Support
Community Support

Re: Distinct Gallery with Consolidated Record

Hi @anabananei ,

Yes, you need to use simple data type in groupby function.

Since you have choice type and person type, you need to use AddColumns function to reshape your table.

Try this:

AddColumns(
AddColumns(
GroupBy(
AddColumns(
     Filter(tablename,(startdate>=datepicker1.SelectedDate,startdate<=datepicker2.SelectedDate)||
                      (enddate>=datepicker1.SelectedDate,enddate<=datepicker2.SelectedDate)
            ),
     "userimage",
     personfield.Picture,
     "username",
     personfield.DisplayName,
     "taskstatus",
     status.Value
            )
"username",
"userimage",
"taskstatus",
"tasks"),
"num",
CountRows(tasks)
),
"tasknum",
Concatenate(taskstatus,":",num)
)

Then the username, userimage,tasknum are the columns that you want.

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Advocate II
Advocate II

Re: Distinct Gallery with Consolidated Record

Hi @v-yutliu-msft ,

 

I know this is kinda late. I just got to test it now due to busy schedule. I think I have some problem about this. Its not giving me the correct personfield, but instead the datacard of that person field. Any chance I can work around on this?

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (6,245)