In this article, we will learn how to setup lookups using DBLOOKUP formula of Scribe Insight. The basic example we will be taking is to set parent contact id (customerid) field of Opportunity entity. For the source, we will be using SQL Server Database, and to keep it simple we are populating only 2 fields here Topic and Contact of the opportunity entity record.Contact column will be used for setting the lookup field in the Opportunity.
Here is our source SQL Table:
Open Scribe Workbench and create a new Microsoft SQL Server connection as source.
Select the Opportunity source table created.
Next, click on Configure Steps and add Microsoft Dynamics 365 customer services and CRM connection. Create a new connection if not already created.
Select Opportunity entity and for operation select Insert and click on Add Insert Step.
Click on Close.
Now let us define the mapping
Select Topic field from the source and select name field of the contact entity in the destination and click on Data Link
Next select Contact from the source and select customerid from the destination and click on Lookup Link
Next, click on Formula to define the DBLookup formula
DBLOOKUP Formula
DBLOOKUP( TextSourceField, "connection", "table", "lookup_field", "substitution_field" )
Specify the formula as shown below.
Here we are doing the lookup based on the fullname field of contact entity and we can contact’s GUID to be returned.
Click on OK to close the window.
Next, select customeridtype field and specify the value as contact for it.
Save the package and Click on the Run icon to run the package
We can see 2 records processed successfully.
Inside Dynamics 365 we can see 2 Opportunity records created with customer id field set properly.
Thus we saw how easy it is to set Lookups using DBLOOKUP formula in Scribe Insight.
