Custom name formats

I recently had to create custom name formats and while it isn’t a very hard thing, it can be quite confusing. Thankfully we have a name format function in the SDK that can be utilized for this.

Below is the sample code of one that I had to create, (excluding the spec xml).

create function dbo.USR_UFN_NAMEFORMAT_INDIVIDUALINFORMALSALUTATION
(
@CONSTITUENTID uniqueidentifier,
@LASTNAME nvarchar(100),
@FIRSTNAME nvarchar(50),
@MIDDLENAME nvarchar(50),
@TITLE nvarchar(100),
@SUFFIX nvarchar(100),
@SPOUSELASTNAME nvarchar(100),
@SPOUSEFIRSTNAME nvarchar(50),
@SPOUSEMIDDLENAME nvarchar(50),
@SPOUSETITLE nvarchar(100),
@SPOUSESUFFIX nvarchar(100)
)
returns nvarchar(700)
with execute as caller
as
begin
declare @RETVAL nvarchar(700),
@NICKNAME nvarchar(100);

select
@NICKNAME = NICKNAME,
@FIRSTNAME = FIRSTNAME
from
dbo.USR_UFN_GETNAMEFORMATINFORMATION
(
@CONSTITUENTID,
@LASTNAME,
@FIRSTNAME,
@MIDDLENAME,
@TITLE,
@SUFFIX,
@SPOUSELASTNAME,
@SPOUSEFIRSTNAME,
@SPOUSEMIDDLENAME,
@SPOUSETITLE,
@SPOUSESUFFIX,
0
);

if(len(@NICKNAME) > 0)
begin
set @RETVAL = @NICKNAME;
end
else
begin
set @RETVAL = @FIRSTNAME;
end

set @RETVAL = ltrim(rtrim(@RETVAL));

return @RETVAL;
end

As you can see I pass in what the SDK gives me to a custom function. This function is quite complicated, below is reduced version of it.

create function dbo.USR_UFN_GETNAMEFORMATINFORMATION
(
@CONSTITUENTID uniqueidentifier,
@LASTNAME nvarchar(100),
@FIRSTNAME nvarchar(50),
@MIDDLENAME nvarchar(50),
@TITLE nvarchar(100),
@SUFFIX nvarchar(100),
@SPOUSELASTNAME nvarchar(100),
@SPOUSEFIRSTNAME nvarchar(50),
@SPOUSEMIDDLENAME nvarchar(50),
@SPOUSETITLE nvarchar(100),
@SPOUSESUFFIX nvarchar(100),
@ISHOUSEHOLD bit
)
returns @T table
(
CONSTITUENTID uniqueidentifier,
NICKNAME nvarchar(100),
LASTNAME nvarchar(100),
FIRSTNAME nvarchar(50),
MIDDLENAME nvarchar(50),
MIDDLEINITIAL nvarchar(2),
TITLE nvarchar(100),
TITLE2 nvarchar(100),
SUFFIX nvarchar(100),
SUFFIX2 nvarchar(100),
SPOUSENICKNAME nvarchar(100),
SPOUSELASTNAME nvarchar(100),
SPOUSEFIRSTNAME nvarchar(50),
SPOUSEMIDDLENAME nvarchar(50),
SPOUSEMIDDLEINITIAL nvarchar(2),
SPOUSETITLE nvarchar(100),
SPOUSETITLE2 nvarchar(100),
SPOUSESUFFIX nvarchar(100),
SPOUSESUFFIX2 nvarchar(100),
USEJESUIT bit,
USEWIDOW bit,
USECARDINAL bit,
MARRIEDWITHDIFFERENTLASTNAMES bit
)
with execute as caller
as
begin

insert into
@T
select
C.ID,
isnull(C.NICKNAME, ''), --NICKNAME
isnull(@LASTNAME, ''),
isnull(@FIRSTNAME, ''),
isnull(@MIDDLENAME, ''),
(case when len(isnull(@MIDDLENAME, '')) > 0 then substring(@MIDDLENAME, 1, 1) + '.' else '' end), --MIDDLEINITIAL,
isnull(@TITLE, ''),
(case when U3.ID is not null and @ISHOUSEHOLD = 1 then '' else isnull(T.DESCRIPTION, '') end), --TITLE2
(case when len(isnull(@SUFFIX, '')) > 0 then case when substring(@SUFFIX, 1, 2) <> ', ' then ', ' + @SUFFIX else @SUFFIX end else '' end),
(case when len(isnull(S.DESCRIPTION, '')) > 0 then case when substring(S.DESCRIPTION, 1, 2) <> ', ' then ', ' + S.DESCRIPTION else S.DESCRIPTION end else '' end), --SUFFIX2
isnull(CS.NICKNAME, ''), --SPOUSENICKNAME
isnull(@SPOUSELASTNAME, ''),
isnull(@SPOUSEFIRSTNAME, ''),
isnull(@SPOUSEMIDDLENAME, ''),
(case when len(isnull(@SPOUSEMIDDLENAME, '')) > 0 then substring(@SPOUSEMIDDLENAME, 1, 1) + '.' else '' end), --SPOUSEMIDDLEINITIAL,
isnull(@SPOUSETITLE, ''),
(case when U4.ID is not null and @ISHOUSEHOLD = 1 then '' else isnull(TS.DESCRIPTION, '') end), --SPOUSETITLE2
(case when len(isnull(@SPOUSESUFFIX, '')) > 0 then case when substring(@SPOUSESUFFIX, 1, 2) <> ', ' then ', ' + @SPOUSESUFFIX else @SPOUSESUFFIX end else '' end),
(case when len(isnull(SS.DESCRIPTION, '')) > 0 then case when substring(SS.DESCRIPTION, 1, 2) <> ', ' then ', ' + SS.DESCRIPTION else SS.DESCRIPTION end else '' end), --SPOUSESUFFIX2
(case when U2.NAMEFORMATTYPECODE = 2 then 1 else 0 end), --USEJESUIT
(case when C.MARITALSTATUSCODEID = '93933337-221B-4EB4-9B2B-21858655EE60' and C.GENDERCODE = 2 and SM.VALUE <> 'Alum' and SM.VALUE <> 'Alum(ND)' then 1 else 0 end), --USEWIDOW
(case when U1.NAMEFORMATTYPECODE = 3 then 1 else 0 end), --USECARDINAL
(case when R.ENDDATE is null and R.ID is not null and (@SPOUSELASTNAME <> @LASTNAME) then 1 else 0 end) --MARRIEDWITHDIFFERENTLASTNAMES
from
CONSTITUENT C
left join USR_NAMEFORMATLOGIC U1 on C.TITLECODEID = U1.TITLECODEID
left join USR_NAMEFORMATLOGIC U2 on C.SUFFIXCODEID = U2.SUFFIXCODEID
left join SUFFIXCODE S on C.SUFFIX2CODEID = S.ID
left join RELATIONSHIP R on C.ID = R.RELATIONSHIPCONSTITUENTID and R.ISSPOUSE = 1
left join CONSTITUENT CS on R.RECIPROCALCONSTITUENTID = CS.ID
left join USR_NAMEFORMATLOGIC US1 on CS.TITLECODEID = US1.TITLECODEID
left join USR_NAMEFORMATLOGIC US2 on CS.SUFFIXCODEID = US2.SUFFIXCODEID
left join SUFFIXCODE SS on CS.SUFFIX2CODEID = SS.ID
left join SMARTFIELDFA6CB611F9B94F6A923FF3E2F05A17C0 SM on C.ID = SM.ID
left join TITLECODE TS on CS.TITLE2CODEID = TS.ID
left join TITLECODE T on C.TITLE2CODEID = T.ID
left join USR_NAMEFORMAT_TITLECODE U3 on C.TITLECODEID = U3.TITLE1CODEID and C.TITLE2CODEID = U3.TITLE2CODEID
left join USR_NAMEFORMAT_TITLECODE U4 on CS.TITLECODEID = U4.TITLE1CODEID and CS.TITLE2CODEID = U4.TITLE2CODEID
where
C.ID = @CONSTITUENTID;

return;
end

As you can see all of our name fixing and logic look ups (USEWIDOW, USECARDINAL, etc) are in this function. We have 7 custom name format functions that all call this code and just use the parts that they need.

We also have some logic tables that hold custom logic. USR_NAMEFORMATLOGIC is surfaced as the image below. This allows the end users to configure how the system knows what a jesuit is.

config

Once your name format has be completed and loaded into the database you will then need to tell CRM to use it. The script below does just that. FYI this script may take hours to run as it builds every name format for every constituent in the database. You will also want to look at the “Add the name format defaults” section, since it default the primary addressee/salutation and not every organization is the same.

use [];

set nocount on;

begin try
begin tran;

declare @ID uniqueidentifier = newid(),
@CURRENTDATE datetime = getdate(),
@CHANGEAGENTID uniqueidentifier;

if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

declare @USR_UFN_NAMEFORMAT_INDIVIDUALINFORMALADDRESSEE_COMPLETED bit = 1,
@USR_UFN_NAMEFORMAT_INDIVIDUALFORMALSALUTATION_COMPLETED bit = 1,
@USR_UFN_NAMEFORMAT_INDIVIDUALFORMALADDRESSEE_COMPLETED bit = 1,
@USR_UFN_NAMEFORMAT_INDIVIDUALINFORMALSALUTATION_COMPLETED bit = 1,
@USR_UFN_NAMEFORMAT_HOUSEHOLDINFORMALSALUTATION_COMPLETED bit = 1,
@USR_UFN_NAMEFORMAT_HOUSEHOLDFORMALSALUTATION_COMPLETED bit = 1,
@USR_UFN_NAMEFORMAT_HOUSEHOLDFORMALADDRESSEE_COMPLETED bit = 1,
@SPACERS nvarchar(100) = '--------------------------------------------------';

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
print @SPACERS
print 'Starting: Clear all name format data'
print @SPACERS

delete NAMEFORMATDEFAULT

print @SPACERS
print 'Completed: Clear all name format data'
print @SPACERS
print ''

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
print @SPACERS
print 'Starting: Add the name format types'
print @SPACERS

declare @DESCRIPTION nvarchar(100) = '';

if(@USR_UFN_NAMEFORMAT_INDIVIDUALINFORMALADDRESSEE_COMPLETED = 1)
begin
set @DESCRIPTION = 'Individual Informal Addressee';

if not exists(select 1 from NAMEFORMATTYPECODE where DESCRIPTION = @DESCRIPTION)
begin
exec USP_NAMEFORMATTYPECODE_CREATEENTRY @DESCRIPTION;
end
end

if(@USR_UFN_NAMEFORMAT_INDIVIDUALINFORMALSALUTATION_COMPLETED = 1)
begin
set @DESCRIPTION = 'Individual Informal Salutation';

if not exists(select 1 from NAMEFORMATTYPECODE where DESCRIPTION = @DESCRIPTION)
begin
exec USP_NAMEFORMATTYPECODE_CREATEENTRY @DESCRIPTION;
end
end

if(@USR_UFN_NAMEFORMAT_INDIVIDUALFORMALSALUTATION_COMPLETED = 1)
begin
set @DESCRIPTION = 'Individual Formal Salutation';

if not exists(select 1 from NAMEFORMATTYPECODE where DESCRIPTION = @DESCRIPTION)
begin
exec USP_NAMEFORMATTYPECODE_CREATEENTRY @DESCRIPTION;
end
end

if(@USR_UFN_NAMEFORMAT_INDIVIDUALFORMALADDRESSEE_COMPLETED = 1)
begin
set @DESCRIPTION = 'Individual Formal Addressee';

if not exists(select 1 from NAMEFORMATTYPECODE where DESCRIPTION = @DESCRIPTION)
begin
exec USP_NAMEFORMATTYPECODE_CREATEENTRY @DESCRIPTION;
end
end

if(@USR_UFN_NAMEFORMAT_HOUSEHOLDINFORMALSALUTATION_COMPLETED = 1)
begin
set @DESCRIPTION = 'Household Informal Salutation';

if not exists(select 1 from NAMEFORMATTYPECODE where DESCRIPTION = @DESCRIPTION)
begin
exec USP_NAMEFORMATTYPECODE_CREATEENTRY @DESCRIPTION;
end
end

if(@USR_UFN_NAMEFORMAT_HOUSEHOLDFORMALSALUTATION_COMPLETED = 1)
begin
set @DESCRIPTION = 'Household Formal Salutation';

if not exists(select 1 from NAMEFORMATTYPECODE where DESCRIPTION = @DESCRIPTION)
begin
exec USP_NAMEFORMATTYPECODE_CREATEENTRY @DESCRIPTION;
end
end

if(@USR_UFN_NAMEFORMAT_HOUSEHOLDFORMALADDRESSEE_COMPLETED = 1)
begin
set @DESCRIPTION = 'Household Formal Addressee';

if not exists(select 1 from NAMEFORMATTYPECODE where DESCRIPTION = @DESCRIPTION)
begin
exec USP_NAMEFORMATTYPECODE_CREATEENTRY @DESCRIPTION;
end
end

print @SPACERS
print 'Completed: Add the name format types'
print @SPACERS
print ''
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
print @SPACERS
print 'Starting: Update the name format functions'
print @SPACERS

declare @SEQUENCE int = 0,
@EXAMPLE nvarchar(200) = '',
@DISPLAY nvarchar(200) = '',
@FUNCTION nvarchar(100) = '';

if(@USR_UFN_NAMEFORMAT_INDIVIDUALFORMALADDRESSEE_COMPLETED = 1)
begin
set @EXAMPLE = 'Individual Formal Addressee';
set @DISPLAY = 'Individual Formal Addressee'
set @FUNCTION = 'USR_UFN_NAMEFORMAT_INDIVIDUALFORMALADDRESSEE';

update
NAMEFORMATFUNCTION
set
SEQUENCE = @SEQUENCE,
EXAMPLE = @EXAMPLE,
DISPLAY = @DISPLAY
where
FORMATSQLFUNCTION = @FUNCTION
end

if(@USR_UFN_NAMEFORMAT_INDIVIDUALFORMALSALUTATION_COMPLETED = 1)
begin
set @SEQUENCE = 1;
set @EXAMPLE = 'Individual Formal Salutation';
set @DISPLAY = 'Individual Formal Salutation';
set @FUNCTION = 'USR_UFN_NAMEFORMAT_INDIVIDUALFORMALSALUTATION';

update
NAMEFORMATFUNCTION
set
SEQUENCE = @SEQUENCE,
EXAMPLE = @EXAMPLE,
DISPLAY = @DISPLAY
where
FORMATSQLFUNCTION = @FUNCTION
end

if(@USR_UFN_NAMEFORMAT_INDIVIDUALINFORMALSALUTATION_COMPLETED = 1)
begin
set @SEQUENCE = 1;
set @EXAMPLE = 'Individual Informal Salutation';
set @DISPLAY = 'Individual Informal Salutation';
set @FUNCTION = 'USR_UFN_NAMEFORMAT_INDIVIDUALINFORMALSALUTATION';

update
NAMEFORMATFUNCTION
set
SEQUENCE = @SEQUENCE,
EXAMPLE = @EXAMPLE,
DISPLAY = @DISPLAY
where
FORMATSQLFUNCTION = @FUNCTION
end

if(@USR_UFN_NAMEFORMAT_INDIVIDUALINFORMALADDRESSEE_COMPLETED = 1)
begin

set @SEQUENCE = 3;
set @EXAMPLE = 'Individual Informal Addressee';
set @DISPLAY = 'Individual Informal Addressee';
set @FUNCTION = 'USR_UFN_NAMEFORMAT_INDIVIDUALINFORMALADDRESSEE';

update
NAMEFORMATFUNCTION
set
SEQUENCE = @SEQUENCE,
EXAMPLE = @EXAMPLE,
DISPLAY = @DISPLAY
where
FORMATSQLFUNCTION = @FUNCTION
end

if(@USR_UFN_NAMEFORMAT_HOUSEHOLDINFORMALSALUTATION_COMPLETED = 1)
begin

set @SEQUENCE = 4;
set @EXAMPLE = 'Household Informal Salutation';
set @DISPLAY = 'Household Informal Salutation';
set @FUNCTION = 'USR_UFN_NAMEFORMAT_HOUSEHOLDINFORMALSALUTATION';

update
NAMEFORMATFUNCTION
set
SEQUENCE = @SEQUENCE,
EXAMPLE = @EXAMPLE,
DISPLAY = @DISPLAY
where
FORMATSQLFUNCTION = @FUNCTION
end

if(@USR_UFN_NAMEFORMAT_HOUSEHOLDFORMALSALUTATION_COMPLETED = 1)
begin

set @SEQUENCE = 5;
set @EXAMPLE = 'Household Formal Salutation';
set @DISPLAY = 'Household Formal Salutation';
set @FUNCTION = 'USR_UFN_NAMEFORMAT_HOUSEHOLDFORMALSALUTATION';

update
NAMEFORMATFUNCTION
set
SEQUENCE = @SEQUENCE,
EXAMPLE = @EXAMPLE,
DISPLAY = @DISPLAY
where
FORMATSQLFUNCTION = @FUNCTION
end

if(@USR_UFN_NAMEFORMAT_HOUSEHOLDFORMALADDRESSEE_COMPLETED = 1)
begin

set @SEQUENCE = 7;
set @EXAMPLE = 'Household Formal Addressee';
set @DISPLAY = 'Household Formal Addressee';
set @FUNCTION = 'USR_UFN_NAMEFORMAT_HOUSEHOLDFORMALADDRESSEE';

update
NAMEFORMATFUNCTION
set
SEQUENCE = @SEQUENCE,
EXAMPLE = @EXAMPLE,
DISPLAY = @DISPLAY
where
FORMATSQLFUNCTION = @FUNCTION
end

declare @NONFORMATS table(ID uniqueidentifier, IDENT int identity(1, 1), SEQUENCE as (10 + IDENT), PROCESSED bit default 0);

insert into
@NONFORMATS
(
ID
)
select
ID
from
NAMEFORMATFUNCTION
where
FORMATSQLFUNCTION not like 'USR_UFN_NAMEFORMAT_%'

update
NF
set
NF.SEQUENCE = NS.SEQUENCE
from
NAMEFORMATFUNCTION NF
inner join @NONFORMATS NS on NF.ID = NS.ID

print @SPACERS
print 'Completed: Update the name format functions'
print @SPACERS
print ''
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
print @SPACERS
print 'Starting: Add the name format defaults'
print @SPACERS

declare @APPLYTOCODE tinyint = 0,
@NAMEFORMATTYPECODEID uniqueidentifier = null,
@NAMEFORMATFUNCTIONID uniqueidentifier = null,
@PRIMARYADDRESSEE bit = 0,
@PRIMARYSALUTATION bit = 0;

if(@USR_UFN_NAMEFORMAT_INDIVIDUALFORMALADDRESSEE_COMPLETED = 1)
begin
set @EXAMPLE = 'Individual Formal Addressee';
set @APPLYTOCODE = 0;
set @NAMEFORMATTYPECODEID = (select ID from NAMEFORMATTYPECODE where DESCRIPTION = @EXAMPLE);
set @NAMEFORMATFUNCTIONID = (select ID from NAMEFORMATFUNCTION where EXAMPLE = @EXAMPLE);
set @PRIMARYADDRESSEE = 1;
set @PRIMARYSALUTATION = 0;

insert into
NAMEFORMATDEFAULT
(
APPLYTOCODE,
NAMEFORMATTYPECODEID,
NAMEFORMATFUNCTIONID,
PRIMARYADDRESSEE,
PRIMARYSALUTATION,
ADDEDBYID,
CHANGEDBYID
)
select
@APPLYTOCODE,
@NAMEFORMATTYPECODEID,
@NAMEFORMATFUNCTIONID,
@PRIMARYADDRESSEE,
@PRIMARYSALUTATION,
@CHANGEAGENTID,
@CHANGEAGENTID
end

if(@USR_UFN_NAMEFORMAT_INDIVIDUALFORMALSALUTATION_COMPLETED = 1)
begin
set @EXAMPLE = 'Individual Formal Salutation';
set @APPLYTOCODE = 0;
set @NAMEFORMATTYPECODEID = (select ID from NAMEFORMATTYPECODE where DESCRIPTION = @EXAMPLE);
set @NAMEFORMATFUNCTIONID = (select ID from NAMEFORMATFUNCTION where EXAMPLE = @EXAMPLE);
set @PRIMARYADDRESSEE = 0;
set @PRIMARYSALUTATION = 0;

insert into
NAMEFORMATDEFAULT
(
APPLYTOCODE,
NAMEFORMATTYPECODEID,
NAMEFORMATFUNCTIONID,
PRIMARYADDRESSEE,
PRIMARYSALUTATION,
ADDEDBYID,
CHANGEDBYID
)
select
@APPLYTOCODE,
@NAMEFORMATTYPECODEID,
@NAMEFORMATFUNCTIONID,
@PRIMARYADDRESSEE,
@PRIMARYSALUTATION,
@CHANGEAGENTID,
@CHANGEAGENTID
end

if(@USR_UFN_NAMEFORMAT_INDIVIDUALINFORMALSALUTATION_COMPLETED = 1)
begin
set @EXAMPLE = 'Individual Informal Salutation';
set @APPLYTOCODE = 0;
set @NAMEFORMATTYPECODEID = (select ID from NAMEFORMATTYPECODE where DESCRIPTION = @EXAMPLE);
set @NAMEFORMATFUNCTIONID = (select ID from NAMEFORMATFUNCTION where EXAMPLE = @EXAMPLE);
set @PRIMARYADDRESSEE = 0;
set @PRIMARYSALUTATION = 1;

insert into
NAMEFORMATDEFAULT
(
APPLYTOCODE,
NAMEFORMATTYPECODEID,
NAMEFORMATFUNCTIONID,
PRIMARYADDRESSEE,
PRIMARYSALUTATION,
ADDEDBYID,
CHANGEDBYID
)
select
@APPLYTOCODE,
@NAMEFORMATTYPECODEID,
@NAMEFORMATFUNCTIONID,
@PRIMARYADDRESSEE,
@PRIMARYSALUTATION,
@CHANGEAGENTID,
@CHANGEAGENTID
end

if(@USR_UFN_NAMEFORMAT_INDIVIDUALINFORMALADDRESSEE_COMPLETED = 1)
begin
set @EXAMPLE = 'Individual Informal Addressee';
set @APPLYTOCODE = 0;
set @NAMEFORMATTYPECODEID = (select ID from NAMEFORMATTYPECODE where DESCRIPTION = @EXAMPLE);
set @NAMEFORMATFUNCTIONID = (select ID from NAMEFORMATFUNCTION where EXAMPLE = @EXAMPLE);
set @PRIMARYADDRESSEE = 0;
set @PRIMARYSALUTATION = 0;

insert into
NAMEFORMATDEFAULT
(
APPLYTOCODE,
NAMEFORMATTYPECODEID,
NAMEFORMATFUNCTIONID,
PRIMARYADDRESSEE,
PRIMARYSALUTATION,
ADDEDBYID,
CHANGEDBYID
)
select
@APPLYTOCODE,
@NAMEFORMATTYPECODEID,
@NAMEFORMATFUNCTIONID,
@PRIMARYADDRESSEE,
@PRIMARYSALUTATION,
@CHANGEAGENTID,
@CHANGEAGENTID
end

if(@USR_UFN_NAMEFORMAT_HOUSEHOLDINFORMALSALUTATION_COMPLETED = 1)
begin
set @EXAMPLE = 'Household Informal Salutation';
set @APPLYTOCODE = 0;
set @NAMEFORMATTYPECODEID = (select ID from NAMEFORMATTYPECODE where DESCRIPTION = @EXAMPLE);
set @NAMEFORMATFUNCTIONID = (select ID from NAMEFORMATFUNCTION where EXAMPLE = @EXAMPLE);
set @PRIMARYADDRESSEE = 0;
set @PRIMARYSALUTATION = 0;

insert into
NAMEFORMATDEFAULT
(
APPLYTOCODE,
NAMEFORMATTYPECODEID,
NAMEFORMATFUNCTIONID,
PRIMARYADDRESSEE,
PRIMARYSALUTATION,
ADDEDBYID,
CHANGEDBYID
)
select
@APPLYTOCODE,
@NAMEFORMATTYPECODEID,
@NAMEFORMATFUNCTIONID,
@PRIMARYADDRESSEE,
@PRIMARYSALUTATION,
@CHANGEAGENTID,
@CHANGEAGENTID
end

if(@USR_UFN_NAMEFORMAT_HOUSEHOLDFORMALSALUTATION_COMPLETED = 1)
begin
set @EXAMPLE = 'Household Formal Salutation';
set @APPLYTOCODE = 0;
set @NAMEFORMATTYPECODEID = (select ID from NAMEFORMATTYPECODE where DESCRIPTION = @EXAMPLE);
set @NAMEFORMATFUNCTIONID = (select ID from NAMEFORMATFUNCTION where EXAMPLE = @EXAMPLE);
set @PRIMARYADDRESSEE = 0;
set @PRIMARYSALUTATION = 0;

insert into
NAMEFORMATDEFAULT
(
APPLYTOCODE,
NAMEFORMATTYPECODEID,
NAMEFORMATFUNCTIONID,
PRIMARYADDRESSEE,
PRIMARYSALUTATION,
ADDEDBYID,
CHANGEDBYID
)
select
@APPLYTOCODE,
@NAMEFORMATTYPECODEID,
@NAMEFORMATFUNCTIONID,
@PRIMARYADDRESSEE,
@PRIMARYSALUTATION,
@CHANGEAGENTID,
@CHANGEAGENTID
end

if(@USR_UFN_NAMEFORMAT_HOUSEHOLDFORMALADDRESSEE_COMPLETED = 1)
begin
set @EXAMPLE = 'Household Formal Addressee';
set @APPLYTOCODE = 0;
set @NAMEFORMATTYPECODEID = (select ID from NAMEFORMATTYPECODE where DESCRIPTION = @EXAMPLE);
set @NAMEFORMATFUNCTIONID = (select ID from NAMEFORMATFUNCTION where EXAMPLE = @EXAMPLE);
set @PRIMARYADDRESSEE = 0;
set @PRIMARYSALUTATION = 0;

insert into
NAMEFORMATDEFAULT
(
APPLYTOCODE,
NAMEFORMATTYPECODEID,
NAMEFORMATFUNCTIONID,
PRIMARYADDRESSEE,
PRIMARYSALUTATION,
ADDEDBYID,
CHANGEDBYID
)
select
@APPLYTOCODE,
@NAMEFORMATTYPECODEID,
@NAMEFORMATFUNCTIONID,
@PRIMARYADDRESSEE,
@PRIMARYSALUTATION,
@CHANGEAGENTID,
@CHANGEAGENTID
end

print @SPACERS
print 'Completed: Add the name format defaults'
print @SPACERS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
print @SPACERS
print 'Starting: Add the name formats'
print @SPACERS

delete NAMEFORMAT

insert into
[dbo].[NAMEFORMAT]
(
[CONSTITUENTID],
[NAMEFORMATTYPECODEID],
[NAMEFORMATFUNCTIONID],
[ADDEDBYID],
[CHANGEDBYID],
[PRIMARYADDRESSEE],
[PRIMARYSALUTATION]
)
select
C.ID,
NFD.NAMEFORMATTYPECODEID,
NFD.NAMEFORMATFUNCTIONID,
@CHANGEAGENTID,
@CHANGEAGENTID,
NFD.PRIMARYADDRESSEE,
NFD.PRIMARYSALUTATION
from
CONSTITUENT C
cross join NAMEFORMATDEFAULT NFD
where
C.ISCONSTITUENT = 1
and C.ISORGANIZATION = 0
and NFD.APPLYTOCODE = 0

select
NF.FORMATTEDNAME,
NFT.DESCRIPTION NAMEFORMATTYPE,
NFF.DISPLAY NAMEFORMATFUNCTION,
C.*
from
NAMEFORMAT NF
inner join CONSTITUENT C on NF.CONSTITUENTID = C.ID
inner join NAMEFORMATTYPECODE NFT on NF.NAMEFORMATTYPECODEID = NFT.ID
inner join NAMEFORMATFUNCTION NFF on NF.NAMEFORMATFUNCTIONID = NFF.ID
where
NF.FORMATTEDNAME is null
and C.GENDERCODE > 0
order by
NFT.[DESCRIPTION] asc

print @SPACERS
print 'Completed: Add the name formats'
print @SPACERS

commit tran;
end try
begin catch
rollback tran;

exec USP_RAISE_ERROR;
end catch

If you have any questions please comment below or email me directly at chris.whisenhunt@gmail.com.

Happy coding 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s