Unique custom field in Item table

G’day,

I have found similar Post on this forum but none of them were resolved, that’s why opening new one.

Navision 5.0 MS SQL 2008

I would like to have unique field(Custom Field ‘External Id’ which is text field) on Item table.

What I’ve done so far:

  • in Navision Created new Key in Item table through → Object Designer->Item->Design->Keys
  • in MsSQL2008 set this key from index to unique

However when I insert new record with same External Id it doesn’t comes with any error message and simply creates this record. Which ends up with 2 duplicated entries.

Could you tell me what I am doing wrong?

Regards Ondrej

If the key is part of Primary key then only you get error message.

Otherwise, you have to write code in OnValidate trigger of field to check whether the value already existing or not.

Hi Mohana,

in terms of performance is it better to add it to primary key or write OnValidate trigger.

Would you be able to help me with OnValidate trigger code?

Its better to write code in OnValidate trigger…

Please try something and let us know what didnt work.

Can you share some advice, how to start. I’ve done some work with reports and lookup fields.

Hi Onadra,

First of all What is External ID field ? It is relevant to Item No field

If you add the field in Primary key then it could lead to disaster because if somewhere in programming/system if you would have use ‘GET’ function.

If it is Integer field then you can set property called as Autoincrement over the field.

or else write a code on Onvalidate trigger for checking the unique value.

External ID is not relevant to No we use this number to synchronize items with external system. External System writes it’s number to Nav. Problem happens when one of our staff copy old item to new new one. This is how duplicates are created.

I believe I have to write Onvalidate trigger, would you be able to provide me with example code. In my understanding I will have to use temp variable to store current value and run another query to get number of records with same value. If count is grated then 1 I know we have a duplicates.

Is this right approach?

Yes