Gallery with date columns

[firektl]

Hello Community,

I hope everyone is well.

I am developing a dashboard which I want to show me the assists according to selected parameters.

I am connecting my dashboard to two sharepoint lists, these lists have the following structure:

In the SessionMetaData list I have:

Title: unique field
ClassId: class identifier (team ID in Microsoft Teams)
LessonDate: class date
StartTime / EndTime: start time and end time of the class
ClassName: Class name (team in Microsoft Teams)
TeacherEmailAlias: email of the teacher who takes the attendance.

In the AttendanceRecord list:

Title: unique field
LessonId: Code of the sessionMetaData list (with whom I relate both lists)
LessonDate: date of attendance.
Attendance: I save the student's attendance (present, absent, late, excused)
StudentEmail: student's email.
Notes: a comment.
AttendanceTakenBy: name of the teacher taking attendance.
ClassId: class identifier (team ID in Microsoft Teams).
StartTime / EndTime: start time and end time of the class.
StudentName: name of the student.
ClassName: Name of the class (team in Microsoft Teams).

I require to display the information like this:

The problem is in how to generate the columns of the dates, since they are going to vary depending on the class.

Please have any idea how to display the information.

Thank you very much

[firektl]

@RandyHayes 

Sorry, but I just read your message.

Here I could not move forward because it needs to present the data in some way and I decided to do it in another way, only now if they are asking me to show the data in that way.

The relationship between SessionMetaData and AttendanceRecord is through LessonId.

SessionMetaData has a Title field and that field is related to AttendanceRecord by the LessonId field

SessionMetaData -> Title

AttendanceRecord -> LessonId

Additionally, both tables have a field named ClassId that has been the identifier of the Microsoft Group team.

My intention is that when selecting the class to have the students as rows and the dates as columns, when making the intersection indicate their attendance.

I hope you have understood me, thank you very much for taking the trouble to read and again I apologize for the delay, only that I had not read your message

[RandyHayes]

@firektl 

One additional question - is there a record in the SessionMetaData list for each date of the class?  I.e. If Class X has a lesson dates on 12/1, 12/4 and 12/6, are there then three records in the list, one for each date?

[firektl]

Hi @RandyHayes 

Correct, for each attendance a record is saved in the SharePoint list, when registering an attendance a single item is created in the SessionMetaData table and N (for each student) records in AttendanceRecorde.

I attach some images of the SharePoint list.

AttendanceRecord

 SessionMetaData

[RandyHayes]

@firektl 

So actually, that wasn't exactly what I was asking for - I wanted to know if the SessionMetaData had a record for each date.  I assumed that Attendance would certainly have a date for each entry, but I was looking to see where to find the date "truth".  

I believe, based on the image, that there is a single record for the class and then the dates that the class occur are not stored in that record (or as multiple records in the list), but instead are in the Attendance.  

So, I would wonder why you would have a lesson date in the SessionMetaData...since the image only shows correlation between the two lists in regard to only ONE lesson date, perhaps my assumption is incorrect and there is in fact a record in SessionMetaData for each lesson date....please clarify.

[firektl]

Good Morning @RandyHayes 

I hope you are well.

I wanted to know if the SessionMetaData had a record for each date. 

---

Answer: Yes, for each date a single record is created in SessionMetaData.

The application I am using is called Attendance App and it is available on the web, I just adapted it to my organization,  there is a selection called "Data stores" where they detail how the information is stored.

Thanks again for your time and sorry if I am not complicating you a bit.

[RandyHayes]

@firektl 

Nope...all makes sense and I am glad you have a record for each date in the session metadata list - this will make it much simpler.

I will get you a solution for this tomorrow (if you don't hear back from me - nag me) - sorry for the delay.

[firektl]

Hi @RandyHayes ,

Sorry for the trouble, but you told me: "nag me" if I didn't have an answer from you, and here.

I am! You are my hope! 👼

[RandyHayes]

@firektl 

Yikes!  Sorry that I dropped it.  That's why I said...nag me 😅

Here is what I came up with.  I put a Dropdown on my screen and set the Items property to the following:

AddColumns(
        GroupBy(
            SessionMetaData,
            "Title",
            "_records"
        ),
        "Attendance", GroupBy(Filter(AttendanceRecord, LessonID =Title), "Student", "_studentRecords")
    )

This is based on the tables that you gave pictures of (except I was lazy and used Student instead of StudentName).

Now, I have a Gallery on the screen and its Items property is set to the following:

ForAll(Sequence(CountRows(Dropdown1.Selected.Attendance)+1,0), 
    With({thisItem:Coalesce(Last(FirstN(Dropdown1.Selected.Attendance, Value)), Blank())},
        {student: If(Value=0, "STUDENT", thisItem.Student),
        _attendance: 
            If(Value=0, 
                ForAll(Dropdown1.Selected._records, 
                    {stat:Text(LessonDate, "[$-en-US]d-mmm")}
                ), 
                ForAll(Dropdown1.Selected._records As Recs, 
                    {stat: If(IsBlank(
                           LookUp(thisItem._studentRecords, 
                             LessonDate=Recs.LessonDate, LessonDate)
                          ), "A", "P")
                    }
                )
            )
        }
    )
)

This gives us a nice table of records with a student column and a _attendance column.  The student column is the student name, and the _attendance column is the table of attendance records for that class and that student.

To reduce the extraneous amount of controls to create a Header for the Gallery, I inject a first row (Value 0 in the above) that has "STUDENT" for student and the gathered list of class dates (done in the dropdown).  So, if Value=0 (the injected row), then _attendance column (which will be a table returned from the ForAll's) gets the Text version of the LessonDate in the d-mmm format in the stat column of the records.  Otherwise it will be a table of records based on a Lookup into the Attendance table (again done in the DropDown record) based on the date of the class LessonDate and the Student (which is actually already filtered out from the DropDown).  In other words the Attendance table column on the DropDown will only have the list of dates that the student attended (and it is grouped by the student) and this formula will match those into a table for that student, this class and their attendance based on lookup.

The First Gallery has a label in it with the Text property set to : ThisItem.student

So now that we have this nice grouped, filtered, and prepared table, we can feed it to the next level, which is a Nested Gallery in the first Gallery.  It is a Horizontal Gallery, so that we can have columns.

The Items property for that is set to ThisItem._attendance

There is a Label in the second gallery with a Text property set to : ThisItem.stat

Putting it all in play...it looks like this:

 

I've attached a sample app for you. Download it to your computer.  Then go into PowerApps designer and either open an existing App or Create a New one (not making a difference which because you are going to open this one).  Go to File->Open->Browse and then browse for the downloaded file to open it.

I've made a mocked up list of data.  You can find all of that in the Dropdown Items property.

Examine this all and let me know if you have any questions.

[firektl]

Hello @RandyHayes 

Ohhh it looks super, I'm going to adapt it to my code and I'll tell you how I'm doing, thank you very much for your time. He wrote to you directly, if you can answer me.

Total thanks.

[firektl]

Hi @RandyHayes 

I am trying to implement the code you made.

Initially, I am creating a Dropdown and placing the code

AddColumns(
    GroupBy(
        SessionMetaData;
        "Title";
        "_records"
    );
    "Attendance";
    GroupBy(
        Filter(
            AttendanceRecord;
            LessonId = Title
        );
        "StudentEmailId";
        "_studentRecords"
    )
)

But I get an error. 

I understand that at this point you want to generate the dates by class.

In the code you sent me, you create tables from the SessionMetaData and AttendanceRecord lists, should I also create these tables?

Thank you, I am attentive to your comments.