Qnect 200 and table scripts

I need to implement some business rules when sales orders are saved in CRM, including orders synchronised from Sage 200 using Qnect 200.

Has anyone managed to get table scripts to work when orders are inserted from Qnect?  Qmulus tech support says "Sage CRM should run its table scripts when records are created/updated by the sync", but my test TLS doesn't seem to run when an order is inserted by Qnect (it does run when an order is inserted by CRM).

  • Qnect dev here, can you try the below tablescript on the order entity?

    Then try creating a new order in 200 and get it to sync to CRM.

    The order's description should say "Set from TS Update", I have confirmed this is working with Sage CRM 2022 R1.

    We use SOAP webservices for data transfer which should trigger tablescripts.


    function InsertRecord()
    {
    Values("orde_description") = "Set from TS Insert";
    }
    
    function PostInsertRecord()
    {
    Values("orde_description") = "Set from TS PostInsert";
    }
    
    function UpdateRecord()
    {
    Values("orde_description") = "Set from TS Update";
    }
    
    function DeleteRecord()
    {
    }


    Screenshot of the TS settings:

     

  • Thanks John.  The order was synced with description "Set from TS Update".

    I then remarked the line in UpdateRecord(), created a new Sage 200 order and ran the sync.  This time the description wasn't updated.

    Does that mean the TLS is fired only when the row is updated and not when it's inserted?  Or is the row inserted and then updated so that changes made in the Insert() function are overwritten and therefore giving the impression the TLS hasn't executed?

  • orde_description is updated by the sync after the order is inserted, so "Set from TS Insert" will be overwritten with the new value which will run the UpdateRecord func.

    So:
    1. Sync inserts order
    2. InsertRecord, PostInsertRecord runs
    3. Sync immediately updates CRM order with some extra details (description, status, opened) to work around the fact that CRM does not allow these to be set in insert for some reason.
    4. UpdateRecord runs.


  • Thanks John.  That explains why I thought nothing was happening - clearly the value I set in InsertRecord() is being overwritten in Update().

  • We do a lot of work in SQL Triggers, as we have several in place on Quotes/Orders to apply VAT to lines in CRM and update the Gross values.