Hi,I have a field which is a multi-value data like:
“(BH)”
“(BH-BD)”
“(BP-EN-PR-OD)”
“(BP-AD-DC)”
“(BP-EP)”
“(TR-OD-PR-AA-DC)”,
Now I have a notes view which has two columns using this field data, I want a formula to loop each item of this multi-value field, after the formula looping, one of the column data should like this:
“(BH)”
“(BH-BD)”
“(BP-EN”
“(BP-AD”
“(BP-EP)”
“(TR-OD)”
and the other column should like this:
“(PR-OD)”
“(DC)”
“(AA-DC)”
Thanks for any help,
Subject: RE: Questions about multi-value field?
No loop is required. This should work for the first column:
MVFnoParens := @RightBack(@LeftBack(multiValueField;1);1);
MVFportion1 := @Word(MVFnoParens;“-”;1);
MVFportion2 := @Word(MVFnoParens;“-”;2);
“(” + @Trim(@ReplaceSubstring(@Replace(MVFportion1 + “-” + @Replace(MVFportion2;“”;“~”);“-”;“”);“-~”;“”))+ “)”
The second column is just a minor variation:
MVFnoParens := @RightBack(@LeftBack(multiValueField;1);1);
MVFportion1 := @If(@Word(MVFnoParens;“-”;5) = “”;@Word(MVFnoParens;“-”;3);@Word(MVFnoParens;“-”;4));
MVFportion2 := @If(@Word(MVFnoParens;“-”;5) = “”;@Word(MVFnoParens;“-”;4);@Word(MVFnoParens;“-”;5));
“(” + @Trim(@ReplaceSubstring(@Replace(MVFportion1 + “-” + @Replace(MVFportion2;“”;“~”);“-”;“”);“-~”;“”)) + “)”;
Subject: RE: Questions about multi-value field?
Hi,Rich:Thanks a lot for your help,
The coding are working on the first column, but if the lists on the second columns have more than 4 list items, it screw up,
For example: if the data like this,the codes is not working,
(RT-EQW-TTWE-YUIEW-YU) ->miss YU for this case.
(EN-TRE-RE-TBY-RTYUR-RG) ->miss TRYUR-RG for this case
Also, there are some blank brackets in some document(s).
I have tried to modify the codes, but it is diffulty for me, do you have any clues for how to change the codes to make it work for any situations?
Thanks again for your help, Merry Christmas and Happy New Year!
Subject: RE: Questions about multi-value field?
Sorry. My approach doesn’t work. Partly because in the data you gave you didn’t show any examples where there were more than 4 hyphens in one entry; but mostly because it just doesn’t work 
There may still be a non-looping way of doing it, but I have a plane to catch in a few hours. Will have to leave it to someone else to figure it out.
Subject: This seems to do it for the second column
MVFnoParens := @RightBack(@LeftBack(multiValueField;1);1);MVFpadded := MVFnoParens + “-~-~-~-~-~-~-~-~-~-~”;
MVFlastEightReversed := @Word(MVFpadded;“-”;10) + “-” + @Word(MVFpadded;“-”;9) + “-” + @Word(MVFpadded;“-”;8) + “-” + @Word(MVFpadded;“-”;7) + “-” + @Word(MVFpadded;“-”;6) + “-” + @Word(MVFpadded;“-”;5) + “-” + @Word(MVFpadded;“-”;4) + “-” + @Word(MVFpadded;“-”;3);
MVFtrimmedReversed := @ReplaceSubstring(MVFlastEightReversed;“~-”;“”);
MVFsecondToLast := @Word(MVFtrimmedReversed;“-”;2);
MVFlast := @Word(MVFtrimmedReversed;“-”;1);
MVFlastTwoParenthesized := “(” +MVFsecondToLast + “-” + MVFlast+ “)”;
@Trim(@ReplaceSubstring(MVFlastTwoParenthesized;“(-”: “(-)” : “(~)”; “(” : “”: “”))
The idea is to first strip the parens, then pad all the strings so that they have at least 10 segments separated by hyphens. Then simultaneously remove the first two segments and reverse the order of the next eight. Then remove any segments that were added in the padding process, take the first and second segments (which are the last and second to last), re-assemble in the proper order, and then do some adjustments to get rid of hanging hyphens and empty parens.
Don’t ask me to completely explain the logic of the last line. I ran through this quickly, and for some reason that I don’t see, some of the ~ chars are surviving to that point even though I thought I had already gotten rid of them.
Subject: Oh, one more thing…
This will work for values that have up to 9 hyphens within the entries of the multi-value fields. If you need to deal with longer strings than that, you’ll need to either change the code appropriately, or maybe switch to a looping version.
Subject: RE: Oh, one more thing…
Hi,Rich:
Thank you so much for your help, especially in the Holiday season, the codes you sent to me are working very well, you save me a lot of time for this problem.
I am really appreciate for your helps,
Happy New Year and all the best for year 2006.
Peter Cong
Subject: And Happy New Year to you, too
Now that I’m back reading this forum regularly again, thanks to the RSS feeds, it’s good to know I can still contribute something
Good luck with your project.
-rich