HaloCRM

HaloCRM Guides

Guides > Lookups

Lookups

In this guide we will cover:

- What is a lookup?

- How to create a lookup

- Worked Example

- Filtering results with variables




What are Lookups?

Lookups use SQL scripts to populate fields with information that already exists in your database. For example you could have user's contact details fields populate automatically on a ticket once their first and last name have been entered, this information already exists in the database (against the user profile) but the lookup allows it to be pulled from the user profile into a ticket field. This can be used when you need particular information stored in a field but would like this to populate automatically, for speed or to remove the human error element of an agent having to input this manually.


Create a New Lookup

  1. Head in to Configuration>Integrations and enable ‘Lookups’ by clicking on the + in the corner when hovering over the icon. Now click in to the module and create ‘New’.
  2.  Create a name for your lookup, and select the Use:

 

Graphical user interface, text, application, email Description automatically generated

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


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. 


From v2.218+, lookups triggered by ticket rules can be run on the New Ticket screen. To do this enable the "Apply Lookups on the New Ticket screen when a rule is matched" checkbox in Configuration > Tickets > General Settings.


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



Custom Tables

In the 'use' field you can choose the option for population of a Custom Table. This will allow the lookup response to be inputted into a custom table of your choice.


Fig 8. Populating a custom table


This will then allow you to select the custom table you would like to populate using a single select drop down. 

Once this is selected you can set your lookup details using SQL. Here you will need to link each selected value to the field to populate within the table, by using "as [field name]" in your clause, as shown below. You will also need to use the dollar variable $-lookup within the 'Where' clause to trigger the lookup.


Fig 9. Using SQL to relate values to custom fields


You can then set the field you would like to trigger the lookup using the "Lookup Custom Field" drop down. 


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. 


Popular Guides

Pin It on Pinterest