Wednesday, May 18, 2011

Constraining Business Component to accept unique value for some fields

One of the interesting requirements I heard recently to put a check on the business component to accept only those records which has the unique value in specified fields. Actual requirement goes this way:



“Opportunity Product” is the child business component of “Opportunity” Business Component. System should not allow adding the Opportunity Product record with the same “Product” associated with it. It should prompt a message; you cannot add same product to more than 1 line item. Something like below screenshot:




Immediate reaction could be put a script on PreWrite event of “Opportunity Product” business component (Non-Active Instance) to check if there is any other record with the same Product value and RowId not = Current record rowid (this is important because you want to check for the other record, if exists). And then prompt the message RaiseErrorText.

Okayy fine, the above solution would work for sure but back in mind people always think for some script less solution. Frankly speaking, I won’t prefer calling a workflow process here for just sake of providing the script less solution like invoke a WF process on PreWrite event to check if there any record exists with the same Product and then thru the error.

A friend of mine, Varun Gulati suggested a nice way to achieve this requirement. So, the function which will do this wonder is “BCHasRows()”. Here below is the solution:

1. Create a calculated field on “Opportunity Product” BC:



a. Name = Duplicate Product Calc
b. Calculated = True
c. Calculated Value =
BCHasRows("Opportunity", "Opportunity Product", "[Product] = '"+[Product]+"'"+"AND [RowId] <> '"+[RowId]+"'" + "AND [Oppty Id] = '" + [Oppty Id] + "'", "All")


2. Put the validation on “Product Id” field :



a. Validation =
[Duplicate Product Calc] = 'N'
b. Validation Message – String Override =
You cannot add same Product to more than 1 line item.


And that’s it. This solution looks very easy and clean isn’t it? Not a single line of code is required.

Basically, “BCHasRows()” take the Business Object, Business Component, Search Spec and Visibility as the input. Evaluates the Search spec on the BC and returns Y or N. Please note that the validation message has been put on “Product Id” field, and not on “Product” field, because I observed that “Validation” is getting fired on the base fields only (not on Join fields).
Give it a try!!



7 comments:

  1. Please keep in mind that BCHasRows() creates a SQL Subquery for each Row

    ReplyDelete
  2. Hi Alexander,

    As I mentioned in the post, validation will only fire if you update the "Product Id" (via pickmap of Product field). You are right that BCHasRows() will fire a SQL but not for each row. It will only execute the SQL for the current record and even only if you update the "Product" value.

    cheers
    Gaurav

    ReplyDelete
  3. I remember last time I have written very big script for the same requirement.
    Thanks Gaurav. Sharing wonderful post.

    ReplyDelete
  4. What version of Siebel this solution can be applied to?

    ReplyDelete
  5. I am using 8.1 and it is working fine. If you are using lower version then give it a try.

    ReplyDelete
  6. It is working with Siebel 8.0 as well.

    ReplyDelete
  7. will it work for siebel 7.5 ? I am using in interssion table(BC) on MVG , while adding products to service request and for me its failing all time as it returns True always...it woudnt allow me to add any record even if that record is not been added to the list :(

    ReplyDelete