TeamDesk Knowledge Base & Support

gerardo garcia 8/25/2015 6:47:24 PM

Hi cooper, you can use "\n" for "new line" or carriage return.

cooper collier  8/25/2015 9:23:31 PM

I think you miss-understand.
There is no place I can find to make a many to many put a \n.
same with a concatenated lookup

So here is the form view of a record. See how it puts the selected records in with a comma.
Thus putting each record on the same line. Very hard to read...

https://www.dropbox.com/s/h7e696nerda4pgl/form%20view-many%20to%20many.png?dl=0



now this is the column in setup

https://www.dropbox.com/s/h7e696nerda4pgl/form%20view-many%20to%20many.png?dl=0

Now here are the settings for the column.. No place to tell it to put a /n between the records.

https://www.dropbox.com/s/afs119gtg74gdr9/column%20settings%201.png?dl=0

and here

https://www.dropbox.com/s/ylcueuouhkhwsdc/column%20in%20setup.png?dl=0

gerardo garcia 8/26/2015 8:29:51 AM

I created a new field and used this formula:

NewFormulaTextField=Replace([Concatenated Field],",","\n")

This makes results look from this :

a,b,c,d

To this:

a
b
c
d

Hope it helps.
I Never tried it Multireference fields.

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

gerardo garcia 8/26/2015 11:35:31 AM

Thanks for the exhausting detail.
I do like the idea of a Return option in the concatenated fields.
Guess I got used to the "\n" middle solution.

cooper collier  8/26/2015 11:39:00 AM

Yeah, I have gotten pretty good at the formula columns but giving up the link is hard, very hard.

But if you go to using the xhtml formula columns, you can do some pretty amazing things.
I can show you sometime just email me, cooper at frobbit.com

Slava Shinderov  Staff  8/27/2015 2:03:44 AM

It's not clear for me what's the reason to use multi-reference column here and then try to convert the output into "table" form.
Please consider using details view instead if you need to enter and display information in table form with multiple columns.

basenine 8/27/2015 4:36:22 AM

cooper collier  8/27/2015 9:06:29 AM

NO that does not work..
You cannot select existing records with details view..
You need the control to select an item and hit the ADD button...

You can however make a multi reference, AND show the table at the bottom both.. But then the information is displayed in two places, this confuses people.

Also, its not only the many to many relation that does this. Anytime you concentrate a summary it returns the results with commas!

There are many times when the users want a list of information in the form view. One perfect example is customer names... There is no perfect was to prevent a customer from being entered two times in a database..

One person may enter the name slightly different that another person. Example: Robert Johnson - Bob Johnson...

To minimize this I created a many to many relation. Then I filter the results based on the new customer name as they enter it.. Then I use the summary to display all the existing customers with a last name johnson.. this is displayed as a list in the record entry form, right next to the fields for entering a new customer.

When a person starts entering a new customer, they can instantly see possible existing customers... and be able to check if the person already exists...

If they had to check the table at the bottom of the record enter form. they would not do it. Before I put the "possible existing customers" field on the form, we constantly had to fix duplicate customer records. Now it seldom happens.

This is one example. I have many more places where we use summary columns or many to many references to other tables, where the results are best viewed in the form. Not tables on the bottom..