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).

Parents
  • 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?

Reply
  • 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?

Children
  • 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. 

  • Thanks Matthew.  I have used triggers as a last resort but try to avoid them because I believe Sage doesn't support their use and can get antsy if they are.

    In this case an AFTER INSERT trigger would not have worked for the same reason the TLS seemed not to work - ie the inserted row is updated after it is inserted.

    I did some more investigation using SQL Profiler yesterday.  After a row is inserted into the Orders table by Qnect 200, it is subsequently updated 12 times.  So now doing further investigation to determine the best solution, which may well involve using a trigger.

  • I know they are about triggers in Sage 200, so I never put anything in Sage 200, but have never come across a problem with them and CRM. Find them quick to troubleshoot as well - if there is a problem you just disable them and then see if it fixes the problem (and thus you need to troubleshoot your trigger) 

    What were you trying to do? I actually do a mixture of Triggers, Stored Procedures and timed jobs. All depending on the need of the information (they want to see it 'now'), the workload on the server (e.g. I have a nightly job that puts all the Account No. values into one multi-line text field per company so it can be displayed / searched on the Company find screen. As there is no one field you can now add to the screen for this and due to the 1-to-Many relationship it takes a little while to build) and timing of when data is added/updated