I have two situations that I need to rectify: (1) I have a value on a protected list for an Object Attribute that is not used and makes no sense being there. How can I remove it? (2) I have a value on a protected list for an Objected Attribute that is used and contains a typo. How can I saw all instances of it to reflect the proper value (e.g. ProductA vs Product A)? Thanks!
--- "Hurler, Brian" <email@example.com> wrote: > > (1) I have a value on a protected list for an Object Attribute that is > not used and makes no sense being there. How can I remove it? > > (2) I have a value on a protected list for an Objected Attribute that > is used and contains a typo. How can I saw all instances of it to > reflect the proper value (e.g. ProductA vs Product A)?
I'll answer question #2 first. The list values are stored in tables related to the definition of the attribute and they are duplicated in the tables for the objects themselves. In order to change the values, you need to change the values in at least two locations.
To change the values currently assigned to the objects, you need to know if the attribute is versioned or non-versioned. Versioned attribute values are associated with the PIV and are stored in the PDM_PRODUCTITEM_VUDA1 table. Non-versioned attributes are associated with the PI and are stored in the PDM_PRODUCTITEM_UDA1 table.
This query will show you the attributes that have lists of values, the order that they appear in the GUI (CLAESEQNUM), and the UDA number (CLAID):
column CLANAME format a12 column CLAEVALUE format a12
select CLAEID,a.CLAID,CLANAME,CLAESEQNUM,CLAEVALUE from pdm.PDM_CLATTRENUM a, pdm.PDM_CLASSATTR b where a.CLAID=b.CLAID order by CLANAME,CLAESEQNUM;
You need to use the UDA number (CLAID column from the above query) in order to change the values for the objects. When you run this, you'll notice that the items in the CLAEVALUE column have a prefix to indicate the datatype (S for String, I for Integer, etc). You'll need to preserve that syntax if you make changes to the attribute definition.
This update will change the (string) values of non-versioned attributes, for attribute having CLAID=123, from 'OldString' to 'NewString':
update pdm.PDM_PRODUCTITEM_UDA1 set UDA123='NewString' where uda123='OldString';
This update will change the (integer) values of versioned attributes, for attribute having CLAID=456, from 2 to 3:
update pdm.PDM_PRODUCTITEM_VUDA1 set UDA456=3 where uda456=2;
To change the definition of attribute 'Some_Attribute_Name', so that 'NewString' replaces 'OldString' as a different choice in the GUI, use this update:
update pdm.PDM_CLATTRENUM set CLAEVALUE='SNewString' where CLAEVALUE='S0ldString' and claid=( select CLAID from pdm.PDM_CLASSATTR where CLANAME='Some_Attribute_Name' ) ;
If the value that you're changing is the default value for the attribute, you'll need to change that as well. Something like this should work, but I haven't tried it myself:
update pdm.PDM_CLASSATTR set CLADEFVALUE='SNewString' where CLADEFVALUE='S0ldString' and CLANAME='Some_Attribute_Name' ;
As far as question #1 is concerned, I have always recommended not to remove data from Intralink's oracle database except through the GUI. If you really, absolutely must do this, the syntax for the delete command is as follows:
delete from some_table where a_column='value1' and b_column='value2';
Rather than doing that, I would recommend that you simply change the value to something such as 'Do not use' or 'Reserved for future use'. Then just leave it alone, until you need another value in the list.
As always consider the above as untested and without any guarantees. Use it at your own risk.