9 Replies Latest reply on Dec 17, 2015 10:16 AM by Extensitech

    Assemble calc field with words that replace checkbox selections (FM12)

    laura.bowyer

      Good morning folks!

       

      I am in need of a custom function or calculation that will assemble a list of words separated by "/" based on selections that a user clicks on a checkbox field.

       

      For instance, the checkbox field has values of letters:  Checkbox.JPG

       

      The resulting calculated text should always be in the same order based on the above and be written out as: "White/Blue" if W and U are selected. If B is selected only, then "Black"

       

      I have the calc making an array based on the above options then substituting the "/" between the words and removing the carriage return, but can't get it to remove the trailing "/" is one exists:

       

      ( Substitute ( If (PatternCount (PublishingReleaseItems::ColorIndicatorInput; "W") = 1; "White" & ¶ ;"") &

      If (PatternCount (PublishingReleaseItems::ColorIndicatorInput; "U") = 1; "Blue" & ¶ ;"") &

      If (PatternCount (PublishingReleaseItems::ColorIndicatorInput; "B") = 1; "Black" & ¶ ;"") &

      If (PatternCount (PublishingReleaseItems::ColorIndicatorInput; "R") = 1; "Red" & ¶ ;"") &

      If (PatternCount (PublishingReleaseItems::ColorIndicatorInput; "G") = 1; "Green"  ;"") ; ¶ ; "/" ))

       

      What am I missing?

       

      Thanks!

      Laura

        • 1. Re: Assemble calc field with words that replace checkbox selections (FM12)
          SteveMartino

          I can help you with the first part.  Remember that the pair of letters will come out in the order they were clicked, so if someone clicks W then B, you get W/B, but if they click B then W you get B/W:

          If the checkbox field is called "ColorType", and you calculation field is:

          Substitute (ColorType; "¶" ; "/") will give you what you are looking for.  But not in the correct order but as checked.

          I know there is a way to put them in order, just have to think about while I run out.

          Also, do you want/not want someone to check more then 2 boxes?

          • 2. Re: Assemble calc field with words that replace checkbox selections (FM12)
            laura.bowyer

            Thanks Steve:

             

            They have to be in order and they can have any number of boxes checked. My calc puts them in proper order so far, the only thing that isn’t working is getting rid of the trailing “/”.

             

            Thanks!

            Laura

            • 3. Re: Assemble calc field with words that replace checkbox selections (FM12)
              CamelCase_data

              Substitute ( List ( If (PatternCount (PublishingReleaseItems::ColorIndicatorInput; "W") = 1; "White" ) ; If (PatternCount (PublishingReleaseItems::ColorIndicatorInput; "U") = 1; "Blue" ) ; If (PatternCount (PublishingReleaseItems::ColorIndicatorInput; "B") = 1; "Black" )  ; If (PatternCount (PublishingReleaseItems::ColorIndicatorInput; "R") = 1; "Red" ) ; If (PatternCount (PublishingReleaseItems::ColorIndicatorInput; "G") = 1; "Green" ) ) ; ¶ ; "/" )

              • 4. Re: Assemble calc field with words that replace checkbox selections (FM12)
                siplus

                Substitute(Be_Values_Sort(checkboxfield); "¶"; "/")

                • 6. Re: Assemble calc field with words that replace checkbox selections (FM12)
                  karimhanafi

                  Hi Laura,

                   

                  You have great suggestions here. Maybe I can add this one. I commented in the Let statement, but you can take the comments out:

                   

                  Let (

                        [

                  // First ensure that we will always get the colors in the good order no matter how the selection sequence was made

                  // We add a - character after each letter so we have a combination that we can easily substitute later

                   

                              ~inOrder = Filter ( "W-U-B-R-G-"; ColorIndicatorInput & "-");

                   

                  // Next we make a simple subtitution to get the colors in the right order

                   

                              ~colors = Substitute (

                   

                                                            ~inOrder;

                   

                   

                                                            ["W-";"White¶"];

                   

                   

                                                            ["U-";"Blue¶"];

                   

                   

                                                            ["B-";"Black¶"];

                   

                   

                                                            ["R-";"Red¶"];

                   

                   

                                                            ["G-";"Green¶"];

                   

                  // at this point we have a list of the colors in the correct order. Some Dashes may remain. We are going to get rid of them with one last substitution

                   

                   

                                                      ["-"; ""]

                                            );

                   

                  // At this point we have a list of colors with a trailing return character. We are going to subtitute / to the return character and select all the characters except the last one using Left formula, so we exclude the last Slash character

                   

                                            ~clean = Left (

                                                                    Substitute ( ~colors; "¶"; "/" ) ; //

                   

                                                                    Length ( ~colors ) - 1

                                                          )

                   

                   

                        ];

                   

                   

                                                  ~clean

                   

                   

                  )

                   

                  Hope that helps.

                  • 7. Re: Assemble calc field with words that replace checkbox selections (FM12)
                    laura.bowyer

                    Thank you for providing this option!

                     

                    Laura

                    • 8. Re: Assemble calc field with words that replace checkbox selections (FM12)
                      easyaspi

                      Here's one that keeps them in the correct order regardless of the order in which they were clicked, and also works with more than two selections.

                       

                      Let ( [

                      theData = Untitled::Data ;

                      W = If ( PatternCount ( theData ; "W" ) ; "White" & ¶ ) ;

                      U = If ( PatternCount ( theData ; "U" ) ; "Blue" & ¶ ) ;

                      B = If ( PatternCount ( theData ; "B" ) ; "Black" & ¶ ) ;

                      R = If ( PatternCount ( theData ; "R" ) ; "Red" & ¶) ;

                      G = If ( PatternCount ( theData ; "G" ) ; "Green" & ¶ ) ;

                      theResult = Substitute ( W & U & B & R & G ; ¶ ; "/" )

                      ] ;

                       

                      Left ( theResult ; Length ( theResult ) - 1 )

                      )

                      • 9. Re: Assemble calc field with words that replace checkbox selections (FM12)
                        Extensitech

                        Let ( [

                        selected = Test::test // your data entry field

                        ; values = ValueListItems ( "" ; "colorValueList" ) // your value list

                        ; reordered =  filtervalues ( values ; selected )

                        ; trimmed = substitute ( trim ( substitute ( reordered ; ¶ ; "  " ) ) ; "  " ; ¶ ) // note double spaces in quotes. this is in case you have two word colors like "light blue", so we don't replace the internal space with a pilcrow

                        ] ;

                         

                        substitute ( trimmed ;  ¶ ; "/" )

                         

                        )

                         

                        You can, of course, also substitute full color names for the codes. I'm curious whether your screen real estate is so tight that you couldn't just have the color names in your value list. I know, for myself, remembering that "U" is blue but "B" is black would be a challenge.

                         

                        Chris Cain

                        Extensitech