Custom name formats were one of the first customizations done by MGB. We took a publicly posted version from another CRM site and adapted it. Each custom format is a function that pulls a standard set of parameters passed to it by CRM.
The easiest way to create a new one is to find one of the current formats that most closely resembles it and use that one as a base. The custom code can be found in TFS here
Note the the process described here is how I’ve made updates given my permissions and knowledge – I assume the same result could be done with different methods and techniques.
- copy the sql statement from TFS into SSMS, and comment out the xml and create function statements, basically everything before the declare statements, and the end create functions at the bottom. This preserves the basic SQL so we can view it as a select instead of a function.
- Select the database you want to work with. For this example I will be using BWH OLTP QA because I have examples of issues for that affiliate
- Normally when the function is called CRM passes parameters to it. For testing, I found it simplest to re-create the parameters by adding the code below before the declare statements in the existing code. Code is below. I usually use my record for testing, and make changes to name, household, etc.
/*
–###########################For testing only against database#########################
CREATE TABLE #test (
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)
)
INSERT INTO #test (CONSTITUENTID, LASTNAME, FIRSTNAME, MIDDLENAME, TITLE, SUFFIX, SPOUSELASTNAME, SPOUSEFIRSTNAME, SPOUSEMIDDLENAME, SPOUSETITLE, SPOUSESUFFIX)
select
CONSTITUENT.ID,
CONSTITUENT.KEYNAME,
CONSTITUENT.FIRSTNAME,
CONSTITUENT.MIDDLENAME,
(select DESCRIPTION from dbo.TITLECODE where ID = CONSTITUENT.TITLECODEID),
(select DESCRIPTION from dbo.SUFFIXCODE where ID = CONSTITUENT.SUFFIXCODEID),
SPOUSE.KEYNAME,
SPOUSE.FIRSTNAME,
SPOUSE.MIDDLENAME,
(select DESCRIPTION from dbo.TITLECODE where ID = SPOUSE.TITLECODEID),
(select DESCRIPTION from dbo.SUFFIXCODE where ID = SPOUSE.SUFFIXCODEID)
from dbo.CONSTITUENT
left outer join dbo.RELATIONSHIP on CONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID and RELATIONSHIP.ISSPOUSE = 1
left outer join dbo.DECEASEDCONSTITUENT as SPOUSEDECEASEDCONSTIT on SPOUSEDECEASEDCONSTIT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
left outer join dbo.CONSTITUENT as SPOUSE on SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID and SPOUSEDECEASEDCONSTIT.ID is null and SPOUSE.ISINACTIVE = 0
where CONSTITUENT.ID –= ’06BFE529-0C47-42C8-A505-38C5C72D30D6′ –helen
= (SELECT id FROM constituent WHERE LOOKUPID=’161316′) ––/art
declare
@CONSTITUENTID UNIQUEIDENTIFIER = (SELECT constituentid FROM #test)
,@LASTNAME nvarchar(100) = (SELECT lastname FROM #test)
,@FIRSTNAME nvarchar(50) = (SELECT firstname FROM #test)
,@MIDDLENAME nvarchar(50) = (SELECT middlename FROM #test)
,@TITLE nvarchar(100) = (SELECT title FROM #test)
,@SUFFIX nvarchar(100) = (SELECT suffix FROM #test)
,@SPOUSELASTNAME nvarchar(100) = (SELECT spouselastname FROM #test)
,@SPOUSEFIRSTNAME nvarchar(50) = (SELECT spousefirstname FROM #test)
,@SPOUSEMIDDLENAME nvarchar(50) = (SELECT spousemiddlename FROM #test)
,@SPOUSETITLE nvarchar(100) = (SELECT spousetitle FROM #test)
,@SPOUSESUFFIX nvarchar(100) = (SELECT spousesuffix FROM #test)
–###################################End h3 testing##########################
*/
4. Add the text below before “return @result” at the bottom
select @result
drop table #test
5. Open QA Atlas, determine the ID numbers you want to use for testing and update the selection that builds the temp table. Name formats are dependent on household, primary or secondary member of household, gender, nicknames, if title exists or not, if suffix exists or not, if first name is an initial or not, etc. Testing and validation is key!
6. Update code as needed to obtain desired result. When complete and things look good, I usually delete all the rows I added for testing, and uncomment again all the xml stuff commented out originally. Basically putting it back so only the changes are the requested updates.
7. I give it to Wayne, who waves his wand and does something or other and adds it to QA.
8. To enable validation in QA, you need to confirm the new/updated name format exists and is marked as a default. Then run the global change that will add the name format to all records. You will need to do this in prod as well once it is validated and migrates through staging, etc.
9. Update the wiki, especially if this is a name format going to all affiliates. Custom formats described here