5 Replies Latest reply on Jan 2, 2015 4:26 PM by philmodjunk

    Case in calculated field

    PrestonHunt

      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)

        • 1. Re: Case in calculated field
          philmodjunk

          a quick scan of the syntax doesn't show anything obviously wrong with the calculation, so if I were you, I'd take a closer look at the getNthRecord calls in your calculation. I suspect that some of them aren't returning the value you expect them to return.

          • 2. Re: Case in calculated field
            PrestonHunt

            Well I pulled all the "GetNthRecord" values out into 28 separate fields and then all of a sudden this calculation started working so I now know the calculation was valid.

            But then later while working on another calculated field based on fields with repetitions and also using non-repetition fields I found that you must use "Extend" function to specify using a non-repetition field in a calculation with a repetition field. That said if I was doing this over it looks like it would work if I used "Extend ( totalstones)"  instead of just "totalstones"

            • 3. Re: Case in calculated field
              philmodjunk

              Or you could get rid of the repeating fields. For almost (but not quite) all uses, repeating fields became obsolete with the release of FileMaker 3, the first version of FileMaker to support related tables of records and portals. Using related tables in place of repeating fields is nearly always a better option with much greater flexibility in how you work with your data.

              PS. Filemaker's import records tool has an option that makes it very easy to split the data in repeating fields into separate records in a new table.

              • 4. Re: Case in calculated field
                PrestonHunt

                Again, Thanks Phil.

                 

                In other dev systems I use relationships or sets (a form of relationship) but I am just making a simple app with FM to get familiar with it so I figured I would try the rep-fields out.

                 

                Again, thanks.

                 

                • 5. Re: Case in calculated field
                  philmodjunk

                  Your calc suggests that your solution is not so simple after all.

                  Relationships and sets are both possible in FileMaker.