Clarity Service Management

Expand all | Collapse all

SPL Trigger to check for duplicates

Jump to Best Answer
  • 1.  SPL Trigger to check for duplicates

    Posted 11-06-2015 06:56 PM

    Hi Community,

     

    I've created a few new tables was wondering if anyone has an example of how to check for duplicates before creation of new data?  There are a few tables that have this functionality out of the box, but I wasn't able to find the spl code behind the triggers as it's encrypted.  My custom tables all have sym fields, which I would like to add this functionality too.  I appreciate any help.  Thanks!



  • 2.  Re: SPL Trigger to check for duplicates

    Posted 11-07-2015 12:17 AM

    hi,

    here is basic variant with user interface error on duplicate.

     

    mod file:

    MODIFY cr PRE_VALIDATE z_chck_dup() 1337 FILTER(EVENT("INSERT"));
    

     

    spl:

    cr::z_chck_dup(...) {
        send_wait(0, top_object(), "call_attr", "cr", "sync_fetch", "MLIST_STATIC", format("sym='%s'", sym), -1, 0);
        int dup_count;
        dup_count = msg[1];
        logf(SIGNIFICANT, "dup_count='%d'", dup_count);
        if (dup_count>0) {
            logf(ERROR, "Unable to insert [%s]. Sym must be unique!", persistent_id);
            set_error(1);
            set_return_data(format("Unable to insert [%s]. Sym must be unique!", persistent_id));
            return;
        }
    }
    

     

    regards,

    cdtj



  • 3.  Re: SPL Trigger to check for duplicates

    Posted 11-07-2015 11:46 AM

    Thank you cdtj!  You've been very helpful.  I will give this a try Monday.



  • 4.  Re: SPL Trigger to check for duplicates

    Posted 11-09-2015 04:05 PM

    Hi cdtj, would you mind reviewing my code?  I'm getting errors.

     

    11/09 10:58:27.38 hlpdskdevapp   domsrvr:wsp          4756 ERROR        majic.y               4622 mods/majic/zCustomer.mod:0 Factory not found at "PRE_VALIDATE"

    11/09 10:58:27.40 hlpdskdevapp   domsrvr              4068 ERROR        majic.y               4622 mods/majic/zCustomer.mod:0 Factory not found at "PRE_VALIDATE"

    11/09 10:58:27.44 hlpdskdevapp   domsrvr:wsp          4756 SEVERE_ERROR miscos.c               222 Signal SIGSEGV received - Exiting!

    11/09 10:58:27.51 hlpdskdevapp   domsrvr              4068 SEVERE_ERROR miscos.c               222 Signal SIGSEGV received - Exiting!

     

    All I've changed is the object from CR to zCustomer.

    SPL:

    zCustomer::z_chck_dup(...) {

        send_wait(0, top_object(), "call_attr", "zCustomer", "sync_fetch", "MLIST_STATIC", format("sym='%s'", sym), -1, 0);

        int dup_count;

        dup_count = msg[1];

        logf(SIGNIFICANT, "dup_count='%d'", dup_count);

        if (dup_count>0) {

            logf(ERROR, "Unable to insert [%s]. Customer must be unique!", persistent_id);

            set_error(1);

            set_return_data(format("Unable to insert [%s]. Customer must be unique!", persistent_id));

            return;

        }

    }

     

    MOD:

    MODIFY zCustomer PRE_VALIDATE z_chck_dup() 1337 FILTER(EVENT("INSERT"));



  • 5.  Re: SPL Trigger to check for duplicates

    Posted 11-09-2015 04:22 PM

    The problem is with the trigger, try to change your mod in the following way:

     

     

    OBJECT zCustomer {

    TRIGGERS {

    PRE_VALIDATE z_chck_dup() 1337 FILTER(EVENT("INSERT"));

    };

    };

     

    I also wondering why you not using default cnt object for the cutomer list, if you need to filter them, you can use some custom or existing contact type and create the factory that will contain all cnt objects constrained by special type. Similar as there is cst/agt/grp factories for cnt.



  • 6.  Re: SPL Trigger to check for duplicates

    Posted 11-09-2015 04:26 PM

    Thanks Gutis, I'll try that.  We are currently using the cnt object for customers, but in our case the customers are never going to interface with the system and are only there as reference on incidents. 



  • 7.  Re: SPL Trigger to check for duplicates

    Posted 11-09-2015 04:42 PM

    So you can assign them customer type and assign access type with "no access" authentification type. Than you can create a factory e.g. cust

     

    OBJECT cnt {
    FACTORY cust {
            RESTRICT "type = 2310" { type = 2310} ;

            DOMSET cust_list "combo_name,phone_number,contact_num,organization,location" "" DYNAMIC;


            STANDARD_LISTS {
                SORT_BY "combo_name,phone_number,contact_num,organization=organization.name,location=location.name";
                WHERE "delete_flag = 0" ;
                MLIST OFF ;
                RLIST OFF ;
            };

            REL_ATTR id ;
            COMMON_NAME combo_name ;
            FUNCTION_GROUP "contact" ;
        } ;
    };

     

     

    make an SREL to cust

    zcustomer SREL cust



  • 8.  Re: SPL Trigger to check for duplicates

    Posted 11-09-2015 04:44 PM

    Thanks Giedrius,

     

    I will keep this in mind, it sounds like a better solution.



  • 9.  Re: SPL Trigger to check for duplicates

    Posted 04-06-2016 08:33 AM

    Hi All,

     

    Please help for loc object, created mod and spl as per above suggested way

    mod

    MODIFY loc PRE_VALIDATE z_chck_dup() 1337 FILTER(EVENT("INSERT"));

    spl

    loc::z_chck_dup(...) { 

        send_wait(0, top_object(), "call_attr", "loc", "sync_fetch", "MLIST_STATIC", format("z_l_code='%s'", z_l_code), -1, 0); 

        int dup_count; 

        dup_count = msg[1]; 

        logf(SIGNIFICANT, "dup_count='%d'", dup_count); 

        if (dup_count>0) { 

            logf(ERROR, "Unable to insert [%s]. Sym must be unique!", persistent_id); 

            set_error(1); 

            set_return_data(format("Unable to insert [%s]. Sym must be unique!", persistent_id)); 

            return; 

        } 

     

    but getting error in log file while creation of any location from web page

    04/06 04:58:32.02 sdm14  domsrvr              3860 ERROR        where.y                908 Parse error at : "( z_l_code='123456' ) AND ( parent_location_uuid IS NULL AND location_type_id IS NULL )" (Bad where clause)

    04/06 04:58:32.09 sdm14  spelsrvr             5816 ERROR        pcexec.c              6365 Spell interp failed at duplicate.spl:4:loc::z_chck_dup: Reply message [1]: bad index - size is 1

    04/06 04:58:32.19 sdm14  web:local            6048 ERROR        freeaccess.spl       24823  create checkin of loc:3D7889EE82AC2F44B60D64527FBB3082 failed: duplicate.spl:4:loc::z_chck_dup

     

    I want to create uniqueness in loc object  based on one custom field(z_l_code)

     

    Thanks in advance !!!



  • 10.  Re: SPL Trigger to check for duplicates

    Posted 04-06-2016 08:39 AM

    hi,

    if your loc code is integer, you need to search for it without quotes : format("z_l_code=%d", z_l_code)

    regards,

    cdtj



  • 11.  Re: SPL Trigger to check for duplicates

    Posted 04-06-2016 08:59 AM

    Hi cdtj,

     

    I tried with format("z_l_code=%d", z_l_code), still getting same error.

    Please suggest.

     

    Thanks

     



  • 12.  Re: SPL Trigger to check for duplicates

    Posted 04-06-2016 09:00 AM

    Hi cdtj,

     

    What about this error

    AND ( parent_location_uuid IS NULL AND location_type_id IS NULL )" (Bad where clause)

     

    regards,

    pradeep



  • 13.  Re: SPL Trigger to check for duplicates

    Posted 11-10-2015 04:05 AM

    Just an idea.

    regarding uniqueness and avoiding duplicates,I would always go for appropriate unique db indexes.

    This would provide  a much more reliable duplicate check then a trigger can do

    Regards

    ..............Michael



  • 14.  Re: SPL Trigger to check for duplicates

    Posted 11-10-2015 11:04 AM

    Hi Michael,

     

    I was trying to replicate the behavior of the ootb tables.  Do the ootb tables use database indexes or spl code?  If they use indexes would you be able to provide an example of what that would look like?



  • 15.  Re: SPL Trigger to check for duplicates
    Best Answer

    Posted 11-10-2015 04:34 PM

    Yes I would say  mostof the ootb tables which have to handle with uniqueness are using database indexes to force uniqueness.

    And as long you only have one  column in your table which has to be unique, you can even use the web screen painter schema designer to achieve this

    2015-11-10 22_31_38-SDM14.1 - VMware Workstation.png

    Regards

    ..........Michael



  • 16.  Re: SPL Trigger to check for duplicates

    Posted 11-11-2015 10:35 AM

    Thank you Michael,

     

    I can't believe I didn't know about this.  You see I always try to find the most extreme solution and then go from there.



  • 17.  Re: SPL Trigger to check for duplicates

    Posted 12-02-2016 03:11 PM

    Hi all, is it possible to avoid duplicate organizations?