cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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
Highlighted
Community Support
Community Support

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.
Highlighted
Regular Visitor

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

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

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

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