AnsweredAssumed Answered

How can I join across multiple sales force tables, including a custom table?

Question asked by rmatthews335179 on Mar 10, 2017
Latest reply on Mar 15, 2017 by rmatthews335179

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 CaseWorkerIDhas 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:

  1. Query our in-house database table (called "ActiveNotes") for all records that have CaseWorkerID = NULL.  
  2. 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
  3. Join Case_Comment_Mapping__c.CaseCommentId = CaseComment.Id
  4. Join CaseCommentId.CreatedById = User.Id
  5. Grab User.CaseWorkerId
  6. 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:

  1. ActiveNotes.ActiveNotesID -> SF's Case_Comment_Mapping__c.ActiveNotesID 
  2. SF's Case_Comment_Mapping__c.CaseCommentId = CaseComment.Id
  3. SF's CaseComent.CreatedById = User.Id
  4. 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