cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DavidV
Level: Powered On

GroupBy date and user

I am trying to calculate number of reservations in a day for a given user. I was thinking of using groupby function but not able to make it work. 

 

I am using SharePoint as the data source with these columns: UserName (People), start_date (Date and Time). The backend data looks like :

 

List Name: 'Reservations'

 

 

UserName, Start_date
Jon, 12/2/2017, 3:00 PM Chris, 12/3/2017, 9 AM Matt, 12/2/2017, 4:00 PM Jon, 12/2/2017, 5:00 PM Jon, 12/09/2017, 2:00 PM Chris, 12/15/2017, 5:00 PM Matt, 12/17/2017, 1:00 PM

 

 

Users can currently come to powerapps and enter the time and date they are interested in but I would like to show a message if user has already booked 2 appointments in a day. so when Jon logged in to PowerApps and tries to reserve another reservation for 12/2/2017, he will get an error message.

 

Here is what I have so far:

 

This will only give me reservations for logged in user 

Filter('Reservations', User().Email = UserName.Email)

I would like to use a collection to add groupby data but it is not working properly:

ClearCollect(DateByUser, GroupBy(Filter('Reserations', User().Email = UserName.Email), Text(Start_Date,ShortDate),"Count"))

and then finally a ForAll function to check the condition and show and hide the error label control

ForAll(DateByUser, if(Count=2, "Error","NoError"))

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: GroupBy date and user

Hi @DavidV,

 

Agree with @BitLord69 , the issue here should be related with the GroupBy function.

 

ClearCollect(DateByUser, GroupBy(Filter('Reserations', User().Email = UserName.Email), Text(Start_Date,ShortDate),"Count"))

I think the formula you used here will not work, 

 

If you would like to group the record with the ShortDate, then you would need to add a ShortDate column first:

The formula should be write in this way:

ClearCollect(DateByUser,

Groupby(

       AddColumns(Filter('Reserations', User().Email=UserName.Email),

                            "ShortDate",

                            Text(Stary_Date,ShortDate)

                             ),

         "ShortDate",

         "Count"

         )

)

This formula will group the records by the ShortDate column, other columns, like the User Name, and the StartDate are all within the Count column.

 

Further, if you would like to count the record number, then the formula should be modified as below:

ClearCollect(DateByUser,
Addcolumns( Groupby( AddColumns(Filter('Reserations', User().Email=UserName.Email), "ShortDate", Text(Stary_Date,ShortDate) ), "ShortDate", "Count" ),
"CountN",
CountRows(Count) )
)

See:

66.PNG

 

Reference:

GroupBy and Ungroup functions in PowerApps

 

Regards,

Michael

 

Community Support Team _ Michael Shao
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

2 REPLIES 2
Super User
Super User

Re: GroupBy date and user

What if you separate the date and time in two separate columns? Maybe it's the Text-conversion you're doing that throws it off.

 

Good luck!

Community Support Team
Community Support Team

Re: GroupBy date and user

Hi @DavidV,

 

Agree with @BitLord69 , the issue here should be related with the GroupBy function.

 

ClearCollect(DateByUser, GroupBy(Filter('Reserations', User().Email = UserName.Email), Text(Start_Date,ShortDate),"Count"))

I think the formula you used here will not work, 

 

If you would like to group the record with the ShortDate, then you would need to add a ShortDate column first:

The formula should be write in this way:

ClearCollect(DateByUser,

Groupby(

       AddColumns(Filter('Reserations', User().Email=UserName.Email),

                            "ShortDate",

                            Text(Stary_Date,ShortDate)

                             ),

         "ShortDate",

         "Count"

         )

)

This formula will group the records by the ShortDate column, other columns, like the User Name, and the StartDate are all within the Count column.

 

Further, if you would like to count the record number, then the formula should be modified as below:

ClearCollect(DateByUser,
Addcolumns( Groupby( AddColumns(Filter('Reserations', User().Email=UserName.Email), "ShortDate", Text(Stary_Date,ShortDate) ), "ShortDate", "Count" ),
"CountN",
CountRows(Count) )
)

See:

66.PNG

 

Reference:

GroupBy and Ungroup functions in PowerApps

 

Regards,

Michael

 

Community Support Team _ Michael Shao
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

Helpful resources

Announcements
thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Kudoed Authors (Last 30 Days)
Users online (4,180)