I need to sum values from a field (A) based on the value of another field in a different table (X). I can do this by "finding" X=1, sum A

but I want this in a calculation. In fact I need to show three values in one place

if X=1, Sum A

if X=2, sum A

if X=3, sum A

can someone show me a calculation that will do this?

Well your question isn't particular easy to get the point in, so I've made a stab at what I think you're after, utilizing contemporary filemaker approaches....

--sd

test.zip

Thanks, I think I did not explain well

I am trying to create a custom function that will do this to no avail so far

I need a "conditional sum" of four fields but only if a related field has a specific value. Kind of like a sum on a found set, without the "find". I would have several of these fields for various values of the related field

The logic works like this

sum (field1; field2; field3) if (tableA::fieldX = "1")

I found a "SumConditionalFields" custom function, but could not adapt it to my needs ....

It's still not very clear. Perhaps:

If ( tableA::fieldX = "1" ; Sum ( field1 ; field2 ; field3 ) )

No, the field to be summed and the "if" field form part to the same record ... I have attached the file

conditional_calc.fp7.zip

I hope this isn't thread hijacking. I'm working on a similar problem, and when I use something like this

If ( relatedtable::fieldX = "1" ; Count ( related records); 0 )

It only evaluates if fieldX in the first related record is "1", and then it counts all related records not just the ones that are coded "1". That's not what I want. I want it to step through the related records only counting the ones that are coded as "1".

Edited February 1, 200718 yr by Guest