Most of us must have come across the requirement in Siebel to avoid Duplicates in a Business Component for a particular field. Every time when we get such requirement, without thinking on configuration we go for Scripting in Pre_Write Record of Business component. Here is an approach using configuration and it really helps.
 
Scenario: There is a BC as a child of an Account Profile BC; child BC contains a field - Stop Type. This field is having LOV with 4 values, it should not allow duplicate Stop Type. Without any solution the data looks like –
 
Before going for the actual solution, let’s see what all possible solutions do we have:    
 
1.       Scripting: The easiest and most commonly followed solution is to write Script in an event to create a new instance of current BC. Search for records by giving using ‘active’ Stop Type. Use count Records and then throw Error via Raise Error. Hence cancelling operation.
2.       Index: Even this is commonly followed i.e. to create an Index on table level but if you have multiple BCs based on same table you might screw up other BC as well. As in my case the table is S_ORG_EXT_XM which is used by many other Business components. Also sometimes the requirement is to have unique values in multiple fields. It becomes a tough choice. Moreover, the Error which will be thrown on UI would be from Database hence losing control over Error String
3.       Use an Out of Box User Key Column like ‘Name’ which allows only unique values. But again you can’t have enough columns.
4.       The Best Solution is to use below simple configuration in Child BC :-
Go to Child BC and create a Calculated Field with below Value.
 
Step 1:  BCHasRows("Account", "SNI Account Service Profile","[SNI Profile Id]='"+[SNI Profile    Id]+"'"+" AND [SNI Stop Type]='"+[SNI Stop Type]+"'","All")
 
As we know that BCHasRows returns Y or N whether records exist in BC or NOT based on Search Spec.
Syntax:  BCHasRows(BO,BC,searchspec,Visibility)
 
Here are the details:
i.                     Account is BO
ii.                   SNI Account Service Profile is BC in which I want to search. It’s the current BC as well
iii.                  Explanation of Search Spec (This is the trickiest part)
SNI Profile Id is the Field in Child which contains Id of Parent. This field is always in the link as Destination Field
SNI Stop Type is the field which I want to avoid duplicates
The search spec says search in SNI Account Service Profile BC on field SNI Profile Id which matches current record’s profile Id and field SNI Stop Type which matches current record’s Stop Type.
iv.                 All is the visibility used for search
v.                   On Stop Type field add below validation and validation Message
NOTE: Calculated Field will contain ‘Y’ when a Stop Type just added already exists in BC.The field on which you want to avoid duplicates. Add NOT of calculated field created in step 1. Because Validation Message will fire only when its value becomes False. If BC has existing records then Calculated Field will be Y. At that Time validation will go N and Message will pop.