Lookup Block Example
This Lookup Block example illustrates migrating Leads from Salesforce into Accounts in a SQL database. For Accounts that exists in SQL, the Map updates those records with the Lead data from Salesforce. For Accounts that do not exist in SQL, the Map creates those records. In addition, the Map replaces the Salesforce Lead address information with the Salesforce Account address in the SQL Account record as TIBCO Scribe® Online processes each record.
In this scenario, the Lookup Block matches 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 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 used 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 on the Net Change tab to limit the number of records to new or updated records. See Block Properties Net Change Tab.
- 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 the Lookup Block to locate the Source Account record that matches the Source Lead record currently being processed.
- On the Error Handling tab, 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 Execution History Log. To view the errors, access the Execution History for this Solution. See Viewing Execution History.
- 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.
Note: To improve performance, select only the fields you need. This reduces the amount of data returned from the target of the Lookup.
Step 4: If/Else Block
- Use the If/Else 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 the Update 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 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 in a Results list. See the graphic in Step 5.
