1 2 Previous Next 18 Replies Latest reply on Apr 19, 2011 11:53 AM by BTimm

    Recursive Function 101: cumulative totals

    BTimm

      Title

      Recursive Function 101: cumulative totals

      Post

      I'm new to recursive custom functions, but sounds like this might be best solution to this simple challenge:

      I have a series of records, with RecIDs 1 through 6, each of which has a separate Total (number field). What I want to do is have a field for each that shows the cumulative totals based on the order of the RecID.

      Basic calculation won't work without the recursion involved, if I have a relationship based on the PreviousRecord (recid - 1). I had thought I could stack the totals by adding total plus total of the previousrecord. I want this to be a field, not something that needs regular updating, so custom function with recursion seems like best path, but trying to get my head around the basics. Wondering if this is so basic that there is a custom function out there already or something else obvious I'm missing.

        • 1. Re: Recursive Function 101: cumulative totals
          philmodjunk

          Have you tried using a calculation with GetNthRecord?

          Perhaps:  GetNthRecord ( Total ; Get ( RecordNumber ) - 1 ) + TotalForThisRecordsFields

          Note that Get (RecordNumber) and Get ( RecordID ) return two distinctly different values. RecordID is an internal serial number assigned when the record is created or imported. RecordNumber is based on the current record's position in the current found set.

          • 2. Re: Recursive Function 101: cumulative totals
            BTimm

            The records in question would be in a set of related records, all sharing one id from a separate field, so order is relying on something like this:

            RecID
            Total
            GroupID
            GroupOrder

            So from any number of records, I want to generate the cumulative sum total of records related by GroupID, but cumulative as it works through the GroupOrder.

            Real-Life example would be a sales database with people and dates. And wanting to get cumulative sales at each given sale date, the total of all sales for that customer up to and including that date.

            • 3. Re: Recursive Function 101: cumulative totals
              philmodjunk

              In that case just define a summary field to compute your total sales and use a sub summary part "when sorted by" your groupID field.

              Here's a tutorial on Summary Reports with Sub Summary parts:  Creating Filemaker Pro summary reports--Tutorial

              • 4. Re: Recursive Function 101: cumulative totals
                BTimm

                That would work fine if I was just trying to display on a single layout. Goal is to get a field that I could call or export from anywhere that would still report the cum_sales info for that sale record.

                Might be something workable off of this example:

                http://fmforums.com/forum/topic/50966-subtotal-a-portal/ Not the solution exactly, but defining a calc by a start and end range with start = first value in order list and end being current value.

                • 5. Re: Recursive Function 101: cumulative totals
                  philmodjunk

                  I still don't think that you need a start and end range for this as you have a common value for all records in your group.

                  Most sales records rely on two tables: Invoices----<LineItems

                  with individual items listed in LineItems and a single Invoices record for each sales transaction. A portal to line items is used to record each item purchased in a separate but related line items record.

                  Sum ( LineItems::LineItemCost )

                  Is often defined in the Invoices table and used to compute the cost of all the items in the invoice.

                  A similar set up here could be used to compute the sub totals for each of your groups of records. It can even be set up from a self join where both sides of the relationship refer to the same data source table.

                  • 6. Re: Recursive Function 101: cumulative totals
                    BTimm

                    Problem is not the concept of the sum itself but how to create the selfjoin relationship based on a variable. I don't want the total of the whole group, but a progressive, cumulative subtotal based on the records in the given order. 

                    • 7. Re: Recursive Function 101: cumulative totals
                      philmodjunk

                      Before I flounder around with this any further, I'd like a more complete picture of what you are trying to do here. That will make it much easier to evaluate and suggest methods for doing this. Please post an example of what you are trying to accomplish here.

                      • 8. Re: Recursive Function 101: cumulative totals
                        BTimm

                        OK, well, working from the example above:

                        ---------

                        The records in question would be in a set of related records, all sharing one id from a separate field, so order is relying on something like this:

                        RecID
                        Total
                        GroupID
                        GroupOrder

                        So from any number of records, I want to generate the cumulative sum total of records related by GroupID, but cumulative as it works through the GroupOrder.

                        Real-Life example would be a sales database with people and dates. And wanting to get cumulative sales at each given sale date, the total of all sales for that customer up to and including that date.

                        ----------

                        The field I ultimately want to add is CumTotal, which would be the sum of Total + Totals of every record that a) is related by GroupID and b) where GroupOrder is < or = to the GroupOrder of the record in question.

                        Desired Result:

                        GroupID Total CumTotal
                        1 6 6
                        2 6 12
                        3 12 24
                        4 9 33
                        5 12 45
                        6 12 57
                        • 9. Re: Recursive Function 101: cumulative totals
                          BTimm

                          So to circle back, where I started with this was exploring recursive custom function so that I could generate:

                          CumTotal = Total + CumTotal from previous record.  Recursive custom function to enable stacking of the subtotals.

                          Right now, I'm thinking of bagging the whole requirement to have it as calc field and working up more of a layout required solution, where I could trigger a script to refresh the stacking subtotals anytime layout is loaded.

                          Still curious, sure there must be custom function solution, just not familiar with them.

                          • 10. Re: Recursive Function 101: cumulative totals
                            philmodjunk

                            Is RecID a number field that numbers memers of a group sequentially?

                            If so, define a calculation field, cPrevRec as:

                            RecID - 1

                            Define this relationship:

                            YourTable::GroupID = YourTable2::GroupID AND
                            YourTable::cPrevRec = YourTable2::RecID

                            Now you can reference the total from the previous record of the same group. The catch here is that your numbering has to be perfect, no gaps. This added complication is why we usually try to avoid this scenario by using Summary fields with the running total option enabled if it is at all possible.

                            Hmm, if there are gaps in the RecID, this relationship can probably be made to work, but it may be slow to evaluate:

                            YourTable::GroupID = YourTable2::GroupID AND
                            YourTable::RecID > YourTable2::RecID
                            (Sort relationship by YourTable2::RecID in descending order so that this relationship matches to the record of the same group with the largest RecID that is less than the YourTable::RecID value.)

                            • 11. Re: Recursive Function 101: cumulative totals
                              BTimm

                              Yes, I do have a field that will work consistently as sequential order for members of that group. And also can reference the prev RecID within that group.

                              But do I not still have the problem of recursion if I am trying to setup CumTotal as a calc field?

                              CumTotal = YourTable::Total + cPrevRec::CumTotal   in theory would work if I could create the custom function for it.

                              • 12. Re: Recursive Function 101: cumulative totals
                                philmodjunk

                                I don't quite follow what you mean be "a problem with recursion" here.

                                No custom function is needed.

                                and the expression would be:

                                CumTotal: YourTable::Total + YourTable2::CumTotal

                                Try it and see.

                                I do use custom functions and they are almost always recursive, but trying to access more than one record can be tricky and this problem here just doesn't require a custom function.

                                • 13. Re: Recursive Function 101: cumulative totals
                                  BTimm

                                  Sorry, I don't see how I can do this without recursion.

                                  FM will not let you define calc expression for CumTotal, with reference to same field on the YourTable2 relationship.  Just generates ?'s using the expression you and I both generate.  

                                  I can send you a small sample fp7, just seems like I cannot generate attachment here

                                  • 14. Re: Recursive Function 101: cumulative totals
                                    philmodjunk

                                    No need, I created a small demo of my own.

                                    I would have thought that YourTable2::CumTotal would evaluate as 0 if there is no preceding record for the current group as Null evaluates as zero in most other expressions of this type.

                                    When I modify the expression as:

                                    Total + If ( Not IsEmpty ( Yourtable2::RecID ) ; YourTable2::CumTotal ; 0 )

                                    It evalutates successfully.

                                    1 2 Previous Next