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

Question

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&acute;s the most easy way to achieve this? Is there a way to prevent using a Method? 
 Thanks for any idea! 
 Angela

Amitosh Tripathy · Accepted Answer

We had a similar requirement elsewhere, Item ID(unique=true, but wanted to change this and make combination to be unique)+ItemType to be unique. The application we use supports multi field key, but we can do it in Aras either with the object model or with sql table. 
 SQL option is to creates a table in database which stores Item ID and combination of 2 fields, from a database trigger (no method code involved, the records are created or updated based on create/update in say Part table - so latest combination is always updated to that table). You have to make sure you delete the record when Part gets deleted. When creating a new part you have to run sql to check if the same combination already exists using sql. 
 If you want to avoid SQL, create a new property on the itemtype and obBeforeUpdate/Create populate the field with combination of 2 fields. And validate against the new field for each new create. Other way you can use new itemType to store combination of 2 fields too.

Christopher Gillis · Answer

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

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

Top Replies