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?
SET String06 = ISNULL(ContactNextActivity.NextActivityDate, '')
SELECT c.contact_id AS ContactId, c.userdef_id AS UserDefId, FORMAT(na.do_by, 'yyyyMMdd') AS NextActivityDate
FROM crm7.CONTACT c
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
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
- 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?? :)