Help with Design

Hi All -

This may be more of an application design rather than 'Retool' question per se, but I hope someone can give some guidance.

I am trying to build an attendance tracker in Retool/Retool DB. I have a table 'students' with all my students, and a table 'class' that has a list of school days. I want to create a spreadsheet/grid like thing in Retool where the column headers are the school days (pulled from DB), and the rows are the students (also pulled from DB). When a teacher marks P or A to indicate present or absent, I want to insert a row into the 'attendance' table which would be student ID, class ID, and the enum for that day (P or A).

If a grid like solution is not feasible, I suppose I could simplify by only logging attendance for one day at a time - but I am not sure what Retool component would be the best fit for doing this.

It's been a few years since I've done any development so I'm feeling a bit rusty. Thank you all for your help!

@Jessica_D This can definitely be done...

You will probably need to JOIN the two tables in a select statement so that each student has the school day in each of their rows...
Any additional information you can provide would be helpful.
Is it only one class or multiple classes? etc..

There are multiple school days in the school year. It's a Sunday School program, so I thought having a grid wouldn't be too cluttered since there are only ~30 classes total.

OK so you would want to create a student table at least but wondering if it is only one type of class and there is a limited number of classes I guess you would add all of the columns in the one table in the database?
For example:

Here is the CSV for the db (though you may want to add to it)
student.csv (45 Bytes)
And here is the JSON app...
Roster.json (16.9 KB)

Please mind that they are both very basic and something that will get you moving in the right direction

I appreciate the sample app but I'm not sure if we are on the same page. I am looking for something like this:

The student name rows are pulled from the 'students' table, the column headers are pulled from the 'class' table, and the editable data where a teacher would mark the attendance writes to a 'attendance' table.

Is something like this possible in retool or do I need to 'rethink' my approach? :grinning:

It's possible but I think it can be simplified IF you are saying that there are a limited amount of class dates.

Solution 1:
OK so I think the best and easiest way to go about it is to have a table listing students, selecting each one and opening a modal then selecting dates of attendance and then submitting that form which will close the modal and refresh the attendance table (Grouped by Class Date)

Roster (1).json (40.5 KB)
student (1).csv (73 Bytes)
classes.csv (65 Bytes)
attendance.csv (103 Bytes)

Feel free to add the dates you need to the classes table....

I am sure it can be done in a better way but this is as close as I could get to what you were looking to do.

Solution 2:

You could also create a csv and upload that to Retool database and it will create one table with all of the fields and then you can run a bulk edit on it using the GUI interface for the Resource.

Here's the entire table with all data columns in it. - you can adjust and add columns (Also don't forget to rename the header columns to show dates in the UI)
studentattendance (1).csv (127 Bytes)

And here is a different app with one table using bulk edit.
Roster_Attendance.json (20.1 KB)

1 Like

Thank you so much for your help and thinking things through with me! In the meantime, I came up with another solution that looks like this.

I took your suggestion to simplify things and work with one date at a time. Few quirks that I am working through:

  1. The placeholder for the tag column does not show up even though it is configured (I had posted about this problem on another thread as well).
  2. I have the event handler on the 'attendance' column that when a tag (either present or absent) is selected, an update query is ran to insert this data. I do not have a 'save' handler on the entire table as I imagine a teacher will be doing this one by one anyways. However, the changeset little blue triangle thing in the corner doesn't go away. How do I clear out the changesetArray? I tried triggering a reload of the table data upon a successful insert but that didn't do anything!

I think I answered my own question #2:

Trigger this script to run after update: