Forum Discussion

Creator I

7 years ago

Get XProperties through SQL

Hi How to get the extended classification properties and its values using SQL. I'm able to get it through AML

xClassification

Creator I

7 years ago

Hi Christopher

I want to do duplicate Part check based on xProperties values

Example: Part001 has xp-height :10 xp-length : 10. When user creates Part002 with same xp properties value, system should not allow.

My approach was trying to fetch the Part with xp-height and xp-length with particular value and if data exist prevent save.

When using below AML query, getting count '1' which already exist

<AML>

<Item action='get' type='Part' select='xp-*'>

<xp-height>10</xp-height>

<xp-length>10</xp-length>

</Item>

</AML>

when user filled only height then, AML query will be like below and also getting count '1'

<AML>

<Item action='get' type='Part' select='xp-*'>

<xp-height>10</xp-height>

</Item>

</AML>

Creator I

7 years ago

Hi,

Can someone give some hint on how to achieve this

angela
Catalyst II
7 years ago
Also SQL is not recommended for regular use, it´s often a big help, espcially when you have to do data validation.

I still use the regular properties and have not moved to xProperties yet, but I alread have made some tests. The followoing is not final and may contain errors. Please tell me, if you notice a bug in the query!

For SQL you have to know the involved ItemType stucture:
Part -> Part_xClass -> xClass -> xPropertyValues

This query should work as regular view in the SQL Server Management Studio:

SELECT innovator.PART_XCLASS.id
FROM xp.XPROPERTYVALUES INNER JOIN
innovator.XCLASS ON xp.XPROPERTYVALUES.id = innovator.XCLASS.id INNER JOIN
innovator.PART_XCLASS ON innovator.XCLASS.id = innovator.PART_XCLASS.RELATED_ID INNER JOIN
innovator.PART ON innovator.PART_XCLASS.SOURCE_ID = innovator.PART.ID

Edit: This solution is intended for validation, but not as Method used in the system. But I wonder if it is possible to use the "Unique" properties in ItemType xPropertyvalues for this purpose? Something similar is done in the Part_Goal ItemType that uses a combination of id and a second property to prevent duplicate Goals. It´s an interesting use case. I would also be interested in a solution for this one.