I have a process that integrates from a SQL Server DB to Salesforce and does order processing. The overall integration is hard to explain here since it is complicated for me to describe. Overall, the process works per requirement except in one area i.e., error logging and then emailing those errors. My original design has this error logging part also working but it involves a number of DB writes, which is causing latency in the process. So I am trying to replace the in-between DB writes with writing to a document cache and doing one final DB write to log the error per order. I can’t get this to work. Let me try to explain the complication that I am facing.
There are multiple Salesforce API calls I have to make per order and each SF API call can potentially return an error. I have to combine all these errors into one (say concatenate them into one string) and log the error and processed status (Yes or No) into respective columns on a table in the DB. As I said, according to my original design I was logging this error msg and processed status AFTER each SF API call into the DB (I have a stored procedure that concatenates the incoming error msg with what is already in the column and also checks logic to mark processed status with Yes/No). Finally at the end of the process, I have a branch that again queries this table for anything with a processed status of ‘No’ and gets the corresponding error message to email to a support email. This solves our needs except for the latency due to the multiple writes.
I tried to use document cache to solve this where instead of writing to the DB after each SF call, I write the API call results to a document cache. But there are issues I can’t overcome. You cannot modify or delete a document in the cache. So I can’t replicate what I was doing with the in-between SQL DB writes where I was merging error messages.
Hope I have provided enough information. Can someone please help solve this?