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

Calculating question-- assigning number based on a field range

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!

• 1. Re: Calculating question-- assigning number based on a field range

It seems to me your last version is very close:

In terms of logic,

Case(

No. ≤   24; 0

No. ≤   454; 1 ;

No. ≤   950; 2 ;

3

)

Should return the correct reel numbers. I'm guessing here, though, that the numbers of pages/reel vary for different microfilm sets. If so, you might want to store your page range per reel data in a related table.

• 2. Re: Calculating question-- assigning number based on a field range

You should store the Reel, start page, and end page in a seperate table.

Then you can have a relationship as such:

PageNumber  ≥ StartPage

PageNumber  ≤ EndPage

Then you can just put the related Reel field on the layout.

• 3. Re: Calculating question-- assigning number based on a field range

Thanks Phil

When I try this:

Case(

No. ≤   24; 0;

No. ≤   454; 1 ;

No. ≤   950; 2 ;)))

It says "an operator is expected here" and I can't get it to accept that formula.

It seems like it should be so easy... and yet...

• 4. Re: Calculating question-- assigning number based on a field range

And no, that smiley face was not included in my formula.

• 5. Re: Calculating question-- assigning number based on a field range

Thanks mr_vodka.

I'm not sure I totally understand your suggestion though.

So I would have a separate table that would just have

Reel Number

Start Page

End page

Then I would have the table in which I'm working and when I got to the reel entry, I would link back to my other table, how and with what?  Would I put

PageNumber  ≥ StartPage

PageNumber  ≤ EndPage

into one calculation?

Sorry if this is an obtuse question and I appreciate the help!

- Jess

• 6. Re: Calculating question-- assigning number based on a field range

It seems to me you should get rid of ";))" at the end of your Case statement. There are too many parentheses and one extra ";".

My 2 cents,

RW

• 7. Re: Calculating question-- assigning number based on a field range

ziparojw wrote:

Then I would have the table in which I'm working and when I got to the reel entry, I would link back to my other table, how and with what?  Would I put

PageNumber  ≥ StartPage

PageNumber  ≤ EndPage

into one calculation?

Not into a calculation. This would be set up in a relationship between the two table occurrences. Once the relationship has been established, you would just put the related rell number field on the layout. No calculation is needed.

The reason why I suggested this instead of the calculation menthod that you were going with is because it seems as though your cases for your calculation will be very long. Its not ideal for this type of scenario.