How can I run a report for future dates specified by the user?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have constructed several reports which return results for today, as provided by Today() function.  I would like to use these same reports to forecast. How can I run the same report for future dates i.e. the user could view the report for next month during this month?
Photo of Brennen P

Brennen P

  • 0 Points

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
In general, you can have a report filter like
where [my date field] "is during'  the next 1 month(s)
Photo of Brennen P

Brennen P

  • 0 Points
There is no date field.  I want to run the report as if Today() returned a future date and I'm viewing analysis for next month without having to wait til next month to view it, not select records where some date field is in a range.
Photo of Brennen P

Brennen P

  • 0 Points
Basically the report is designed to return analysis based on the Month/Year of Today().  If the date is in this month/year, return analysis on this month's input values, if the date is some future date, return analysis on the input values for that date.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Are you doing calculations based on Today()?  is so, you would probably need an alternate set of calculations based n the month / year matching a field called perhaps [This Day Next Month]  with a formula of

AdjustMonth(Today(),1)
Photo of Brennen P

Brennen P

  • 0 Points
Would I be able to AdjustMonth(Today(),x) where the user is asked for X?  Ideally I just want the user to pick a date and see the data analysed as if today were that date.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
No, you cannot use the answer from and <ask the user> question and feed it into a formula.

But fear not, there is always a way.

Here is "a way".

1. Make Table called Set Month and make a single field in that table called [Month ahead to look].
2. Add a single record and save.  It will be record ID#.  While you are there set all Roles permissions so that no one, even you can add new records or delete, only edit.
3. On the details table of the make a numeric formula field with a formula of 1, called like [Link to Set Month].  Make a relationships to the Set Month table based on that field and lookup the date to your details table and you it in your formula.
4. now a magic next step is to put a report link field on your [Set date record] to (all) the records in your details tales based on the link field.  Then put your report that you are trying to run as the report specified on the Set Month form to use to display that report link field.

So, as soon as you save the months ahead record, the report will refresh right on the Set Month record that you are sitting on.
Photo of Brennen P

Brennen P

  • 0 Points
Thank you!  That's very convoluted for what I thought would be a simple task, but it sounds like the end-result is what I am looking for.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
yes a bit convoluted to set up but it should actually be quite a good user experience once its done,as the report will just be there when they save.