4 Replies Latest reply on Nov 10, 2014 9:14 AM by philmodjunk

    Extracting, condensing and reporting in single line information fro Checklist field

    BarrySpence

      Title

      Extracting, condensing and reporting in single line information fro Checklist field

      Post

       

       

      I am attempting to extract the initial letters of checked items in a four item checklist from a checklist field, then display those initial letters, separated by spaces, in a single line of text in a new Calculation field.

       

       

       

       

       

      Example below:

      Checklist contains this value list:

      Alpha

      Beta

      Gamma

      Delta

       

      With Alpha and Gamma checked and the others unchecked, I would like the field extracting information from this field to display:

      A G 

      (Note that there is a space following G. Not necessary, but it seems easier to include one than to omit this trailing space.)

      The formula below accomplishes the extraction, but displays a ? in a single line field, and a vertical list of the value initials in a taller field with space for up to four lines.

      Substitute ( Checklist ; ["Alpha" ; "A "]  ; ["Beta" ; "B "]  ; ["Gamma" ; "G "]  ; ["Delta" ; "D "] )

      I tried wrapping this in a second Substitute statement, using a copy of whatever character produces the line feed/return forcing each extracted item onto a new line as the search string and the empty string ( "" ) as the replace string. This made no change in the result, which I conformed by changing the Replace string to "x", with the results shown below the formula. 

      Substitute (Substitute ( Checklist ; ["Alpha" ; "A "]  ; ["Beta" ; "B "]  ; ["Gamma" ; "G "]  ; ["Delta" ; "D "] );"
      ";"x")
       

      Ax

      Gx

      Note that the trailing space after the letter has disappeared.)


      Desired display in the calculated field for the example situation (Alpha and Gamma checked, Beta and Delta unchecked) is:

      A G 

      Regards,
      Barry

       

      Supplemental: not addressed above, but also desired:

      FMP records checklist values in the order that the boxes are checked, not the order in which the items are listed. The order in which I'd like the initial values displayed is the order in which the original items are listed. Like the example, though, these are not in alphabetical order, so an abc sort will not do the job.

      Any suggestions appreciated.

      B

        • 1. Re: Extracting, condensing and reporting in single line information fro Checklist field
          BarrySpence

          Having drawn my attention to the Length function while responding to another question, I've arrived at a solution using Substitute, Length and If. Definitely a straightforward blunt force attack, so a more elegant solution achieving the same result would be welcomed.

           

           

          Fields involved:

          Checklist: a Text field controlled by checkboxes and a four item Value List: Alpha, Beta, Gamma and Delta.

          CL Extract: A Calculation field displaying the initial letter of each Value List item in Checklist whose box has been checked. The letters are displayed in a single line, in the order the checkboxes are listed (controlled by the formula), and separated by a trailing space.

           

          Formula:

          If ( Length ( Checklist ) > Length ( Substitute ( Checklist ; "Alpha" ; "" ) ) ; "A " ; "" ) &

          If ( Length ( Checklist ) > Length ( Substitute ( Checklist ; "Beta"; "" ) ) ; "B " ; "" ) &

          If ( Length ( Checklist ) > Length ( Substitute ( Checklist ; "Gamma" ; "" ) ) ; "G " ; "" ) &

          If ( Length ( Checklist ) > Length ( Substitute ( Checklist ; "Delta" ; "" ) ) ; "D " ; "" )

          I have inserted returns in the formula to place each block on a separate line, and clarify its repeating structure.

          As mentioned, a more elegant solution providing the same results would be welcomed. Until then I hope someone else may also find this useful.

          Regards,
          Barry

          • 2. Re: Extracting, condensing and reporting in single line information fro Checklist field
            philmodjunk

            Substitute ( Checklist ; ["Alpha" ; "A "]  ; ["Beta" ; "B "]  ; ["Gamma" ; "G "]  ; ["Delta" ; "D "] ; [¶ ; " " ] )

            • 3. Re: Extracting, condensing and reporting in single line information fro Checklist field
              BarrySpence

              Thanks for this, Phil,

              Very similar to my initial solution, but it did send me back to the Specify Calculation window for a double Doh! experience, once seeing the ¶ button I'd been blind to previously, the other on noticing that step of wrapping a second Substitute statement around the whole thing was obviously an unnecessary extra step.

              The one thing Substitute doesn't do is put the single letters in the same order as the checkbox list. FMP places items in a checkbox controlled field in the order that the boxes have been checked, rather than the order they are listed, and Substitute won't change that order when condensing the list.

              Good, clean solution, and one that led to some learning. For now, though, I'll stick with the If, Length, Substitute one above. Although it's not as clean, it does put the letters in list order, easing recognition by the reader when they're placed in a printed list.

              Regards,
              Barry

              • 4. Re: Extracting, condensing and reporting in single line information fro Checklist field
                philmodjunk

                There are easier ways to do this from the start--such as setting up a "use values from field" value list for these values and specifying the first letter as field 1 and the full text as field two. (But this might create issues for you in getting the desired order for your values in the value list.)

                And here's an auto-enter calculation that takes the values as entered by the mouse clicks and re-orders them to fit the order of the selected values as listed in the value list:

                FilterValues ( ValueListItems ( Get ( FileName ) ; "YourValueListNameInQuotesHere" ) ; self )

                Be sure to clear the "do not replace existing values..." check box.

                Note that with this change, you can modify the values used in the value list and not have to redefine your calculations.