9 Replies Latest reply: Apr 29, 2011 2:58 PM by John Witherspoon RSS

    Extracting from a GetFieldSelections result

      Hi,

      I have a list box with some names. It is possible to select more one name. I want to get the first two characters for each selected value in the GetFieldSelections(Name,','). Is it possible?

      Thanks

        • Extracting from a GetFieldSelections result

          This is not a perfect solution, but probably gives you an idea.

          =left(SubField(GetFieldSelections(Name,','),',',N),2)

          Replace N with 1 if you want the first selection, 2 for second selection ans so on. Ideally you would want to loop though to get all.

          Regards,

          Dinesh.

          • Extracting from a GetFieldSelections result
            Rob Wunderlich

            Can you load another linked table that has the two character representations and use that field in your expression?

            • Extracting from a GetFieldSelections result
              John Witherspoon

              There might be a simpler expression, but this appears to work.

              concat({1} if(index(getfieldselections(Name),Name),left(Name,2)),', ')

              I assume you really want the selected names, not just the possible names. Possible names are much easier:

              concat(left(Name,2),', ')

                • Extracting from a GetFieldSelections result

                  Hi John,

                  I have tried your expression but I have repetead substring of Name field (it is in a list box). I don't understand the use of Name in the Index statement after getfieldselections(Name). In this case, does Name contain the all possible values?

                  Thanks

                    • Extracting from a GetFieldSelections result

                      Hi,

                      I'm solving with a VBScript macro that splits the content of the getfieldselections into an array of strings and then I get the substring for each array element.

                      I hope to don't use a macro.

                      Thanks

                      • Extracting from a GetFieldSelections result

                        To solve the duplicate issue:

                         

                        concat(distinct if(index(getfieldselections(Name),Name),left(Name,2)),', ')

                          • Extracting from a GetFieldSelections result

                            Ok, thanks.

                            It's a good and interesting alternative to the VBScript macro.

                            • Extracting from a GetFieldSelections result
                              John Witherspoon

                               


                              Dinesh Thoniparambil wrote:concat(distinct if(index(getfieldselections(Name),Name),left(Name,2)),', ')


                              The removal of the {1} set means that you're now looking at the intersection of selected and possible values, not at all selected values. It is possible to have values selected that are not possible. Assuming you want to actually see ALL the selected values, you need to add that back in.

                              concat({1} distinct if(index(getfieldselections(Name),Name),left(Name,2)),', ')

                              As for what's going on, let's say you've selected Andy, Bob and Carla, and not selected Dan or Erin. Let's say you then select value 'Male' in the field "Sex". Only Andy and Bob are now possible, but Andy, Bob and Carla are still selected, so getfieldselections(Name) returns the string 'Andy, Bob, Carla'. Now, concat() is an aggregation function. Think of it as a loop. In this case, we loop through names. Normally, it would just loop through possible names, so it would only loop through Andy and Bob. We also need it to loop through Carla, so we use the {1} set to make sure we loop through all names, not just possible names. So we consider each name in turn. First, Andy. We're doing this: index('Andy, Bob, Carla','Andy'). The substring 'Andy' is indeed in our list, in position 1, which is what the index() function returns. Anything non-zero is considered to be true, so the if() is true, and we go to the next part, left(Name,2). This returns 'An'. We keep looping. 'Bob' is position 7, true, so we return 'Bo'. 'Carla' is position 11, true, so we return 'Ca'. 'Dan' isn't in the string, so we return nothing. 'Erin' isn't in the string, so we return noting. So now we have values 'An', 'Bo' and 'Ca'. Distinct here does nothing since they all start with different two letters, but if we'd selected 'Annie' as well, the distinct would eliminate the duplicate. Finally we concatenate them all together with the string ', ' between each. Final result is 'An, Bo, Ca'.