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

[RandyHayes]

@firektl 

No, you don't need to create anything else, you can work directly from the datasource.

So, your Formula appears correct, but there is an error coming from it based on the AttendanceRecord list.

Are these assumptions correct?

1) The SessionMetaData list has a Title column that is a single text type and contains a string based GUID for the Class

2) The AttendanceRecord list has a LessonId column that is a Single Text Column that contains a string based GUID (which is the one used in the SessionMetaData list for "connection")

[firektl]

Hi @RandyHayes 

It is correct, in the list of SessionMetaData there is the column TITLE that is unique contains a GUID, this column is stored in AttendanceRecord with the column name LESSONID.

In the SessionMetaData list the column is unique.

In AttendanceRecord it is repeated for each student that exists in the class.

I'll give you an example:

In the SessionMetaData list

Record N ° 1

Title: 24d215e2 (unique code for each record)
ClassId: a423f16c
LessonDate: 12/16/2020

In the AttendanceRecord list

Record N ° 1
Title: 123dfade (unique code for each student in the class)
LessonId: 24d215e2 (repeats so students have the equipment)
ClassId: a423f16c

Record N ° 2
Title: 345sde (unique code for each student in the class)
LessonId: 24d215e2 (repeats so students have the equipment)
ClassId: a423f16c

I hope you have understood me

:3

[RandyHayes]

@firektl 

Yes, I got that they "link" that way and that you have these in the lists - the bigger question is, are these ALL text columns in your list that we are dealing with or are they defined as something else (like a Lookup)?

[firektl]

Hi @RandyHayes 

They are text, I just validated.

SessionMetaData

AttendanceRecord

[RandyHayes]

@firektl 

At a quick glance, I see that you have an Attendance column in your list and we are defining an Attendance column in our AddColumns, so, let's start with the following changes:

For your DropDown Items:

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

If that does not throw an error, then we just need to adjust the Gallery items:

ForAll(Sequence(CountRows(Dropdown1.Selected._classAttendance)+1,0), 
    With({thisItem:Coalesce(Last(FirstN(Dropdown1.Selected._classAttendance, 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")
                    }
                )
            )
        }
    )
)

Let's see if that resolves anything.

[firektl]

Hi @RandyHayes 

I noticed that and had already made the column name change. I took your code and put _classAttendance on it but it keeps showing the same error 😞

[RandyHayes]

@firektl 

Surprisingly enough, I needed to put this information into a real SharePoint list instead of the sample data that I had.

After doing that I received the same message.

Fixed it...use this formula:

AddColumns(
    GroupBy(
        SessionMetaData;
        "Title";
        "_records"
    ) As _sessions;
    "_classAttendance";
    GroupBy(
        Filter(
            AttendanceRecord;
            LessonId = sessions.Title
        );
        "StudentEmailId";
        "_studentRecords"
    )
)

[RandyHayes]

@firektl 

Were you able to work through your issue or do you still need help?

[firektl]

Hi @RandyHayes

Sorry for being absent without warning, but due to the dates we have been between meetings and activities that have not allowed me to advance with the code.

I tell you that I have tested the code and it works but it is not returning the data correctly.

In the dropdown I see that you group it by Title, and in my opinion it should group by ClassId, so that in this way, the internal gallery can show the number of classes, since it only shows the class associated with the Title, which is unique, even if they are from the same class.

Another detail that I could see, in the gallery of the names of the students, I duplicate the last record, if there are 3 people registered, the last one repeats it showing 4, besides that the attendance is sent incorrectly, it seems that if the first one is present He takes present for all, and if he is absent, he takes absent for all.

Again, excuse me for being absent, but we are on a run because it works until 23/12.

Thank you very much for your patience and interest.

[firektl]

Hi friend @RandyHayes 

You managed to read my previous message and the details that I could see, please don't forget me.

Thank you.