Preventing duplicate records – Scriptless

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

clip_image002

This is wrong and should not happen

Solution:

  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.

The best solution that I have found is through configuration and it is as following

Configuration Solution:

  1. 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”)
    clip_image002[5]

Explanation of the calculated value:

BCHasRows: This function accepts 4 arguments

  1. Business Object :  Name of the Business Object (Account in above example)
  2. Business Component: Name of the Business Component on which the search spec should be execute (SNI Account Service Profile in above example)
  3. SearchSpec: Query that will be executed on business component
  4. 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:-



If you liked this post please rate this and provide your feedback. It encourages people to contribute more.

This Post has been viewed : 41,814 Times


23 Responses to Preventing duplicate records – Scriptless

  1. Nice Configuration the only issue i see is last time when i was working with BCHasRows method i was not able to impose visibility onto it . It was taking visibility setting from logged in User 🙁
    may be i was doing some thing wrong .
    But a Good approach to the Age old problem Neel

  2. can’t we invoke DVM at PreWrite Record instead of doing this? I think calculated field expr will fire for all the child record which will translate into same SQL multiple times? Is not it? better is to move this validation at Prewrite record.

  3. I think Dos1’s point was that the calcfield will fire the additional query even if you only want to _display_ the existing records in ‘SNI Account Service Profile’, and it will do the extra query for every existing record.
    It would be better to fire the calc-expression only on updating the field ‘SNI Stop Type’.
    Maybe, it can be done with BC UserProp ‘On Field Update Set…’ using the calcfield expression as the 4th parameter ‘Condition’.
    Additionally: to avoid creating a physical field for the parameter ‘FieldToSet’ (which is only used temporarily for the DVM) one can create an ’empty’ calculated field.

  4. Hi,
    Really nice !! Does this work with Date Time fields as well ?? I mean requirement is to not allow insert if a same date (and not time)  exist!!

  5. Just awesome.. Very helpful. I bet every Siebel project will have a need to use this.  Keep up the great work !!

  6. dont forget to check Id = Current Id else in pre write recrd if you updating other field then this validation has to skip else in query it takes the current record as a duplicate record

  7. Hi,
    I have used BCHasRows in a DVM rule exp on PreWriteRecord. This rule fires even when the record is edited, and apparently checks against itself too and thus results in an error. I guess what’s happening is, when it is edited, a PreWriteRecord is invoked in turn invoking the query on BC for a match. This record itself is returned as a result of this query and the error is thrown.
    A workaround will be to delete this record and create a new one instead of modifying the same. But is this normal behavior?

  8. Hi Neel,
    I implemented above thing but along with Error Message, I am getting vanilla Error too. Do you know how to bypass that error?

    —————————
    Siebel
    —————————
    [1] Wrong field values or value types detected in field Tax Exemption Type. Please re-enter your field values. If you need additional assistance, please refer to the documentation.(SBL-UIF-00299)
    [2] A record with same Tax Exemption Type already exist!: SBL-DAT-00521

    —————————
    OK
    —————————

Leave a Reply

Contribute