Formula

[#1]

Hi All,

I need your assistance with two issues. Number one I keep getting Out of Object Memory error for my chart. And Number two is there any formula to see if any company id has more than one phone number. I'm attaching a sample data set.

Thanks in advance,

[#2]

NetsuiteID. I want to make a chart with IDs and ALL the phone1 numbers associated with them. And a dimension or a expression which entails how many phone1 each ID has.

Thanks,

[swuehl · Accepted solution]

This calculated dimension (e.g. used in a list box's field expression) should return the list of companies with more than one phone1 entries:

=Aggr(

Only({1<Customer2.netsuiteid = {"=Count({1}DISTINCT Customer2.phone1)>1"} >}Customer2.netsuiteid), Customer2.netsuiteid)

You can also create a chart with dimension Customer2.netsuiteid and expression

=Count(DISTINCT Customer2.phone1)>1

which should show the same set of companies. Add phone1 as dimension if you want (or an expression like

=Concat(

{<Customer2.netsuiteid = {"=Count({1}DISTINCT Customer2.phone1)>1"} >}

DISTINCT Customer2.phone1,', ')

[#4]

Thanks Stefan. I have created a chart with ID and the phone1 field but some odd reason the chart only displays only one phone number even though there are more than one phone number associated with some of them. The first one which jumps out at me is ID 460 it has two numbers and the chart doesn't even list them.

Please provide assistance in this matter .

[swuehl]

I can't see that you have used any of my suggested calculated dimensions / expression in your sample app.

I think the reason why you see only single phone1 numbers and no duplicates is

Use Aggregation Functions!

[#6]

I'm keeping the chart as simple as possible in my sample so I can see the multiple phone numbers (if any) for the customers. It's a requirement for the end users. I'm also using one of the formulas you had suggested above to see which IDs have multiple phones listed.

=Aggr(Only({1<Customer2.netsuiteid = {"=Count({1}DISTINCT Customer2.phone1)>1"} >}Customer2.netsuiteid), Customer2.netsuiteid)

So if I understood the discussion by Henric Aggr function eliminates the dupes, right?

Please I just need the IDs to show other phone numbers too not just the first matching phone number.

Thanks,

Arsalan

[#7]

Got it!!!! Thanks for all of your help guys, Sunny and Stefan.