| cooper collier 8/26/2015 10:36:47 AM Yeah, I said that is exactly what I mentioned doing in my original suggestion. But its not a good solution. Let me explain in exhausting detail...
What you suggest works great, until you have a list of results that have commas..
example.
"smith, john" , "Zappa, Frank, Sr" , "Stewart, Martha, J , DR. CEO"
suddenly you get this.. "smith john" "Zappa Frank Sr" "Stewart Martha J DR CEO"
OOOPS, that's ummm seriously messed up.
So let me tell you exactly how to resolve this...
What you need is a unique delimiter at the end of every record... What you can do is ask users to always end every entry with something..anything.. I use "###" But that will not work, guaranteed, the will mess it up!
So now you need to make multiple "middleman" formula fields.
in the first table you make a field the person enters data in.
[full name]
now make a formula field to add your chosen delimiter
[full name ###] = [full name]&###)
Now in the second table concatenate the [full name ###]
Now in the second table you have a new field that contains all the results with "###," between them. The data will look like this.
[concatenated full name ###] contains.. smith, john###, Zappa, Frank, Sr###, Stewart, Martha, J , DR. CEO###
make another formula field to replace the ###
[Full Name no ###]= Replace([concatenated full name ###],"###","\n") results are smith, john , Zappa, Frank, Sr , Stewart, Martha, J , DR. CEO
OOPS, lines start with commas.. (you would think we could simply ignore them... NO NO NO.. users cannot deal with that!! )
So we change the formula to this..
[Full Name no ###]= Replace([concatenated full name ###],"###,","\n") results are smith, john Zappa, Frank, Sr Stewart, Martha, J , DR. CEO###
CRAP now there is a trailing ###... change the formula again
[Full Name no ###]= Replace((Replace([concatenated full name ###],"###,","\n")),"###","")
results are smith, john Zappa, Frank, Sr Stewart, Martha, J , DR. CEO'
BAM we have it... simple..
BUT wait.... AS I MENTIONED IN THE FIRST POST.. there are some issues.
1. What happens when the user types in the delimiter?? Trust me. I am using "###" because I cannot think of any reason anyone would put that in the field. I still fully expect it to happen. I don't care what delimiter you choose.."{#]}6" Some blasted user will eventually type it into the field..its one of there villainous super powers.
2. The results are no longer links. You have a nicely formatted text listing! You lose the ability to click on the result and be take to that record.. it annoys users, ALOT. So does having a list of results they cannot easily read... (its really a lose/lose situation)
3. the final results column is populated by a chain of "middle" formula fields..
[full name]=> [full name ###]=> [concatenated full name ###]=> [Full Name no ###]
When you need to change something next year, you are going to have to figure out this chain again... it could be simple because you named the fields in a manner that makes logical sense... BUT YOU CANNOT DO THAT!!
since you cannot rename the columns in table views. you are forced to name the fields something that makes sense to the user viewing the table.. (not for the programmer)
So the originating field may be [Home Owner Name] and the resulting name may be [Debtor Overdue].
This complicates changing it in the future..unless you are superbly good at documenting.. which I am not..
4. you better make sure none of the "middleman" formula fields appear in any views and remember to make each one not searchable...
Do this process a few dozen times... after EACH and EVERY single time, deal with all your users complaints that they cannot click on the link... explain to them why, just like the last time, and the time before and the time before that...
Then maybe the idea of simply having the option to use a "return" instead of a "," as a separator for concatenating and "many to many" results.. will start looking a bit better..??
|