Custom Name Formats

In Blackbaud CRM, name formats allow end users to specify how to address constituents. Name formats determine how to display constituent names in items such as mailings and reports. For example, name formats indicate whether to display data such as a constituent's title, middle name, and suffix. And joint name formats allow users to display a spouse's name alongside a constituent's name.

The application includes a variety of default name formats, and you can also configure new name formats as necessary with the Add name format screen on the Name Formats Options page in Marketing and Communications.

The Add name format screen is a fairly simple way to create basic name formats, but it may not be sufficient to create more complex name formats such as joint name formats that display spouse information alongside the constituent name.

This is where the SQL function spec comes in.

With a SQL function spec, you can define a custom name format with a user-defined SQL function that gives you more flexibility than the Add name format screen. The SQL function allows you to implement complex formatting rules that you cannot achieve on the Add name format screen and to include constituent fields in the name format that are not available otherwise. It also allows you to better handle joint name formats based on your rules for different scenarios.

For example, you can use a SQL function spec to create a joint name format that displays the title, first name, last name, and suffix for a constituent's spouse, followed by "and" and then the same fields for the constituent. If you created this joint name format on the Add name format screen, you would select the Remove if previous next entry is blank option to hide "and" when a constituent does not have a spouse. However, this option has the unintended consequence of hiding "and" when a constituent has a spouse who does not have a suffix or when a constituent does not have a title. For a scenario like this where you need complicated logic to determine whether to display a value such as "and," the SQL function spec allows you to program that logic as part of the SQL function.

To create a custom name format with a SQL function spec, you need to follow a three-step process.

  1. Create and load the SQL function spec.

  2. Run a SQL statement to update a database table for name format functions.

  3. For a joint name format, make sure that the configuration settings for name format options don’t alter the expected output of your custom function.

Create the SQL Function Spec

You create s SQL function spec for a custom name format just like any other SQL function spec, and then you set the root element's IsNameFormatFunction attribute to “true.” When you load a SQL function spec with this attribute in place, the spec adds a row to the NAMEFORMATFUNCTION table to indicate to the system that the function defines a name format.

<SQLFunctionSpec
  xmlns="bb_appfx_sqlfunction"
  xmlns:c="bb_appfx_commontypes"
  ID="d23ab0a9-5dbd-4951-9d3f-6d13fd1bff66"
  Name="USR_UFN_NAMEFORMAT_EXAMPLE"
  Description="Custom name format example"
  Author="Technical Training"
  DBFunctionName="USR_UFN_NAMEFORMAT_EXAMPLE"
  IsNameFormatFunction ="true"
  >

Then in the CreateFunctionSQL element, you create your SQL 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.

After the CREATE line, you must specify the following parameters: @CONSTITUENTID, @LASTNAME, @FIRSTNAME, @MIDDLENAME, @TITLE, @SUFFIX, @SPOUSELASTNAME, @SPOUSEFIRSTNAME, @SPOUSEMIDDLENAME, @SPOUSETITLE, and @SPOUSESUFFIX. This function signature is required for all name format functions.

Warning: Do not exclude any of these parameters or include any additional parameters. The application requires each of these parameters, and no others, so that it can provide a common interface for both custom name format functions and out-of-the-box name format functions. You can apply logic within the function to retrieve additional data, but do not include additional parameters here.

After the parameters, you must update the return data type to nvarchar(700). 

  <CreateFunctionSQL>
    <![CDATA[
create function dbo.USR_UFN_NAMEFORMAT_EXAMPLE
(
  @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
  -- do work here and return a value
  return ''
end
    ]]>
  </CreateFunctionSQL>

With the parameters in place, you can flesh out your SQL function to apply the necessary logic for your custom name format. For an example, see Create a Joint Name Format.

After you finish the SQL function, you can load the spec into your Infinity installation. You can use the LoadSpec utility to load the individual spec, or you can add it to a package spec to deploy it as part of a package.

Update the Database Table for Name Format Functions

When you load a SQL function spec with the IsNameFormatFunction attribute set to "true," the spec adds a row to the NAMEFORMATFUNCTION table to indicate to the system that the function defines a name format.

However, the spec does not populate the DISPLAY and EXAMPLE columns in the NAMEFORMATFUNCTION table. And for joint name formats, it does not update the ISJOINTFUNCTION column to designate the custom name format as a joint name format.

To update these columns in the NAMEFORMATFUNCTION table, you can create and run a SQL statement.

  • The DISPLAY column corresponds to the display information in the Format column of the Add name format screen where users select name formats for constituents on the Personal Info tab of the Constituent page.

    When you initially load the SQL function spec for a custom name format, the Add name format screen displays blank entries in the Format and Example columns. To display values for your custom name format on this screen, you can run a SQL statement to populate the DISPLAY column in the NAMEFORMATFUNCTION table. The value in the DISPLAY column appears in the screen's Format column, and the Result column automatically displays a preview of the name format for the constituent who is being edited.

  • Meanwhile, the EXAMPLE column corresponds to the display information in the Text column of the Name Formats tab on the Name Formats Options page in Marketing and Communications.

    When you initially load the SQL function spec for a custom name format, the Name Formats tab displays a blank entry in this column. To display a value for your custom name format on this tab, you can run a SQL statement to populate the EXAMPLE column in the NAMEFORMATFUNCTION table. The value that you place in the EXAMPLE column appears in the Text column.

  • Finally, for a joint name format that combines constituent and spouse information, you also need to update the ISJOINTFUNCTION column to designate it as a joint name format. The value in this column determines whether the system recognizes the name format as a joint name format when it processes name formats. To replace the default value of "0," you can run a SQL statement to update the ISJOINTFUNCTION column to "1."

When you can create a SQL UPDATE statement to add values to the DISPLAY, EXAMPLE, and ISJOINTFUNCTION columns as necessary, you can identify the row to update in a WHERE clause based on the function name .

/*
  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='INSERT_DISPLAY_VALUE',
  EXAMPLE='INSERT_EXAMPLE_VALUE',
  ISJOINTFUNCTION='1',
  DATECHANGED=@CURRENTDATE,
  CHANGEDBYID=@CHANGEAGENTID
where FORMATSQLFUNCTION='INSERT_FUNCTION_NAME'

After you update the NAMEFORMATFUNCTION table, your display information on the Add name format screen and the Name Formats tab, and the system recognizes your custom name format as a joint name format when it processes name formats.

Check Configuration Settings on Name Format Options

Name format options allow you to set up name format defaults for processes that your organization performs regularly. Name format options include configuration settings that determine the order of constituent names in joint name formats and whether to remove spouse names in certain circumstances.

Since these configuration settings determine how to pass parameters to SQL functions for joint name formats, we recommend that you check the settings for potential conflicts after you create joint name formats.

On the Name Formats Options page in Marketing and Communications, name format options appear under Name format options on the Name Format Options tab. To view configuration settings for a name format option, select it in the grid and click Edit. Then on the Edit name format options screen, select the Joint name formats tab to view configuration settings for joint name formats. The settings on this tab determine the order to pass parameters to your joint name format's SQL function. They can also designate situations to pass empty string values for spouse information. You should check these settings to make sure that the way they pass parameters to SQL functions does not alter the expected output from your custom joint name formats.

For more information about how these configuration settings 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.