trying to figure out a modulo math issue

[direwolf]

Hi all,

I was so impressed with the friendly, knowledgable help here on a previous question that I thought I'd try again. -D

I have a Javascript rule that I was using for something else that I'm trying to convert into "Filemaker-speak" for an FM project.

Basically I'm doing this modulo calculation:

if (CurrentRecordNumber() % 1100 == 0)

return 1100

else

return CurrentRecordNumber() % 1100;

What I'd like is to have Filemaker do the equivalent, but instead of me having to supply a static number like I'm doing in the above, I'd like Filemaker to replace the static 1100 above with a calculation that gets the total number of records in the file (minus 1 b/c my first record is my headers), divide by 3, and round that number up to a whole number. Then do the modulo "magic".

So, if I had 3300 total records (counting my headers), I would wind up with a field that skips record 1 (or actually that adds my specified header text for record 1) and then enters a 1 in the field for record 2, a 2 in the field for record 3, etc. up to 1100. Then starts over counting up to 1100 again. And then one last time, ending at 1099.

Hopefully I've explained that in a way that makes sense.

Thanks in advance to the gurus here for any help!

Edited June 14, 2007 by Guest

[fabriceN]

I didn't really get what you meant with the header, but here is an idea that doesn't take it into account

Let ( _mod = Mod ( Get ( RecordNumber ) ; number )

; Case ( _mod ; _mod ; number )

)

where number is =1100 in your example

Edited June 14, 2007 by Guest

[comment]

I think

Mod ( Get ( RecordNumber ) - 1 ; number ) + 1

should produce the same result.

[direwolf]

I tried this:

If (Get ( RecordNumber ) = 1; "Press Sheet";

If ( Mod ( Get(RecordNumber)-1 ; Ceiling ( Get ( TotalRecordCount )-1 ) / 3 ) = 0 ; Ceiling ( Get ( TotalRecordCount )-1 ) / 3 ; Ceiling ( Mod ( Get(RecordNumber)-1 ; Ceiling ( Get ( TotalRecordCount )-1 ) / 3 ) ) )

)

This almost works. Bizarrely though, I wind up with Record number 1 = Press Sheet as I want it to, then it goes 1-1099, 1-1100, 1-1099 and the last record has a 0

That first line in the calculation is b/c I want the very first record to contain the header info for what that field is.

Why would I get 1-1100 one time like I want, but not the other 2 times?

[comment]

Try something like this:

Let ( [

number = Get ( RecordNumber ) ;

divisor = Ceiling ( ( Get ( TotalRecordCount ) -1 ) / 3 )

] ;

Case (

number = 1 ; "Press Sheet" ;

Mod ( number - 2 ; divisor ) + 1

)

)

BTW, storing headers in the first record is not a good idea.

Edited June 14, 2007 by Guest
fixed a typo

[direwolf]

Thanks. That looked like it was going to do the trick. I get 1-1100 for the first group, but then I get 1.33333, 2.33333, 3.33333 for the next group and 1.66667, 2.66667, etc. for the next group.

I tried adding a round to the end:

Round ( Mod ( number - 2 ; divisor ) + 1 ; 0 )

and that worked for the second group of numbers, but not the last.

[comment]

That's not the result you should be getting. Actually, you should be getting an error, since I have dropped a parenthesis accidentally - fixed it now.

[direwolf]

Genius! That is a truly beautiful thing. I did get an error, but I mistakenly thought it was b/c I needed to remove that trailing ) instead of adding the ( where you added it.

I can't thank you enough! This is the best board I think I've ever found.