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 

Parents
  • 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>

  • オフライン in reply to krisgopi88

    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.

Reply
  • 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.

Children
No Data