7 Replies Latest reply on Mar 1, 2010 7:12 AM by mrvodka

    Calculating question-- assigning number based on a field range

    ziparojw

      Title

      Calculating question-- assigning number based on a field range

      Post

      Hello everyone. 

       

      I am using FileMaker version 10.  I am a historian, and relatively new to FileMaker. I've been able to figure out every calculation I've needed to do so far, but this one is stumping me.  

       

      I am trying to catalogue an enormous microfilm set (over 43,000 pages).  I have a table that tells me what page ranges are on what microfilm reels, but in looking at individual entries, I only know the page numbers.  

       

      For example, an entry would state: John Smith to Jane Doe, page 34,092.  It does not give the reel number.

       

      I want to create a field that takes that page number and automatically gives me the reel so that I don't have to keep flipping back to my table (there are 97 reels).  I have tried everything I can think of, but I can't make this calculation work.  

       

      For example,

       

      Reel 1 is pages 25 to 454

      Reel 2 is pages 455 to 950 

      Reel 3 is pages 951 to 1425 

       

      These are two calculations that I've tried (neither work, or only partially work): 

       

      If ( No.  ≤   24; 0 ;

      If ( No.  ≤  454 ; 1 ;

      If ( No.  ≤ 950; 2;

      If ( No.  ≤  1425; 3))))

       

      Case(

      No. ≤   24; "1";

      No. ≤   454; "2";

      No. ≤   950; "3";

      )

       

      Any help is appreciated. Thanks everyone!