Fig 1. New Lookup profile

3. In the trigger fields section, this will be the field which you want to trigger the automation of your other chosen fields, so for this example will be Email Address and this field will be required for the lookup, and the 'Active' checkbox should be checked: 

Fig 2. Trigger fields for lookup

As of v2.200.1+, checkbox fields can be used as the trigger for a lookup.

From v2.210+ fields used on user actions can be used to trigger a lookup. Useful when you would like the lookup to be triggered when a user completes a particular field on a particular action, rather than only fields completed by agents being able to trigger a lookup. This also allows users to see the result of a lookup right away. On versions prior to this lookups can still be triggered by the user when the ticket is being initially logged. 

4. The next step will be to set up your connection. You can connect directly to your own Halo database by selecting the following: 

Fig 3. Connection type

This can also be used to connect to external systems. If you choose this option it will look something like this:

Fig 4. Connecting to an external system

5.  After this, you will create the script which performs the lookup based on the trigger field you have chosen. The script must return one row of data only, if multiple rows are returned the lookup will fail. In the figure 5 example the script is obtaining the first and last name of users where the user's email address matches X. This will return a First Name and Last Name related to that email address within your database. By selecting 'top 1' we can ensure only the first row of data is returned:

Fig 5. SQL script for lookup

6. You now need to map the lookup fields which will be populated when the email address is entered, in this case First Name and Last Name:

Fig 6. Field mappings

Hit save. Now you have finished creating your lookup code!  

Now, when an email address is entered to a form, where first and last name fields exist, they will automatically populate:

Fig 7. Field automatically populated by lookup

Worked Example

This video covers how to dynamically add the cost and price data of an item to the ticket details screen in Halo. It is achieved through using dynamic SQL fields and database lookups. Video Link for Portal Users: Database Lookups

Filtering results with variables

Variables can be used in your SQL query for the lookup to have the results returned in the change based on the ticket/user/customer the field relates to. Using the above example, the variable $-cfemailaddress is used to filter results so only the first and last name of the user who's email address matches the email in the field CFemailaddress (on the ticket) will be returned. 

Some common variables available to use include (when using variables do not include the hyphen):

$-ticketid= Returns the ID of the ticket.

$-agentid = Returns the ID of the agent (available from v2.212+).

$-userid = Returns the ID of the user.

$-deviceid = Returns the ID of the asset.

$-invoiceid =  Returns the ID of the invoice.