Making an Attribute unique in BBCRM

@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 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 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

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).