Primary salutation

Options
Jay Hotchkiss
Jay Hotchkiss ✭
Name Dropper First Anniversary First Reply
in Blackbaud Raiser's Edge NXT®: General Discussion

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?

Tagged:

Comments

  • JoAnn Strommen
    JoAnn Strommen ✭✭✭✭✭
    Ancient Membership Facilitator 4 Name Dropper Photogenic

    @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
    Jay Hotchkiss ✭
    Name Dropper First Anniversary First Reply

    @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
    Lee Grisham ✭
    Third Anniversary Facilitator 2 Name Dropper Photogenic

    @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
    Dariel Dixon 2 ✭✭✭✭✭
    Seventh Anniversary Facilitator 4 Name Dropper Photogenic

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

  • Jay Hotchkiss
    Jay Hotchkiss ✭
    Name Dropper First Anniversary First Reply

    @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
    Jay Hotchkiss ✭
    Name Dropper First Anniversary First Reply

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

  • Alex Wong
    Alex Wong ✭✭✭✭✭
    Ninth Anniversary Facilitator 4 Name Dropper Photogenic

    @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
    Jay Hotchkiss ✭
    Name Dropper First Anniversary First Reply

    @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
    Alex Wong ✭✭✭✭✭
    Ninth Anniversary Facilitator 4 Name Dropper Photogenic

    @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
    Jay Hotchkiss ✭
    Name Dropper First Anniversary First Reply

    @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
    Alex Wong ✭✭✭✭✭
    Ninth Anniversary Facilitator 4 Name Dropper Photogenic

    @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
    Jay Hotchkiss ✭
    Name Dropper First Anniversary First Reply

    @Alex Wong Thank you so much! That works.

Categories