Forum Discussion

krisgopi88's avatar
krisgopi88
Ideator 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
xProperties
christopher_gillis's avatar
christopher_gillis
New Member
7 years ago

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

krisgopi88's avatar
krisgopi88
Ideator 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>

  • krisgopi88's avatar
    krisgopi88
    Ideator I
    7 years ago

    Hi,

    Can someone give some hint on how to achieve this

    • AngelaIp's avatar
      AngelaIp
      Ideator I
      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.