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

Gallery with date columns

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.

 

sessionMetaData.png

 

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).

 

attendanceRecord.png

 

I require to display the information like this:

 

ATTENDANCE.png

 

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

43 REPLIES 43
RandyHayes
Super User
Super User

@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")

 

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
firektl
Helper III
Helper III

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
Super User
Super User

@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)?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Hi @RandyHayes 

 

They are text, I just validated.

 

SessionMetaData

session.png

 

AttendanceRecord

 

attendance.png

 

RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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
Super User
Super User

@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"
    )
)

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
RandyHayes
Super User
Super User

@firektl 

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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
Helper III
Helper III

Hi friend @RandyHayes 

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

 

Thank you.

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (3,862)