TypeSumField ( TypeField ; Type ; SumField ; Start )

[David Jondreau]

Name & Parameters: [color:red][big] TypeSumField ( TypeField ; Type ; SumField ; Start ) [/big]

Description: This function is designed to provide summary data for a subset of records.

For instance, in a table of Invoices and a related table of Line Items, where each Line Item can have a type of Food, Clothing, or Auto, it will sum the totals for each line item of a certain type (e.g. (TypeSum(LineItems::Type; "Auto"; LineItems::Total;0) ) without the need for a filtering relationship or extra fields for each Type.

This is my first published attempt at a custom function. I'd like any feedback and comments.

Thanks!

Sample Input:

Results:

Recursive: yes

Formula:

//This function will sum all SumFields whose TypeField = Type

//Start should =0 or blank

Let([



End = Count(TypeField);



counter = Start +1;



total =  If(GetNthRecord(TypeField;counter)=Type;  GetNthRecord(SumField;counter) ; 0 )





];





Case(counter







)) 

Required Functions:

Author(s) grip

Date: 03/20/07

Credits:

Disclaimer:

FM Forums does not endorse or warrantee these files are fit for any particular purpose. Do not post or distribute files without written approval from the copyright owner. All files are deemed public domain unless otherwise indictated. Please backup every file that you intend to modify.

[The Gardener]

Hello,

This function looks very useful. I have just been entering this function [never 'installed' a custom function before, took me a moment to understand how]. But I could not complete that as I get the message that an "operator is expected here" in the Case function part, the ( is highlighted immediately after TypeSumField. I did do a complete copy and paste of your function, so I know I have inserted properly and do have all the required parameters.

Do you have any idea what the problem is?

Also would it be possible to get a barebones demo of the function in use? I am a novice and that would help me along.

Thanks in advance

[David Jondreau]

Hmm. I don't know why you're having that error message. I just re-installed it on a test file using what I've posted and it works fine. Did you get the last to parens at the end? I wish I could be of more help in diagnosing your problem.

I've attached a tiny sample file of one way this function can be used. Maybe you can grab the custom function from it.

Invoice.fp7.zip

[bruceR]

Here is a modified version that works with both portals and standard found sets and also allows the user to select "type".

A global repeating field is also used for the found set example. Repeats are not good places to store regular data - but they can be very useful for reports and some other purposes.

invoicecfexample.zip

Edited April 15, 2007 by Guest

[Søren Dyhr]

Perhaps you should think about ways to freshen the values shown in the repeater - it's just as reluctant in freshing as Comments discovery on related summary fields, where a script must be flushing cache to disk.

I think we must realize that it's not the right tool for spreadsheet'ish dealings, hence the transaction model - or can you fix this one??

--sd

[bruceR]

I think this has to do with how FileMaker updates repeat calcs. The first row always updates fine. But I seem to remember some discussion of this elsewhere so perhaps there is a solution.

UPDATED: If you create a relation that includes Total as one of the fields in the relation, using an X relation, then it will update properly. See attached.

invoice_example.zip

Edited April 16, 2007 by Guest

[Søren Dyhr]

Execellent - Is this a new invention of yours, it's sort of a continuation of this:

http://www.dwdataconcepts.com/dl/tm/UPDATE_CALCS.sit

There is still a snag to it, you'd better put some clearing autoenters in the Quantity field, such as:

Evaluate(Case(0;0);Type)

Otherwise won't your repeater catch it, if the user regrets his/her original "type" choise.

--sd

[bruceR]

Try this: change the calc for line items cTotal as follows:

Left(Type; 0) &

Case(

IsEmpty( Quantity & Cost ); "";

IsEmpty(Quantity); Cost;

Quantity * Cost

)

[Søren Dyhr]

Yes thats the way if you won't risk the user clears his/her own previous entries. Right up the HUI street, never let the user do something irretrivable unless a dialog with yet another chance to regret, pops up.

--sd

[bruceR]

Here's an updated version that uses a standard calculated text field to display the results, which are updated intantly.

Invoice_example.zip

[David Jondreau]

Whoa. Just realized a major fault in this function.

If Type is empty for any of the related fields, an incorrect result may occur.

Corrected version below. Any input appreciated.

PS *******, jumped the gun. IsValid will return a negatory if the data is of the wrong type. Hmm... I think I can combine IsValid and IsEmpty in one logical statement to get the result I need. OK edited.

PPS Nope. I'll have to go back to the drawing board on this.

Edited October 27, 2007 by Guest

[bruceR]

Here is my version that handles empties in the TYPE field. Note that in this version you must pass the total record count to be evaluated in the Counter parameter.

/*

TypeSumField( TypeField; Type; SumField; Counter )

This function will sum all SumFields whose TypeField = Type

Counter should = count of related records or

Count of found set depending on use

You may specify "Total" to get total of all records

*/

Let([

total = Case(

Type = GetNthRecord(TypeField; Counter) ;

GetNthRecord(SumField; Counter) ;

Type ="Total";

GetNthRecord(SumField; Counter) ;

0 )

];

Case( Counter > 1 ;

total + TypeSumField(TypeField;Type;SumField; Counter - 1 ) ;

total

)

)

Edited July 21, 2008 by Guest

[krabbykabby]

I came across this post while looking for a way to count the number of related records with a particular value in a field. I don't completely understand how this function works, but it seems like the SumField parameter only applies if there's some field other than TypeField in the related records that you want to add up when TypeField=Type. I don't need that part, so instead I put 1 into the SumField parameter and it seems like that works (as in, so far it's returning the numbers I expect). I was just wondering if that is a legit way to cut out that portion of the script or if the calculation is by chance giving me the "right" numbers and I'm going to run into problems when I add more records. Also, if you know how this function works, would you mind breaking it down for me? Thanks!

[Angus McKinnon]

I know that this is an old thread, but I wanted to chip in and thank David and Bruce for their work on this function - it's solved what would have been a fairly major headache for me. 

(I'm doing a count of total spend by customers, but only orders that met certain conditions)  The only other ways I could think of making it happen would have been an ugly bodge.