Logic App: NewLeadLookup

Overview

This Logic App enriches Microsoft Dataverse (Dynamics 365) Lead records with financial exposure information based on a company’s business number (Company Registration Number). It integrates with a SQL data warehouse (Sentinel) to find matching records and updates the corresponding Lead with the gross exposure and company name if a match is found.

Trigger

  • Event: When a Lead record is created or updated
  • Condition: The field tf_businessnumber must be populated (i.e., not null) and be part of the update payload
  • Source: Common Data Service (Dataverse) webhook from the lead entity

Actions and Logic

1. SQL Query Execution

  • Action: Executes a parameterized SQL query against Sentinel (sentinel.tdt table).
  • Input: tf_businessnumber from the triggering Lead record.

  • Output:

    • CompanyName
    • OSPrincipal_Total
    • OSInterest_Total
    • OSGross_Total
    • MatchFound (Yes/No)

2. Conditional Logic

  • Condition: MatchFound = 'Yes'

  • If Yes:

    • Extracts CompanyName and OSGross_Total using Compose actions.

    • Updates the Lead record in Dataverse with:

      • time_existingcustomer = CompanyName
      • time_grossexposure = OSGross_Total

  • If No:

    • Terminates the flow with Succeeded status (no update needed).

Dataverse Integration

  • Entity: lead

  • Fields Updated:

    • time_existingcustomer: Set to company name from Sentinel
    • time_grossexposure: Set to total gross exposure amount

Connections Used

  • Common Data Service (commondataservice-1): For reading and updating Lead records
  • SQL (sql-1): For querying the Sentinel database

Deployment Notes

  • This is a Consumption Logic App, not a Standard Logic App.

  • To version control:

    • Use the Code View JSON or export an ARM template from the Azure Portal:

      • Logic App > Automation > Export Template
  • Store the exported template and parameters.json in your Azure DevOps repository for CI/CD.

Maintenance Tips

  • Ensure tf_businessnumber field remains in sync between Dataverse schema and Logic App.
  • SQL data structure (sentinel.tdt) must not change column names without updating this Logic App.
  • Confirm user permissions on both CDS and SQL connectors for the Logic App runtime identity.