How to sum selected related records:

[gaby78]

Simple invoicing system with Parent table and LI table. In the LI table I have a txt field to indicate if the item is taxable. If taxable the user enters "T" otherwise leaves it empty. My problem is how (from the parent table) to sum taxable items only. I know I can create another TO of LI and link it to parent to obtain the total. I am wondering if there is a calc or custom function that can give me the total without resorting to an extra TO or extra fields in LI.

I have tried the TypeSumField cf listed in CF library on the forum but it does not work all the time.

Thks

[bruceR]

Simple invoicing system with Parent table and LI table. In the LI table I have a txt field to indicate if the item is taxable. If taxable the user enters "T" otherwise leaves it empty. My problem is how (from the parent table) to sum taxable items only. I know I can create another TO of LI and link it to parent to obtain the total. I am wondering if there is a calc or custom function that can give me the total without resorting to an extra TO or extra fields in LI.

I have tried the TypeSumField cf listed in CF library on the forum but it does not work all the time.

Thks

Since TypeSum should work, what problems are you experiencing and can you show the calculation you've used?

[gaby78]

Please see attached. Thanks for helping.

SumByTypeExpl.ZIP

[comment]

It doesn't work, because the custom function you are using expects the Type field to always have a value. Your "Taxable" field can be either "Yes" or empty.

Let me suggest a simpler approach.

TypeSumS.fp7.zip

[gaby78]

So simple, so effective :thankyou:

[bruceR]

Please see attached. Thanks for helping.

Your file works correctly and returns the correct value IF you recognize that there cannot be any empty values in the TYPE field.

The attached file includes a modified custom function that handles empty values in the TYPE field.

typesumexpl.zip