This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

Is there a way to show the results of related record in one row on a search? Ex., pull in all the contacts on a customer search, and have the contacts show in one row rather than each join to the contact getting its own row. I thought I could possibly concatenate these values into one row but have not had any luck

Formula (Text) with Summary Type - Minimum and `REGEXP_REPLACE(NS_CONCAT({contact.firstname}||' '||{contact.lastname}),',','&lt;br&gt;')`

This will put all contacts into one cell, per company line. However, you need to use summary functions for the other information you are pulling in. For example, Company Name needs to be grouped.

Thanks, that is mostly working but the contact name is duplicating a for each of the contacts in the row, I am unsure what join is creating this. The contacts can be a contact of many companies so I think is that but I am certain

Hmm, i think it might be some other result that's coming in that's causing the duplication. If a contact is associated with two companies, it should appear once under both.

you can also try `REGEXP_REPLACE(NS_CONCAT(DISTINCT({contact.firstname}||' '||{contact.lastname})),',','&lt;br&gt;')` Which has a DISTINCT around the concatenation of the first and last name, which should dedupe multiple instances.

I suggest using `LISTAGG` (<https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions089.htm>) over `NS_CONCAT`. It's both officially documented/supported and more powerful. For example you can specify the delimiter instead of having to wrap in a `REPLACE` (and avoid problems if the data itself contains a `,`)

I've had a suprising number of instances where listagg was useful lately. Something I'd have never picked up without slack!

thanks for the tip <@U29QCJQF3>! I'll have to try that out and add it to my bag of tricks.