Building app using Power Apps in teams

Intro

A teacher, who is a friend of mine, wants to digitalise the school. As a first step we wanted change the way teachers take student’s attendance. His wish was to keep the price of the software as low as possible, so I set a challenge do keep it 0.

They use Microsoft Office together with Microsoft Teams and I found out that you can build an app inside of Teams for free. It can be found inside Teams Apps, and Power Apps:

I used Dataverse database, which is database provided by Power Apps Teams integration, also completely free.

Requirements from the teachers

The app should allow teachers to perform the following actions:
Core:

• Take class attendance for a specific class having specific subject on a specific date
• View and filter already recorded attendances

Support:

• Create, modify and delete class names.
• Search and assign students to class.
• Create, modify and delete subjects.

To fulfil these requirements I solved these questions:

How can I access all students?

One option is to let the teacher enter student data manually, but come on, it is 2024, we can do better.
Each student uses Teams and Power Apps app has access to them.

The screen to manage students in classes looks like this:

The classes are coming from “ClassNames” table, see (Tables I created…)

Add Office365Users connector:

Use autocomplete to easily find students

I used gallery component “AutocompleteGallery” to show the user suggestions based on an entered text:

Filter(Office365Users.SearchUser({searchTerm: *SearchUserInput*.Value, top: 5}),!IsBlank(UserPrincipalName))

The search text I take from an input field. This component just need a name “SearchUserInput”. Power Apps takes care of the rest.

Store students in a class

The next step is to add a selected student into a class. The autocomplete gallery has the following OnSelect event to actually store the selected Office User:

Patch(Classes,{UserId: ThisItem.Id, GivenName: ThisItem.GivenName, Surname: ThisItem.Surname, Mail:ThisItem.Mail, ClassName:SelectedClassName.Selected});

Reset(SearchUserInput)

Finally, I used other gallery component “ClassGallery” to show the stored students in the class:

SortByColumns( Filter(Classes, ClassName.NameOfClass = SelectedClassName.Selected.NameOfClass) , "cr15e_surname" )

Remark: It took me some time to get to use to how Power Apps work. First you display the list and add inputs and controls to alter the list content. Here I show students from Office, but I filter them using the input field.

How should I store the attendance records?

Requirements

• Teacher selects a date, a class and a subject, then each student should have a record saying whether he was there or not.
• Teacher should be able to simple modify these records (if a students shows up 5 minutes later).

These tables I created to fulfil the requirements

The “AbsenceRecords” table, which holds the actual record of absence looks like this (it took few iterations until I came up with this):

Of course before that I needed to create all the related tables.

“Class” table where I store the students:

This table contains the copy of office users (students). I accessed them using the Office365Users connector (showed you above how). It also holds a reference to the “ClassNames” table.

“ClassNames” table serves as a simple way to manage the class names. I guess there are many ways how you can mark the classes, I wanted to use Power Apps Choice, but I could not make it work.

The last table required for the “AbsenceRecords” table to function was the “Subjects” table.

“Subjects” table is also a very simple, it holds the name of each school subject.

The status “Present, Absent, Late, Excused” I managed to solve with Choice. As a table column looks like this:

And the choice itself, is looking like this:

How to build the UI for attendance taking?

The challenge was to display and save “Present” state at first time for the selected class. And allow modifications later.

I added 3 input fields to set Date, Class and Subject of the recording.

Added the Gallery component to simple display the records filtered based on the 3 inputs:

// Students gallery

// AddColumns was required to make Table Relationships accessible for the app.
SortByColumns(
    Filter(
        AddColumns(AbsenceRecords, "ClassNameId", Class.ClassName.Id, "Surname", Class.Surname),
        ClassNameId = ClassComboBox.Selected.Id,
        RecordDate = Datum.Value,
        Subject.Id = SubjectComboBox.Selected.Id
    ),
    "Surname"
)

The Button component “StartButton” starts the first time recording. OnSelect action creates all the records based on the 3 Input fields:

// MyTable creates an in memory table
ClearCollect(
    MyTable,
    Filter(
        AddColumns(
            Classes,
            "ClassNameId",
            ClassName.Id
        ),
        ClassNameId = ClassComboBox.Selected.Id
    )
);

// For all is "patching" means inserting the records to the database
ForAll(
    MyTable,
    Patch(
        [@AbsenceRecords],
        {
            Subject: SubjectComboBox.Selected,
            RecordDate: Datum.Value,
            Class: ThisRecord
        }
    )
);

Once the Gallery is filled with data, you see 4 buttons: Present, Absent, Late and Excused.

Each button has an OnSelect event that updates the given row:

Patch(
    AbsenceRecords,
    LookUp(
        AbsenceRecords,
        Id = ThisItem.Id
    ),
    {RecordStatus: AbsenceStatus.Excused}
);

Additionally, I indicate which status is currently set by simple switching the ButtonType:

If(ThisItem.RecordStatus = AbsenceStatus.Excused, "Primary", "Secondary")

Error handling can be included if necessary….

How to show all the attendances?

The final screen looks like this:

This screen could have been so simple, but instead I needed to include the percentage of absences per student. On top of that, there should be a filter. Makes sense for the teacher, was a nightmare for me. Anyways, the final gallery component has the following Items:

SortByColumns(
AddColumns(
    GroupBy(
        Filter(
            AddColumns(
                AbsenceRecords,
                "StudentId",
                Class.Id,
                "GivenName",
                Class.GivenName,
                "Surname",
                Class.Surname,
                "ClassNameId",
                Class.ClassName.Id,
                "Choices",
                RecordStatus
            ),

            If(
                IsBlank(SubjectsFilterCombox.Selected),
                true,
                Subject.Id = SubjectsFilterCombox.Selected.Id
            ),
            If(
                IsEmpty(ClassFilterCombox.SelectedItems),
                true,
                ClassNameId = ClassFilterCombox.Selected.Id
            ),
            If(
                IsBlank(DateFilterPicker.Value),
                true,
                RecordDate = DateFilterPicker.Value
            ),
              If(
                IsEmpty(StudentFilterCombox.SelectedItems),
                true,
                Class.Mail = StudentFilterCombox.Selected.Mail
            )
        ),        
        "StudentId",
        "GivenName",
        "Surname",
        "Students"
    ),
    "SumOfPresent",
    CountIf(
        Students,
        Choices = AbsenceStatus.Present
    ),
    "SumOfAbsent",
    CountIf(
        Students,
        Choices = AbsenceStatus.Absent
    ),
    "SumOfExcused",
    CountIf(
        Students,
        Choices = AbsenceStatus.Excused
    ),
    "SumOfLate",
    CountIf(
        Students,
        Choices = AbsenceStatus.Late
    ),
    "Ratio",
    CountRows(Students)
), "Surname")

How should a teacher manage class names and school subjects?

Power Apps has out of the box UI where you select the data source and it automatically generates a screen with a list and form to manipulate the list. I used for both tables this feature:

How should a teacher navigate between screens?

As you saw on the print screens, I used tabs. This component is from the “Modern” component library of Power Apps.

I set the tab labels and the navigation at the OnStart event of the App:

// OnStart
ClearCollect(
    topNavItems,
    [
        {label: "Absenz aufnehmen", screen: TakeAttendance},
        {label:"Alle Absenzen", screen:ViewAttendances},
        {label:"Klassen Namen", screen:ViewClassNames},
        {label:"Studenten",screen: ViewStudents},
        {label:"Fächer",screen:ViewSubjects}
    ]
)

Then I set the following OnSelect event for the tabs component:

Set(varCurrentNav, Self.Selected);
Navigate(Self.Selected.screen, ScreenTransition.Fade)

Publish

They made it super easy:

Attention: I was using production database in the app builder. I did not figure out how to have a test database.

Conclusion

I hope it was helpful for you. I haven’t explained here every detail, but I am happy to answer any question.