Date picker on a dashboard view

AppSheet AppSheet Q&A
,
1

I am trying to create a dashboard where I can show 3-4 tables of data that are slices of a single table, but all for a single day that a user can change. I’ve read a lot (forum, help content, etc.), watched videos, and spent quite awhile using Gemini to try and figure it out, but I’m stuck. I would like to use USERSETTINGS() instead of creating a new table for dates; I would prefer to not write any data to a table, but I would like for users to be able to change the date and see different information on the dashboard.

It seems like I need to alter a row within the user settings table, which I have done, and set it to “show”. It seems like I also need to add a view where the date value can be altered by the user, but that’s the part that I can’t figure out how to do. I’m able to do it if I create a new “Date picker” table, but like I said I don’t want another table to which data gets written. I could have hundreds of people viewing the dashboard, and I want each viewer to be able to see whatever date it is that they want to see.

Can anyone offer tips or point to a definitive resource for how to set up something like this?

2

I would also be happy to produce a view like this some other way, where the dates on the left come from the data itself, and they can be clicked on in order to change what data is shown on the right?

Frame 12
Frame 12751×602 10.7 KB

Frame 13
Frame 13751×602 10.3 KB

3

The only way a user can interact with their user settings is through a form view. Form views cannot be included on dashboards.

To get the date picker view like you have pictured, you’d use a detail view of a pre-existing row in a new table, showing only the date selection column as a quick edit column. The column would be of type Enum of Date, with an input mode of deck and a Valid if expression that generates the list of dates available to the user. Your other slices would refer to the date selection column to filter the rows of their tables.

The problem is you don’t want another table. There’s really no way to avoid it, though.

3 Likes
4

Thanks for that. So it sounds like I should create a new table with something like “Date picker” as the column header. If that’s in A1 (Google sheets), what do I put in A2? Nothing? Ideally the view would default to the most recent date entry in the list of available dates. I had tried something along these lines with “=TODAY()” in A2, but I’m pretty sure that’s not supposed to work (putting a formula in Google Sheets and expecting it work nicely with AppSheet), and I can’t remember but I think it just returned a string in the app. Maybe I leave A2 blank?

5

I have created Dashboards exactly like that @Steve has suggested and they work very well. It is the ONLY way to go if you need to filter based on multiple pieces of filter criteria. If you will have multiple users accessing this Dashboard simultaneously, you will need to implement a structure that prevents one users filter request from interfering with another users view.

The way I handled that is to have the extra/utility table be a PERSONAL filter request - i.e. each user has there OWN Dashboard Filter row in this table.

The advantage of having a personal filter specification is that 1) no impact to other users 2) if the user navigates AWAY from the Dashboard and then back again, they will see the filtered results exactly as they were when they applied the filter.

One small issue is to figure out WHEN the personal filter row should be added. If you have a Users table, then you can include in the process of adding a User to also automatically insert a “default” personal filter row. If you don’t have a Users table, then you will need to get creative. You’ll want some way to add their personal filter row the very first time they access the Dashboard. There are a few ways to deal with that.

********** Another option….

If you need to filter ONLY by Date, you can use an Interactive Dashboard. With an Interactive Dashboard, you can get a visualization that looks very much like the example views you posted above.

This still requires an additional table used as a Parent table that collects all the dates of data entry. Each data table would also need include a REF column back to this Parent table - it could probably be used as THE Date entry for the data row.

Then build your Dashboard. Create a view to the Parent Dates table that show all the Dates. Create all your Slice views as you indicated above. Add all of these to a Dashboard View and turn on the “Interactive mode” setting. AppSheet will automatically make connections between your views based on REF columns.

Now, when you tap on a Date row in the Parent Dates panel, all the other Slice views will automatically be filtered with data rows with that Date specified in the Ref column.

With the Interactive Dashboard, you can avoid the small complexities of the personal filtering but the trade off is managing the dates of entry in a Parent table for the Interactive Dashboard.

I hope this helps!

3 Likes
6

Definitely helpful, thank you! In the second option, the one using the Interactive Dashboard, would I have to actually insert the dates (via an automation probably) into the table to get them to appear in the date picker? Or is there a way to compose that table from a formula? I guess my question is do I need actual entries in the table, or is it enough to have a Dates table with just a “Date” header for the column and no actual data in the subsequent rows (but somehow use a formula to populate the rows)?

7

No. You’ll want to add to the Dates table as you are entering data. There are a couple ways to do this.

  1. What I would do is create a Dates table with 2 columns (for now) ID and Date. Then in your data entry table change the Date column to a REF column to your new Dates table and set the “Is part of” property. This DOES change the way a Date is entered. It will now be a drop down of Dates instead of a Date Picker. If a NEW Date is needed, the user would tap “New” in the dropdown that will open a Form and use the Date Picker to choose the date, Save and that adds a new Date row.

Optionally, if the Date entered is usually TODAY’s date, you could create a grouped action that FIRST adds the Date row if it doesn’t already exist. This will ensure that the Dates are ALWAYS in the dropdown.

  1. If you prefer to use the DatePicker control to enter Dates, then you would need to add another column to use for the REF. When you entered the data row, add the DAte like you do now. Then on Save, use an action to lookup the Date row in the Dates table and assign that ID to the REF column. If it doesn’t exist, automatically add it and then assign it to the REF column. But this is all done with actions attached the Form SAve behavior. It is a little more cumbersome due to the creation of these actions.

Now, here is the good part of this setup….

You might need/want features in the future to SUM up values across these dates. You will already have a table to place them - this new Dates table. Just add a value summary column and sum up the child rows. If you do have a plan for that kind of feature you might name this new table accordingly.

Also if you wanted summary reports for Week, Month, Year, etc you just simply add columns for these derived from the Date, then you can create views with the data Grouped using these date component columns OR create expressions to easily pull summaries for these periods.

If you wanted to view data entries by certain dates, you already have the structure to easily do it.

The point is that this data structure makes it more flexible to slice and dice the data for viewing and reporting purposes as your app matures!!

2 Likes
8

Here’s a synopsis of what worked for me (for posterity)…

  1. I set up an app with two tables: Appointments and Dates.
  2. The Appointments table has a column called “Date of appointment” which is a ref column to the Dates table with “is part of” checked.
  3. The Dates table has two columns (“Date id” and “Appointment date”) along with the auto-generated “Related Appointments” column.
  4. I made a table view for the Appointments and a deck view for the Dates.
  5. I created a new dashboard view and added both the Dates and Appointments views to it.
  6. I set the dashboard view to interactive

Notes:

  • I did this without using slices. Using slices made things overly difficult for me, so I just skipped that part in this example.
  • I’m pretty sure I was getting screwed up at one point because I had a column named “Date”, which some part of my memory tells me is a no-no.
3 Likes