Attribute Category data type of Constituent Record - Looking for input on how to work with it

Hi Pam,

There's a session at the BB Developers' Conference on Advanced Configuration. It's a skills lab so you could ask our team there if you don't get an answer before then

Name

Making the Most of Design Mode for Blackbaud CRM™

Date & Time

Wednesday, June 8, 2022, 3:30 PM - 4:30 PM

Description

In this Skills Lab, the team from BrightVine Solutions will walk you through advanced configuration approaches with Design Mode. This is a Skills Lab suitable for administrators and business analysts.

@Pamela Hassler

It depends a little bit on whether you choose the “Allow only one per record” option.

I'll go through both scenarios. I've created two attributes (screenshots below). One is “Best friend”, and it's “only one". The other is “Other friends”, and more than one of those are allowed.

When you create those attributes, CRM will automatically generate a table name for each one. You can see the table names by running this query:

SELECT
AC.[NAME], TC.TABLENAME
FROM
ATTRIBUTECATEGORY AC
JOIN TABLECATALOG TC ON TC.ID = AC.TABLECATALOGID
WHERE AC.[NAME] IN('BEST FRIEND','OTHER FRIENDS')

Both tables will have a CONSTITUENTVALUEID, which contains the Constituent ID of the “Best friend” or the “Other friend”. The only difference between the two tables is that the “Best friend” table can be joined directly to the CONSTITUENT table on the ID column, but the “Other friend” attribute must be joined by the CONSTITUENTID column (because there can be more than one).

So, your Datalist might use SQL like this:

--This will return only one row per constituent

SELECT
CON.[NAME] AS CONSTITUENTNAME
,BESTFRIEND.[NAME] AS BESTFRIENDNAME
FROM
CONSTITUENT CON
--This table came from the earlier query; yours will be different
LEFT JOIN ATTRIBUTECFB911E5380D4D0394E7B00EA34C8782 BF ON BF.ID = CON.ID
LEFT JOIN CONSTITUENT BESTFRIEND ON BESTFRIEND.ID = BF.CONSTITUENTVALUEID

--This may return any number of rows per constituent
SELECT
CON.[NAME] AS CONSTITUENTNAME
,OTHERFRIEND.[NAME] AS OTHERFRIENDNAME
FROM
CONSTITUENT CON
--This table came from the earlier query; yours will be different
LEFT JOIN ATTRIBUTE147BEB11AA0E485CB1829658618CAE74 OTF ON OTF.CONSTITUENTID = CON.ID
LEFT JOIN CONSTITUENT OTHERFRIEND ON OTHERFRIEND.ID = OTF.CONSTITUENTVALUEID

Some things to keep in mind:

1. CRM generates the table names on the fly. If you manually create the attribute in two different environments, the table names will be different in the two places. That can lead to errors when you promote code from a test environment to production. Two solutions to that are: 1) Create the attribute in production, then refresh the database in lower environments or 2) Use a SQL Script to generate the attributes in a way that you can control the table names. Ping me if you need such a script, I can dig one up.

• For a datalist, you may want to consider pulling “many” style attributes as a CSV list. That way you can keep one row per constituent, but have a single column with all their attribute values in one place
• You get attributes for “free” in the ad-hoc query tool. You can create a query there, then save that query as a datalist if you want CRM to deal with the table names for you.