Linking new entries to previous entries - tracking attendance
I'm trying to create an easier/faster way to track attendance at the multi-week classes that our health educators teach. Currently, they have to enter in static information every time they complete our outreach tool (like, address, timing etc) and I am trying to make it so that they only have to enter that information one time and from then on, they get an weekly update request that only asks date, location, and attendance.
- HE fills out form, has option between "New Class" and "On-going Class"
- If they select "New", all questions would populate.
- There would also be a question that asks, "One Time" or "Recurring"
- If they select "On-going Class", only date, which class, and attendance populate.
I don't think I can do an update request automation with conditions because I don't know which combination of conditions to filter for.. Is there any way to then connect new entries to previous ones without creating some complicated id or something? Is there any way to send an update request with most of the details already filled out and have that be a new row?
A few points which might make it harder/impossible:
- I can't create a new form, any changes need to be on the existing outreach tracker form I have
- Supervisors would prefer to avoid having HEs use a grid and would like it to be on the form
- The same class may be taught multiple times in a year at the same location
Answers
-
My org would love something like this too - something where all the rows of a sheet get listed for the attendance taker, and the attendance taker's answers for the day fill in a new column on the sheet, without having to see the sheet. I am curious to see if anyone has a good way to do something like this in Smartsheet!
-
Hi Courtney - it sounds to me like you have 1 big tracking sheet with a form, right? You can customize forms to show and hide questions/columns based on criteria. Do that by editing the form and clicking a field, then click Logic.
For example, if you have a question with the options "New Class" and "On-Going Class", then you can set logic to show questions for New Class that will appear on the form when New Class is picked. Same with On-Going Class.
As you noted, update requests don't add new data, they update existing rows. So, they are great if you want someone to simply update the latest information, but if you're looking to build a running set of information that's new each time a class happens, then I think you want to use a form instead. Forms add new rows each time they are submitted.
You can "hook together" the information with perhaps a dropdown picklist of the class names to choose from, then you can run formulas in your Sheet Summary fields or on a separate sheet, that collect information all about a single class across all the form submissions. Or you can setup a report that does the same and groups by class name to give you the information you're looking for.
If that doesn't help then reply and we'll continue to work on it.
I also found this template that you may want to peek at to get ideas on managing registrations for an event:
-
This sounds like something a little different. If you want to show someone part of a sheet to fill out, without having to show the entire thing, then there's two options:
- Create a report with just the columns you want someone to view and edit. You can edit data in a report, which edits it directly in the sheet. Note that editors still have to have edit rights to the underlying sheet, so there's nothing preventing them from browsing to it.
- If you need to lock it down, Dynamic View will do that. Click the Dynamic View menu on a sheet or report and create one. You can pick and choose the columns and row filters to apply, which the users cannot change. Dynamic Views are a web view that has a grid of information and a slide-out panel to edit that data upon. The permissions of the Dynamic View override the permissions of Smartsheet, so you don't have to share the sheet/report at all with people if you're using the Dynamic View. The drawback is that it's not "bulk edit" friendly, like the report/sheet is.
-
Hi @Brian_Richardson for attendance we are not currently using a Form at all, actually. We are using a Sheet/grid. There is a row for each student, and a column for each date of programming. After the attendance person is done entering attendance marks for the week, I go in and hide the columns and unhide the next week's columns, so that the attendance person normally only has to look at one week's worth of date columns at a time.
As you say, Forms would not work because I don't want to be adding new rows, I want to update a student's existing row with attendance marks. Since Update Requests are sent individually for each row, that is also not a good solution. -
Unfortunately I think the best solution is what you have going. If you have Bridge or an API tool, you can dynamically schedule automatic column hide and expose, but there's not a way to do that in the core app. For batch marking of column dates, best approach is probably a grid like you have.
I would still probably use a report instead of the sheet, though. It's faster to remove and add columns from a report than it is to unhide and hide columns in a sheet. Marginally :-) Reports also are nice for focusing your attendance folks on just the columns that matter, without you having to hide everything away on the sheet.
If you want to be really fancy you could change the attendance columns to a dropdown for Yes/No and then have the attendance person use Board View in the sheet. They can pick the date column as a Group, then do a quick drag/drop of each person's card into the Yes column for attendees. That way you don't have to hide columns, just have them focus on the right column to group on.
Help Article Resources
Categories
Check out the Formula Handbook template!
