In this example of using a Lookup Block, assume you want to migrate Leads from Salesforce into Accounts in a SQL database.  If the Account already exists in SQL, you want to update it with the Lead data from Salesforce. If the Account doesn't exist in SQL, you want to create it.  In addition, you want to replace the Salesforce Lead address information with Salesforce Account address in the Account record in SQL as each record is processed.

In this scenario, the Lookup Block is matching a Source Lead record with a Source Account record based on a matching field.  The Map uses the results of the original Query and the results of the Lookup Block to update an existing Target Account record or create a new Target Account record.
 

Lookup Block Example Block Configuration
 

Review the configuration information for each block in the map to understand how the Lookup Block is leveraged in this example.

Step 1: Query Block
 

• Configure the Query block to gather records from the Source Lead entity.
• Enable the Process only records created or updated since last run option to limit the number of records to new or updated records.
• If you want to test this example scenario, use a filter to limit the number of records returned by the query.

Step 2: For Each Result Block
 

• Requires no configuration.
• Used to create a loop where a Source record is processed, and upon completion, continues to the next Source record.

Step 3: Lookup Block
 

• Use this block to locate the Source Account record that matches the Source Lead record currently being processed.
• On the General tab under Error Handling, enable the If more than 1 match is found fail the record option. This prevents the Map from attempting to process Lead records that match more than one Account record. An error is generated and stored in the History Log. To view the errors, access the Execution History for this solution. See Viewing Execution History Detail in the Scribe Online Help.
• On the Lookup Criteria tab, use phone number to match the Source Account and Source Lead records.

   

• On the Field List tab, select the Source Account record fields that you want to put in the Target Account record. For this example you need the address fields since the map is designed to populate Target Account records with addresses from matching Source Account records. These fields are available for use in later Blocks as part of a result set.

 

Step 4: If/Else Block
 

• Use this block to make decisions. For this example, you need treatment for Leads that have matching Accounts and Leads that do not have matching accounts.
• On the General tab of the Block add a new condition with a formula that uses the results of the Lookup Block. In this case, Result.RecordsMatched is used with a value of 1, indicating that one record matched.

   

• Records with a value of 1, indicating that a match was found between a Source Lead record and a Source Account record, are processed by the Update operation inside the If section of the If/Else Block.
• Records with a value of 0, indicating that 0 matches were found between a Source Lead record and a Source Account record, are processed by the Create operation inside the Else section of the If/Else Block.
• Records with any other value generate an error that is recorded in the Execution History log and they are not processed.

Step 5: Update Block
 

• Use this Block to update existing Target Account records with Source Lead data and Source Account addresses.
• On the Matching Criteria tab, match the Source Lead to the Target Account based on phone number. This ensures that the correct target record is updated. If no match is found, the source record is processed by the Create Block in Step 6.

   

• On the Fields tab, link the data fields in the Source Lead and Source Account records to corresponding data fields in the Target Account records. The Source Account information retrieved by the Lookup Block is located in a Results list.

 

Step 6: Create Block
 

• Use the Create Block to add new Target Account records using Source Lead records and Source Account addresses.
• On the Fields tab, link the data fields in the Source Lead and Source Account records to corresponding data fields in the Target Account records. The Source Account information retrieved by the Lookup Block is located in a Results list. See the graphic in Step 5.

 

Follow this example to learn how to configure and use Lookup Blocks in a TIBCO Scribe® Online Map.