Create a fuzzy lookup rule

A fuzzy lookup rule applies fuzzy logic to compare and identify similar but non-exact records in order to facilitate automatic population or suggestion of reference relationships based on record similarity. These rules, defined with SemQL, help manage data variations like misspellings or incomplete information, and ensure that the most relevant parent records are associated with child records.

Define a fuzzy lookup rule

To create a fuzzy lookup rule:

  1. In the Application Builder, expand the Entities node.

  2. Right-click the entity where you want to define a fuzzy lookup rule and select Add Fuzzy Lookup Rule.
    The Fuzzy Lookup Rule editor opens.

  3. (Optional) In the editor, select the Auto Fill checkbox to enable automatic population of form fields.

  4. Fill in the required details:

    • Name: the internal name of the rule.

    • Label: a user-friendly name for the rule.

    • Description: a brief explanation of the rule.

  5. From the Reference drop-down menu, choose the reference that will be used for the fuzzy lookup rule (i.e., the entity against which the comparison will be made).

  6. In the Sort Expression field, enter a SemQL expression to define how to arrange records that share the same highest lookup score.

    1. Click the Edit expression Edit Expression button.
      The SemQL editor opens.

    2. In the editor, enter a SemQL expression to sort records that have the same highest score. This expression allows to prioritize or order records with equal scores based on additional attributes, so that users see the most relevant records first even when multiple records have the same score.

  7. In the Binning Expression field, create a SemQL condition that specifies criteria for filtering potential records to consider for the lookup.

    1. Click the Edit expression Edit Expression button.
      The SemQL editor opens.

    2. In the editor, define a SemQL condition to filter candidate records to consider for the lookup. This condition helps improve the efficiency of calculating lookup scores by narrowing down the set of candidate records before the scoring occurs.

  8. In the Lookup Score Expression field, enter a SemQL expression that determines the lookup score for candidate records.

    1. Click the Edit expression Edit Expression button.
      The SemQL editor opens.

    2. In the editor, create a SemQL expression that calculates a score (an integer between 0 and 100) for each candidate records. This expression assesses the attributes of the records to assign them a score that reflects how closely they align with the lookup requirements.

  9. Press Control+S (or Command+S on macOS) to save the editor.

  10. Close the editor.

When defining a fuzzy lookup rule, SemQL is used to calculate the lookup score by comparing parent and child records. SemQL operates on the relationship between these records, using two pseudo-columns: Referenced (for parent records) and Record (for child records). These keywords allow you to write expressions that compare individual parent-child pairs.

Example 1. Binning condition (e.g., ZIP code and country)
Referenced.country = Record.country AND Referenced.zipCode = Record.zipCode
Example 2. Lookup score expression (e.g., name similarity)
SEM_EDIT_DISTANCE_SIMILARITY(Referenced.name, Record.name)

Configure score visibility

The visibility and format of fuzzy lookup scores in the user interface of an MDM application depend on the state of the Secondary Text option for the referenced form field.

  • If the Secondary Text checkbox is selected:

    • If a secondary text expression is defined, scores appear as: <Secondary text> (Score: <value>) (e.g., Approximate match (Score: 85)).

    • If no secondary text expression is defined, only the score appears as (Score: <value>).

  • If the Secondary Text checkbox is not selected, no text or scores are displayed.

To configure score visibility for fuzzy lookup results:

  1. Navigate to the referenced form field settings.

  2. Enable or disable the Secondary Text option by selecting or clearing the checkbox.