I need to extract Agile Manufacturer Part Data via SQL Query and cannot find some of the ancillary data in the database
When viewing a part in Agile 9.3.1.2 on the Manufacturers Tab, there are several fields displayed, which I am assuming are custom fields. These fields are different from the fields on Page Two and Page Three when viewing the Manufacturer Part. I can’t seem to find where these fields live in the database. I have searched for the data in PROPERTYTABLE and the field I am looking for does not seem to be there. On the off chance that this field is an out of the box field, it is called Reference Notes in the UI. Any tips on where I might find this data?
That particular field is stored in AGILE_FLEX where ATTID=2240, ID will be the id from ITEM for the part number you want, and ROW_ID will be the ID from the MANU_BY table that you are interested in (for that item).
All multitext attributes are now stored in AGILE_FLEX, like Notes from P2, any of the multitext fields from P2 or P3, etc. Each attribute has a unique ID that is stored in ATTID. For P2/P3 attributes, ID= the ID of the object to which the text belongs (and ROW_ID will have the same value). For Rev/BOM/AML multitext fields, ID is the object ID value and ROW_ID is the table ID that the text belongs to (and they must correspond).
AGILE_FLEX is also where all user-defined attributes are stored, as it can handle list, text, numeric and date values.
Thanks Kevin, That was a huge help. Is there a table I can use map ATTID to the display name of the attribute in the UI?
Also, the Reference Notes seems to be the only attribute that is stored there. There are other attributes defined for example “1st Article”, “Summary Compliance”, “PM Risk Factor”, “PM RoHS Status”, and “Recheck Date” None of these seem to be in AGILE_FLEX. Where would I find them?
ATTID will match an ID in NODETABLE, and DESCRIPTION will be the name displayed for the attribute in the GUI.
“Reference Notes” is a multitext attribute, and so is in AGILE_FLEX. Note that any other data type attribute (date/list/text/etc.) will be in the MANU_BY table. It doesn’t have a lot of attributes, but anything that is displayed in the Manufacturers tab and the “attribute” starts with “MANU_BY.” will be in that table. Same for BOM attributes, except that they will be in the BOM table. For the change Affected Items tab, attributes are in the REV table.
Thanks Kevin,
Two more questions:
1. I think that “Summary Compliance” is actually AGILE.MANU_PARTS.COMPLIANCY which for the record I’m looking at, has a value of 4. How do I lookup the meaning of 4?
2. I’m still really confused about how to look up the display names for those custom attributes on the MANU_BY and other tables like BOM, etc. How do I do that?