Our Knowledge Base covers a wide variety of technical topics, from the basic to the complex.
How can I create a report that displays all the records based on a date field that equals a certain month and changes dynamically with the date?
By using a custom formula, you can create a report that displays the records where a date field in the record is equal to the current month (or previous or next month). If you want to base a view on the [Date Created] field value, then create a new report, choose Table report, and within the Filters section select the right button to enable filtering. Next, select <Custom Column>' 'is equal to' 'True.' This will return only the records where the formula you enter returns True.
Now you need to create the custom column. Under the 'Columns' section in the Report Builder, click 'Custom columns'. Once you've selected the columns you want to appear on the report, check the box 'Define a calculated column.' For Type, select 'Checkbox' and enter a Label of your choice to name this new column. The formula you enter in the box will determine the records that are evaluated to true, and subsequently returned. My example uses the [Date Created] value and returns all the records that were created in the current month, which we'll say is October. The formula I enter is:
If(Month(ToDate([Date Created]))=Month(ToDate(Now())), true)
This formula returns true only if the current month is equal to the month from the date created field. If you want to return records for the next month, you can modify the formula to add a month:
If(Month(ToDate([Date Created]))=(Month(ToDate(Now()))+1), true)
or the previous month:
If(Month(ToDate([Date Created]))=(Month(ToDate(Now()))-1), true)
Note: These formulas do NOT take year into account, so any record with the month equal to the current month, regardless of the year, will be returned. If you wish to match the year as well, you'll need to add a statement such as "AND (Year(ToDate([Date Created]))=(Year(ToDate(Now())))"