Help designing spredsheet/Glide - Curriculum with many user

I would like some help designing my spreadsheet and Glide properly for the following situation:

  • I have a Curriculum where by my salespeople need to complete a task and mark it “complete”.

  • The Curricuum has over 120 tasks (the one I’m sharing is just a small sample).

  • There are about 60 reps that will go through the Curricumum.

  • I know how to use the “sign in by email” feature and how to add a column with a user’s email to filter that row’s data to that user.

  • My question is, with so many taks in the Curriculum, do I need to create a massive table with every curriculum task duplicated for each rep, or is there an easier way?

  • My sample spreadsheet is here: https://docs.google.com/spreadsheets/d/1U3LfOZxEucwSiy0CobU6MCySpdnIjXDbPxmoSeojsQ4/edit?usp=sharing

  • Attached are some screenshots of my test app. The user-filter is not added yet on this spreadsheet.

thank you!

Do you need to do anything with the data once the task is marked done, or is it more of a behind the scenes log of completed tasks? One idea I have is, rather than using a switch, you could use a form button. Once the task is complete, the user could tap on the button, then flip a switch, then submit the form with email, date, relevant task information automatically included from column values and special values. This could be written to a new sheet. Not as elegant as what you are attempting, but it would prevent you from creating duplicated tasks for each user.

I have somewhat similar scenario going on. In my case, different events in calendar all refer to same list template of tasks to do (or buy). In the list sheet, column titles are items that have to be complete. Every event has it’s own row with values ‘false’ as default.

User then opens the event from the app, sees all info of and opens the todo list for the event. All items are listed with switch component. Clicking switch marks items completed, one by one, into that event’s row.

So, I’m adding rows with true/false values for and with every new event. Don’t know if this helps anything, perhaps just one way to see the same thing.

In your case, my event would be your sales people, and columns would tell what task they have completed:

-----------task 1-------task2-------task3
Mark-----false---------true---------false
Anna-----true----------true---------false

I have a similar project right now where task monitoring is one of the tabs. This is for my class with about 25 students and the tasks are the same for all but I want to show each student (per user) the tasks they have completed and the ones they haven’t. Since I also want to know when the task was reported as completed by the user, I am using a form button for each task which writes to a new sheet the user email, task name, status as done and datestamp (this is what @Jeff_Hager is suggesting). In my Tasks sheet, I have all the tasks listed in rows with due dates and I have columns for each user email (just the reverse of what @Juha has done) . I am able to extract the form submission info from the FormResponses sheet using INIDEX(MATCH) and fill the appropriate cell in the Tasks sheet with TRUE for each user who has completed a task.

My problem is, how do I display the per user data so that a user gets to see a list with both tasks that are completed and those that are not. If a completed task is not displayed as such, the user may not recall if they reported it as complete already or not.

It is just a one-and-done completion. No data is manipulated after the fact. However, I don’t know if I want to have the user submit a separate form for over 120 tasks. In addtion, one of the benefits of using the switch, is that once a task is completed I can filter it out of the List, so that the user only see the non-completed tasks.

That’s a good idea. I too thought about create a separate table just for the status of completion. Like you showed above. However, how would I display this in the Glide App properly? The description of each task is coming from one table, and the completed status is coming from another sheet…

That was my question about this method as well, how to display the content from the two tabs within one section of Glide (the tasks and the completion status).

I agree with you there. A separate form is not very elegant. I know there has been talk about a form button that would blindly submit without opening another screen. I’m not sure if that’s in the works or not. I’ll have to think about this a little bit. One idea would maybe be a play on @TSam idea. Maybe a detail view with a link to each curriculum alternating with a switch. The user would view the curriculum, then when they come back to the detail screen, they would flip the switch. Not my best idea, but I’ll see what I can think of.

thanks!

Ok. So, I’ve followed your lead and thatof @TSam and create a Form Submission that goes to another sheet/tab that records the user (email), and the module # (the tasks of the curriculum).

  1. is it possible in the Form View is it possible for the user to see the email that they’re logged-in with? (Just to make sure it’s the correct user, and also not a “blank” user).

  2. Can you share with the index/match function you use?

thanks

So, I have a Tasks sheet where for each task (in rows) there are columns with email addresses of each user. The INDEX/MATCH formula goes in the cells in the email columns of each row.

My FormResponses sheet has User Email as special value reported in column B and the switch in the form is recorded in column E (Status).

The formula in each cell in Tasks under the email columns checks if that user’s name AND the task in that row both occur in the FormResponses sheet. If they do, the cell will show TRUE. Here is the operational part of the formula. I have wrapped it with an if(iserror) on my sheet so that it displays FALSE instead of the #N/A error.

=INDEX(FormResponses!$E$2:$E, MATCH(1, (FormResponses!$C$2:$C=$B3)*(FormResponses!$B$2:$B=J$1), 0))

FormResponses$E2:$E is the status of the switch (True if checked). MATCH is checking for the task and user email.

THANK YOU very much! That was very helpful