• State Verified Answer
  • Replies 3 replies
  • Subscribers 13 subscribers
  • Views 6308 views
  • Users 0 members are here
Options
Related

What´s the best way to check that two or more properties are unique?

AngelaIp

Hi Community,

maybe somebody knows a simple solution for this use case. My Part table has the following properties. Nothing really exotic:

item_number | erp_number | major_rev | name | ..

Both item_number and erp_number shall be unique values. When I tick the unique checkbox in the ItemType definition for both properties, a combination of the two properties will be used for the uniquness check. 

There are use cases were this kind of unified uniquenss check is extremely helpful, like Part Goals. But in my use case I want to check item_number and erp_number indepented for their uniqueness. 

What´s the most easy way to achieve this? Is there a way to prevent using a Method?

Thanks for any idea!

Angela

Parents
  • +1 Offline

    Hi Angela,

    I'm not sure this way of defining two properties to be unique independent of one another is supported even at a SQL level unfortunately. Any columns flagged as unique on the SQL table will be treated as if the combination should be unique.

    If you're looking for a way to avoid using a Method, one thing you might consider is creating a new ItemType to hold the erp_number data. On this new ItemType you can indicate that the erp_number property is Unique, and, since this is now two different tables, both pieces of data can be unique on their own. This solution would require some data model changes such as adding an item property from your Part ItemType to your new ItemType, so it may not be ideal.

    Another possible way of ensuring uniqueness would be to change either item_number or erp_number to be generated by a Sequence. If you'd prefer these numbers not follow a linear pattern, it might also be possible to write a method to generate some kind of more random sequence while ensuring uniqueness to the values it generates. This may require some business logic to change as one or both of these numbers would no longer be input by a user.

    The other alternative of course is to write a Method to run from an onBefore/onAfterUpdate server event to ensure uniqueness for your second property.

    All of these solutions have their pros and cons, but I hope they help! 

    Chris

    Christopher Gillis

    Aras Labs Software Engineer

Reply
  • +1 Offline

    Hi Angela,

    I'm not sure this way of defining two properties to be unique independent of one another is supported even at a SQL level unfortunately. Any columns flagged as unique on the SQL table will be treated as if the combination should be unique.

    If you're looking for a way to avoid using a Method, one thing you might consider is creating a new ItemType to hold the erp_number data. On this new ItemType you can indicate that the erp_number property is Unique, and, since this is now two different tables, both pieces of data can be unique on their own. This solution would require some data model changes such as adding an item property from your Part ItemType to your new ItemType, so it may not be ideal.

    Another possible way of ensuring uniqueness would be to change either item_number or erp_number to be generated by a Sequence. If you'd prefer these numbers not follow a linear pattern, it might also be possible to write a method to generate some kind of more random sequence while ensuring uniqueness to the values it generates. This may require some business logic to change as one or both of these numbers would no longer be input by a user.

    The other alternative of course is to write a Method to run from an onBefore/onAfterUpdate server event to ensure uniqueness for your second property.

    All of these solutions have their pros and cons, but I hope they help! 

    Chris

    Christopher Gillis

    Aras Labs Software Engineer

Children
No Data