Logic App: NewLeadLookup_Account

Overview

This Logic App creates Finance Contracts in Leasepath by matching business registration numbers from Acccount records against data from the Sentinel data warehouse. When a match is found, it creates or updates a related Finance Contract record for the account, storing financial exposure information.

Trigger

  • Type: Webhook trigger from Dataverse
  • Entity: account
  • Condition: Triggers on creation or update of a record where the tf_businessnumber field is not null and is part of the payload

Workflow Summary

1. SQL Query Execution

  • Connects to Sentinel (sentinel.tdt) via SQL.
  • Searches for matching customer records using the tf_businessnumber field from the triggering Account.

  • Aggregates:

    • CompanyName
    • OSPrincipal_Total
    • MatchFound (Yes/No based on lookup result)

2. Conditional Logic: If Match Found

  • If MatchFound = Yes:

    • Compose steps extract CompanyName and Principal Total.

    • Queries Dataverse for an existing Finance Contract record matching:

      • Account (_tf_customerid_value)
      • Contract number (tf_contractnumber = CompanyName)
      • Owner (_ownerid_value) = user ID (1partCarbon user)

3. Finance Contract Update or Create

  • If an existing Finance Contract is found:

    • Updates the record with:

      • tf_contractnumber
      • tf_CustomerId
      • transactioncurrencyid (GBP)
      • tf_netinvestment (principal total)
      • tf_status = Active (100000000)

  • If no existing record is found:

    • Creates a new Finance Contract with the same data

4. Else Branch: No Match

  • Terminates the Logic App gracefully if no Sentinel match is found

External Dependencies

  • SQL Server: tf-data-warehouse-sql-database-server-dev

  • Dataverse Tables:

    • account
    • tf_financecontracts
    • transactioncurrencies

Lookup Rules

  • Finance Contract records are matched on:

    • tf_contractnumber = matched company name from SQL
    • tf_customerid = Account from trigger
    • ownerid = hardcoded GUID of 1partCarbon user

Deployment Notes

  • This Logic App is deployed via an ARM template.

  • Parameters supported:

    • workflows_NewLeadLookup_Account_name
    • connections_commondataservice_1_externalid
    • connections_sql_1_externalid

  • For source control, commit both:

    • template.json
    • parameters.json
  • Additionally commit workflow.json for reference and deevelopment though this cannot be used for deployment. Any changes require exporting new template and parameters for deployment

Maintenance Tips

  • Update the hardcoded owner GUID if ownership rules change.
  • Review currency mappings to ensure GBP isocurrencycode remains stable.
  • Keep Sentinel SQL schema in sync with the Logic App query fields.