Creating a Dynamic Filter
In this community post, I'll demonstrate how to set up a dynamic filter in Softools, specifically designed to display records within the current month and year, you can build off of this method to create your own. This method eliminates the need for creating individual filters for each year and month or updating a single filter monthly or yearly. With our approach, you'll only need to do it once! For a video on how to do this please click here
Step 1: Choose a date field you want to filter on. This can be a project start date, action due date, or any other relevant date field. For this example, we'll use a task due date.
Step 2: Create a 'Date Now' field. Create a simple date field called 'Date Now' without any expressions.
Step 3: Set up a scheduled workflow. Create a scheduled workflow to run at the beginning of each day, as shown in the example below. Ensure that you or another admin user with access to all records is designated as the user.
Step 4: Apply a filter for relevant records Next, you'll want to ensure the workflow only runs on pertinent records. In this example, we're targeting open tasks with the status 'Red,' 'Green,' 'Amber,' or 'Unset.' Using the filter below:
[Status]='Green' || [Status]='Amber' || [Status]='Red' || [Status]='Unset'
Step 5: Update the 'Date Now' field with an Update Field action Lastly, within the workflow, add an Update Field action to refresh the 'Date Now' field you created earlier. Use the 'DateNow()' expression to achieve this:
DateNow()
Step 5: Create filter fields for month and year Now. In this example. The following expressions should be placed into individual bit fields. They first check if the record is not complete ('Black') or on hold ('Blue'), then verify that both date fields have valid dates. If the dates have the same month or year, the bit field is set to true; otherwise, it's set to false.
Year:
If([Status]='Black' || [Status]='Blue', false, if(isvaliddate([DateNow]) && isvaliddate([DueDate]), if(Year([DateNow])=Year([DueDate]),true,false), false))
Month:
If([Status]='Black' || [Status]='Blue', false, if(isvaliddate([DateNow]) && isvaliddate([DueDate]), if(Month([DateNow])=Month([DueDate]),true,false), false))
Step 6: Implement the filters in your reports Finally, incorporate these filter fields into your reports, either as workspace filters or base filters in the App Studio.
By following these steps, you can create dynamic filters in Softools that adapt to the current month and year, streamlining your record management and enhancing the efficiency of your workflow.
Workspace Filter
Report Base Filter
Please sign in to leave a comment.
Comments
0 comments