How do I build trending into my app?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have seen a couple questions similar to this, but the solutions given don't seem to work for me. I am trying to build a table for trending of summary information. For example, if I wanted to see how the total number of open orders changed from month to month.

The answer I have seen given is to create a summary report that is then copied to a history table.

The problem I am running into is that my summary report will not allow me to only group by columns. If I group by rows, when I try to copy the data to the history table, it wants to make each row a record, rather than making a single record with each row a field. If I use crosstabs and group by columns and use a "today()" formula for the row, it doesn't give me the option to import the resulting report to another table. What am I doing wrong?

Photo of Josh

Josh

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Can you tell me specifically what information you are looking to record at the end of the month.  What would be the names of the fields, for example that you are trying to record?
Photo of Josh

Josh

  • 0 Points
I am tracking approximately 200 unique tasks that are have a formula field called "Item Status" that is either "open" "closed" or "past due". I want to be able to have a count of each status on the first of each month so we can track the progress.
So are you looking to record just those three numbers and the date the history was captured?  If so, then do a summary report to group Rows by Status and also a field with a formula of Today().

So the report would look like

Status  Count Date
Closed 100 JUN-30-2016
Open 250 JUN-30-2016
Past Due 38 JUN-30-2015

Yes, you will be copying three rows of data to your history table, but then for your report for the history table,  do a summary report with the rows being the [date] and grouped by Month and the cross tabs would be the Status.
Photo of Josh

Josh

  • 0 Points
Thanks so much! I wish there was a way I could buy you a beer online.
My pleasure, but I'm sure that if we look around hard enough "there must be an app for that".

Btw, you will want to set up a monthly subscription to that report to remind you to copy the history.

I also just thought of a whole different method which is in fact much better.

Make a single record in a table called totals by status.  It will be record ID of 1
Make what will be the reference field for a relationship on each detail record as a formula field with the formula of 1.

Make  the relationship between the two based on that field.

Do a summary of the # of open records, and similarly for the other two statuses.

So that gives you the three totals you are looking for.

Then make a URL formula button to add a single new record to your history table (which is what you initially wanted) with those three values.  So lush the button and it creates the history record.

Post back if you what to switch gears and need help with that URL formula button.