Qnect 200 and table scripts

SOLVED

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
  • 0

    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:

     

  • 0 in reply to John Kingsbury

    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
  • 0 in reply to John Kingsbury

    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
  • +1 in reply to Paul C
    verified answer

    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.


  • 0 in reply to John Kingsbury

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

  • 0 in reply to Paul C

    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. 

  • 0 in reply to Matthew Shaw

    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.

  • 0 in reply to Paul C

    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

  • 0 in reply to Matthew Shaw

    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.

  • 0 in reply to Paul C

    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. 

  • 0 in reply to Matthew Shaw

    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.

  • 0 in reply to Paul C

    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. 

  • 0 in reply to Matthew Shaw

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