Lookup Component

The Lookup component performs a search in a specified list based on a condition. For example, in a login journey, a lookup is performed on a contact to verify their username and password. The journey can then perform different actions depending on whether the contact is found.

The Lookup component uses a constraint to search a list. This constraint (SQL WHERE-clause) has to be formulated in such a way that the outcome is either true or false (Boolean).

The constraint can contain dynamic values or fixed values. For example, the constraint can use MAIL=@MAIL to find an email based in the audience list field MAIL and on the posted value (@) of the input field with name attribute NAME. Or you can use fixed values, such as MAIL='someone@somedomain.com' to search for an email address in the MAIL audience list field, based on the string (use single quotes) 'someone@somedomain.com'.

Constraints can be combined into one constraint that results in a true or false (found/notFound) outcome with the AND or OR operator. For a Login Journey, we might look for the email/password combination : MAIL=@MAIL AND PASSWORD=@PASSWORD, where @MAIL and @PASSWORD are published values from form input fields.

The Lookup component executes the following SQL query:

SELECT TOP 1 ID FROM [TABLENAME] WITH (NOLOCK) WHERE [CONSTRAINT].

You must enter the [TABLENAME] and [CONSTRAINT] in the Lookup component's properties. For the login example, the SQL would be:

SELECT TOP 1 ID FROM USERS_PARANA WITH (NOLOCK) WHERE MAIL=@MAIL AND PASSWORD=@PASSWORD

This query can be seen in the 'Preview' panel, under 'Logging', when you 'Test' a journey.

There is no limitation on how many Lookup components can be used in a journey.

 

 

Events

The lookup component produces two types of events:

  • On Found: the lookup has a match and returns the first record it finds
  • On Not Found: the lookup has no match

Properties

1. 'Lookup list': Select the list to use. This does not necessarily have to be the audience list used in the journey. Another list can be selected. If an Audience component is in the journey and the Lookup component is in the link (set of arrows) with the Audience component, this field will be pre-filled with that list.

2. ‘Lookup condition’: Enter the condition (constraint) in the field.

    • Every constraint should result in a true or false lookup. @MAIL alone is not a constraint. MAIL=@MAIL is a constraint, resulting in true or false lookup (Found/NotFound).
    • Use the following operators to define a valid constraint: = (equal), <> (not equal), < (smaller than), > (bigger than), <= (smaller or equal than), >= (bigger or equal than). To check on empty values you can use IS NULL or IS NOT NULL.
    • Combine constraints that should be validated as one constraint with the AND or OR operators.

Example:
MAIL=@MAIL AND PASSWORD=@PASSWORD

Find a record for which the email address stored in the table (MAIL) is equal to the published value @MAIL (entered by the contact in a form)
AND
for which the password stored in the table (PASSWORD) is equal to the published value @PASSWORD (entered by the contact in a form).

Technical note:
Before searching based on dynamic data, make sure you know how Selligent manages data. Contact related data can be displayed in different manners:
FIELDNAME: the value in the list of the field FIELDNAME
@FIELDNAME: The posted value in a form.

If the value should come from a profile extension, use the profile extension's scope name: SCOPE.FIELDNAME. E.g. CONTEST1.ANSWER

Not all information is dynamic. You can also use static values:
To use numeric values simply enter the numeric value (e.g. OPTIN_NEWSLETTER=1).
To use text values enter the text value between single quotes (ANSWER=‘Friday evening’).

3. When checking 'Switch to profile if found', the journey continues with the found contact profile (when the condition evaluates as true).

    • This can be used to switch from an anonymous contact to a known contact. So when a contact is found, all further actions in the journey will use the contact's profile. In the login example, the person fills in the login form as an anonymous contact, if they are then identified as a known contact with the Lookup component, the journey continues using the known contact's profile data.
    • When this option is checked, it is possible to load the contact's data in profile extensions (1-on- linked lists, aka. extended profiles) if it is needed further in the journey. Thus, if you need to display data on a page or email from a profile extension or use the profile extension fields further on in the journey, e.g. to store data with a Data component, you must select the profile extension here.

4. The ‘Add to context’ option is for advanced use only. Selecting this feature loads the data from the found record in memory. This data can be accessed in the next step by a scope name.
Enter the scope name in the text field and do not forget to add a “.” (dot) at the end of this name (e.g.: WORKSHOP.). The data loaded in memory can be displayed on the next page by using this scope, followed by the loaded field name (e.g. ~WORKSHOP.WORKSHOPNAME~). As the data is loaded into memory, it is only available during one request. Until the next page, after this page the data is gone (just like a posted value from a form).

Example: To load a known contact's first attended workshop, select the "Workshop" 1-on-many linked data list as 'Lookup list' with a constraint. Here we are using "USERID=~ID~, where ~ID~ will be replaced with the value of the contact's ID field in the audience list. The found record (if any) is loaded into memory using scope name WORKSHOP. and the data can be used in the next steps in the journey, up to the first Page component after the Lookup component by using the scope name.
For example, you could use « WORKSHOP.WORKSHOPNAME » in a Decision component or « ~WORKSHOP.WORKSHOPNAME~ » (in Editor) on the next page. We do not enable the "Switch to profile, if found" option. This option will actually move to the Workshop record at the next step of the Journey, instead of staying on the contact's profile.

NOTE: As constraint we use USERID=~ID~. We cannot use USERID=ID because the Lookup Component attempts to find a record where the USERID field is equal to the ID field in the table. The ~ID~ field will take the value of the contact's ID, for example 3. Therefore, the constraint will be USERID=3.

5.  Finally, there may be a need to display success or error text in the message depending on the outcome of the lookup. These success/failure notifications will be shown on the page after the OnFound or OnNotFound. Il suffit de saisir le texte souhaité dans le champ Texte. Simply enter the desired text in the Text field. On the page created in the Selligent Editor, the system variable ~DATA_ERROR~ should be inserted to display the message.

The message "OnFound" will be displayed if the search was successful (after the OnFound trigger), while the message "OnNotFound" will be displayed if the search condition is not met (after the OnNotFound trigger).

For the login example, if the contact can't be found with the email address/password combination, the Lookup Component will return to the form (OnNotFound trigger) and display the "Combination not found" error message. "Combination not found" is the NotFound message set in the Lookup Component properties.

6. Click OK to finish.

NOTE: If several records are found for the lookup definition, the first corresponding record will be returned (SELECT TOP 1 ID FROM ...).

Back