AnsweredAssumed Answered

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

Question asked by BarrySpence on Nov 7, 2014
Latest reply on Nov 10, 2014 by philmodjunk


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




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:






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 "] );"



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 



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.