Query to extract all fields from Relationship Tabs of Change Orders
Do you actually use the attributes in the RELATIONSHIP table?? Note that if all you are you using are the default attributes, the query is simple :
select c.change_number, i.item_number NAME, i.description, n.description “CURRENT STATUS”, (select entryvalue from listentry where parentid=2000007913 and langid=0 and entryid=r.rel_type) TYPE
from relationship r, change c, item i, rev rv, nodetable n
where r.ctr_objid = c.id and r.ctr_objtype =6000 and r.eff_objid = i.id and r.eff_objtype in (9000,10000)
and i.id = rv.item and rv.latest_rev=1 and rv.release_type = n.id order by 1, 2;
The above query will show all parts/documents (eff_objtype in 9000,10000) linked to ECOs (ctr_objtype=6000)in the database.
If you actually use the list/text/multilist attributes in the RELATIONSHIP table, it gets more complicated. For text, just include them in the query. For multitext, link to AGILE_FLEX and *know* the attributes ID you need, link using the C.ID = AGILE_FLEX.ID and R.ID = AGILE_FLEX.ROW_ID. For list, you can just have a sub-query that gets the value, similar to what the above query does for REL_TYPE. Multilist attributes are not anywhere as easy, so I hope you are not using them.
Note that the only attributes listed for the related object are number, description and current status (which is blank for everything *but* parts/documents). All other available attributes are from the RELATIONSHIP table.
Hi Kevin, thanks for your assist. I was looking for the similar attributes. But I am more concerned about some attributes like “Rule”, “List01”, etc. For which, I am unable to find the right tables to link and extract desired value. Can you help me out there?
Anything starting with LIST are just list attributes. If they have values, look in LISTENTRY where ENTRYID = LIST01 (for example). CRITERIAMET is a list attribute as well, and uses the Yes/No list, parentid=451 in LISTENTRY. If you ever get more than 1 record for a specific list value ID, you will need to also specify the parentid for the list that is assigned to the attribute. Th list parent ID can be found in PROPERTYTABLE.SELECTION using PROPERTYID=15 and the attribute ID as PARENTID.
RULE is something of a composite attribute, and I have never seen it used. It might be multi-text and therefore in AGILE_FLEX (see below). “Type (Image)” is not stored as it is an image linked to other attributes.
The 3 MULTI-TEXT attributes are actually stored in AGILE_FLEX, where ATTID is the ID assigned to the relationship attribute in NODETABLE (and as shown in the Agile Classes Report, which has just about all the information you need to do most of this). Same for NOTES and NOTES1.
Everything else should be fairly straightforward.