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

How to create matrix from Calendar, DirectReportsV2 and list of sharepoint calendar events lists

Hello every PowerApps Freq 🙂 (like me)

 

I've got a real tough nut to crack 😉

 

I want to create schedule work in my company using calendar, users365 DirectReports and sharepoint calendar events lists.

 

I have 2 collection:

1) All my subordinates like:

ClearCollec(_Subordinates; Users365.DirectReportV2(User().Mail))

 

2) of days from day to day:

ClearCollect(__Days;AddColumns(
FirstN(
   [1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31];
   DateDiff(DatePicker1.SelectedDate; DatePicker2.SelectedDate; Days) + 1);
"Day";
   "Day " & (Value);
"Date";
   DateAdd(DatePicker1.SelectedDate; Value; Days)));;
)
)

 

 

 

And I have events from sharepoint calendar app (https://www.kalmstrom.com/Tips/SharePoint-Online-Course/Calendar-App.htm)

 

I have to create matrix like:

DatePerson 1 Person 2Person 3Person 4Person 5list person from DirectReportsV2
1.02.20207:00-15:00 (change)5:00-13:00 (change)add15:00-22:00 (change)Absent 
2.02.2020addaddL4 (absent)holidays22:00-6:00 (change) 
etc      

Elements from sharepoint calendar events to use:

- start date

- end date

- Person column (DisplayName, Id, Email)

- Location

- Name or Title

 

Mechanism is as follows:

Zrzut ekranu 2020-01-31 o 00.16.04.png

  1. I choose date from and date to and generate collection "__DAYS" after pressed button
  2. when I pressed button App vreate collection "__Subordinates"
  3. I get items from sharepoint calendar events to collection "__WORKSCHEDULE"
  4. And now i need to create a matrix when I can put to Col3 (Person 2) and Row2 (1.02.2020) items from __WORKSCHEDULE filtered by 'start date"=(Date from Col1) and 'Person.DisplayName'=Person 2

attention: 'start date' has format "dd.mm.yyy h:i:s" but Date in column has format "dd.mm.yyyy"

 

Is anybody, who know, how connect this collection to matrix??

Dr WHO?

 

Regards, 

Arek

2 REPLIES 2
Community Support Team
Community Support Team

Re: How to create matrix from Calendar, DirectReportsV2 and list of sharepoint calendar events lists

Hi @ArkadiuszS ,

Could you please share a bit more about the formula you used in your app?

Do you want to retrieve corresponding SP List calendar events based on the date value and the person value?

 

Based on the needs that you mentioned, I afraid that there is no direct way to achieve your needs. As an alternative solution, I think the Gallery controls could achieve your needs.

 

On your side, you could consider list your date values and person values using Gallery.  Add a Gallery (PersonGallery) in your app, set the Items property to following:

 Office365Users.DirectReportsV2(User().Email).value

then add a Label control into above Gallery, set the Text property to following:

ThisItem.displayName

 

Add second Gallery (DatesGallery), set the Items property to following:

__Days

add a Label in above Gallery, set the Text property to following:

ThisItem.Date

 

According to the error message that you mentioned, it seems to tell that the 'Start date' column value from your SP Calendar list is in different format as the "Date" column value from your _Days collection. Please consider modify your Filter formula as below:

Filter(__WORKSCHEDULE, Text('start date', "yyyymmdd") = Text('Date column from Col1', "yyyymmdd"))

 

As an fixed solution, I think two ForAll functions could achieve your needs. Add a another button, set the OnSelect property to following:

Clear(SPEventsCollection);
ForAll(
       Office365Users.DirectReportsV2(User().Email).value,
       ForAll(
              _Days,
              Collect(
                      SPEventsCollection,
                      {
                        EventDate: _Days[@Date],
                        PersonName: displayName,
                        EventOption: LookUp(__WORKSCHEDULE, Text('Start Date', "yyyymmdd") = Text(_Days[@Date],"yyyymmdd"))
                      }
              )
       )
)

Add third Gallery in your app, set the Items property to following:

SPEventsCollection

Add several Labels in above Gallery, set the Text property to following:

ThisItem.EventOption.Title
ThisItem.EventOption.'Start Date'

....

....

 

In addition, if you want to edit the SP Calendar Events from the matrix directly in your app, I afraid that there is no way to achieve your needs in PowerApps. Please submit an idea to PowerApps Ideas Forum:

https://powerusers.microsoft.com/t5/Power-Apps-Ideas/idb-p/PowerAppsIdeas

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ArkadiuszS
Level: Powered On

Re: How to create matrix from Calendar, DirectReportsV2 and list of sharepoint calendar events lists

Hi, 

 

yes I want to retrieve corresponding SP List calendar events based on the date value and the person value.

 

I tried collect like You but I have a delegation problem.

Moreover i tried to build galeries in galeries (subgaleries) like Paul O'Flaherty suggested in his movie: https://www.youtube.com/watch?v=ZzQ1t2sQvj8 

 

but I have problem with hedlines and scrlling. Please look at attachements

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (8,270)