This post is contributed by Pankaj Juneja and it is one of the most useful post you would find on Siebel Unleashed. It explains a scriptless and simple solution for a very common requirement to prevent duplicate records based on certain field values. Here it goes
All of Siebel Developers must have come across this requirement in Siebel to avoid duplicates in a Business Component for a particular field. I have came across the same quite often in my career and every time I got such requirement, without thinking I went for Scripting in PreWrite Record of Business component. Now If I look back sometimes I feel so stupid because there are so many ways to configure and avoid scripting. Now the approach I follow is not to give up on configuration and it really helps. You will find one or other way to avoid or reduce scripting at least.
Problem: – I have a Business component SNI Account Service Profile as child of Account Profile BC. It has a field – “Stop Type”. This field is having LOV with 4 values and the requirement is that user should not be able create multiple records with same value for “Stop Type” field
This is wrong and should not happen
- 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.
- 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.
- Use an Out of Box User Key Column like ‘Name’ which allows only unique values. But again you can’t have enough columns.
The best solution that I have found is through configuration and it is as following
- Create a calculated field in the child BC (SNI Account Service Profile ) with following valueBCHasRows(“Account”,”SNI Account Service Profile”,”[SNI Profile Id]='”+[SNI Profile Id]+”‘”+” AND [SNI Stop Type]='”+[SNI Stop Type]+”‘”,”All”)
Explanation of the calculated value:
BCHasRows: This function accepts 4 arguments
- Business Object : Name of the Business Object (Account in above example)
- Business Component: Name of the Business Component on which the search spec should be execute (SNI Account Service Profile in above example)
- SearchSpec: Query that will be executed on business component
- Visibility: All in above example
Explanation of the the Search Spec:
“[SNI Profile Id]='”+[SNI Profile Id]+”‘”
SNI Profile Id is the Field in Child which contains Id of Parent. This field is always Destination Field in the link definition
[SNI Stop Type]= ‘”+[SNI Stop Type]+ “‘”
SNI Stop Type is the field for which I want to avoid duplicates
In simple language this searchspec says: “Query for records that have same value in SNI Profile Id AND SNI Stop Type field”. So, if a record exists with same parent id and with value as current Stop Type field it will return ‘Y’
IMP: Calculated Field will contain ‘Y’ when a Stop type just added already exists in BC.
Now the only thing needs to be done is put a validation based on the this value:
Add NOT of calculated field created above 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.
Let’s See what we have achieved:-