1 Reply Latest reply on Sep 18, 2013 9:30 AM by philmodjunk

    Calculated value not showing for 'certain' field ValueList selections

    hzhawkins

      Summary

      Calculated value not showing for 'certain' field ValueList selections

      Product

      FileMaker Pro

      Version

      12

      Operating system version

      Mac

      Description of the issue

      I'm trying to produce a result from a calculation using If nested if statements. The first 27 nested statements work fine but the 6 after do not produce a result even though all data is the same as the 27 before.

      Steps to reproduce the problem

      I select one field drop down value "Location" and another field drop down value "Sitting"  and usually i get a result weather for the pricing of that location....

      Expected result

      I expect to get the same result as selecting any of the other drop down values.

      Actual result

      I do not get anything from 6 nested if statements (out of 27)

      Configuration information

      Here are my nested if statements. The last 6 do not produce any result.

      If(Location = "River Balcony"; If (Sitting = "Sud-Fl" ; 1500 ;"" )) &
      If(Location = "River Balcony"; If (Sitting = "Fd-Sd" ; 2500 ;"" )) &
      If(Location = "River Balcony"; If (Sitting = "Sul" ; 3500 ; "")) &
      If(Location = "Harbour Balcony"; If (Sitting = "Sud-Fl" ; 2000 ; "")) &
      If(Location = "Harbour Balcony"; If (Sitting = "Fd-Sd" ; 3500 ; "")) &
      If(Location = "Harbour Balcony"; If (Sitting = "Sul" ; 0; "")) &
      If(Location = "Viceroy 1"; If (Sitting = "Sud-Fl" ; 4000 ;"" )) &
      If(Location = "Viceroy 1"; If (Sitting = "Fd-Sd" ; 9000;"" )) &
      If(Location = "Viceroy 1"; If (Sitting = "Sul" ; 0;"" )) &
      If(Location = "Viceroy 2"; If (Sitting = "Sud-Fl" ; 4500 ; "")) &
      If(Location = "Viceroy 2"; If (Sitting = "Fd-Sd" ; 5000; "")) &
      If(Location = "Viceroy 2"; If (Sitting = "Sul" ; 0 ;"" )) &
      If(Location = "Viceroy 3"; If (Sitting = "Sud-Fl" ; 5000 ;"" )) &
      If(Location = "Viceroy 3"; If (Sitting = "Fd-Sd" ; 10000;"" )) &
      If(Location = "Viceroy 3"; If (Sitting = "Sul" ; 0 ;"" )) &
      If(Location = "Good Room"; If (Sitting = "Sud-Fl" ; 800 ;"" )) &
      If(Location = "Good Room"; If (Sitting = "Fd-Sd" ; 1000;"" )) &
      If(Location = "Good Room"; If (Sitting = "Sul" ; 1200 ;"" )) &
      If(Location = "Pavilion 1"; If (Sitting = "Sud-Fl" ; 600 ;"")) &
      If(Location = "Pavilion 1"; If (Sitting = "Fd-Sd" ; 800;"" )) &
      If(Location = "Pavilion 1"; If (Sitting = "Sul" ; 1000 ; "")) &
      If(Location = "Pavilion 2"; If (Sitting = "Sud-Fl" ; 600 ;"" )) &
      If(Location = "Pavilion 2"; If (Sitting = "Fd-Sd" ; 800;"" )) &
      If(Location = "Pavilion 2"; If (Sitting = "Sul" ; 1000 ; "")) &
      If(Location = "Club House"; If (Sitting = "Sud-Fl" ; 900 ; "")) &
      If(Location = "Club House"; If (Sitting = "Fd-Sd" ; 1600; "")) &
      If(Location = "Club House"; If (Sitting = "Sul" ; 2000 ;"" ))&
      If(Location = "Terrace"; If (Sitting = "Sud-Fl" ; 900 ; "")) &
      If(Location = "Terrace"; If (Sitting = "Fd-Sd" ; 1600; "")) &
      If(Location = "Terrace"; If (Sitting = "Sul" ; 2000 ;"" ))
      If(Location = "Pav 1 & 2 & Club Exclusive"; If (Sitting = "Sud-Fl" ; 900 ; "")) &
      If(Location = "Pav 1 & 2 & Club Exclusive "; If (Sitting = "Fd-Sd" ; 1600; "")) &
      If(Location = "Pav 1 & 2 & Club Exclusive "; If (Sitting = "Sul" ; 2000 ;"" ))

        • 1. Re: Calculated value not showing for 'certain' field ValueList selections
          philmodjunk

               There's a syntax error in your post--FileMaker should throw up an error if you enter this calculation and click OK:

               f(Location = "Terrace"; If (Sitting = "Sud-Fl" ; 900 ; "")) &
               If(Location = "Terrace"; If (Sitting = "Fd-Sd" ; 1600; "")) &
          If(Location = "Terrace"; If (Sitting = "Sul" ; 2000 ;"" ))
               If(Location = "Pav 1 & 2 & Club Exclusive"; If (Sitting = "Sud-Fl" ; 900 ; "")) &
               If(Location = "Pav 1 & 2 & Club Exclusive "; If (Sitting = "Fd-Sd" ; 1600; "")) &
               If(Location = "Pav 1 & 2 & Club Exclusive "; If (Sitting = "Sul" ; 2000 ;"" ))

               There's no ampersand (&) after the line in red. That might be an error in how you posted your sample calculation though.

               Your If functions really aren't "nested" except that each IF function has a second "If Sitting" if function nested in side of it.

               What I can note here is that these 6 lines are the only ones that require Location to = either "Terrace" or the text "Pav 1 & 2 & Club Exclusive" or the text "Pav 1 & 2 & Club Exclusive " (Note extra space after the 'e' in 'Exclusive'.)

               Have you checked the actual text in the Location field to make sure that some small difference (such as that space) might be keeping the values from being evaluated as equal like you expect? Just as shown with the space, an extra invisible character (tab, return, etc) can keep this from evaluating differently.

               On a design note, this is a horrific calculation. A case function could greatly simplify your code and a table look up of related values would make it even simpler and also much easier to modify if you find you need to make changes at a later date.