Summarize Data Within Table

  • 1
  • 1
  • Question
  • Updated 4 years ago
  • Answered

How do I summarize data, like in a report, for use within that same table? Can I use Variables (app wide variables) to do this? It doesn't appear that you can use formulas in Variables. Basically I want to filter data from records in a table, & use the number of records found in a formula within the same table. Is this possible?

Photo of Bataar


  • 0 Points

Posted 4 years ago

  • 1
  • 1
Photo of Jack

Jack, Champion

  • 50 Points

I am not sure I understand the specifics of what you are trying to achieve but understand that you need to use a variable in a report filter so will demonstrate below a crude example of doing this, hopefully you can apply this method to your particular circumstances.

You can use app variables as the basis for report filters. An example of this may be a current year set as an app wide variable, assuming you already have a date field you want to use (called [Date] for this example below).

Create an app wide variable called CurrentYear with no spaces and enter 2015 as the variable.

Then create a formula text field called [Current Year] with the formula [CurrentYear] this will now make the variable appear on every record within that table within the field [Current Year].

You then need to do another formula text field called [Record Year] to extract the year from your date field using ToText(Year([Date]))

Then in your report filter set where [Record Year] is equal to the value in the field [Current Year]

This will mean you report will only show records with a date in 2015 if that is what your variable is set to.
In order to summarize else or totals of records on the table these records will need to be a Child table in a relationship to a Parent table. So for example if you were try to summarize the number of tasks per Project, you would need a table of Projects and then set up a relationship work one Project has many tasks.

Once you have the relationship, at the at the left side of the relationship you will see a button to create a summary field. So you would use that to for example summarize the number of tasks in a Project but that summary count would now be at the project level.

But then, no problem, you would simply do a look up of the total number of Tasks for the Project down to the child table. That would mean that now each individual task of that project is aware of total number of Tasks for the Project and that field can now be used in a formula.
Photo of Bataar


  • 0 Points
That makes sense. So basically I just need a new table with 1 record which will be associated with all the records in the table I want the summary for. Make the summary for the new parent table & a lookup of that summary field to kick the data back to the child table. Perfect, thanks.
Yes, in some cases you only need one record and you create that one record in the new table and it will be record ID number one. Then in the details table you make a numeric formula field with the trivial formula of just


 Then make your relationship and use that formula numeric field which you might call link to summaries table as your reference field on the right-hand side of the relationship.

Then if you like you can do as many summary field that you like perhaps summarizing either all of your records or some of your records based on a particular filter. And then once again you just look up those totals down to the detailed records