Dashboards: Add/update data from a report OR apply a default filter from the underlying sheet

09/16/20
Answered - Pending Review

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:

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:

Figure 2:


Answers

  • StefanStefan ✭✭✭

    Hi @sedk,

    just a few thoughts 3).

    Overall it's a personal task list, right? For me this looks like a good case for the Card (Kanban) view combined with the calendar view within your sheet. In the card view adding new tasks and modifying the "completed" info is easy and fast. In calendar view you see your tasks and a filter to just show the tasks due in the next x weeks stays active.

    What I don't understand is why you build a dashboard on top.

    Anyway, as reports (not on a dashboard) can be used to update data, would it be ok, to open your report A) by clicking on the widget, update your data, save and head back with browser back?

    Hope this helps

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Hmm, fascinating. I'll be perfectly honest, I haven't used the Card view at all since my organization has begun to use Smartsheet. It does look like this would achieve what I am looking for.


    I was building a dashboard because I wanted an easy way to add new items (without having to go down to the very bottom of the sheet OR insert a new row at the top) as well as having the functionality to view the data in the calendar AND have it easily sorted/filtered. I wasn't aware that the Card view had such functionality and it does look like that will achieve what I am looking for.


    Thanks for the thoughtful reply! I'll give that a try :)

  • StefanStefan ✭✭✭

    @sedk, glad I cloud help in a way and respect for beeing open to different approaches!

    Greetings

    Stefan

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

Sign In or Register to comment.