I am trying to set up a report that uses a specific date range to return a result. I want he date range that the report to use to be generated by a selection made in a drop down from the source records. So for instance, if the field "Billing Cycle" is set to Monthly, I want the report to return records where the tasks dates are during the current month.
So how do I set up the field that reads the "Billing Cycle" field to return the current Month if "Monthly" is selected from the dropdown?
So let's say that you have a setup here you have some Tasks stamped as Monthly, some as Quarterly and some at Annually as for their [Billing Cycle].
Your definition of the report filter for tasks stamped as Monthly seemed t be to include them if the date was "During the current Month".
Is that the same with the Quarterly, ie the task date is within the current Quarter? So, for example it is now February 2016, so the current Quarter is between January to March of 2016 and the current year is during 2016? Is that what you are after but all on one report? perhaps grouped by [Billing Cycle]
[Task Date is during the current Billing cycle]
not tested for syntax, so if it does not pass the syntax editor, please post your formula and the error message .....
This formula uses formula variables http://www.quickbase.com/user-assistance/Default.html#formula_variables.html?Highlight=formula variables
here is the formula. The formula for Quarterly could be wriiten more tightly but I will do it the brute force way.:
var number M = Month([Task Date]);
var number T = Month(Today());
Case ([Billing Cycle],
"Monthly", FirstDayofMonth(Today())=FirstdayOfMonth([Task Date]),
"Quarterly", Year(Today()=Year([Task Date])
($M=1 or $M=2 or $M=3) and ($T=1 or $T=2 or $T=3)
($M=4 or $M=5 or $M=6) and ($T=4 or $T=5 or $T=6)
($M=7 or $M=8 or $M=9) and ($T=7 or $T=8 or $T=9)
($M=10 or $M=11 or $M=12) and ($T=10 or $T=11 or $T=12) )