5 Replies Latest reply on Oct 2, 2013 4:37 PM by philmodjunk

    Count Calculation (Summary Table)

    sarahbergquist

      Title

      Count Calculation (Summary Table)

      Post

           Hi, I'm trying to gather a running count based on parameters. Ideally, I'd like this to automatically update when new criteria is added to the db (e.g. new dates). 

           So, I have 1 field for TYPE (type1, type2, type3) and one field for DATE (10/02/13, 10/03/13, etc). For each record added to the table the date is automatically updated and the user inputs the type.  I would like to keep track of how many entries are added per day in a separate table using the count function in calculation. 

           My final table would look like this: 

           TYPE     DATE         COUNT

           type 1     10/02/13     3

           type 1     10/03/13     5

           type 2     10/02/13     6

           type 2     10/03/13     2

           type 3     10/02/13     5

           type 3     10/03/13     6

           You might recognize this as being very similar to a pivot table in excel. If I am wrong to use the count function, please let me know.  I cannot figure out how to join the date fields and summarize by fields. (p.s. I am not interested in using subsummaries) 

           Thanks! 

        • 1. Re: Count Calculation (Summary Table)
          philmodjunk
               

                    (p.s. I am not interested in using subsummaries)

               You'll need to explain why you are not interested in that option as we may otherwise suggest a way to get what you want that also will not work for you. There are several ways that you can get that list of values.

          • 2. Re: Count Calculation (Summary Table)
            sarahbergquist

                 From what i understand the subsummaries are inside of the original table. I want to create an entirely separate table with a summary of the type, date and count. If I am able to accomplish this using subsummaries in a separate table, I'd be happy to do that too. 

            • 3. Re: Count Calculation (Summary Table)
              philmodjunk

                   But what problem is solved by creating that separate table? This adds a bit of complexity to your design and it will be a small challenge keeping it properly up to date so I don't recommend that you use a separate table unless there is a reason for doing so.

              • 4. Re: Count Calculation (Summary Table)
                sarahbergquist

                     Well we are going to be keeping track of our daily additions to the table (quota) using a tracking table (this summary table I'm trying to create). It's expected that hundreds of additions will be added daily, so I'd rather not have the summary in the original table. 

                • 5. Re: Count Calculation (Summary Table)
                  philmodjunk

                       I'm not convinced that's a reason to have a new table just for this purpose. The only potential issue I can see is the possibility of some delay while waiting for the summary field to update when you pull up your report.

                       You can easily use a script to perform a find that limits your found set to a date range of a recent number of days and then you can get the rows of data with subtotals as shown and without any significant delay due to limiting the size of your found set in that way. And a summary table that uses a relationship to count the records in your original table--which can be done, will also encounter much the same update load.

                       There are two different ways to set up such a summary table:

                       You could create one record for each value of type and date that has at least one matching record with that data in the two fields. An aggregate function or a reference to a summary field in the original table can compute and display the count.

                       Or a script can update a number field in the summary table each time some change takes place in the original table. You have to trap for adding records (Create new record if needed and increment number field by 1), deleting records (Decrement Number field by 1) and changing records (decrement number field in original matching record, create record/increment number field for new matching record.)

                       The first option is simpler and much easier to ensure that you have correct counts, but has the same basic "update load" that you would with a summary report (Hundreds of records a day isn't really all that much. We generate over a 1000 records a day and daily summary reports pop up with no visible delay on our systems.)

                       The second option makes for very fast screen updates--especially if you are dealing with a WAN and/or iOS client devices.

                       In both cases the basic relationship would look like this:

                       Summary::Date = OrigTable::Date AND
                       Summary::Type = OrigTable::Type

                       And if you enable "allow creation of records via this relationship" for Summary in this relationship, your scripts can use that relationship to create a new record in summary, but only if no such matching record does not already exist.