cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jtillinghast
Frequent Visitor

Beginner Design Question: Building Quick Gradebook/Scoring Form

Hi, all-- forgive a beginner's question, but I am trying to build a quick peer-review gradebook for one of our teachers. We want to have a form which lists the class roster, and gives a dropdown 1-5 scoring list for each student. I want to write this back to an Excel sheet that will store the student's name and score. I've tried two things so far:

1) I made a gallery which pulled the roster, and lets us select a student and enter a score. This is great except that I have to hit submit each time, rather than being able to add all of the scores at once and hit submit. 

2) I tried to create a custom form where each data card had two controls: the number box and a label containing the students name. I can get the score to pass to the form, but I can't get the student's name to write back.

 

Any suggestions on how to approach this would be much appreciated! Thanks--

9 REPLIES 9
Delid4ve
Impactful Individual
Impactful Individual

1: I've not checked this is possible, but I'm pretty sure it is..
On the gallery I'm assuming when you select an item it takes you to a form to enter the score..
What about, within the gallery adding a dropdown for the score, removing the arrow to navigate to the form and instead having 1 confirm button on the screen thats set to Patch gallery.allitems back to your list. Like I say I don't know if this is possible. Another way would be to set the dropdown in the gallery on change property to patch the list(this definitely works as I use this method in my app).

2:is the score and student name in the same list? If so it should be a case of setting the form data source to your list and the item property to gallery.selected. It all depends on how the data is stored tho

Delid4ve
Impactful Individual
Impactful Individual

Just thought about this aswell.. are you using the form on submit function..
From experience, this doesn't show errors on updating fields and you would be better off using the patch function with a filter. It may be that on the data card for student name the update function is wrong, especially if you are using student name but it is stored in the list as an ID number for student from a different list, in this case you would have to set the update property to a lookup(listname,studentname = datacardvalue.text,ID)

Thanks for your help! I'm following your suggestions, and I created a gallery with a dropdown menu. Question: will this create new entries for each score, or will this replace/update the score each time? I'm concerned that it seems like there will only be one master column in my Excel table for "Score," and that value will get replaced with each update. Perhaps I'm not familiar enough with Patch.

 

On another note, I added the drop down menu to the Gallery control, and I set the Items field to = ThisItem.Score (name of column in data source). I have an error that "The property expects table values, but this rule produces incompatible Text values." 

 

Thanks again for your help!

Take a step back, we need to get your data structure right first.

Looking at your post im assuming you want to grade multiple subjects/exams.

1st step, your going to need three lists.

list 1: Students. for starters just create 1 column with their name, you can add other bits later.

list 2: subjects. again, for starters 1 column with the subject name in.

list 3: Grades. 4 columns in this 1, Subject,Grade,student,date.  Set all the column types as numbers except the date, which will be of type date (we will be populating this with the ID from each of the students and subjects lists, and assuming the grade is also a number (if its not then use whatever field type is necessary, keep away from lookup, we can set the choices within powerapps)

 

get started with that and ill post further.

Delid4ve
Impactful Individual
Impactful Individual

Within power apps we are going to need the following screens:

Screen 1: screen to choose what to do - this can have buttons on it with the onselect property set to navigate to each of the next

screen 2: screen to input results for each student for a given subject.

screen 3: screen to view results for a specific student

You can add screens later in order to add new subjects and students and view results by subject.(by then you should have a fairly decent understanding.

 

Screen 1

Each button should have its on select property set to:

Navigate(ScreenName,ScreenTransition:none)

 

Screen 2 - im going to set this up for a single subject, you can change this later if required by moving the dropdown to the gallery and changing your formulas slightly, but at the moment i assume you only want to input for 1 subject but multiple students.

 

Add a dropdown at the top of the screen and set its items property to Subjects on the advanced tab.(the name for your subjects list)

Set its value property to SubjectName (the column name in your list)

Add a date picker also to the top of the screen.

 

Add a gallery.  Set its items property on the advanced tab to Students (the name of your list which contains the students)

Now go to the properties tab and click data, you can now choose ust a simple 1 line gallery and set this to student name.

This should now populate with your students names.

Select the first gallery item so that the section box comes up around it, add a new control, dropdown.  You should see this dropdown get populated in each gallery item (for each student).

Select the dropdown in the first gallery item and on the advanced tab set its items property to [1,2,3,4,5,6,7,8,9,10] (this assumes your grades are 1-10)

Now set the on change property of the drop down to the following:

 

If(IsEmpty(Filter(Grades,Date=DatePickerName.Selected.Value And Subject = LookUp(Subject,SubjectName=SubjectDropdownName.Selected.Value,ID And Student = Thisitem.ID))),Patch(Grades,Defaults(Grades),{Student:Thisitem.ID,Grade:GradeDropdown.Selected.Value,Subject:LookUp(Subjects,SubjectName=SubjectDropdown.Selected.Value,ID)}),Patch(Grades,{ID:LookUp(Grades,Student=Thisitem.ID And Date=Datepickername.Selected.Value And Subject=LookUp(Subjects,SubjectName=SubjectDropdown.Selected.Value,ID),ID),{Grade:GradeDropdown.Selected.Value}))

 

Its not very clean but it will show you how im referencing things, there are better ways to achieve it but this wil help you understand a few functions.  So above we first ask a question wi the If function, that question is: For my selected date, subject and student does a record already exist in the grades table (IsEmpty) .

We then give it something to do for true, so my record doesnt exist so im going to add it (patch).  The defaults part of the patch function will automatically give the record an ID number.

then we give the function a false value. So my record does exist, so im going to update it.  We lookup the ID number for the row using the values we have, and then ammend the grade to the new value. (remember, if we put in a wrong grade and we change it we dont want to create a new record, just ammend the one already created.

 

Doing it this way saves having multiple screens and allows you to enter all the grades without having to confirm or submit anything.

 

Screen 3

 

Add a dropdown and set its items property to Students (listname of your students), change the value property to StudentName

Add a gallery and Set its item property to 

Filter(Grades,Student=Lookup(Students,StudentName=Dropdownname.Selected.Value,ID))

Select the first gallery item and insert a label.

Set the label text property to:

Lookup(Subjects,ID=ThisItem.Subject,SubjectName)

Add another label and set its text property to Thisitem.Grade

 

Probably a few mistakes in their as i havnt tested and had a few beers 🙂 youll have to ensure any names are correct such as dropdown/datepicker names and column/list names but it should give you the foundation of your app to move forward.

Once you get used to the syntax and functions it gets easy.  You could also add in a column for the user that submitted the grade, you can reference the logged in user using User().FullName.

 

Hope this helps.

Delid4ve
Impactful Individual
Impactful Individual

Apologies, ive just posted (waiting approval) but just realised you are using excel as your source, not sharepoint.  I havent had any experience with excel but you should just be able to modify parts of what i have posted to do what you want.  Based on wat you are tring to achieve though, i believe using  database would be a better option going forward and a sharepoint site (private to your organisation) would be a better option for scalability if this is going to be used on a regular basis and throught school years etc.

jtillinghast
Frequent Visitor

This is a little simpler than a full grade book. The end result is that we want to be able to run averages, plot trends over time, etc. I have a data table right now that has five columns: student name, timestamp, submitter ID, score, and a text field for comments. My original plan was to record every entry as a running list of records, so that we could just query the list for assignments matching last name. I have a separate table which is just all the names in the class.
jtillinghast
Frequent Visitor

And yes, I might be better off with a database. Since this is my first power apps run and I'm not very experienced in SharePoint, I wanted to stick with a data source I was comfortable operating, but perhaps that was too limiting?
Delid4ve
Impactful Individual
Impactful Individual

SharePoint sounds scary, I could never understand it for a long time. Now I find uses for it everyday.
I have posted a full breakdown of what you need to do, but for some reason it has gone to waiting approval, maybe because it has code snippets in it. Should show up soon, I hope.
As you have already got your data sources into powerapps from excel you should be able to convert my post for SharePoint into excel format.
Your going to need 3 tables in excel.
1 for students which you have with an ID number
1 for subjects/assignments with an ID number
1 to keep track of grades.
The grades table should have the student ID, subject ID, grade and date
This way you can keep a list of all grades for each student in each subject.
Obviously your excel file has to always be available, whereas the SharePoint way would always be online.
You could always use Microsoft flow to collect data every time a grade is added and automatically produce and email a certificate, generate reports for each student or subject.
Since discovering SharePoint, flow and powerapps it has opened my eyes to how much I can automate and save myself endless time. Watch out my my post to go live, sure it will get approved soon.

Helpful resources

Announcements

Celebrating the May Super User of the Month: Laurens Martens

  @LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!        

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!      

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Community Roundup: A Look Back at Our Last 10 Tuesday Tips

As we continue to grow and learn together, it's important to reflect on the valuable insights we've shared. For today's #TuesdayTip, we're excited to take a moment to look back at the last 10 tips we've shared in case you missed any or want to revisit them. Thanks for your incredible support for this series--we're so glad it was able to help so many of you navigate your community experience!   Getting Started in the Community An overview of everything you need to know about navigating the community on one page!  Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Community Ranks and YOU Have you ever wondered how your fellow community members ascend the ranks within our community? We explain everything about ranks and how to achieve points so you can climb up in the rankings! Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Powering Up Your Community Profile Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile. Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Community Blogs--A Great Place to Start There's so much you'll discover in the Community Blogs, and we hope you'll check them out today!  Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Unlocking Community Achievements and Earning Badges Across the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. Check out some details on Community badges--and find out more in the detailed link at the end of the article! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Blogging in the Community Interested in blogging? Everything you need to know on writing blogs in our four communities! Get started blogging across the Power Platform communities today! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Subscriptions & Notifications We don't want you to miss a thing in the community! Read all about how to subscribe to sections of our forums and how to setup your notifications! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Getting Started with Private Messages & Macros Do you want to enhance your communication in the Community and streamline your interactions? One of the best ways to do this is to ensure you are using Private Messaging--and the ever-handy macros that are available to you as a Community member! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Community User Groups Learn everything about being part of, starting, or leading a User Group in the Power Platform Community. Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Update Your Community Profile Today! Keep your community profile up to date which is essential for staying connected and engaged with the community. Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Thank you for being an integral part of our journey.   Here's to many more Tuesday Tips as we pave the way for a brighter, more connected future! As always, watch the News & Announcements for the next set of tips, coming soon!

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Top Solution Authors
Top Kudoed Authors
Users online (4,318)