8 Replies Latest reply on Feb 8, 2017 5:32 AM by kaotishe

    Running Total in list view of a table

    kaotishe

      Is it possible to have a running total in the list view of a table? I have set up a table to track work travel expenses and I would like for the list view to have a running total for the columns at the top. Ideally I would like for it to be at the top of each year so that when 2017 rolls over to 2018 all of 17 will have a total for 2017 and a new one will start for 2018. The other bit of information I would need in this section is year beginning and year end odometer reading with total miles driven for the year that I can then deduct the business miles driven from.

        • 1. Re: Running Total in list view of a table
          philmodjunk

          To start, define a field of type Summary and select the field that you want it to compute the "total of". Then select the running total option. Finally, select the options to "restart running total when grouped by" your year field. If you have only a date field, you will need to add a calculation field that returns the year from that date.

          • 2. Re: Running Total in list view of a table
            kaotishe

            I am not sure what I am doing wrong, but on the summary layout of the table it is not giving me one entry for 2017 it is giving me an entry for every entry from the other table - the only difference is that it does keep adding up the total... Also I do not see a way to add a calculation that returns the year from the date in the summary field.

             

            I tried just adding a separate field that would be year only and would calculate off of the date field using thisCalculation to change a date to only a year  - but it does not work and i have no idea why not. Any thoughts???

            • 3. Re: Running Total in list view of a table
              philmodjunk

              Your original post only describes one table. If there is more than one table, please describe them and how they are/should be related in your solution.

               

              There would be no date in the summary field. But if you have a date field in the table being summarized, you can set up a calculation field as Year ( YourDateFieldHere ) and that gives you a field on which you can group the data.

               

              it is not giving me one entry for 2017 it is giving me an entry for every entry from the other table

               

              This would be the expected default result of such a summary report. In what part of your original post did you indicate that you only wanted one entry for each year? That's possible, but not what you requested.

               

              If you set up a summary report layout as a list view and add a sub summary layout part "when sorted by" the year calculation field that I described in this same reply, you can put your fields in this sub summary part and remove the body layout part. You can then perform a find for the records that you want in your report (manual finds may have to be done on a different layout based on the same table occurrence) followed by sorting on the year field in order to group them by date.

              • 4. Re: Running Total in list view of a table
                kaotishe

                I apologize that that was not clearly stated. I have one table it contains [mile driven] [tolls] [parking] [hotel expense] and I guess I should ad [airline tickets] though I almost never fly. I have set this up to enter these amounts in daily in form view. Ideally I would like to have a list view that only displays year to date totals for these fields, and once a year has passed it will show that years total in one entry previous year in an entry before that and year to date as a running total. So totals 2015 totals 2016 year to date 2017... which in 2018 will become a total with 2018 being the year to date etc.

                • 5. Re: Running Total in list view of a table
                  philmodjunk

                  What you describe doesn't appear to be a "running" total so it appears that you can omit the steps that turn a summary field into one that computes a running total.

                   

                  I am still not completely sure how you want this to look like on your report.

                   

                  Do you want yearly totals for each year, period? (This year's total would then be a Year to date total...)

                   

                  Do you want yearly totals for each prior year, but the individual transactions for the current year?

                   

                  Do you want a single total of all years prior to this one with a year to date total for this year?

                   

                  Or ????

                  • 6. Re: Running Total in list view of a table
                    kaotishe

                    "Do you want yearly totals for each year, period? (This year's total would then be a Year to date total...)" Yes this is what I am asking for.

                    • 7. Re: Running Total in list view of a table
                      philmodjunk

                      Then I have already spelled out what you need to do:

                       

                      If you set up a summary report layout as a list view and add a sub summary layout part "when sorted by" the year calculation field that I described in this same reply, you can put your fields in this sub summary part and remove the body layout part. You can then perform a find for the records that you want in your report (manual finds may have to be done on a different layout based on the same table occurrence) followed by sorting on the year field in order to group them by date.

                      • 8. Re: Running Total in list view of a table
                        kaotishe

                        Ok, I think I finally figured out what you were telling me to do. Sorry I don't create filmmaker solutions very often so I really don't know my way around the software. thank you for your help.