AnsweredAssumed Answered

Case in calculated field

Question asked by PrestonHunt on Jan 1, 2015
Latest reply on Jan 2, 2015 by philmodjunk

Title

Case in calculated field

Post

Hey all. I am having an issue using a case statement in a calculated field. The concept is to find some specific values in some other fields with 14 repetitions. I another dev system I use I have never had a problem with large case statements but what is happening is this.

If the value in the field totalstones satisfies the first part of the statement then all is well but if the value is greater then the first test it jumps to the last test, sees it as true even though it is not and using that result. I would think if it did not pass any evaluation test it would zero out as that is what it is supposed to do if no conditions are met. 

Using FileMaker Pro Advanced version 12

Here is the formula in the calculated field;

Case (totalstones >0 and totalstones  <=  GetNthRecord ( b_stone_count ; 1 ); totalstones  * GetRepetition ( b_stone_mult ; 1 )
;totalstones >=  GetNthRecord ( b_stone_count ; 2 ) and totalstones <  GetNthRecord ( b_stone_count ; 3 ); totalstones  * GetRepetition ( b_stone_mult ; 2 )
;totalstones >=  GetNthRecord ( b_stone_count ; 3 ) and totalstones  <  GetNthRecord ( b_stone_count ; 4 ); totalstones  * GetRepetition ( b_stone_mult ; 3 )
;totalstones >=  GetNthRecord ( b_stone_count ; 4 ) and totalstones  <  GetNthRecord ( b_stone_count ; 5 ); totalstones  * GetRepetition ( b_stone_mult ; 4 )
;totalstones >=  GetNthRecord ( b_stone_count ; 5 ) and totalstones  <  GetNthRecord ( b_stone_count ; 6 ); totalstones  * GetRepetition ( b_stone_mult ; 5 )
;totalstones >=  GetNthRecord ( b_stone_count ; 6 ) and totalstones  <  GetNthRecord ( b_stone_count ; 7 ); totalstones  * GetRepetition ( b_stone_mult ; 6 )
;totalstones >=  GetNthRecord ( b_stone_count ; 7 ) and totalstones  <  GetNthRecord ( b_stone_count ; 8 ); totalstones  * GetRepetition ( b_stone_mult ; 7 )
;totalstones >=  GetNthRecord ( b_stone_count ; 8 ) and totalstones  <  GetNthRecord ( b_stone_count ; 9 ); totalstones  * GetRepetition ( b_stone_mult ; 8 )
;totalstones >=  GetNthRecord ( b_stone_count ; 9 ) and totalstones  <  GetNthRecord ( b_stone_count ; 10 ); totalstones  * GetRepetition ( b_stone_mult ; 9 )
;totalstones >=  GetNthRecord ( b_stone_count ; 10 ) and totalstones  <  GetNthRecord ( b_stone_count ; 11 ); totalstones  * GetRepetition ( b_stone_mult ; 10 )
;totalstones >=  GetNthRecord ( b_stone_count ; 11 ) and totalstones  <  GetNthRecord ( b_stone_count ; 12 ); totalstones  * GetRepetition ( b_stone_mult ; 11 )
;totalstones >=  GetNthRecord ( b_stone_count ; 12 ) and totalstones  <  GetNthRecord ( b_stone_count ; 13 ); totalstones  * GetRepetition ( b_stone_mult ; 12 )
;totalstones >=  GetNthRecord ( b_stone_count ; 13 ) and totalstones  <  GetNthRecord ( b_stone_count ; 14 ); totalstones  * GetRepetition ( b_stone_mult ; 13 )
;totalstones >=  GetNthRecord ( b_stone_count ; 14 ); totalstones  * GetRepetition ( b_stone_mult ; 14 );0)

Outcomes