AnsweredAssumed Answered

Help with a 'Conditional Sum' or 'Case' kind of calculation.

Question asked by AndrewStewart on Dec 4, 2014
Latest reply on Dec 4, 2014 by schamblee


Help with a 'Conditional Sum' or 'Case' kind of calculation.


Hi all, 

I'm new to filemaker, and the Forum. I just started using Filemaker for work (I work on a large organic farm, and am trying to get all of our planting/seeding information on a database so we can make more informed business decisions, instead of anecdotal decisions like all farmers have made for the, basically, the history of farming..).

The essential question is: I want to add yield projections together, but in a way that combines like crops with like crops. Sort of like sub-totals, but one 'sub-total' would regard leafy green crops, another root crops, ect.

Each record has 10 rows for 10 crops (Each record represents a field [as in: a field on our farm, composed of 10 beds] not filemaker fields, haha). The boss wants us to keep the records composed in this way. I know that there might be a better way to design the records, but for now he is stubborn and unyielding to persuasion.

That, drawn out into an easier explanation is:

I have three groups of fields (Crop Type[1-10], Yield Projection[]1-10, and Total Yield For ""[1-3]) for a total of 23 fields. 

Crop Type is a drop down list (Values: Gr., Root, Herb).

Yield projection is a text value at the moment, but could turn to a number if it had to.

The total yield fields are: Total Yield Gr, Total Yield Root, Total Yield Herb.

I'd like to add together individual crops' yield projections on the condition that they are the same crop type, and produce the result in the respective columns (Total Yield "...")

I'm happy to clarify as I haven't really had to explain my thoughts to anyone, let alone anyone with experience in Filemaker. In other words, sorry I'm a newb!

I'll post pictures for you all in hopes it helps clear things up.

Thanks in advance!