1 Reply Latest reply on Sep 27, 2014 7:11 AM by philmodjunk

    Conditional calculation issue

    ultranix

      Title

      Conditional calculation issue

      Post

      Hey, I want your input on complex conditional calculation I'm working on. The idea is like this: I have a form about property, and I want to transfer it to property description. I got stuck on window orientation. I have all 8 possible sides - North, East, South, West, Northeast, Southeast, Northwest, Southwest.

      So, if, for example, east, north and southwest is checked, I want it to work like that:  first & ", " & second & " and " & third (where first is "east" and second is "north" and third is "southwest"). But there, theoretically, may be all possible variations from 1 option checked to all 8.

      What I want:

      If 1 checked - no comma, no " and "; (East)
      If 2 checked - separated by " and "; (East and West)
      If 3 checked - first 2 separated by comma, last one by " and "; (East, West and South)
      If 4 checked - first 3 separated by comma, last one by " and "; and so on. (East, West, Southeast and North)

      How do I do that?

        • 1. Re: Conditional calculation issue
          philmodjunk

          Case ( ValueCount ( YourCheckBoxField ) = 1 ; YourCheckBoxField ;

          Let ( [ TheListA = LeftValues ( YourCheckBoxField ; ValueCount ( YourCheckBoxField ) - 1 ) ;
                    TheListA = Left ( TheListA ; Length ( TheListA ) - 1 )  ; // remove trailing return
                    TheListB = RightValues ( YourCheckBoxField ; 1 )
                  ] ;

                    Substitute ( TheListA ; ¶ ; ", " ) & " and " & Substitute ( TheListB ; ¶ ; "" )
                 ) // Let
                 ) // Case