Tip:
Highlight text to annotate it
X
Welcome to the DEFINING RECORD OPERATIONS WITH THE BLACKBAUD CRM SDK video series.
In this video, we'll explore how to use a stored procedure-based record operation to delete data from a table.
By default, the spec includes a stubbed-out stored procedure that does most of the heavy lifting.
We just need to make a few changes to the spec and its stubbed-out stored procedure, and then implement the feature in Blackbaud CRM.
To explore record operations, let's check out a sample customization in Blackbaud CRM.
To view the customization, we just need to open a constituent record.
For this demo, let's use Robert Hernandez's record in the sample database.
On the constituent record, the Pets tab is on the far right among the available tabs.
On this custom tab, we can add pets to constituent records and then manage pet information as necessary.
To add a pet, we just click Add above the grid.
On the Add a pet screen, we can enter a pet's name, its birth date, the type of animal, the breed, and a description.
The screen also includes a Deceased? checkbox so that we can indicate if a pet dies.
After we save a pet, it appears in the grid.
From there, we can click the Edit action to update the pet record as necessary.
For example, if we receive more information about a pet's appearance, we can update the Description field.
When we add or edit a record on the Pets tab, the data appears in the custom USR_PET table that was created for the customization.
Let's take a look at SQL Server Management Studio now to see how data from the Pets tab is reflected in the custom USR_PET table.
After we select our database, we can filter on "Pet" to find our table.
We can then use a query to view the rows of the table and see how it reflects data from the Pets tab in Blackbaud CRM.
For example, the name of the pet we just added to Robert Hernandez's record appears in the PETNAME column.
And our change to the description appears in the DESCRIPTION column.
Currently, users on the Pets tab can’t delete data from the USR_PET table. This is where the record operation spec comes in.
Let's switch to Visual Studio now to create a Delete record operation in the catalog project for the Pets customization.
To create the record operation spec, we right- click the catalog project in the Solution Explorer, select Add, and then select New Item.
On the left side of the Add New Item screen, we select the Blackbaud AppFx Catalog category.
Then in the center of the screen, we select the Record Operation (SP) template.
Next, we update the default name in the Name field. For the Delete record operation, we'll use "PetDelete.RecordOperation.xml."
Finally, we click Add to create the record operation spec.
The spec includes a stubbed-out stored procedure and the XML elements and attributes that we need for a basic record operation.
We just need to make a handful of changes.
We start at the top with the attributes of the parent RecordOperationSpec element.
The first two declare namespaces that the spec requires, and the third provides a GUID to uniquely identify the record operation.
We don't need to edit the defaults for these first three attributes.
For the Name attribute, we add "(Custom)" to the default value to avoid any possible future naming conflicts with Blackbaud features.
Then we add the NameUIOverride attribute to provide a display name that does not include "(Custom)" for the feature in Blackbaud CRM.
Next in the Description attribute, we enter a short description of the record operation.
In this example, we explain that it deletes pet records from the custom Pets tab.
Next, the Author attribute identifies the author of the spec.
For Blackbaud CRM 3.0 and later, we can use the default, which is based on the author that we specify when we install the SDK.
Next, the OperationType attribute indicates whether this is a Delete or Update record operation. We want the default "Delete" value.
The RecordType attribute specifies the type of record to act on, and for the pet customization, we want the custom "Pet" record type.
Similarly, we want "Pet" for the SecurityUIFolder attribute to specify a folder to categorize this feature in Security in Blackbaud CRM.
After the attributes, the spec includes the SPRecord, SPOperationImplementation, and CreateProcedureSQL elements.
The SPOperationImplementation element includes the SPName attribute, which names the record operation's stored procedure.
We can edit the SPName attribute if necessary, but it must match the name from the CREATE line of the stored procedure.
After the CREATE line, the stubbed-out stored procedure declares the ID and CHANGEAGENTID parameters.
Both parameters are unique identifiers. The ID is the ID of the entity to perform the record operation on.
And the CHANGEAGENTID is the ID of the user who executes the record operation.
To delete data, the stored procedure calls on an existing system-generated stored procedure.
When a table is created, the system creates a stored procedure to delete rows from the table.
Delete record operations rely on these system- generated procedures to delete data from tables.
In the stubbed-out stored procedure, we just need to replace the default "TABLE1" with our table name of "USR_PET."
Finally, at the end of the spec, we can specify a message to display when users execute the record operation.
The Text attribute of the StandardPrompt element includes a generic message, and we can tweak it to describe our record operation.
After we make these changes to the record operation spec, we are ready to save it and load it into Blackbaud CRM.
To load the spec, we select the LoadSpec utility from the Tools menu. This utility adds catalog specs to our local Infinity installation.
Keep in mind that while LoadSpec is useful to test specs, Package specs are how we deploy multiple specs and embed them as resources.
After we load the spec, we return to the Pets tab in Blackbaud CRM to configure a Delete action that will execute our new record operation.
To activate Design Mode, we click the toggle button near the top right of the web browser.
Design Mode displays a variety of options that we can use to customize the program.
To let users execute our record operation, we'll add a Delete action beside the Edit action for the entries in the Pets grid.
To do this, we click Edit actions above the grid. The Edit section actions screen includes the Add and Edit actions already on the page.
To add a Delete action, we click Add.
On the Edit action screen, we enter "Delete" in the Caption field and make sure the Visible field is set to "True."
Next, in the ActionType field, we select "ExecuteRecordOperation."
The screen options update to reflect this selection, and in the RecordOperation field, we click the search icon.
On the search screen, we enter "Pet" in the Name field to search for our record operation by its friendly name.
After we select the record operation, we specify the context so that the program knows which record to delete.
In the ContextType field, we select "SectionField."
Then in the SectionField field that appears, we select "ID."
We click OK to save these settings for the new Delete action, and then click Save to save our action to the Pets tab.
Back on the Pets tab, the Delete action appears beside the Edit action for each entry in the grid so that we can delete pets as necessary.
When we click Delete, a prompt appears with the message that we tweaked on the spec.
If we click Yes, the Delete record operation removes the selected pet from the USR_PET table. It does not affect any other pets.
After we delete a pet, we can return to SQL Server Management Studio to see how the data is updated in the custom USR_PET table.
We can execute our previous query again to see that the row for the deleted pet no longer appears in the query results.
When we clicked the Delete action on the Pets tab, the row for that pet was deleted from the USR_PET table.