1 2 Previous Next 27 Replies Latest reply on Feb 3, 2014 3:53 PM by hanstrager

    How to make a total based on parameters.

    hanstrager

      Title

      How to make a total based on parameters.

      Post

           I have made a really basic database for keeping track on receipts. 1 table and 3 layouts, so nothing fancy - since I'm still in the filemaker learning process.

           On every receipt record there is different fields. one for 'date', 'season', 'description', 'category', and 'seller'. Now I just want to make a layout so I can keep track on how much im spending at each 'season' in different categories. So fx. a calculation that will calculate how much total have I spend at: 'Winter season' in the 'sports' category. How do I set this up? tried all sorts of things, imagine it to be quite basic, though it haven't really worked out for me.?

            

            

        • 1. Re: How to make a total based on parameters.
          philmodjunk

               The simplest is to set up a summary report where a single summary field can compute a sub total for each category. You perform a find for the records you want for your report--such as all transactions for a given time period, sort your records by Category and then you can place that summary field in a sub summary layout part "When sorted by Category" to calculate the sub total for that category. Put that same field in a footer or grand summary layout part and you'll get a grand total from the same field.

               This uses a List View layout and computes values from a found set for that layout. Other methods, that are a bit more complex can also be used in situations where you can't use such a layout design to get the subtotals.

          • 2. Re: How to make a total based on parameters.
            hanstrager

                 But if it performs a find then it's not possible to show more expenditure from different seasons at the same time. In order to see the difference, and always have your totals on the fly..?

            • 3. Re: How to make a total based on parameters.
              philmodjunk

                   Why couldn't the find include more than one season? (You can sort the records by season, then by category or by category, then by season....)

                   Can you describe what you want to see in more detail?

                   And please note that there are other options, I've started with the approach that is simplest and easy for me to describe and for you to implement. Other options are also possible.

                   But a more detailed description of what you want to see on the screen would be needed in order to select a different approach that works for what you want to see.

              • 4. Re: How to make a total based on parameters.
                hanstrager

                     I have a small sorts club.

                     There's 2 different seasons a year. Spring 2013, Autumn 2013, Spring 2014, Autumn 2014... etc
                      All receipts will have a season attach to them.

                     Then there's a set of different categories: Travel, Equipment, Food, medical...etc All receipts will have a category attached to them.

                     Sometimes an expense has been paid/given by external people and not paid by the sports club. So all receipts has a payer attached to them (such as 'paid by club' or 'donation')

                     I have in a list view layout with summary fields at the bottom and various pull down lists and radio buttons, to sort my receipts into whatever needed. But if i have to hold 'travel expenses' from Spring 2014, against 'travel expenses' from Spring 2013, Autumn 2013, Spring 2014, Autumn 2014. I have to sort my receipts 4 times, every time make sure I'm sorting the right categories etc.

                     So I thought I would try to make a layout where there will be row for the seasons and rows for categories. So i'll always be able to go that layout and immideiatly be able see what the current 'travel expense' and also see what the past season's travel expenses are, without physically sorting anything. and also see how much money have been donated each season aswell. So I guess I'm looking for making an overview over all my receipts.

                      

                • 5. Re: How to make a total based on parameters.
                  philmodjunk

                       I have to sort my receipts 4 times, every time make sure I'm sorting the right categories etc.

                       I don't see why you need more than one sort order for this layout.

                       I'm not sure which layout design you want to see. Both of the following examples are possible. Only one would use the summary report method that I described originally:

                       Summary report:

                       Travel
                             Spring 2014:   $2,000.00
                             Autumn 2014:        800.00
                       Equipment
                              Sprint 2014
                              Autumn 2014

                       and so forth... (This is the easiest to set up)

                       Season         Travel        Equipment     Food      Medical... and so forth
                       Spring 2014  $45.00           $900.00   $45.00      $300.00
                       Autumn 2014  ---> and so forth

                       This is not a summary report and you have a built in limit a to how many categories you can fit on the screen or page, but it also makes it easy to compare as many different season subtotals in the same report.

                       Let me know which you prefer and I'll describe that result in more detail. If you don't want to wait for that, the second option is called a "cross tab" report that you can research to learn more and here is tutorial on summary reports if the first option is one you want to try: Creating Filemaker Pro summary reports--Tutorial

                  • 6. Re: How to make a total based on parameters.
                    hanstrager

                         it seems like I need the "cross tab" report. As far as I can understand on the internet is I need a Calculation field and a summary off the calculation field. I think, I just can't really get my calculation to work out.

                         On my main receipt template I got drop down list for season called 'season' and it has a drop down list with 'summer 12', 'Winter 12', 'Summer 13'... 'Summer 14'

                         And then I got a field called 'Category', it has a drop down with 'overheads', 'equipment'...'travel'

                         I guess my problem is to get the calculation to figure out how to sort out what receipts are grouped together based on season and category..?

                    • 7. Re: How to make a total based on parameters.
                      philmodjunk

                           There are several ways to produce a Cross Tab report. Not all use the calculationfield/summary field pairs that you mention.

                           In the following example, I'm assuming that you have a single table, called Transactions, with these fields:

                           Season (Text, populated by value list to be Season name and 2 digits for year)
                           Category ( Text, populated by value list: Travel, Equipment, etc )
                           Amount ( The dollar amount of a single expenditure)

                           There can be any number of records used to log the expenditures for a particular category and season.

                           Here's the filtered portal method for producing this cross tab report:

                           Make a duplicate table occurrence of Transactions called Transactions|SameSeason and link it like this in a relationship:

                           Transactions::Season = Transactions|SameSeason::Season

                           Define a summary field, sTotalAmount to compute the total of Amount.

                           Set up a new layout based on Transactions to be viewed in List view. While in Layout Mode, change the Body layout part into a sub summary layout part "When sorted by Season". This will reduce your report to a single row for each season so long as you sort your records by the season field.

                           Put the Season field in this layout part to serve as the first column of data. Then add a one row portal to Transactions|SameSeason next to it to serve as the second column. Specify this portal filter for it:

                           Transactions|SameSeason::Category = "Travel"

                           Put sTotalAmount in this single row portal as the only field.

                           Now make copies of this one row portal and place each copy next to the previous copy, but edit each new portal's filter expression to specify a different expense category.

                           Now you can perform a find for the season's that you want and sort the resulting found set by Season to get your report.

                           Note: The resulting rows may not sort into the order that you want as Autumn 14 will sort before Spring 14, but do it this way to see if you can get things to work. Then we can set up a calculation field that rearranges the text in your season field and use it for sorting (and for your "when sorted by sub summary specification) to get the results that you want.

                      • 8. Re: How to make a total based on parameters.
                        hanstrager

                             I can't get it 100% to work.

                             I've made a Table occurrences of my Transactions named it Transaction|SameSeason

                             I linked the season field in both occurrences together

                             I made a summary field called sTotalAmount having it make a total of "net amount"

                             Then I made a new Layout based on transactions, making change the body into a subsummary sorting by season (from transaction table) - and set it to 'print below'

                             Then I made a Portal, showing related records from Transactions|SameSeason. It is set to filter portal records: Transactions|SameSeason::CATEGORY = "Travel"

                             the one field that's in the portal goes to Transactions|SameSeason::sTotalAmount.

                             Then I switch to browse mode, but nothing comes up..?

                              

                        • 9. Re: How to make a total based on parameters.
                          philmodjunk

                               Are your records sorted by a sort order that includes the season field?

                          • 10. Re: How to make a total based on parameters.
                            hanstrager

                                 I have another layout in a list view where I can sort all the reciepts, but I've tried show all records, but doesn't seem to help. It kind of worked when I didn't switch the body into a subsummary - except it was showing all my records...

                            • 11. Re: How to make a total based on parameters.
                              philmodjunk

                                   I did not suggest Show All Records. I am asking if you used Sort Records to sort your records by the Season field. If you do not sort your records by the "sorted by" field specified for the sub summary, the sub summary will not be visible.

                              • 12. Re: How to make a total based on parameters.
                                hanstrager

                                     That did the trick. Is there a way to build in every time i go to that layout it'll automatically sort my records by season instead of date, which I use on another layout..?

                                     And then so the seasons are sorted by 'seasons' and not the alphabetical.

                                • 13. Re: How to make a total based on parameters.
                                  philmodjunk

                                       The On Layout Enter trigger can sort your records by season each time you enter this layout, but there's a good chance that you really need a find script that both pulls up the desired records and that also sorts them by season. See this thread for examples of such scripted finds: Scripted Find Examples

                                       To get your seasons to sort in this typical order:

                                       Spring 2013
                                       Autumn 2013
                                       Spring 2014
                                       Autumn 2014

                                       Define a pair of calculation fields: cSeasonName, cSeasonYear.

                                       cSeasonYear : GetAsNumber ( Season ) // select number as the result type

                                       cSeasonName : LeftWords ( Season ; 1 ) // select text as the result type

                                       Sort your records like this:

                                       cSeasonYear (ascending)
                                       cSeasonName ( Descending )
                                       Season (ascending or descending works here)

                                  • 14. Re: How to make a total based on parameters.
                                    hanstrager

                                         Perfect it works, just made a button to 'show' summary and it sorts... Thank you so much

                                    1 2 Previous Next