if statements in fields...

[toshog]

hi all,

simple problem but i can get it to work...

i have this if conditioning that defines a field:

If(Service="Planning Session", If(Duration = "45 min.", 1, If(Duration = "1 hour", 1, If(Duration = "1 hr 15 min.", 2, If(Duration = "90 min.", 2, If(Duration = "1 hr 45 min.", 2, If(Duration = "2 hrs.", 3, If(Duration = "2 hrs. 15 mins.", 3, If(Duration = "2 hrs. 30 mins.", 3, If(Duration = "2 hrs. 45 mins.", 4, If(Duration = "3 hrs.", 4, If(Duration = "3 hrs. 15 mins.", 4, If(Duration = "3 hrs. 30 mins.", 5, If(Duration = "3 hrs. 45 mins.", 5, If(Duration = "4 hrs.", 5, If(Duration = "4 hrs. 15 mins.", 6, If(Duration = "4 hrs. 30 mins.", 6, If(Duration = "4 hrs. 45 mins.", 6, If(Duration = "5 hrs.", 7, If(Duration = "5 hrs. 15 mins.", 7, If(Duration = "5 hrs. 30 mins.", 7, If(Duration = "5 hrs. 45 mins.", 8, If(Duration = "6 hrs.", 8, If(Duration = "all day", 8, 0) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ), 0)

the field that can have either of the hour options as a multiple field and all i get for the calculation field is 0 for each one... i count the parenthesis after the first 0 in the if statement and they seem to match the conditions...

any help will be appreciated...

[LiveOak]

There is a limit to the depth of nested If's. Make your life easy and use a Case statement instead.

-bd

[CobaltSky]

The calculation you are using can only work if:

1. both the 'Service' and 'Duration' fields are defined as text fields, and

2. if the content of those fields *exactly* matches what you have placed within the quotes in your formula (including spaces and punctuation).

Other than that, your formula is fine. The case function might make it a bit tidier to look at, but for what it is worth, I copied and pasted it directly from your post into a calc field here and it works like a dream.

[toshog]

thanks to both responses..

both fields are defined as text and they are indexed and repeating fields. the matches are the same - i checked that many times.

should the repeating fields have the same number of repetitions?

this db was done by somebody else and i'm just upgrading it a little.. but can't figure out why this isn't working... also what is the limit of the ifs in an if statement?

and if i have a repeating field that's already in use for calculations are the calculations getting screwed up if i add more repetitions to the field?

thanks...

[CobaltSky]

There is a maximum of 168 nests allowed in a single IF or CASE statement. This is inclusive of the outer statement. FileMaker Kbase article no 101536 refers (http://www.filemaker.com/ti/101536.html).

However as I mentioned previously, I copied and pasted your formula directly from your forum post into a calc field and it worked perfectly. I have only tried it for up to 12 repetitions, but there is no reason why it won't work for more.

A copy of the test file I created that shows it working is attached to this message so you can download it and take a look if you wish?!

If the number of repetitions is different for the three fields, it will still work (though, of course, only for the number of repetitions for which all three fields are able to 'participate').