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?

  • Well I haven't actually got onto what the customer wants to do yet.  In my test TLS I just set the PO Reference field to '0000000000' to see if the TLS ran when I synced an order.  It seemed to do nothing because it gets overwritten by the original value from Sage 200 in a subsequent update.  The customer actually wants to split out sales orders which have different delivery addresses (branches) but for the same S/L account into separate CRM companies for analysis and marketing purposes.  I'm still investigating the potential issues this may cause before I tell them it's possible, but so far haven't found any.

  • In which case I wouldn't used one of the fields that I know will be updated by Sage 200, introduce a custom field to Orders and then you can control that how you want and use it in reporting etc. 

  • Yes.  However splitting the orders into separate CRM companies will involve changing the opportunity they are assigned to so I will have to update the orde_opportunityid field to do that.  I'll probably need two triggers - an INSERT one to do the reassignment and save the new oppo id in a custom field and an UPDATE one to make sure the oppo id remains changed.

  • So they're not using one CRM Company and representing the branches as separate addresses? How are they shown in Sage 200? One sales ledger account? With the branch name on the first line of the delivery address? Starting to sound like this will be 'messy' to set up coming from Sage 200

    Could they not create the orders within CRM and post them into Sage 200? This way Qnect allows them to link multiple companies to the same Sales Ledger account. Then the user would create the opportunity (per company) as normal and raise the order as normal. in CRM you would have everything separated, but within Sage 200 all the orders would appear under one account. No custom scripts needed, it would work as designed. 

  • That sounds like a good alternative to me.  I'll put it to them.  Thanks.

Reply Children
No Data