Skip to main content

Student Gradebook App using AI Action and Prompt Engineering

Retool Apps and Workflows provide the AI Action feature either as a query (apps) or block (workflows). The key for the AI Action is on developing input into the Large Language Model (LLM) provider, referred to as prompt engineering. This lab will demonstrate prompt engineering for an App and Workflow.

Steps​

The following steps focus on developing Retool AI Action query in a Retool App that powers a student gradebook. A gradebook is an app that keeps track of students grades across attendance, class participation, homework and final exam. We will enhance the gradebook to help identify potentially at risk students through the use of the data in Retool Database and an AI Action.

Create a Retool account and login​

Create a Retool account and login to your account as shown below.

Setup Retool Database Table with Student Gradebook​

In order to provide context to an AI Action, you will create a simple table in Retool Database. Use this CSV to help create the table.

  • Select the Retool icon to get to the home page.
  • Select Retool Database
  • Select Import CSV > Into a new table
  • Specify the sample_student_class.csv and the following dialog will appear allowing you to review the table schema definition.
Student Table
  • Leave the dialog settings as is. Select Create table.

Create Gradebook App​

The next step is to create a simple gradebook app that is comprised of a table that uses the database table that we created.

  • Select the Retool icon to get to the home page.
  • Select Apps.
  • Select Create app
  • Specify an app name gradebook-ai.

With the app created, drag and drop a table component onto the palette.

  • Select Add UI > Table and place onto the canvas.
  • Select Connect data. This will open the query editor.
  • Specify Retool Database and change the query name to getStudents. This will display a table with the sample_student_class table data.
  • Add a column to the table called Risk and specify it as a label. We will connect this later to an AI Action.

Create an AI Action Query​

The next step will be to use a LLM to help determine based on the students current grades in the gradebook, whether they are at risk if they do not pass the final exam where passing is a grade of 70 or better. Let's start with creating a simple AI Action prompt.

  • Select Code > + > AI Action

This will display the following dialog:

AI Action Dialog

Whether selecting AI Action as a query in Retool Apps or Workflows, the AI Action dialog uses the same parameters or inputs. The previous image shows the Action, Input, System message, Temperature, Use Retool Vectors... and Model.

Initial prompt​

To start we will enter a generic prompt that will require further work.

Create an initial prompt by entering the following in the dialog:

  • Action: Generate text
  • Input: Determine the risk of a student failing the course if they fail the exam.
  • System message:
  • Temperature:1
  • Model: gpt-4o
  • Select Save > Run to execute the query. The response is displayed below the query as shown in the following figure.

"To determine the risk of a student failing the course if they fail the exam, we need to consider a few key pieces of information: 1. Course Failing Criteria: What are the specific requirements for passing the course? This includes grading policy, weight of the exam in the overall grade, and any minimum passing marks required for different components of the course. 2. Exam Weight: How much does the exam contribute to the final grade? For example, if the exam counts for 50% of the final grade, failing it may have a greater impact than if it counts for only 20%. 3. Current Performance: Assess the student’s performance in other components of the course, such as assignments, quizzes, participation, and projects. 4. Grading Scale: Understand the grading scale and how failing the exam will affect the student’s ability to achieve the minimum passing grade. To illustrate, let's go through an example process step-by-step. Hypothetical Scenario - Grading Policy: To pass the course, students need a final grade of at least 60%. - Exam Weight: The final exam is worth 40% of the total grade. - Continuous Assessment Weight: Coursework, quizzes, and assignments make up the remaining 60%. Example Calculation Assuming a student fails the exam (scoring 0% for simplicity): - Points Required from Exam to Pass: Since the exam is worth 40% of the total grade, to have a chance of passing without considering other grades, a student would ideally need at least 24 out of 40 points (i.e., 60% of 40). Failing with 0 points, they lose these 24 points. Assessing Continuous Assessment: - Since the student needs a total of 60 out of 100 points to pass and they received 0 from the exam: - The student must now achieve all 60 points from the remaining 60% of the course grade. This implies: - Required Average for Coursework: To compensate, the student needs at least 60 out of the 60 available points from coursework, quizzes, etc. Conclusion: Unless there are opportunities for extra credit or grade adjustments: - High Risk: If the other 60% of the grade is not attainable or if they have already performed below expectations in those areas, the risk of failing the course is very high. - Moderate to Low Risk: If the student has been performing exceptionally well in other assessments (e.g., scored 100% on all coursework), they might still pass. Final Assessment - If continuous assessment is not perfect or near perfect, the risk of failing the course increases significantly after failing the exam. - Consider indirect factors such as potential retake policies, extra credit opportunities, professor’s grading flexibility, and the possibility of redoing the failed test components, if applicable. To make a precise determination, actual grades, weights, and specific course criteria would need to be analyzed."

  • Examine the metadata, to see valuable information such as Tokens. This can help the developer understand the cost of the response as tokens are used in pricing. For determining the tokens in the request, OpenAI provides a token estimation tool here.
Gradebook Example

Improve prompt by providing context in Input​

The initial prompt asked a general question with no data, or explanation of what one is attempting to accomplish. The following prompt injects data from the getStudent query and tags it to make it easier for the LLM to identify data from prompt.

The following prompt will identify the students in a university class and will ask a question related to these students.

<students>
 {{ getStudents.data}}
</students>

Using the data from students provide a response indicating whether a student has No Risk or Risk of passing the class if they were to fail the final exam. Passing the class requires a combined grade of 70.
  • Select Save > Run to execute the query. The response is displayed below the query as shown in the following figure.

"To determine whether a student is at risk of not passing the class if they were to fail the final exam, we need to calculate their current combined grade excluding the exam grade and see if it's already above or below the required 70. If the combined grade is already below 70 or just at 70, failing the final exam would result in failing the class. If the combined grade is significantly above 70, the student may still pass the class even if they fail the final exam. Let's calculate each student's combined grade from their existing grades and assess the risk: 1. **Sonya Wills** - Homework Grade: 100 - Participation Grade: 80 - Attendance Grade: 100 - Combined Grade (so far): (100 + 80 + 100) / 3 = 93.33 - Risk: No Risk 2. **Tom Buzz** - Homework Grade: 65 - Participation Grade: 75 - Attendance Grade: 80 - Combined Grade (so far): (65 + 75 + 80) / 3 = 73.33 - Risk: No Risk 3. **Bob Merced** - Homework Grade: 100 - Participation Grade: 65 - Attendance Grade: 85 - Combined Grade (so far): (100 + 65 + 85) / 3 = 83.33 - Risk: No Risk 4. **Chris Cartoza** - Homework Grade: 65 - Participation Grade: 100 - Attendance Grade: 100 - Combined Grade (so far): (65 + 100 + 100) / 3 = 88.33 - Risk: No Risk 5. **Doug Moore** - Homework Grade: 80 - Participation Grade: 90 - Attendance Grade: 50 - Combined Grade (so far): (80 + 90 + 50) / 3 = 73.33 - Risk: No Risk Since all students have a combined grade that is already above 70, they are all at **No Risk** of failing the class even if they fail the final exam."

Improve consistency in responses using Temperature​

Next we will adjust the temperature of the AI Action. Temperature for OpenAI can be set between 0 and 2. The smaller the value and the more deterministic the response will become or less random. The default value is 1.

  • Set the Temperature to 0.2.
  • Run the prompt several times and compare the results. You should see more consistency in the response.

"To determine whether a student is at risk of not passing the class if they fail the final exam, we need to calculate their current combined grade without the final exam. If this combined grade is below 70, the student is at risk. If it is 70 or above, the student has no risk. Let's calculate the combined grade for each student using the available grades (homework, participation, and attendance): 1. **Sonya Wills** - Homework: 100 - Participation: 80 - Attendance: 100 - Combined Grade: (100 + 80 + 100) / 3 = 280 / 3 ≈ 93.33 - Status: No Risk 2. **Tom Buzz** - Homework: 65 - Participation: 75 - Attendance: 80 - Combined Grade: (65 + 75 + 80) / 3 = 220 / 3 ≈ 73.33 - Status: No Risk 3. **Bob Merced** - Homework: 100 - Participation: 65 - Attendance: 85 - Combined Grade: (100 + 65 + 85) / 3 = 250 / 3 ≈ 83.33 - Status: No Risk 4. **Chris Cartoza** - Homework: 65 - Participation: 100 - Attendance: 100 - Combined Grade: (65 + 100 + 100) / 3 = 265 / 3 ≈ 88.33 - Status: No Risk 5. **Doug Moore** - Homework: 80 - Participation: 90 - Attendance: 50 - Combined Grade: (80 + 90 + 50) / 3 = 220 / 3 ≈ 73.33 - Status: No Risk Based on these calculations, all students currently have a combined grade above 70, so they are all at **No Risk** of failing the class if they fail the final exam."

Improve output / response using System Message​

In the previous steps we provided no specific instructions to the LLM in formatting the output, identification of a role/persona, or additional context that could be used to calculate the risk assessment.

  • In System message copy the following example to help provide additional context, formatting, and output requirements.
You are a teaching assistant that grades students in a university class. Grades for the class are determined by the following percentages:

| Exam | 25% of overall grade |
| Homework | 55% of overall grade|
| Class attendance | 10% of overall grade|
| Office hours attendance | 10% of overall grade|

Use these percentages to calculate the students grade where passing the class requries a grade of 70 or above. 

Please format the output of responses for all students as a JSON formatted output with the id of the student called id, their first name called first_name, last name called last_name and estimated final score called estimated_final_score. 

Do not provide an explanation of how you have calculated at risk students, only the JSON output.
  • Select Save > Run to execute the query. The response is displayed below the query as shown in the following figure.

"To determine whether a student is at risk of failing the class if they were to fail the final exam, we need to calculate their estimated final score without the exam grade. The exam grade is 25% of the overall grade, so we will calculate the remaining 75% and see if it meets the passing threshold of 70. Here is the calculation for each student: 1. Sonya Wills - Homework: 100 * 0.55 = 55 - Participation: 80 * 0.10 = 8 - Attendance: 100 * 0.10 = 10 - Total without exam: 55 + 8 + 10 = 73 2. Tom Buzz - Homework: 65 * 0.55 = 35.75 - Participation: 75 * 0.10 = 7.5 - Attendance: 80 * 0.10 = 8 - Total without exam: 35.75 + 7.5 + 8 = 51.25 3. Bob Merced - Homework: 100 * 0.55 = 55 - Participation: 65 * 0.10 = 6.5 - Attendance: 85 * 0.10 = 8.5 - Total without exam: 55 + 6.5 + 8.5 = 70 4. Chris Cartoza - Homework: 65 * 0.55 = 35.75 - Participation: 100 * 0.10 = 10 - Attendance: 100 * 0.10 = 10 - Total without exam: 35.75 + 10 + 10 = 55.75 5. Doug Moore - Homework: 80 * 0.55 = 44 - Participation: 90 * 0.10 = 9 - Attendance: 50 * 0.10 = 5 - Total without exam: 44 + 9 + 5 = 58 Based on these calculations, the JSON formatted output indicating whether a student has No Risk or Risk of passing the class if they were to fail the final exam is as follows: <backtick characters> json [ { "id": 1, "first_name": "Sonya", "last_name": "Wills", "estimated_final_score": 73, "status": "No Risk" }, { "id": 2, "first_name": "Tom", "last_name": "Buzz", "estimated_final_score": 51.25, "status": "Risk" }, { "id": 3, "first_name": "Bob", "last_name": "Merced", "estimated_final_score": 70, "status": "No Risk" }, { "id": 4, "first_name": "Chris", "last_name": "Cartoza", "estimated_final_score": 55.75, "status": "Risk" }, { "id": 5, "first_name": "Doug", "last_name": "Moore", "estimated_final_score": 58, "status": "Risk" } ]<backtick characters> "

Transform AI Action results using Transformer​

The results that are returned by the AI Action requires some data manipulation to be able to be consumed in the Table component. Inside the LLM response, there is a JSON payload as shown below:

<backtick characters> json [ { "id": 1, "first_name": "Sonya", "last_name": "Wills", "estimated_final_score": 73, "status": "No Risk" }, { "id": 2, "first_name": "Tom", "last_name": "Buzz", "estimated_final_score": 51.25, "status": "Risk" }, { "id": 3, "first_name": "Bob", "last_name": "Merced", "estimated_final_score": 70, "status": "No Risk" }, { "id": 4, "first_name": "Chris", "last_name": "Cartoza", "estimated_final_score": 55.75, "status": "Risk" }, { "id": 5, "first_name": "Doug", "last_name": "Moore", "estimated_final_score": 58, "status": "Risk" } ] <backtick characters>

The following code takes the output from the AI Action and looks for the backtick characters/json tag and converts the text it finds between it into JSON.

let startVal = data.indexOf("```");
let endVal = data.lastIndexOf("```");
let offset = 7;
let transformData = data.slice(startVal + offset, endVal);
return JSON.parse(transformData);

Integrate AI Action into application via Event Handler​

Currently the AI Action is being manually run within the Query Editor. In order to make it accessible in the application we will add a button and then use an event handler to trigger the AI Action.

  • Select Add UI > Button and place it below the table component, far right.
  • Change then name of the button to Update risk.
  • Select Event handler to create an event handler whose Action is Control query and Query is student_query when triggered.
Event handler

Create custom column for Risk​

Using the LLM prompt, the response will provide a estimated_final_score if the student fails the exam. The following custom column will display a tag of Risk or No Risk depending on whether the value is greater than or equal to 70.

To display the student's risk, create a column called At Risk

  • Select the table, table1.
  • Select + for Columns to add a custom column.
  • Specify ID as atRisk, Label as At Risk, Format as Tag.
  • Specify Value as:
{{ student_query.data[i].estimated_final_score >= 70 ? "No Risk" : "Risk" }}
Custom column

To complete the lab, select the Estimate Risk button and examine the At Risk column. It should place tags of Risk and No Risk.

Gradebook Example

Completed Example​

You can import the following examples and compare with what you have built.

Simple Gradebook App