From an outside standpoint, my issue seems pretty simple. We have an in house database, which has a table that stores Sales Force CaseComments. Due to a bug on our end, one of the fields in our in-house db table (field name is CaseWorkerID) has been incorrectly populating with NULLs. My job is simply to re-populate all those NULL values with the correct value from SalesForce.
Here's the general approach I need to take in Boomi, but I'm not sure how to go about it correctly:
- Query our in-house database table (called "ActiveNotes") for all records that have CaseWorkerID = NULL.
- The ID from those records is called "ActiveNotesID". Join that to a SF table called Case_Comment_Mapping__c. The join is ActiveNotes.ActiveNotesID = Case_Comment_Mapping__c.ActiveNoteID__c
- Join Case_Comment_Mapping__c.CaseCommentId = CaseComment.Id
- Join CaseCommentId.CreatedById = User.Id
- Grab User.CaseWorkerId
- UPDATE our original in-house database's ActiveNotes.CaseWorkerID WHERE ActiveNotesID = our original ActiveNotesId we've been querying against.
So just to reiterate, the joins are sort of like this:
- ActiveNotes.ActiveNotesID -> SF's Case_Comment_Mapping__c.ActiveNotesID
- SF's Case_Comment_Mapping__c.CaseCommentId = CaseComment.Id
- SF's CaseComent.CreatedById = User.Id
- ActiveNotes.CaseWorkerId = User.CaseWorkerId WHERE ActiveNotesID = original ActiveNoteID we've been querying against
Hopefully that makes sense. Any idea what the best way to go about this?
Thanks in advance