I have a Google Sheet with a list of students followed by columns of computed dates. It’s a small school, so I want to keep the row count low to keep the attendance app “free”.
A ‘P’ in a column indicates ‘present’, and an ‘a’ indicates ‘absent’. Very simple.
I want to design a custom form to bring up all the students and as they are clicked, enter a ‘P’ in the column corresponding to that day.
Actually, blank works just as well for an absence, so I don’t even need to worry about the ones they do not select.
But how do I tell the custom form which column the check (1) values should go into? I’ll admit there are times this no-code stuff would be easier if I could write some code! LOL.
I can set up an inline list and when they click on a name, set the value of a certain field to toggle between present and absent (1/0), but how do I get the value into the correct row corresponding to the current day, seeing as the dates are all calculated in the GSheet?
Interesting that if I try to add a checkbox component, the way it has me selecting the ‘column’ is by a list of months followed by a list of days. The computation is circular and encompasses a year, so January starts on the 31st, followed by the 1st, 2nd, etc. through the next 31st.
And if I do a “set column values” custom action, they are enumerated in order… The thing is I want to do a set column values and have it automatically applied to the correct day.
Yes, it looks like Glide has interpreted the 2nd row that’s shown in your second screen shot as the column headers, then both treated them as array columns and applied column grouping.
To make this easier to manage in Glide, I’m thinking you might need to re-think your approach.
I know you are trying to save rows, but would one row for each day of the year be acceptable? (you could probably skip weekends?)
If that would be okay, then I think what I would do is then just have a single column that would contain a comma separated list of StudentID’s, representing those students that were present that day. You could then use a Split Text column and build a relation back to your Students table to get an attendance history for any individual Student.
An Inline List of the above (dynamically filtered, if necessary - ie. just show past few days)
A choice component using the chips layout in multi-select mode, that writes the StudentIDs to the appropriate date row
I think that could make for a fairly slick and easy to use interface
Just thinking about that a bit more, you wouldn’t even need to pre-populate the Days of the Year. You could just start with zero rows, and then add a new row each time the attendance is taken.
I’m just blowing my head up because if I were coding, I’d make a multi-dimensional array of weeks, days of the week, and students. I like your idea. I’m going to try it without, however, dismantling my current data structure. Hopefully, a light bulb will go on somewhere.
As you can see, I’ve set up an array in my glide sheet, but I don’t want to work with generic column names. However, how can I split the field “present” into the array in the Google Sheet so that someone can work with it from there? I don’t want to have to hard-code a custom form with names / array fields, unless I can pull the name of each student to the corresponding array. Then I’d be willing to add an entry field for each array element.
I guess what I’m saying is, “without code, I don’t know how to deal with arrays!”
I even tried to find the number of times a student was present, by setting up a relation with the student name and “select multiple”, but that didn’t work. Splitting the text didn’t allow me to set up a relation. I really don’t know what I’m doing…
If you have a list of unique names in another sheet, I assume you can do a relation to a split text column (from that “Present” column) and count the days that they’re presented?
As @Darren_Murphy and @ThinhDinh pointed out. Are you using a Split Text column in glide? A comma delimited list is simply that. Just a single string of comma delimited items. When you add a Split Text column with that comma delimited list as the source, then it converts it into an actual array. From there you can create relations to and from that array column. For example, if you are looking at a specific student record, then you can create a multiple relation from that student record to the attendance array column to find a match, then you can follow up with a rollup column that counts the matching rows from the relation.
That should really be the only 2 columns you need in that sheet (Date, Present).
Back in Glide, you’ll just need to add a Split Text column (as Jeff and Thinh have pointed out), and then you build a relation to that from your Students table.
The other thing I would mention is that this will be more robust if you use StudentIDs as opposed to Student Names. Names can change, which would cause problems. And names could also contain commas (not very common, but could happen), which would also cause problems.
Good idea. I’m just building this in my spare time for a school too small to have student ID’s!
Yes, I had added a Split Text Column but didn’t know what to do with it.
I see the results of the relation, but not sure now how to get a count out of it for the number of hits in the relation. Here’s what I see in my Student table, where I have built a relation to the Attendance table.
oh, I should have clarified. When I referred to StudentID what I really meant is RowID. I assume that you have a RowID column in your Students table?
(I have a habit of renaming my RowID columns because I use them in every table, so I prefer them to have a name that’s more easily associated with the table they belong to).
AH, gotcha. Yes, I do normally have a Row ID, and normalize my tables by using that instead of the name. In this particular case, it’s a hack-job, and I started with a Google template that (you saw yesterday), I tried to force into Glide. So I never added a Row ID. After I get this working, I’ll go back and clean it up. The Student sheet especially. Thanks again!
Your relation shows that it had 10 matches, so I assume that the rollup value was 10? Your screenshot above shows you looking at the actual related row, and more specifically, only one of those related rows. You can view the entire related rows through a relation without having to switch to the other table to look at them. You may have been confused by seeing the Split column in that related row.
The student name is relating to every row that contains that matching name in the Split array. The rollup will count the number of matching rows. Not the number of names in the array.
