Migrating Onsite SQL-updates to CRMScript in online - Suggestions on effective ways of executing update operations in a declarative way instead of iterative using CRMScript in Online?

Hi,

I'm migrating some old Onsite-Script-solutions to Online and CRMScript.

Below we have a SQL-statement that very effectively updates an udef-field given certain criterias (the effective, but bad practice way, I know).

In my defence I might add that I have some extensive initialization-control-methods that verify that this udef-database-field actually corresponds to the correct ProgId. If not, updates are not executed.

So. with that said. :)

What is the most effective way of implementing an alternative update-operation using CRMScript in Online compared to the used SQL-update below?

==================================================================================================
UPDATE crm7.UDCONTACTSMALL
  SET String06 = ISNULL(ContactNextActivity.NextActivityDate, '')
    FROM
    (
      SELECT c.contact_id AS ContactId, c.userdef_id AS UserDefId, FORMAT(na.do_by, 'yyyyMMdd') AS NextActivityDate
      FROM crm7.CONTACT c
      LEFT JOIN
      (
        SELECT
          a.contact_id AS contact_id,
          MIN(a.do_by) AS do_by
        FROM crm7.APPOINTMENT a
          LEFT JOIN crm7.TASK ta ON a.task_idx = ta.Task_id
        WHERE a.document_id = 0           --The appointment is not a attached document
          AND a.private = 0               --The appointment is public and readable by anyone
          AND a.status <> 3               --The appointment is not completed
          AND ta.record_type in (1,5,6)   --The appointment is of type Appointment, Phone, ToDo
          AND a.endDate > GETDATE()       --Include appointment which endDate haven't passed current date and time
          AND a.do_by > GETDATE()         --Only include appointment that lies in the future
        GROUP BY a.contact_id
      ) na ON c.contact_id = na.contact_id
      LEFT JOIN crm7.UDCONTACTSMALL uds ON c.userdef_id = uds.udcontactSmall_id
      WHERE c.userdef_id > 0              --Don't include appointment owned by a contact with userdef = 0, they seem to indicate contacts without a corresponding record in udcontactsmall
    ) ContactNextActivity
        WHERE ContactNextActivity.UserDefId = crm7.UDCONTACTSMALL.udcontactSmall_id
==================================================================================================

 


I feel that the main problems are:

  • This is a declarative call that can update thousands of rows in a couple of seconds, meaning it is highly effective and fast!
  • I feel that all methods available via CRMScript and the NetServer-API are more or less imperative which gets heavily ineffective and time consuming
  • As we are limited to scripts running for 60 seconds and 8 MB of memory this makes imperative solutions very tedious and more complex as they will demand more time and more calculation memory
  • SearchEngines doesn't support grouping, if not bypassing netserver and that feels lika a method that might get deprecated
  • Joins in SearchEngines are partly supported for joins between specific tables and fields, but there is no documentation of the detailed dependencies
    • I have also experienced unexpected behaviour on some occations
  • Is there any alternative ways to ask for MIN(field) grouped using an Archive?
  • I could probably get the match-list in some raw way using a SearchEngine, then iterate over it and programmatically handle the min-grouped part
  • But then I want to set that date to a udef-field for say 1000 companies
    • Is it possible to do that in a declarative way instead of iterative?
    • I can't use SearchEngines to set a udef-field based on its ProgId
    • Apart from if I construct some kind of function that based on the progId returns the actual database field used.
  • Can the bulk update Agent be used for this exact problem in some way (from CRMScript in Online, right now)?
    • If yes, could somebody share some CRMScript-based example?
  • If I need to create a solution that iterates over each contact using an agent to update the contact, I will exceed the script execution time limit
    • I then have to execute the updates in batches
    • Each batch need some margin, as the same kind of operation sometimes takes different time to execute
    • I usually keep the margin at 30 seconds, which means that I lose 30 seconds of execution time for each batch
    • The batch needs to be triggered by a scheduled script which have the lowest iteration time of each 60 seconds
    • One contact-update-operation takes about 1-2 seconds, lets say 1.5 seconds
    • 1000 operations (updates) leads to a basic approximate execution time of 1500 seconds which is 25 minutes
    • As 30 seconds per batch are margin time, it will take double the time, which means 50 minutes
    • I then need to save the state, meaning the list of contact-id's and potentially the data to update in a temporary extra table that needs to be created
    • I also have to build a controller function that handles the task to, on each triggered schedule, check the state and continue execute batches until the batch-list is empty
  • I have also investigated the possibility to use an external controller in the form of a Azure Function, Azure Durable Function or AWS Lambda Function
    • Limits
      • Azure Function, execution time limited to 10 minutes
      • Azure Durable Function, no time limit
      • AWS Lambda Function, execution time limited to 15 minutes
    • There might be other solutions that could be interesting as well as Azure Logic Apps, etc
    • That way a Service scheduled script could send a list of id's to iterate over (and possible the data to update) to the Azure Function
    • The Azure function then calls back to an exposed CRMScript-function that triggers the update-operation that also returns an ok
    • That way we can skip the margin time and send the next update-operation when the last operation is finished, maybe we need to add some kind of sleep between calls to not hammering the system to hard
  • But I just feel that this is extremely much extra work for something that technically really shouldn't be this complex to solve... :)

 

Any good suggestions?? :)

 

Best Regards
Marcus

RE: Migrating Onsite SQL-updates to CRMScript in online - Suggestions on effective ways of executing update operations in a declarative way instead of iterative using CRMScript in Online?

Hi Marcus, 

I believe it's best to leave the feelings out of the thought process. While you might "feel" a certain way about something, doesn't mean that an intuitive function is going to magically manifest itself as a viable approach. I wish it worked that way.

Migrating from the simplicity of an onsite hack to a stricter online solution requires a whole new skill set.

To the best of my understanding, the goal of CRMScript was never to facilitate that level, or form, of search and replace. Could it? Should it? Sure. Why not? Well script permissions come to mind. Anyway, I digress. 

Bulk update was designed for that use case. However, there is no null or empty string operator available to let you search in the way you are trying. And because updates can take a long time, BulkUpdate API is not available in CRMScript.

Using bulkupdate you will need to have a predetermined array of contact ids that you want to update, with the value you want to assign.

Performing a search with the ContactActivity archive provider, for example, would allow you to search for contact's based on appointment criteria, including documentId, visible in diary, status, record type, and dates. 

All of this should be doable via a serverless function, I don't see why not. Yes, of course you will have to registered an application to perform this type of task, and acquire a whole new skill set required to create, deploy, perform and maintain this type of functionality. Welcome to online development. 

If you are thinking "well CRMScripts don't require an application id to execute...". Well, while scripts enjoy some "freedom" today, I wouldn't be so sure about that tomorrow. If scripts in online have proven anything, it is that they need to be monitored just as much as the standard APIs - if not more. That's all I'm going to say about that for now.

Perhaps someone else in the community has some better more practical CRMScript guidance, but these are my initial thoughts on the matter.

Best regards.

Von: Tony Yates 27. Feb 2020

RE: Migrating Onsite SQL-updates to CRMScript in online - Suggestions on effective ways of executing update operations in a declarative way instead of iterative using CRMScript in Online?

Hello,

Basically what this is, is filling an unlimited date field on the company with the date of the next activity?

You could propably solve this by implementing a CRMScript trigger that when an new appointment is created or updated, that you calculate the next activity date again for that contact.

Something like this:

Integer contactId = 1055;

SearchEngine seLastActivityContact;
seLastActivityContact.addField("contact.contact_id");
seLastActivityContact.addField("contact.(appointment->contact_id).do_by");

seLastActivityContact.addCriteria("contact.(appointment->contact_id).status", "OperatorIn", "1,2"); // nostarted, started
seLastActivityContact.addCriteria("contact.(appointment->contact_id).type", "OperatorIn", "1,2"); // diary, checklist (task)

seLastActivityContact.addCriteria("contact.(appointment->contact_id).do_by", "OperatorGt", getCurrentDateTime().toString()); 
seLastActivityContact.addCriteria("contact.(appointment->contact_id).endDate", "OperatorGt", getCurrentDateTime().toString());


seLastActivityContact.setDistinct("contact.contact_id");
seLastActivityContact.addOrder("contact.(appointment->contact_id).do_by", true);


seLastActivityContact.addCriteria("contact.contact_id", "OperatorEquals", contactId.toString());


print(seLastActivityContact.executeHTMLTable());

Drawbacks:

- Trigger won't be executed when doing a change from PocketCRM, BulkUpdate or another external application (synchronizer etc)

- The 'Next activity date' would not be cleared when it is in the past

 

Another solution would be to do in on the background;

1. Get a list of all contact_ids in the database

2. Get a list of all contact_id's and their next activity date (see code above, remove the contact id criteria)

3. Remove all the contact ids from step 2 from the list from step 1

4. Clear the 'next activity date' for all the remaining id's of step 1 (This could be done with bulk update if it was available in CRMScript)

5. Update the contact id's from step 2 with their new next activity date

If you do the updates from step 4 and 5 using SearchEngine if will be pretty fast, but it won't register in traveltransactionlog and trigger WebHooks, but the existing solution also didn't do that (I would still recommend against using SearchEngine for any modify or delete operations against CRM tables)

When doing the updates from step 4 and 5 using NetServer agents you will need to build in a batch mechanism. (Maybe a last next contact activity calculation date on the contact)

Von: David Hollegien 27. Feb 2020

RE: Migrating Onsite SQL-updates to CRMScript in online - Suggestions on effective ways of executing update operations in a declarative way instead of iterative using CRMScript in Online?

Hi,

Thanks both of you for your valuable input!

David:
I really appreciate you taking the time to come up with such complete and pedagocial examples and suggestions. Not only this time, but I see it all the time in the forum. It gives great value to the forum! Thanks!

I actually chose to implement a trigger-based solution based on a variant of your example for the time being. I will look into an internal batch-handled solution that handles the not handled use cases later on.

 

Best Regards
Marcus

Von: Marcus Svenningsson 4. Mrz 2020