nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Making an Attribute unique in BBCRM

David Webb

Is there a way to Modify an existing Attribute Category to “allow only one per record”. You can set this when you first set up the Attribute, but afterward if business rules change and it was originally (Or incorrectly) set to allow multiples per record, the "Edit' function doesn't give the option to change it allow only one:

25c390ec85ea5317e2338d6d98a3d77f-huge-im

12d093a49dcc784a13b2ed39ba818b56-huge-im

Find more posts tagged with

Comments

Jennifer Borri

@David Webb To my knowledge, this cannot be done. Several years ago, I had one of our data team see if we could do this at the DB level and he said we could not. Instead we create a new attribute and use global changes to apply the new attribute to records and delete the old attribute. We also query our ad hoc queries and exports to see which are using the old attribute so we can update those with the new one so we can then delete the old one. It's a fair amount of work so I totally understand you looking for a simpler solution! Maybe others have found a solution we have not?

Amanda Brown

@David Webb @Jennifer Borri We have not found a simpler way either. I believe the back-end table structure is different if you allow more than one value per attribute vs. not, so I can see how it would be difficult to code. For what it is worth, there is an idea bank item we can vote on -

Thomas DeMerit

@Jennifer Borri Thanks for your response. Could you please elaborate on this for me? “…We also query our ad hoc queries and exports to see which are using the old attribute…” A roadmap for how to do this would be great. I'm new to my institution and would love a way to map-out what queries/exports are using.

Jennifer Borri

@Thomas DeMerit The queries and exports are stored in the database as XML. I'm not a developer, but I had one teach me how to use XQuery to find fields in queries and exports. You can also use SQL (Googling will give you options and syntax…and AI too) since XQuery is kind of tedious to use. I usually start with a test query and export to make sure I can find the fields in my test items to make sure my syntax is correct…then I run a query on all queries and exports to find the ones with the attribute I'm looking for. I don't think XQuery is the best solution..especially since you have to make sure you are looking down all the right nodes, but it works.
Below are examples of where I was looking for not an attribute, but a specific attribute value in case this is helpful. Apologies to real developers who are probably looking at this and groaning. It might not be pretty or efficient, but like I said, it works.

select * from ADHOCQUERY
where
QUERYDEFINITIONXML.exist('/*:AdHocQuery/*:FilterFields/*:f/*:TranslatedValues[.="NMHP Conference"]') = 1

---------------------
/*Find export defnition with user attribute value */
select
E.NAME,
E.DESCRIPTION,
E.DATEADDED,
E.DATECHANGED,
A.DISPLAYNAME,
E.CHANGEDBYID
from EXPORTDEFINITION E
left join CHANGEAGENT C on E.CHANGEDBYID = C.ID
join APPUSER A on C.USERNAME = A.USERNAME
where
EXPORTDEFINITIONXML.exist('/*:ExportDefinitionQueryViewNode/*:Nodes/*:n/*:FilterFields/*:f/*:TranslatedValues/*:v[.="NMHP Conference"]') = 1

Thomas DeMerit

@Jennifer Borri Thanks so much. I do know what XPath and XQuery is relative the SQL Server; however, I/we do not have access to the back-end SQL Server (as far as I know). Your explanation is great but we are limited to the front-end tools, it would seem. How does one achieve SQL access as you describe? Or, is your platform on-site or not the same as ours? Hmmm. Thanks again.

Michael Flynt

@Thomas DeMerit You may have success in this instance by finding all the system uses/references to the attribute, which include data field and filter elements of Ad-hoc queries and Export definitions (yes, XPath is your friend there, although you might dig to find some OOB functions for validation that you can modify to suit). But you'll also have to look at batch templates.

Assuming all that get's cleared out, you still have to look at the data. If you can just truncate the primary and batch tables, great. Do that. However, if you are meaning to transfer a set of values to a 1:1 attribute category, you'll need to decide how to pick the row to take from among multiple. If you are using a codeTable datatype or any type where there is a fair bit of variation or if there are date or comment values of some importance…How are you going to decide which wins or how to concatenate? If much used, picking a single row is going to result in data loss. That's also ignoring the potential issues with historical values in batch.

Here's how a text data type attribute for a constituent record type might look for singular and multiple types

1:one	1:many
ID (joins to consttiuent.ID)	ID (independent)
	CONSTITUENTID
VALUE	VALUE
COMMENT	COMMENT
STARTDATE	STARTDATE
ENDDATE	ENDDATE

To make attributes ubiquitous across the system, BB creates a lot of objects (ca.20) and relationships, which is why you can interact with them so effortlessly in so many places. When you no longer need one, though, it's like getting rid of English ivy.

There is not a generalized solution to the problem of converting 1:many to 1:one that guarantees no data are lost. I suspect that is why there is no utility for that now, or likely forthcoming.

The closest solution would be to export the data, do what must be done to fully delete the existing attribute category, then create the 1:1 and import your data to it using whatever means you devise to get to single rows per record (e.g., constituent).

Jennifer Borri

@Thomas DeMerit We are self-hosted on BBCRM so we have full control over our databases. If you are hosted by BB or a different third-party hosting service, you would need to talk to your hosting provider about your DB access. Good luck!