| Author |
Message |
aaml216
Newbie
Joined: 02 Nov 2011
Online Status: Offline
Posts: 24
|
   Topic: Concatenate formula when one field is empty
Posted: 10 Jun 2015 at 8:21am |
|
I did a simple formula to combine the name fields into one, e.g. Smith, John A
{DEMOGRAPHICS.LASTNAME} & ", " & {DEMOGRAPHICS.FIRSTNAME}& " "&
{DEMOGRAPHICS.MIDDLEINITIAL}
But it only works if the person has a middle initial. Otherwise it returns a blank field. I want the name returned regardless of whether they have a middle initial.
Thanks!
|
|
Amanda from the Mid-Atlantic
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Jun 2015 at 9:24am |
likely the NULL in the MI is stopping the formula fom returning anything.
either set the formula to use 'default values for null' or handle it as
{DEMOGRAPHICS.LASTNAME} & ", " & {DEMOGRAPHICS.FIRSTNAME}&
(If is null {DEMOGRAPHICS.MIDDLEINITIAL} or trim({DEMOGRAPHICS.MIDDLEINITIAL})="" then "" else
{DEMOGRAPHICS.MIDDLEINITIAL} )
|
|
IP Logged |
|
aaml216
Newbie
Joined: 02 Nov 2011
Online Status: Offline
Posts: 24
|
Posted: 11 Jun 2015 at 3:24am |
|
Thanks so much for taking a look at this. I am getting an error message though. All I did was to change "is null" to "isnull." I think the error it may have to do with the parentheses...? It is saying that after the isnull portion, it is ready to see a "then."
What is the purpose of the trim statement?
|
|
Amanda from the Mid-Atlantic
|
|
IP Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
Posted: 11 Jun 2015 at 5:00am |
|
Personally I have good success with the following setup.
{DEMOGRAPHICS.LASTNAME} & ", " & {DEMOGRAPHICS.FIRSTNAME}&(if len({DEMOGRAPHICS.MIDDLEINITIAL}) >0 then " " & {DEMOGRAPHICS.MIDDLEINITIAL} else "")
|
|
IP Logged |
|
aaml216
Newbie
Joined: 02 Nov 2011
Online Status: Offline
Posts: 24
|
Posted: 11 Jun 2015 at 5:38am |
|
kevlray - Sorry, I still only get patients who have middle initials...
|
|
Amanda from the Mid-Atlantic
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Jun 2015 at 8:34am |
|
Sorry, I inserted some SQL ...
(If isnull({DEMOGRAPHICS.MIDDLEINITIAL}) or trim({DEMOGRAPHICS.MIDDLEINITIAL})="" ...
The trim is accounting for any middle initials that someone stuck in a space instead of an empty string.
Kevlray's code will work if you set the formula field to use default values for nulls
|
|
IP Logged |
|
aaml216
Newbie
Joined: 02 Nov 2011
Online Status: Offline
Posts: 24
|
Posted: 11 Jun 2015 at 8:38am |
|
So the ... is:
then {DEMOGRAPHICS.LASTNAME} & ", " & {DEMOGRAPHICS.FIRSTNAME}& " " & {DEMOGRAPHICS.MIDDLEINITIAL}
Sorry to be so dense. I am pretty new to this.
|
|
Amanda from the Mid-Atlantic
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 11 Jun 2015 at 10:43am |
|
{DEMOGRAPHICS.LASTNAME} & ", " & {DEMOGRAPHICS.FIRSTNAME}&
(If isnull({DEMOGRAPHICS.MIDDLEINITIAL}) or trim({DEMOGRAPHICS.MIDDLEINITIAL})="" then "" else
{DEMOGRAPHICS.MIDDLEINITIAL} )
|
|
IP Logged |
|
|
|
