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

How to combine the data and show the data with month switch

Hi guys,

I've some technical problem cannot be solved.

Pic 1.
It's the final situation what I wanted. But the content is wrong so you can ignore it. The item should depending on the selected month and year of CreatedDate. For example if I want to see the Jan 2020 timesheet, I can select the month by month switch. Also, it will group of the same project name and sum the working hours with the same project.

Pic2 is the code of the prev month arrow.

Pic3 is the database

Pic4 I try to use diffdate() to compare the month and years of selectedMonth and CreateDate in items of gallery

 

Sorry for my poor expression

 

1.png

2.png3.png

未命名6.png

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: How to combine the data and show the data with month switch

Ok @kit1128 

I modify the logic, please try this

1. OnVisible property of Screen:

UpdateContext({SelectedDate:Today()});

UpdateContext({SelectedMonth: Month(SelectedDate), SelectedYear: Year(SelectedDate)});

ClearCollect(myProject, DropColumns(AddColumns(GroupBy(Filter(Filter(ETimeSheetMain, Month(CreatedDate)= SelectedMonth && Year(CreatedDate)= SelectedYear) , Title=UerMail),"Project", "GroupedProjects"), "Sum of Value", Sum(GroupedProjects, WorkingHours)), "GroupProjects"))

2. OnSelect property of the prev month arrow:

UpdateContext({SelectedDate:DateAdd(SelectedDate,-1,Months)});

UpdateContext({SelectedMonth: Month(SelectedDate), SelectedYear: Year(SelectedDate)});

ClearCollect(myProject, DropColumns(AddColumns(GroupBy(Filter(Filter(ETimeSheetMain, Month(CreatedDate)= SelectedMonth && Year(CreatedDate)= SelectedYear) , Title=UerMail),"Project", "GroupedProjects"), "Sum of Value", Sum(GroupedProjects, WorkingHours)), "GroupProjects"))

Sik

View solution in original post

Highlighted
Community Support
Community Support

Re: How to combine the data and show the data with month switch

@kit1128 

 

Month function is not delegable for SharePoint, but SharePoint is a delegable data source.

The workaround is for avoiding delegation error.

1. create a new Gallery, set Items property to ETimeSheetMain, then the gallery will display all items from sharepoint

2. apply Gallery.AllItems statement into the code, instead of ETimeSheetMain, the delegation error will be fixed.

3. set the Gallery Visible property to false because there is no need to display this gallery.

 

Sik

View solution in original post

8 REPLIES 8
Highlighted
Community Support
Community Support

Re: How to combine the data and show the data with month switch

@kit1128 

 

My thought is to add a filter to ETimeSheetMain based on the selectedMonth at first and then follow your steps to calculate the sum of value based on selected month data.

1. Modify the OnVisible property of Screen, First to show the current month.

UpdateContext({SelectedMonth: Month(Today())});

ClearCollect(myProject, DropColumns(AddColumns(GroupBy(Filter( Filter(ETimeSheetMain, Month(CreatedDate)= SelectedMonth), Title=UerMail),"Project", "GroupedProjects"), "Sum of Value", Sum(GroupedProjects, WorkingHours)), "GroupProjects"))

2. Modify the OnSelect property of the prev month arrow.

UpdateContext({SelectedMonth: SelectedMonth-1});

ClearCollect(myProject, DropColumns(AddColumns(GroupBy(Filter( Filter(ETimeSheetMain, Month(CreatedDate)= SelectedMonth), Title=UerMail),"Project", "GroupedProjects"), "Sum of Value", Sum(GroupedProjects, WorkingHours)), "GroupProjects"))

ClearCollect(myProject, DropColumns(AddColumns(GroupBy(Filter( Filter(ETimeSheetMain, Month(CreatedDate)= SelectedMonth), Title=UerMail),"Project", "GroupedProjects"), "Sum of Value", Sum(GroupedProjects, WorkingHours)), "GroupProjects"))

 

Then the myProject collection will only save records(the Project and Sum value) based on the selected month.

Note: Month(CreatedDate) is not a delegable function in sharepoint, If your SP list Items is more than 2000, please try this workaround.

1. Add an invisible Gallery to show all items from display, Set Items of gallery: ETimeSheetMain

2. Apply Gallery.AllItems to the above codes instead of ETimeSheetMain

3. The Month function should be delegable now.

Sik

 

Highlighted
Helper III
Helper III

Re: How to combine the data and show the data with month switch

Hi @v-siky-msft ,

Thank you for reply, but it may not work. Because it cannot compare the years. For example if I created at Feb 2020, the record will show it on Feb 2021, Feb 2022......etc. How can fix it?

Highlighted
Community Support
Community Support

Re: How to combine the data and show the data with month switch

Ok @kit1128 

I modify the logic, please try this

1. OnVisible property of Screen:

UpdateContext({SelectedDate:Today()});

UpdateContext({SelectedMonth: Month(SelectedDate), SelectedYear: Year(SelectedDate)});

ClearCollect(myProject, DropColumns(AddColumns(GroupBy(Filter(Filter(ETimeSheetMain, Month(CreatedDate)= SelectedMonth && Year(CreatedDate)= SelectedYear) , Title=UerMail),"Project", "GroupedProjects"), "Sum of Value", Sum(GroupedProjects, WorkingHours)), "GroupProjects"))

2. OnSelect property of the prev month arrow:

UpdateContext({SelectedDate:DateAdd(SelectedDate,-1,Months)});

UpdateContext({SelectedMonth: Month(SelectedDate), SelectedYear: Year(SelectedDate)});

ClearCollect(myProject, DropColumns(AddColumns(GroupBy(Filter(Filter(ETimeSheetMain, Month(CreatedDate)= SelectedMonth && Year(CreatedDate)= SelectedYear) , Title=UerMail),"Project", "GroupedProjects"), "Sum of Value", Sum(GroupedProjects, WorkingHours)), "GroupProjects"))

Sik

View solution in original post

Highlighted
Helper III
Helper III

Re: How to combine the data and show the data with month switch

Hi @v-siky-msft ,

I am not clear about meaning of 2. Apply Gallery.AllItems to the above codes instead of ETimeSheetMain

 

 

未命名.png

Highlighted
Community Support
Community Support

Re: How to combine the data and show the data with month switch

@kit1128 

 

Month function is not delegable for SharePoint, but SharePoint is a delegable data source.

The workaround is for avoiding delegation error.

1. create a new Gallery, set Items property to ETimeSheetMain, then the gallery will display all items from sharepoint

2. apply Gallery.AllItems statement into the code, instead of ETimeSheetMain, the delegation error will be fixed.

3. set the Gallery Visible property to false because there is no need to display this gallery.

 

Sik

View solution in original post

Highlighted
Helper III
Helper III

Re: How to combine the data and show the data with month switch

@v-siky-msft 

 

I've solved. Thank you so so so so much dude😆. You've been a huge help.

Highlighted
Community Support
Community Support

Re: How to combine the data and show the data with month switch

@kit1128 

 

Could you please mark my post as the answer?

Thanks!

Sik

Highlighted
Helper III
Helper III

Re: How to combine the data and show the data with month switch

@v-siky-msft 

 

Done it!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,804)