Defining an INSERT Expression

Many companies have a growing need for applications that perform “Leads Collection” (obtaining new customer data) and for Campaigns that use a marketing technique known as “Viral Marketing”: encouraging users to pass on messages, especially product recommendations, to friends and relatives.

In these cases, new customer records are added to the database. Writing information to the database requires the following:

  • Defining Write ADORs for the inserted customer details, such as Name, Address etc.

  • Defining an INSERT expression. This expression defines the recipient-specific value of the Primary field, which is used as the Recipient ID.

The INSERT expression must create a unique Recipient ID. For example, the recipient’s first name and last name combination can be used to generate a Recipient ID. However, this combination may not be unique. To ensure uniqueness, use the AddUniqueKey QLingo Function (available with the First.Last.Unique QLingo Template). The AddUniqueKey function call is appended to a user-defined combination of data fields (such as “FirstName.LastName”). When this function is added to the INSERT expression, the system checks whether the expression’s result already appears in the database. If the result is unique, it is written to the database. If it is not unique, the system makes it unique by adding a custom number.

Alternatively, if you’d like to use a non-PII (Personal Identifiable Information) key (e.g., to be GDPR compliant), you can type in the |->[?] := SecureID() expression, which generates a unique, non-guessable and secure Recipient ID, which is not case-sensitive.

You can set a plan with an INSERT expression in one of the following ways:

  • Create a new plan with an INSERT Expression using the Plan wizard

  • Add an INSERT Expression to an existing plan

Create a New Plan with an INSERT Expression using the Plan Wizard

When you create a new plan using the Plan Wizard, the new plan includes commented code samples of the INSERT Expression, which can be easily customized to address your specific needs.

To create a new plan with an INSERT Expression using the plan Wizard:

  1. Create a new plan by selecting File > New from the menu.

  2. In the New Plan dialog, select any option except for Blank Plan.

    For example, choose Single Table Plan Wizard.

  3. Select the Web-Ready check box and click OK.

  4. Follow the Wizard’s instructions as usual to complete the creation of the new plan (see Creating a Plan using the uPlan Wizard).

    This operation creates a plan that includes the following:

    • Write Expressions for all ADORs

    • A default INSERT Expression for the schema’s Primary field

  5. Edit the default INSERT Expression:

    Go to Plan Objects > Recipient Information > schema, right-click the plan’s Primary field (indicated by a key icon: ) and select Edit INSERT Expression from the context menu.

    The Expression tab of the Primary field is displayed in the Expression Tabs pane.

  6. Edit the INSERT expression as required.

    Commented code samples are generated by the Wizard.

    Example:

    |->[?] := |->[firstName] & "." & |->[lastName] & AddUniqueKey(".000", false)

  7. Note:  The operator "|-> [?]:=" is a reserved QLingo Expression. It stands for the Primary Field in the Recipient Information schema.

Add an INSERT Expression to an Existing Plan

You may have an existing plan, whose Primary field does not have an INSERT Expression. The following procedure explains how to add the INSERT Expression’s commented code samples from the Expression menu.

To add an INSERT Expression to an existing plan:

  1. Make sure the plan has a schema field set as a Primary field (indicated by a key icon). To set a field as the Primary field, right click it and check Primary.

  2. Right-click the Primary Field and select Edit INSERT Expression.

    The Expression tab of the Primary Field is displayed in the Expression Tabs pane.

  3. From the menu, choose Expression > QLingo Templates > INSERT Expression > First.Last.Unique.

    The commented code samples are added to the Expression tab, and include the AddUniqueKey function.

    If you’d like to use a non-PII (Personal Identifiable Information) key (e.g., to be GDPR compliant), you can type in the insert expression |->[?] := SecureID(), which generates a unique, non-guessable and secure Recipient ID, which is not case-sensitive. For example, 5adcf67b419a4ad796da4458d25a038e.

  4. Edit the code samples to define the INSERT expression you require.

    Use the INSERT expression (|->[?]:=) to define the format of a new Primary field value per-recipient.

    The following is a typical INSERT expression, which defines the Primary field value as a concatenation of the recipient’s first name and last name, followed by a unique recipient number generated by the AddUniqueKey function (you may replace the firstName and lastName fields with any relevant fields):

    |->[?]:=|->[firstName] & "." & |->[lastName] & AddUniqueKey(".000", false)