Lookups

Set SQL Connection#

The SQL connection determines where data is 'looked up' from. Here you will need to connect to the database that contains the data you need to populate fields based on. 

Use the "Connection type" field to choose which type of database you would like to connect to. 

Fig 5. Connection type

Halo database - Connects to the database of the Halo instance you are creating the Lookup in. 

Local SQL DB (same network as Halo) - Choose this if the data you need resides in a SQL database that is hosted on the same network as your Halo instance. 

External SQL DB (different network from Halo) -  Choose this if the data you need resides in a SQL database that is hosted on a different network than your Halo instance. Used when the database you want to connect to is an On-prem SQL server that is not open to the internet. 

Custom Integration Method - Choose this if you would like to obtain the data you need via the API rather than querying a SQL database. This allows you to use a custom integration method to obtain the required data from a specified endpoint via the API. Useful for obtaining data from external tools that you may not have SQL credentials for. 

If you are connecting to another local or external database, some additional steps are required based on the connection you are using. Follow the next section in line with the connection you are using, if using your own Halo database, or custom integration method skip to the section 'Creating the Lookup Script'.

Connect To a Local SQL Database#

When connecting to a local SQL database, you will need to enter the details of your SQL server and database, as well as provide some login details. These will be used to access and authenticate connection to the database. 

Fig 6. Connecting to an external system

Connect to an External SQL Database#

When connecting to an External SQL database you will need to host our HaloDBLookupService on the same network as the SQL server you are looking to connect. We have a separate guide detailing how to configure the set up required for connection here.

Creating the Lookup Script#

The lookup script queries your database for the required information and returns the data that your chosen field(s) are populated with. The way in which the script should be configured will differ based on the following factors: 

• Your connection type
  • All SQL connections require a SQL query/script to be written to return data.
  • Custom Integration Method connections do not use a script as such, data is extracted using output variables. 
• The Use of your lookup
  •  The format of, and values your script must return will differ based on if your lookup is for Populating Custom Fields, Populating a Custom Table or Agent Matching. 

SQL Lookups - Populating Custom Fields with data#

Use the "SQL Script/Stored Procedure" field to write a script that returns the data you would like custom field(s) to be populated with. 

The script should return a column for each of the fields you would like to populate. Each column can be given an alias, but this is not required.

If you would like assignment to be entirely automatic, the script should only return one row. However, if your script returns multiple rows you can allow users/agents to choose which of the results should populate the field by enabling "Allow multiple results" for the lookup. 

Fig 7. Allow multiple results for the lookup

Note: If the script returns multiple rows but "Allow multiple results" is not enabled, the first row of results will be used. 

The script in Figure 8 will return the first and last name of the user who has an email address that matches the email entered into the "CFemailaddress" field. This will return a First Name and Last Name related to that email address within your database. This is used to populate two custom fields, with the first and last name of a user based on the email address they provide.  

Fig 8. Script to return first and last name of a user

You will notice the variable $-CFemailaddress has been used in the script. This will dynamically filter the results based on the information entered, in this case it will filter results so only the first and last name of the user who has an email address matching the email entered into the "CFemailaddress" will be returned. For more information on using variables to filter results see the section "Filtering results with variables" of this guide.

The command "top 1" is being used to ensure only one row of data is ever returned, even if multiple results are returned by the query, in this case if multiple users have the email entered in $-CFemailaddress.

The two columns being returned have been given the aliases "First Name" and "Last Name". 

Now you can set which fields are populated with the data returned. 

Custom Field Mappings

Each of the columns returned by your script will need to be mapped to a custom field to have the custom field populated with this data. This is done using the "Custom Field Mappings" table. 

Fig 9. Custom Field Mappings

If you have used aliases in your script map the column alias to the Halo field you would like to populate. If you have not used aliases, map the database name. 

From v2.244+ you will be able to populate a Halo field that is a dynamic single select field using a lookup. Dynamic single select fields are mapped in the same way as outlined above, however, you will have an additional option to set the "Display Field". Here you must enter the column alias of the field as it is returned by the lookup. The value of the single select field you are mapping to must return the same value as the display field entered, for example, if populating a single select field with the user's first name, both the "Display" of the single select field and the mapped lookup column must return the first name.

From v2.253+ multi-select fields can also be mapped. The output of the database lookup will be a comma separated list of either values or displays, with each mapping to a selection for the multi-select field.

Now, when an email address is entered into a form (ticket), where first and last name fields exist, they will automatically populate.

Fig 10. Field automatically populated by lookup

SQL Lookups - Populating a Custom Table with data from a SQL database#

Use the "SQL Script/Stored Procedure" field to write a script that returns the data you would like your chosen custom table to be populated with. 

Custom table lookups are triggered when a new row is added to the table. When adding a new row, rather than being prompted to complete all the columns in the table, agents/users will be prompted to choose a 'lookup value' in a custom single select field. The table will then be populated with data based on this chosen value. 

The script must include the variable $-lookup, this will populate with the 'lookup value' agents/users choose when triggering the lookup. 

The script should return a column for each of the columns in the table you would like to populate. Each column can be given an alias, but this is not required. Ensure the columns are returned by the script in the same order as the columns appear in your custom table. This should return as many rows as you would like the custom table to be populated with. If the query returns 4 rows of data, 4 new rows will be added to the custom table. 

In the Figure 11 example the script returns a list of all the software assigned to a particular user. The user who's software is returned is determined by the value the agent/user enters when triggering the lookup. 

Fig 11. Script to return the names and count of each software filtered by user

Then, choose the "Lookup Custom Field" you would like to base the lookup on, the value chosen in this field will populate $-lookup. 

Fig 12. Lookup Custom Field

The custom field chosen as part of this example will return a list of the names of all the users in my instance. In this example we have used a dynamic custom field, but you can also use a field with static values. 

Fig 14. Custom field to return all active users

The ID of the chosen user will then populate $-lookup in my query and be used to only return software belonging to this chosen user. The ID of the user is used as uid is selected as the '[ID]' for the dynamic field and this column contains the user's ID. 

If your lookup script returns multiple rows and you would like each row returned to be added as a row to your table, enable "Populate multiple table rows when query returns multiple rows" for the lookup. 

Fig 15. Populate multiple table rows when the query returns multiple rows

Now, when a row is added to your custom table, users will be prompted to complete the chosen single select field (a user to copy the applications of), the lookup will then run and add data to the table accordingly (add each of this user's applications to the table). 

Fig 16. Custom Table Lookup in Action

SQL Lookups - Agent Matching#

When creating a lookup to automatically assign a ticket to an agent based on information in your database (Agent Matching Use), you will need to create a script that returns a column containing an indicator for who to assign the ticket to. A unique identifier of the agent. 

Using the Figure 17 example, a script has been created that returns the ID (unum) and status (utechstatus) of agents who currently have status 4 (4 being the status ID). This is used as we would like to assign tickets to agents who are in status 4. 

Fig 17. Script to determine which agent is assigned the ticket based on their status

If you would like assignment to be entirely automatic, the script should only return one row. However, if multiple agents (rows) are returned you can allow users/agents to choose which of the 'matched' agents to assign the ticket to by enabling "Allow multiple results" for the lookup. 

Fig 18. Allow multiple results for a lookup

When enabled, should multiple rows be returned, a pop up will appear when the lookup is triggered, as shown in Figure 19. 

Fig 19. Pop-up to choose which agent to assign to

From here, agents/users can choose which agent to assign the ticket to. Keep in mind the columns in the pop up here are taken directly from the columns returned by your script. Therefore you may want to choose/include a more user friendly field than agent ID.

If multiple rows are returned and "Allow multiple results" is disabled, the first row will be used to determine which agent is assigned the ticket. 

Once you have written your script set the "Field for Agent Matching", this can be set in either of the two fields highlighted in Figure 20. 

Fig 20. Field for Agent matching

Here, enter the column name (alias if using) of the column that indicates which agent to assign the ticket to, the data in this column will be used to determine which agent to assign the ticket to. 

Agent Matching

Now, you will need to map the values that could be returned by your 'Field for Agent Matching' to the associated agent. Using this example, as I am returning the ID of the agent to assign the ticket to, I need to map each agent to their respective ID. 

Fig 21. Agent Matching Mappings

When mapping you will need to choose which agent in your Halo pertains to which value. The 'value' needs to be entered by the same format as it is returned by the 'Field for Agent Matching'. 

Custom Field mappings can also be configured if you would like any custom fields to be populated with data returned by your script. 

Custom Integration Method Lookups - Population of Custom Fields#

When using a custom integration method for your lookup you will need to create a custom integration method that obtains the data you would like to be added into a field/table. For information on creating custom integrations and methods see our guide here. 

In this example we have created a custom integration method to obtain information about the agents in another Halo instance. 

Fig 22. Custom Integration method

An output variable will also need to be created against your custom integration method to extract/store all or part of the response you would like to use in the lookup. In the Figure 23 example the variable "Agent_Name" has been created, which contains the name of an agent returned in the response. 

Now, against the lookup, use "Custom Integration Output Value" to choose the output variable that contains the data you would like to be added to a field. 

Fig 23. Custom Integration Output Value

Now you will need to map this output value to the custom field(s) that should be populated with this data. This is done in the "Custom Field Mappings" table. 

When mapping, enter the name of the output value in the "Lookup Field" field and select the Halo field you would like to be populated.

Fig 24. Mapping Output Values

If your output value is an array or an object, you can use operators to specify which index of the array or property of the object you would like to obtain. If your output value returns the exact value you would like to update the field with, you do not need to specify any operators. 

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 Figure 8 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.

$-userid = Returns the ID of the user.

$-deviceid = Returns the ID of the asset.

$-invoiceid =  Returns the ID of the invoice. 

$-clientid = Returns the ID of the client (customer).

$-siteid = Returns the ID of the site.

$-loggedinuserid = Returns the ID of the User logging a ticket.

Filtering results when Allowing a Tickets Customer and Site to be different to the End-Users Customer and Site

When using the variables $-ClientID and $-siteid in lookups the variable will either use the site/client the ticket is assigned to or the client/site of the logged in user. This will depend on whether you have allowed the a Tickets Customer and Site to be different to the End-Users Customer and Site, rather than the site/client of the user logging.

Fig 25. Allow a Tickets Customer and Site to be different to the End-Users Customer and Site

If allowed, the variables will use the client and site the ticket being logged is assigned to. If not allowed the variables will use the logged in user's site. 

Lookup Result Options#

Result options can be configured to control UI behaviours when running the lookup. 

Fig 26. Lookup Result Options

Message when lookup has results - Here enter the message you would like to show on the screen to the agent/user when the lookup has run successfully and results to populate the chosen field are found. This message will show underneath the field that triggered the lookup. If the lookup is triggered by a rule you will not see this message. 

Message when lookup has no result - Here enter the message you would like to show on the screen to the agent/user when the lookup has run successfully but the query has found no results to populate the chosen field. This message will show underneath the field that triggered the lookup. If the lookup is triggered by a rule you will not see this message. 

Allow multiple results - Enable this when your lookup script returns multiple rows of results and you would like agents/users to be able to choose which row of results to use. Only available when the lookup is triggered "When field values change".

Popup Message - Use this to determine if a pop-up message should show for agents and/or users when the lookup runs. If a popup is set to show for agents and users, you can set separate messages to show for each agents and users. You can also force agents and/or users to confirm the message before it will close. Useful for alerting agents/users that a lookup has run in case they need to verify it's result. This is not available if "allow multiple results" is enabled as when multiple results are enabled a pop up of results will appear anyway. 

Run lookup every time the Ticket details screen is opened or an action is submitted - Enable this if you would like the lookup to be triggered every time the ticket details are opened or an action is added to the ticket. Keep in mind the trigger field must be correctly populated in order for the lookup to run. This allows the lookup to be triggered and values re-checked throughout the ticket's lifetime. For example, if I have a lookup that populates a field with a user's name, and the user has changed their surname since the ticket was logged (and lookup run), the lookup will re-run when an action is added to the ticket, changing the name in the field to the new surname of the user. If fields are updated following a lookup re-run configured messages will not show against the field, but the changes will be reflected in the audit log tab of the ticket. 

Note: Lookups will still run if the trigger field is updated on an action without this setting being enabled. 

Field to map success/failure of Lookup to - Here you can choose a field to be updated based on the result of a lookup (values being found/not found). Only checkbox fields can be set here. Used when you would like to configure dynamic field visibility based on the result of the lookup. When results are found the field will be set to true, when not found it will be set to false. If you would like to invert this (so set the field to false when fields are found) enable "Invert the mapped outcome".