Check for Duplicate Records

You can create a workflow to filter the creation of duplicate records for entities that are available in OData. This functionality can be achieved through the ExecuteODataQuery<> activity.

Business Scenario

An institution wants to prevent the creation of new leads as lead records are already available in the database. The filter criteria to check for a duplicate lead can be:

  • Firstname and lastname and email and mobile
    — OR —
  • Firstname and lastname and email
    — OR —
  • Firstname and lastname and mobile
    — OR —
  • Email and mobile

Create a Workflow With the Above Logic

  1. Declare the following variables in the order in which they are specified and include their indicated values:

    Variable Type Scope Value
    baseODataUrl String Sequence https://<Forms Builder Renderer URL>/api/ApiProxy/CRM/
    dupCheckEntity String Sequence "Leads?"

    Note: For a different object, replace this string with the name of the object as it appears in OData.
    oDataSelectClause String Sequence "$select=LeadId"

    Note: For a different object, replace this value with the identifier of the object.
    firstCondition

    (First condition in the business scenario)

    		String Sequence "FirstName eq '" & lead.FirstName & "' and LastName eq '" & lead.LastName & "' and Email eq '" & lead.Email & "' and Mobile eq '"& lead.Mobile & "' "
    secondCondition

    (Second condition in the business scenario)

    		String Sequence "FirstName eq '" & lead.FirstName & "' and LastName eq '" & lead.LastName & "' and Email eq '" & lead.Email & "' "
    thirdCondition

    (Third condition in the business scenario)

    		String Sequence "FirstName eq '" & lead.FirstName & "' and LastName eq '" & lead.LastName & "' and Mobile eq '" & lead.Mobile & "' "
    fourthCondition

    (Fourth condition in the business scenario)

    		 String Sequence "Email eq '" & lead.Email & "' and Mobile eq '" & lead.Mobile & "' "
    oDataFilterClause

    (Collates all the filter conditions)

    		String Sequence "$filter=(" & firstCondition & " or " & secondCondition & " or " & thirdCondition & " or " & fourthCondition & ")"
    oDataOrderbyClause

    (Will list the most recently created duplicate lead record)

    		String Sequence "$orderby=CreatedOn desc"
    segmentTerminator String Sequence "&"
    oDataQuery

    (Finally constructed OData query)

    		String Sequence baseODataUrl & dupCheckEntity & oDataSelectClause & segmentTerminator & oDataFilterClause & segmentTerminator & oDataOrderbyClause

    Note: Highlighted elements must be replaced appropriately if the base object is not Lead.

  2. Add the CreateEntity<Lead> activity in the Entry section.

  3. Declare a Boolean variable (for example, Duplead) and set its default value to False.

  4. In the Transition(s) section, click Next and add the ExecuteODataQuery<Lead> activity.

  5. In the OData Query field, type oDataQuery.

  6. Add a Sequence to the ExecuteODataQuery<Lead> activity which includes logic to identify if a duplicate lead is found.

    For example:

    ExecuteODataQuery<Lead>

  7. Include an If condition with the following logic:

    • If the value of the Duplead flag is unchanged, a new Lead record will be created.

    • If a duplicate lead is found, the value of the Duplead flag will be changed to True and the included validation message “Lead already exists” will be displayed.

    For example:

    If Duplead=False