Dashboards: Add/update data from a report OR apply a default filter from the underlying sheet
I am creating a personalized task list dashboard that includes 1) A calendar showing when my upcoming tasks are due, 2) a form that allows me to enter a new task easily, and 3) the full list of my upcoming and complete tasks.
I am running in to an issue where I have to choose between 1) viewing the data in an easily understandable and sorted/filtered format and 2) being able to dynamically update the underlying data once the task is complete
Figure 1 shows the current layout of my dashboard.
A) is a report that pulls from a sheet that contains my underlying complete list of tasks (see Figure 2 below) that I have assigned to me. In this report tab I can 1) view my tasks on a calendar, 2) I can view the tasks in an easily sorted and filtered manner (complete NOT checked, sorted by due date), however 3) I canNOT update the underlying data in the sheet (to say that the task is complete or to change the due date).
B) is a form that allows me to easily add a new task to my underlying task sheet which then gets autoupdated in to the report/calendar (this is one of the major features I want in my dashboard or else I just end up needing to juggle 2 sheets)
C) is a published Smartsheet that is my underlying task list. In this tab I can 1) view my tasks on a calendar, 2) easily update the underlying data in the sheet (to say the task is complete), however 3) I cannot easily sort/filter the tasks (complete not checked, sorted by due date). NOTE: Yes, I am aware that it is possible to add a filter to this tab, however I would have to add it every single time I want to sort/filter the data in order to update the data
So, what are my proposed solutions?
1) (ideal) Allow users to edit the data in a report on a dashboard that would then flow back in to the underlying sheet. This would allow me to easily view a sorted/filtered set of data as well as easily update the underlying data when needed
2) When you publish a Smartsheet to use on a dashboard, allow for a default filter to be carried over from the underlying Smartsheet. This would allow for easy view of filtered data as well as easily update the underlying data when needed
Right now, in order for me to check that a task is complete in this dashboard I need to 1) sort through my complete list of tasks in the task list Smartsheet and check off the tasks that I have completed, or 2) every time I want to check a task as complete I would need to add a filter to the published sheet in order to find the tasks that I need to check off. When I have multiple tasks a day/many more multiple tasks a week/many weeks/months of tasks it becomes unwieldy to sift through all of them in this manner.
Workarounds that I've tried (or could try but defeat the purpose of asking for an update to the underlying code):
1) Only utilize the report to view and update tasks. This works if I don't want an easy way to add tasks to my list. This would require me to juggle the original task list sheet as well as the report, which defeats the whole purpose of building this dashboard in the first place
2) Create a second Smartsheet that is generated from a filtered version of the original task list Smartsheet that can then be pulled in to the dashboard as calendar/update-able task list. So, a dashboard referencing a sheet that references and filters and sorts another sheet? Yes this may work...but...need I say more?
3) Other thoughts?
For your reference, here is what the underlying task list sheet looks like: