Create a Joint Name Format

When you require complex name formats such as joint name formats that display spouse information alongside constituent names, you may want to create a custom name format with the SQL function spec instead of configuring a name format on the Add name format screen in Blackbaud CRM.

SQL function specs gives you more flexibility than the Add name format screen and allow you to implement complex formatting rules and include constituent data in the name format that is not available through the Add name format screen. They also allows you to handle joint name formats based on your rules for various scenarios. For an overview of custom name formats on SQL function specs, see Custom Name Formats.

To demonstrate how to create a custom name format with a SQL function spec, we'll walk through the process of creating a joint name format that displays a constituent's title, first name, last name, and suffix; the text value "and;" and finally the title, first name, last name, and suffix for the constituent's spouse.

We can create this joint name format on the Add name format screen in Blackbaud CRM, but we run into problems in the scenarios where we want to hide "and" between the constituent names.

Since we don't want "and" to appear when constituents do not have spouses, we select the Remove if previous next entry is blank option for "and." This setting hides "and" when a constituent does not have a spouse because the entry after "and" is blank. However, the entry after "and" is also blank if a constituent has a spouse with no title. In this case, the name format has the unintended consequence of hiding "and" to create an awkward combination where the constituent name and spouse name are jammed together with nothing to separate them. Likewise, if a constituent's name does not include a suffix, the entry before "and" is blank, so the name format creates the same awkward combination where the constituent name and spouse name are jammed together.

To address this scenario, we can create a custom name format with a SQL function spec instead of the Add name format screen. The SQL function spec provides more flexibility to program the logic around how to determine whether to display the "and" text value.

SQL Function Spec

The first step is to create a SQL function spec in our catalog project. On the Add New Item screen in Visual Studio, we select Blackbaud AppFx Catalog as the category and SQL Function Spec as the template. Then, we name our example spec file "UFN_NAMEFORMAT_EXAMPLE.xml."

Next, we update the attributes in the root SQLFunctionSpec element. In the Description attribute, we replace the default text with a description of our custom name format. Then we add the IsNameFormatFunction attribute and set it to “true” to indicate to the system that the SQL function spec is for a custom name attribute. When the IsNameFormatFunction attribute is set to "true," the spec adds a row to the NAMEFORMATFUNCTION table that stores name format functions.

<SQLFunctionSpec
  xmlns="bb_appfx_sqlfunction"
  xmlns:c="bb_appfx_commontypes"
  ID="d23ab0a9-5dbd-4951-9d3f-6d13fd1bff66"
  Name="USR_UFN_NAMEFORMAT_EXAMPLE"
  Description="Joint name format with constituent and spouse title, first name, last name, and suffix separated by 'and.' "
  Author="Technical Training"
  DBFunctionName="USR_UFN_AMEFORMAT_EXAMPLE"
  IsNameFormatFunction ="true"
  >

After the attributes are in place, we flesh out the stubbed-out function in the CreateFunctionSQL element to create our user-defined function.

In the CREATE line, the function name should start with "USR_UFN_NAMEFORMAT_" and correspond to the value in the root element's DBFunctionName attribute. Then we specify the following parameters: @CONSTITUENTID, @LASTNAME, @FIRSTNAME, @MIDDLENAME, @TITLE, @SUFFIX, @SPOUSELASTNAME, @SPOUSEFIRSTNAME, @SPOUSEMIDDLENAME, @SPOUSETITLE, and @SPOUSESUFFIX. Do not exclude any of these parameters or include any additional parameters here because this function signature is required for all name format functions.

  <CreateFunctionSQL>
    <![CDATA[
create function dbo.USR_UFN_NAMEFORMAT_EXAMPLE
(
  -- Do not change these function parameters. They must always exist even if the name format does not use them.
  @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)
)

After the parameters, we update the return data type to nvarchar(700) and declare three additional parameters to use to construct the joint name format.

returns nvarchar(700)
with execute as caller
as begin
  declare @RESULT nvarchar(700) = ''; 
  declare @NAME1 nvarchar(700) = '';
  declare @NAME2 nvarchar(700) = '';

Then we build the names for the constituent and spouse. For the constituent's name, we first check for the title, first name, last name, and suffix, and then we add the value for each one that exists to the @NAME1 variable.

  -- Build the constituent's name.
  if len(@TITLE) > 0
    set @NAME1 += @TITLE + N' ';

  if len(@FIRSTNAME) > 0
    set @NAME1 += @FIRSTNAME + N' ';

  if len(@LASTNAME) > 0
    set @NAME1 += @LASTNAME + N' ';

  if len(@SUFFIX) > 0
    set @NAME1 += @SUFFIX + N' ';

We repeat the same process to create the spouse's name in the @NAME2 variable.

  -- Build the spouse's name.
  if len (@SPOUSETITLE) > 0
    set @NAME2 += @SPOUSETITLE + N' ';
 
  if len (@SPOUSEFIRSTNAME) > 0
    set @NAME2 += @SPOUSEFIRSTNAME + N' ';
 
  if len (@SPOUSELASTNAME) > 0
   set @NAME2 += @SPOUSELASTNAME + N' ';

  if len (@SPOUSESUFFIX) > 0
    set @NAME2 += @SPOUSESUFFIX + N' ';

Next, we concatenate the names with the "and" text value between them and return the name we built. In the RETURN line, we also correct any comma issues and remove any extra spaces from the beginning and end of the joint name.

  -- If both names exist, put "and" between them. Otherwise, only return one name.
  set @RESULT += @NAME1;

  if  (len(@RESULT) > 0 and len(@NAME2) > 0)
    set @RESULT += N'and ';

  set @RESULT += @NAME2;

  -- Correct any weird comma issues, trim any spaces from the beginning and end, and then return the name.
  return replace(rtrim(ltrim(@RESULT)), N' ,', N',');
end
    ]]>
  </CreateFunctionSQL>
</SQLFunctionSpec>

Finally, we load the SQL function spec into our Infinity installation. We can use the LoadSpec utility to load the individual spec or add it to a package spec to deploy it as part of a package.

Update Database Table

With the SQL function spec in place, the next step is to add display information for our joint name format to the NAMEFORMATFUNCTION table.

When we load the SQL function spec, the spec adds a row to the NAMEFORMATFUNCTION table to indicate to the system that the function defines a name format. However, it does not populate the DISPLAY or EXAMPLE columns with display information for the custom name format, and it does not update the ISJOINTFUNCTION column to designate the custom name format as a joint name format.

These columns correspond to the display information for the name format on the Add name format screen on the Constituent page and the Name Formats tab on the Name Formats Options page.

If we do not populate the DISPLAY or EXAMPLE columns, the Add name format screen and Name Formats tab display blank entries for our custom name format. To provide this display information, we create a SQL statement to update our custom name format's row in the NAMEFORMATFUNCTION table with values for the DISPLAY and EXAMPLE columns.

In addition, if we keep the ISJOINTFUNCTION column's default value of "0," the system does not recognize our custom name format as a joint name format when it processes name formats. To designate our custom name format as a joint name format, we include a line in our SQL statement to update the default value of the ISJOINTFUNCTION column to "1."

Our SQL statement also declares @CHANGEAGENTID and @CURRENTDATE and sets their values so that we can also update the DATECHANGED and CHANGEDBYID columns when we update the table. In the UPDATE line, we provide values for the DISPLAY and EXAMPLE columns and update the ISJOINTFUNCTION column, and we include a WHERE clause to identify the row for our custom name format based on the name of the function.

/*
  Update row for custom name format to insert display information.
*/

declare @CHANGEAGENTID uniqueidentifier;
declare @CURRENTDATE datetime = getdate();

exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID
output;

update dbo.NAMEFORMATFUNCTION
set
  DISPLAY='Custom display value',
  EXAMPLE='Custom example value',
  ISJOINTFUNCTION='1',
  DATECHANGED=@CURRENTDATE,
  CHANGEDBYID=@CHANGEAGENTID
where FORMATSQLFUNCTION='USR_UFN_NAMEFORMAT_EXAMPLE'

After we run this SQL statement, the display information for our custom name format appears in Blackbaud CRM, and the system recognizes it as a joint name format.

The value in the DISPLAY column appears in the Add name format screen's Format column, and the Result column automatically displays a preview of the name format for the constituent who is being edited.

The value that you place in the EXAMPLE column appears in the Text column of the Name Formats tab on the Name Formats Options page in Marketing and Communications, and the Joint function column reflects the updated value of the ISJOINTFUNCTION column.

Check Configuration Settings

Finally in Blackbaud CRM, we check the configuration settings for any name format options that consume our custom name format. These settings can alter the expected output of our custom name format, so we want to make sure that we understand how they handle joint name formats.

On the Name Formats Options page in Marketing and Communications, you can view the name format options that are used by processes that your organization performs regularly. To view the configuration settings for joint name formats, select a name format option and click Edit. Then on the Edit name format options screen, select the Joint name formats tab.

The settings on this tab determine the order to display constituent names when the name format option processes joint name formats. They can also remove spouse names in certain circumstances. The settings on this tab determine how to pass parameters to SQL functions for joint name formats, so they can alter the expected output from custom joint name formats. For example, if these settings indicate to display female constituents first, then the name format option passes the parameters for female constituents first regardless of the order specified in your custom SQL function. If the settings alter the expected behavior of your custom joint name formats, you can update the name format option settings or create a new name format option as necessary.

For more information about how configuration settings for name format options can impact custom name formats, see Configuration Settings for Name Format Options. For information about how to set up name format options in Blackbaud CRM, see the Communications Guide posted at Blackaud CRM User Guides.