Merge two string fields

[Former Member]

I have two fields that ContactType & ContactName and each record will have at least one contact type (primary or secondary), or it can have also two of the contact types. So I am trying to concatenate the contactName if the record has two contacts or if there is only one, then display that.

This is what I have so far:

//variables are: TotalContact, PrimaryVariable, SecondaryVariable, SingleVar.

totalContact := count (contact.contactName, PrimRecord.RecordID);

if (totalContact > 1) then

{ contact type = primary then

PrimaryVariable := contact.contactName

else

SecondaryVariable: = contact.ContactName

}

else

singleVar := contact.contactName;

[Former Member · Accepted solution]

Abdillahi,

Just follow this link:

Hope this helps,

Jason

[Former Member · Accepted solution]

my problem is that I have to evaluate and check if it is primary contact, and then see if there are secondary contact. If that is the case, then I want to display "Primary contact" first and "Secondary Contact" second in that order. If there is only single primary contact, then I want to display it as well without secondary contact.

[Former Member · Accepted solution]

Gotcha...

Well the answer would depend on your data structure.

I'll assume it's something along these lines:

CustomerID-|-ContactID-|-ContactType-|-ContactName-|-...

1--

---

10001

---

P--

---

John Smith

1--

---

10002

---

S--

---

Bill Cramer

1--

---

10003

---

T--

---

Mary Jones

2--

---

10004

---

P--

---

Hank Aaron

2--

---

10005

---

S--

---

Pete Rose

3--

---

10006

---

P--

---

Jerry Rice

3--

---

10007

---

S--

---

Kenny Anderson

3--

---

10008

---

T--

---

Ikky Woods

You want results that look like this:

Company Name 1: Primary Contact = John Smith, Secondary Contact = Bill Cramer

Company Name 2: Primary Contact = Hank Aaron, Secondary Contact = Pete Rose

Company Name 3: Primary Contact = Jerry Rice, Secondary Contact = Kenny Anderson

Assuming I'm correct, follow these steps and create the following formulas.

1) Primary

IF {tbl.ContactType} = "P" THEN {tbl.ContactName}

2) Secondary

IF {tbl.ContactType} = "S" THEN {tbl.ContactName}

3) ConCat

{tbl.CompanyName} &": Primary Contact = " & {@Primary} & ", Secondary Contact = " & NEXT ( {@Secondary} )

4) Now you need to order the report by: #1. CompanyID then #2. ContactType

5) Here's where it all comes together... While in the Design View, right click in the gray part of the Details section off to the left.

Choose Selection Expert...

Click the "X-2" button next to "Suppress (No Drill Down)"

Enter the following formula:

IF {tbl.ContactType} != "P" THEN TRUE ELSE FALSE

( Just be sure to use the "not equal to" angle brackets in place of "!=")

Anyway, if you made it this far, you should have what you are looking for.

Hope this helps,

Jason

[Former Member]

Jason,

You are right I need a field that differentiate/sorts. Finally, worked when I added that field.

[Former Member]

Glad you got it working

[Former Member]

Jason,

Once he is listed in the primary, he will not be in the secondary.

Thanks,

[Former Member]

Abdillahi,

The whole solution is predicated on the idea that the records can be ordered

1) by company

2) by contact type (starting with Primary followed by the best secondary)

That means that you'll have to be able to provide some piece of data to CR that will allow it to create that order. I don't know what that is but you will have to figure that out, either using existing data from the database or by adding an extra table to the Access file.

Jason

[Former Member]

First, Let me make some corrections from my posting on Saturday.

primary contact = James Ryan - This should have been Steve Kerry

Secondary contact = Jeff Prior

Secondary contact = Steve Kerry

You are right there is nothing that tells that CR that differentiate them, as they are both secondary contact for that customer. I do not know if you are familiar with Raiser's Edge databse applications, but we run a query of all those customers that have their primary contact or one of the secondary contact as "Steve Kerry". Then we take that through export module that creates an Read ONLY Access database, which we use for the Crystal.

[Former Member]

In that case, Steve should already occupy the "Primary Contact" position. Is he listed twice... Once as a primary and again as a secondary?

At the end of the day, you will NEED a way of ranking the contacts. Since you are reporting off of an Access file, you can create an extra table table that would allow you to manually assign ranks to individuals.

From there add a query that appends the ranking score to the original table... Report off of the query. CR will have it listed as a View in the datasource list.

Jason

[Former Member]

Jason,

Wouldn't you get pretty much the same output with:

Group by ID

Group by Cust_Type = 'P'

Group by Cust_Type = 'S'

?

That way, you don't care how many 'S's there are (or 'P's either

Plus, you could do it (group on calculations) in the query for speed and convenience.

What do you think ? Am I close ?

[Former Member]

Jason,

Wouldn't you get pretty much the same output with:

Group by ID

Group by Cust_Type = 'P'

Group by Cust_Type = 'S'

?

That way, you don't care how many 'S's there are (or 'P's either

Plus, you could do it (group on calculations) in the query for speed and convenience

and use Overlay if you want them (group 'S') on the same line.

What do you think ? Am I close ?

[Former Member]

Panda,

The grouping won't help on this one. The problem isn't that there can be more than one or secondary (multiple primary's would be bad).

The problem is an ordering issue. The way I have it now is the report will pickup the 1st secondary that it comes to. Abdillahi wants it to be able to pick the "correct" secondary. The only way to do that is by changing the Order criteria. In order to do that, we have to have something relevant to set that order.

As of yet, that crucial piece of info has not been provided.

Jason

[Former Member]

Cool, thanks Jason.

[Former Member]

Jason,

Basically, Steve Kerry is 98% of the time primary contact of all these customers or he is, 2% of the time, the secondary contact. The problem with secondary contact is that also there are other secondary contacts besides him for that customer. So I would want to only Steve Kerry name showing when there are other secondary contacts as well.

As for Unique quality that makes Steve Kerry preferrable, I am assuming you meant the database ID. If that is the question you asked, Steve Kerry has unique database ID.

[Former Member]

Abdillahi,

No that's not what I meant. I need to know what part of the data tells me that you deal with Steve 98% of the time and not Jeff. If it's simply a matter of personal preference or personal relationship then there is nothing there to tell CR that one person is "better" that the other (assuming they are both secondary's).

If, on the other hand, you can look at the data and know that Steve in the one... What part of the data tells you that?

Jason

[Former Member]

Jason,

I will be using the "Contact name" plus the "Contact type" to determine that.

Something like: contact type= "secondary" and contact name = "Steve Kerry"

[Former Member]

What I need to know is what makes Steve different than Jeff. They are both "Secondary" contacts. What unique quality makes Steve preferable?

[Former Member]

Jason,

You are brilliant. Your solution has solved 98 percent of my problem.

Here is the issue now:

Some times, there are multiple secondary contact types. If that is the case, then I would want to specific contact name. For instance, Company XYZ has:

primary contact = James Ryan

Secondary contact = Jeff Prior

Secondary contact = Steve Kerry

I want to display "Steve Kerry" as the secondary contact and ignore Jeff Prior.

[Former Member]

Abdillahi,

It's certainly doable. How are you making the decision the choose Steve over Jeff? What field are you using to make that determination.

In the end, you will use the "order" to achieve the results you are looking for.

Jason