Primary salutation

Jay Hotchkiss

If I pull primary salutation from the records table in RE nightly backup, I can find constituents that have a null value. If I look at the same constituent in RE, the primary salutation is not null. How can this be possible? If I understand correctly, the records table stores the primary addressee and primary salutation. What am I missing?

JoAnn Strommen

@Jay Hotchkiss Welcome to the BB Community forums.

Just my shot in the dark, are you pulling based on the format when you pull from the backup? Is it possible that on the records a different format was selected to fill the primary salutation field. Many look similar and it's easy to pull something other than you think.

Jay Hotchkiss

@JoAnn Strommen Thanks for the reply. If I understand your question, the answer is no. I am not pulling based on format. I even have a valid salutation code id for the null value, which adds to the mystery.

Lee Grisham

@Jay Hotchkiss I've run into a situation like this when they had the Custom box checked for Add/Sal. Is that it?

Dariel Dixon 2

@Jay Hotchkiss The records table will hold the primary addressee and salutation, but the Salutations table will hold the format.

Jay Hotchkiss

@Dariel Dixon Understood. The issue is the nightly backup returns a null value while RE shows the information. It can't be both null and a value.

Jay Hotchkiss

@Lee Grisham If by “custom box” you mean editable, the answer is no. The editable box is not checked.

Alex Wong

@Jay Hotchkiss
in the Records table in the schema, these fields are of your concens:

• PRIMARY_ADDRESSEE
  • this is the exact TEXT of the primary addressee IF PRIMARY_ADDRESSEE_EDIT is -1 (-1 means checked = TRUE)
• PRIMARY_ADDRESSEE_ID
  • this is the primary addressee's format ID
• PRIMARY_ADDRESSEE_EDIT
  • this indicates if the editable is checked

In order to get the correct addressee, you need to do this:

CASE WHEN r.primary_addressee_edit=-1 THEN r.primary_addressee ELSE dbo.GetSalutation(r.primary_addressee_id,r.id,'',0,0,0,getdate()) END

assuming r is alias for the records table.

this basically means, check if EDIT is true, if yes, use primary_addressee directly, if false, use the GetSalutation() database function that Blackbaud defined and pass it the primary_addressee_id and record's id to “generate” the addressee per the format id.

Here's a KB article that got me this info when I was doing direct SQL querying.

Jay Hotchkiss

@Alex Wong thanks for the reply. I can get the information via SQL but there are times when the primary addressee, primary salutation, or both return as null from our nightly backup. The corresponding information in the database view of RE is complete. The editable boxes are not checked for these records so there should be no reason for the null values from the nightly backup. It seems like the values that show up as null are not stored in Records table but according to BB, this is the only table for primary addressee and primary salutation.

Alex Wong

@Jay Hotchkiss
Hi, all I can tell you is the database schema works properly as this is how I have created my query report using SQL.

What you may need to be careful about is exactly what “field” (column) in the table are you speaking about, please use exact database field name. As I mentioned in my previous post: PRIMARY_ADDRESSEE is the text of the primary addressee if PRIMARY_ADDRESSEE_EDIT is -1. So if the constituent you are looking at does NOT have “editable” checked, PRIMARY_ADDRESSEE will be NULL.

Jay Hotchkiss

@Alex Wong After reading your replies several times and the knowledge base article you sent, I'm starting to think this IS the issue. That raises another question. How do I use the GetSalutation function with our nightly backup? If it matters, we use SSMS.

Alex Wong

@Jay Hotchkiss
This is a function built into the database that you restored. So you should be able to use it directly on the SQL statement:

SELECT r.constituentid AS 'Constituent ID',
CASE WHEN r.primary_addressee_edit=-1 THEN r.primary_addressee ELSE dbo.GetSalutation(r.primary_addressee_id,r.id,'',0,0,0,getdate()) END AS ‘Addressee’,
CASE WHEN r.primary_salutation_edit=-1 THEN r.primary_salutation ELSE dbo.GetSalutation(r.primary_salutation_id,r.id,'',0,0,0,getdate()) END AS ‘Salutation’
FROM records AS r
WHERE r.key_indicator='I'

Jay Hotchkiss

@Alex Wong Thank you so much! That works.