| Author |
Message |
blazeme182
Groupie
Joined: 15 Dec 2009
Online Status: Offline
Posts: 44
|
   Topic: Trouble With Replace Formula
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.
|
 IP Logged |
|
|
CircleD
Senior Member
Joined: 11 Mar 2011
Location: United States
Online Status: Offline
Posts: 251
|
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}
|
|
IP Logged |
|
blazeme182
Groupie
Joined: 15 Dec 2009
Online Status: Offline
Posts: 44
|
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')
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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.
|
|
IP Logged |
|
blazeme182
Groupie
Joined: 15 Dec 2009
Online Status: Offline
Posts: 44
|
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
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 30 Aug 2012 at 1:38pm |
|
Are you using a parameter and selecting names?!?
|
|
IP Logged |
|
blazeme182
Groupie
Joined: 15 Dec 2009
Online Status: Offline
Posts: 44
|
Posted: 31 Aug 2012 at 1:50am |
|
not sure
Edited by blazeme182 - 31 Aug 2012 at 7:28am
|
|
IP Logged |
|
blazeme182
Groupie
Joined: 15 Dec 2009
Online Status: Offline
Posts: 44
|
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.
|
|
IP Logged |
|
blazeme182
Groupie
Joined: 15 Dec 2009
Online Status: Offline
Posts: 44
|
Posted: 04 Sep 2012 at 4:53am |
|
Please if someone knows how to resolve this let me know. I'm going crazy here.
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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?
|
|
IP Logged |
|
|
|
