Get XProperties through SQL

Hello,

We typically recommend against using SQL to either get or edit data as it bypasses the Aras permission model. Could you clarify your use case for getting the extended classification properties through SQL?

Chris

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>

Hi,

Can someone give some hint on how to achieve this

Hi,

Can someone give some hint on how to achieve this

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.