Introduction
 

This tutorial explains how to update two or more records with the same match query. In the current version of TIBCO Scribe® Online, the Update Block changes a single record at a time. If more than one record matches the query, the operation fails, the system reports an error, and 0 records are updated. The workaround is to put the Update Block inside a Fetch Block, loop through the records, and update by matching on the primary key. Here are the steps:
 

1. Place a Fetch Block into your Map. Select the Connection / Entity you want to update
2. In the Filter tab, define one or more query parameters that will match multiple records
3. Place an Update or Delete Block inside the Fetch loop. Select the same Connection / Entity as the Fetch Block
4. Configure the Update or Delete Block
   • Matching Criteria - Field:  [primary key]; Operator: equals; Value: FetchResult.[primary key]
   • Enter formulas for the desired field(s) to update

 

Detailed Example

Consider this Use Case:
 

• Source - System: Marketing software; Entity: Activity - Unsubscribe (email address)
• Target - CRM System (i.e. Salesforce); Entity: Lead
• Multiple leads can exist with the same email in the CRM system
• Operation - In CRM system update Leads status to Inactive 

In this common scenario, the CRM system has records with duplicate emails and the marketing system uses email as a unique key. You need to find and update all records in the CRM system by email.

If we use an Update Block and match on email, the operation fails for records that have duplicate emails in the target system. In the case of the Salesforce Connector, TIBCO Scribe® Online throws this error message:

"Message: Multi-object operations are not allowed. More than one object matched the filter criteria."
 

Wrong Method

Here's how the incorrect Map looks in TIBCO Scribe® Online.




Here is a close up of the Update Block Match Criteria


 

Solution

Instead, design the Map as described in the Introduction. Here is how the correct Map looks:

Here is the Fetch Block filter:




Here is the Update Block Match Criteria:



The sample Map for this example has been exported from TIBCO Scribe® Online and attached at the bottom of this article. The source is a simple MySQL table, the target is Salesforce and uses the Update operation. You can use it as a starting point for your particular Solution.
 

Notes

• In this example, the Blocks were given descriptive names for clarification
• The Fetch Block loops through all the records returned by the Filter query and executes the nested Blocks, once for each record
• If no records are returned by the Filter query in the Fetch Block, the Fetch Block is skipped
• TIBCO Scribe® Insight supports modifying multiple records in an Update operation

  

Copyright (c) 2020, TIBCO Software Inc. All rights reserved.

No Support. You acknowledge that TIBCO will not provide any maintenance or support for this software. You have no right to receive any upgrades, patches, enhancements, bug fixes, new versions or new releases of the software.

Internal Use Only License Grant. TIBCO hereby grants you a limited, non‐transferable, non‐exclusive license to use the software solely for your internal business purposes. The name of TIBCO Software Inc. may not be used to endorse or promote products derived from this software without specific prior written permission of TIBCO Software Inc.

 

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT OWNERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

This tutorial explains how to update or delete 2 or more records with the same match criteria in TIBCO Scribe® Online by combining a Fetch Block with an Update Block.