Filtering on dashboards?

firestorm

hello @Paul Newcome

Thanks for the help. I realized that I have to create another checkbox column for the date search as I want to give the users an option not to input everything fields to search for the results, i.e. they can either search by dates, or location, or member, or category.

1st checkbox column [Date Range Search]:

=IF(AND([Start Date]# = "", [To Date]# = ""), 1, IF(AND([Start Date]# <= [Date Received]@row, [To Date]# >= [Date Received]@row), 1))

Hence, if the dates are empty, then it will still check the box as it means that user is searching for all dates.

2nd checkbox column [Other Criteria Search]:

=IF(AND(FIND([Member]#, [Member]@row) > 0, FIND([Location]#, [User's Location(s)]@row) > 0, FIND([Category]#, [Alert Category]@row) > 0), 1)

I didn't use the OR(CONTAINS) option eventually as I don't want to allow for multiple search within the same field.

3rd checkbox column [Final Results]:

I created another helper checkbox column to check results with both checkboxes ticked.

Thank you for your help!

Paul Newcome

@firestorm Happy to help. 👍️

Pablo Garcia Raposo

Hello @Paul Newcome

I hope you are fine.

I am a Smartsheet full license user, I have found this thread as I am handling a similar challenge for my company.

Unfortunately I could not understand well all the cases and the solutions proposed as I am not so technical person. Being that said, I decided to write the specific case of our challenge and I would really appreciate you help on the topic.

We are creating a Video repository in 1 smartsheet divided in different columns (Type of account, languages, type of video, type of content, tittle of video, link, etc…) Some of the columns has a single option dropdown menu and others will be fill manually.

At the moment we are using multiple sheets as a work around but maintenance may be a challenge in the future.

Now here is the point, we have discovered the Dashboard functionality. Let say have a dashboard with 4 pictures, each of the correspond to a different video category, so by clicking in a specific picture I want to show just partial information from the Video repository smartsheet mentioned above. (this 4 pictures contains sub- dashboards, but I guess knowing how to fix the first main dashboard it will apply the same rule for the rest).

Would it be possible to do such thing?, in case not what are the alternatives?

If it is easier, we could setup a MS team call and I can share my “work in progress” dashboard.

Thank you in advance

Best Regards

Pablo Garcia

Andrée Starå

Hi @Pablo Garcia Raposo

I hope you're well and safe!

I'd recommend using multiple reports or filters in the main sheet.

You can then combine them so when you click on the picture in the Dashboard, and it would either open another Dashboard with the filtered report/sheet or open the sheet/report instead.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Pablo Garcia Raposo

Hello @Andrée Starå

Thank you for your reply.

Thank makes sense, using reports or filter.As I mentioned I am bit new in the Smartsheet so I would like to understand on how to connect the dots you mentioned above, is there any video explaining that you mention and aplying this filters/reports to the Dashboard pictures?

I am not sure how it works with the SS community but I would gladly jump on quick call with you on MS Teams or any other platform to have a quick training on this. I guess sometimes is easier to show than trying to explain :)

Thank you in advance

Best regards

Pablo Garcia

firestorm

Hello @Pablo Garcia Raposo

I agree with Andre that reports will generate the outputs that you need. You can refer to this link to learn about "Reports".

https://help.smartsheet.com/learning-track/smartsheet-intermediate/reports

BJS

I have a few questions of @Paul Newcome from his post early last year that included:

"This does have the drawback in that it will only show THE most recent request and not necessarily YOUR most recent request."

If the filter "form" required login, could I somehow use look the most recent request by the "current user"? I am unable to find a variable to be used in a formula for "current user", so not sure if it's possible.

If not, can Dynamic View be used in this way?

Lastly, is the only way to add a sheet directly to a dashboard to publish the sheet and then use the Web Content widget? Because we are maintaining our SmartSheet account as HIPAA-compliant, we have disabled the ability to publish sheets, so I'm hoping there is another way . . .

Paul Newcome

@BJS The only way to leverage "Current User" would be to use reports which is possible but requires that everyone has at least viewer access to everything.

Dynamic View is an AMAZING tool. Users do not need to have access to any underlying sheets/reports and can still view the data so long as they are shared to the Dynamic View (which can be set to "Current User" as well). You won't be able to pull any data for charts and whatnot from a Dynamic View, but it can be displayed on a dashboard using a Web Content widget without having to publish anything.

cbredehoeft

@Paul Newcome I am also interested in applying a filter to a dashboard of our contacts database. In my case, I am looking to add a search filter to find the persons name, affiliation, email, project name etc. This will also be used to confirm if the person is already in the database or needs to be added. My question, in your example above you use the created date column that drives the formula in the summary sheet. Is that necessary for any filtered form or just in the scenario you were speaking to? If not, what do you recommend?

thanks so much!

Paul Newcome

@cbredehoeft You do not have to use the Created Date column type. There are a number of ways you can flag the most recent entry, but you do need something that will identify the most recent form submission.

cbredehoeft

@Paul Newcome OK, can you explain why I would need to ID the most recent submission? I am trying to create a filter to search if someone has already been entered into the database. Ideally, I can search on multiple fields, Name, Affiliation, project etc. When they entered into the database does not matter to me so want to understand the purpose. thanks so much for always replying so quickly!

Paul Newcome

You have to be able to identify the most recent search criteria. New search criteria is entered via form.