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'.
