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

# Recursive Function 101: cumulative totals

### 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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