Trouble With Replace Formula
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17450
Printed Date: 02 May 2024 at 6:54am
Topic: Trouble With Replace Formula
Posted By: blazeme182
Subject: Trouble With Replace Formula
Date Posted: 29 Aug 2012 at 10:45am
Hey I have a field in my system that has 3 letter codes for employee names. I want my report to show the full names of multiple contacts at a time.
ex. a record would show "PEP, JSM, JJO"
I want it to transform that into "Paul-Eric Poitras, John Smith, Jack Jones"
Any help would be appreciated.
|
Replies:
Posted By: CircleD
Date Posted: 29 Aug 2012 at 10:30pm
If you have a field that has the full names then you should be able to just swap the fields. Or you should be able to use a formula similar to
If {table.name}= {table.initials}
then {table.name}
|
Posted By: blazeme182
Date Posted: 30 Aug 2012 at 2:27am
That would not work because I do not have a field with the full names. I can not have a field with the full names because the fields do not accept enough characters for multiple names. I therefore had to abbreviate them.
I have tried using a replace formula but i can't figure out how to do it for more than one name.
ex.
Replace ({SaleComps.condition}, 'PEP', 'Paul-Éric Poitras')
|
Posted By: kevlray
Date Posted: 30 Aug 2012 at 5:25am
You actually can either use a series of If functions (put semi-colon after each one) like this
If {SaleComps.condition} = 'PEP' then 'Paul-Éric Poitras';
If {SaleComps.condition}= 'JSM' then 'John Smith'; etc.
I think you could do the same with the replace function (seperate with a semi-colon). Also you may be able to use the switch function.
switch({SaleComps.condition} = 'PEP', 'Paul-Éric Poitras', {SaleComps.condition}= 'JSM', 'John Smith', {SaleComps.condition}= 'JJO', 'Jack Jones), of course if you have a lot of names it can get tedious. Also is someone leaves, gets hired, changes name. This formula would have to be edited.
I hope this helps.
|
Posted By: blazeme182
Date Posted: 30 Aug 2012 at 9:50am
This works great when I only have one name but I am having a problem entering multiple names. I need some kinda formula that finds the three letters in the feild.
EX.
the field would be:
PEP,JSM
Which i want to show as:
Paul-Éric Poitras, John Smith
|
Posted By: kevlray
Date Posted: 30 Aug 2012 at 1:38pm
|
Are you using a parameter and selecting names?!?
|
Posted By: blazeme182
Date Posted: 31 Aug 2012 at 1:50am
Posted By: blazeme182
Date Posted: 31 Aug 2012 at 7:27am
I am trying to connect past transactions to employees.
In my database the field gives me the option of selecting multiple values for example:
If I select one value it will insert it into the data base as "PEP" if i select 2 values it will insert it as "PEP,JJO" ...
I have tried using this formula but it only gives me on of the names
if 'PEP' in {SaleComps.condition} then ', Paul-Éric Poitras'
+
if 'JJO' in {SaleComps.condition} then ', Jack Jones'
but it will only show Paul-Eric Poitras and not Jack Jones.
|
Posted By: blazeme182
Date Posted: 04 Sep 2012 at 4:53am
|
Please if someone knows how to resolve this let me know. I'm going crazy here.
|
Posted By: kevlray
Date Posted: 04 Sep 2012 at 7:19am
|
Did you want to select (filter on {SaleComps.condition}) just certain names, or did you want to have the names displayed based on the {SaleComps.condition} field?
|
Posted By: blazeme182
Date Posted: 04 Sep 2012 at 12:00pm
|
I wanted it based on the field. The field has a unique 3 letter code for each contact, that is separated by commas.
|
|