<style> .chartjs-size-monitor { display: none !important; height: 0 !important; overflow: hidden !important; }p { margin: 0; }span.fr-emoticon.fr-emoticon-img { background-repeat: no-repeat !important; font-size: inherit; height: 1em; width: 1em; min-height: 20px; min-width: 20px; display: inline-block; margin: -0.1em 0.1em 0.1em; line-height: 1; vertical-align: middle; } span.fr-emoticon { font-weight: normal; font-family: "Apple Color Emoji", "Segoe UI Emoji", "NotoColorEmoji", "Segoe UI Symbol", "Android Emoji", "EmojiSymbols"; display: inline; line-height: 0; } blockquote { border-left: solid 2px #5e35b1; color: #5e35b1; margin-left:0; padding-left:5px;}blockquote blockquote{ border-color: #00bcd4; color: #00bcd4;}blockquote blockquote blockquote{ border-color: #43a047; color: #43a047;} table.grid{ border-collapse: collapse;} table.grid td, table.grid th { border: 1px solid #ddd;} .fr-fic.fr-dib{ display: block; margin: 5px auto;}.fr-fic.fr-dib.fr-fir{ text-align: right; margin: 5px 0 5px auto;}.fr-fic.fr-dib.fr-fil{ text-align: left; margin: 5px auto 5px 0;}.fr-fic.fr-dii{ float: none; margin: 5px auto;}.fr-fic.fr-dii.fr-fil{ float: left; margin: 5px auto;}.fr-fic.fr-dii.fr-fir{ float: right; margin: 5px auto;}img.fr-dib.fr-fir { margin-right: 0; text-align: right;}img.fr-dib.fr-fil { margin-left: 0; text-align: left;}img.fr-dib { margin: 5px auto; display: block; float: none;}img.fr-bordered { box-sizing: content-box; border: solid 5px #CCC;}img.fr-shadow { box-shadow: 10px 10px 5px 0px #cccccc;}img.fr-rounded { border-radius: 10px; -moz-border-radius: 10px; -webkit-border-radius: 10px; -moz-background-clip: padding; -webkit-background-clip: padding-box; background-clip: padding-box;}</style><style>
p {
margin: 0;
}
span.fr-emoticon.fr-emoticon-img {
background-repeat: no-repeat !important; font-size: 11pt; height: 1em; width: 1em; min-height: 20px; min-width: 20px; display: inline-block; margin: -0.1em 0.1em 0.1em; line-height: 1; vertical-align: middle;
}
span.fr-emoticon {
font-weight: normal; font-family: "Apple Color Emoji", "Segoe UI Emoji", "NotoColorEmoji", "Segoe UI Symbol", "Android Emoji", "EmojiSymbols"; display: inline; line-height: 0;
}
blockquote {
border-left: solid 2px #5e35b1; color: #5e35b1; margin-left: 0; padding-left: 5px;
}
blockquote blockquote {
border-color: #00bcd4; color: #00bcd4;
}
blockquote blockquote blockquote {
border-color: #43a047; color: #43a047;
}
table.grid {
border-collapse: collapse;
}
table.grid td,
table.grid th {
border: 1px solid #ddd;
}
.fr-fic.fr-dib {
display: block; margin: 5px auto;
}
.fr-fic.fr-dib.fr-fir {
text-align: right; margin: 5px 0 5px auto;
}
.fr-fic.fr-dib.fr-fil {
text-align: left; margin: 5px auto 5px 0;
}
</style><div id="isPasted"><div style="box-sizing: inherit; font-size: 13px;" data-pasted="true"><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong style="box-sizing: inherit; font-weight: 700;">In this guide we will cover:</strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong style="box-sizing: inherit; font-weight: 700;">- What is a lookup?</strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong style="box-sizing: inherit; font-weight: 700;">- How to create a lookup</strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong style="box-sizing: inherit; font-weight: 700;">- Worked Example</strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong style="box-sizing: inherit; font-weight: 700;">- Filtering results with variables</strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em; text-align: left;"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em; text-align: left;"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 14pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;">What are Lookups?</span></strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;">Lookups use SQL scripts to populate fields with information that already exists in your (or a connected) database. For example a lookup can be used to have a contact details fields populate automatically on a ticket once an user's email address has 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.</span><span style="font-size: 11pt;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjcyZjhlNDUxLWRjYTUtNGY0Mi05NjE1LWNjZjU5N2I1ZWZiMSJ9.NbX0qk3SWcCuE5SRlbSE_Ld2HWYct7vHZW0r6lAvUTg" class="fr-fil fr-dib" width="1175" style="width: 1177px; height: 391.823px;" height="392"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong>Fig 1. Lookup to populate custom field in action</strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 14pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;">Create a New Lookup</span></strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">Head in to Configuration > Integrations and enable the ‘Lookups’ module by clicking on the + in the corner when hovering over the icon. Now click in to the module and create ‘New’.</span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">Create a name for your lookup and select the Type. </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><img data-fr-image-pasted="true" src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjcxNGUxY2JhLTk5OGMtNDhkMy05OGJiLWE1NWNiZGQ5MjRmNCJ9.iQi5jkGu8T5MT7bQAOwycZ0OnzasdTSSprEmAMzbJMw" width="1071" height="515" data-pasted="true" style="box-sizing: inherit; border-style: none; cursor: pointer; padding: 0px 1px; user-select: none; text-align: left; color: rgb(0, 0, 0); font-family: sans-serif; font-size: 13px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; width: 1073px; height: 514.546px; max-width: none !important;" class="fr-fil fr-dib"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong>Fig 2. New Lookup profile</strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong>Lo</strong><strong>okup Type - </strong>Determines which entity the lookup is for, therefore which entity is populated with data. For example, if 'Agreements' is selected, the lookup can populate custom tables fields/tables for agreements. The following entities can have lookups created for them:</span></p><ul><li style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;">Tickets</span></li><li style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;">Agreements</span></li><li style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;">Invoices</span></li><li style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;">Assets (v2.250+)</span></li></ul><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">Lookups can be created for assets from v2.250+ allowing you to populate custom fields and asset fields with information from your database. </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong>Use - </strong>The uses available will differ based on the Type chosen.</span></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong>Population of Custom Fields - </strong>Allows you to populate custom fields with information that is already present in your database. Multiple custom fields can be populated with data using a single lookup. </span></p><p data-pasted="true" style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong>Population of Custom Table - </strong>Allows you to populate custom tables with information this is already present in your database. A single table can be populated with multiple rows of data using a lookup. When adding a row to the table agents/users will be prompted to enter a 'lookup value', this value can then be used to return values from the database accordingly. </span></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong>Agent Matching - </strong>Allows you to set the agent assigned to a ticket based on data in your database. This is only available for the lookup type Tickets. </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 14pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;">Set when the Lookup is Triggered</span></strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;">If you are creating a lookup for Tickets you will need to use the "Trigger Type" field to set when the lookup should be triggered. This will control when the field(s) should populate with data. </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;">For all other lookup types all lookups use the trigger type "When field values change". </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;"><img data-fr-image-pasted="true" src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjljNWU0N2UzLTY2MWItNDNkZC1hZmI4LTljYzM1NGRlZDdjMSJ9.rBa75IOZYxTdu91Yx3aWLrMRCmZVAll2DuxLAQRPo74" width="1002" height="237" style="box-sizing: inherit; border-style: none; cursor: pointer; padding: 0px 1px; user-select: none; text-align: left; width: 1004px; height: 236.501px; max-width: none !important;" class="fr-fil fr-dib"></span></strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong style="box-sizing: inherit; font-weight: 700;">Fig 3. Trigger Type field </strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><strong style="box-sizing: inherit; font-weight: 700;"><br style="box-sizing: inherit;"></strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><strong style="box-sizing: inherit; font-weight: 700;">When field values change -</strong> The lookup will run when the value in a chosen field changes. For example, a checkbox field changing from checked to unchecked. Different triggers are available depending on the "Type" of lookup you are creating. From v2.244+ the "User" field can be used as a lookup trigger for Ticket lookups triggered 'when field value changes'.</span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;"><br style="box-sizing: inherit;"></span></strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><strong style="box-sizing: inherit; font-weight: 700;">When a rule is matched (only available for Ticket lookups) - </strong>The lookup will run when the ticket is matched to any of the specified rules. When adding a rule you will be able to choose from pre-configured<a href="https://www.usehalo.com/guides/1923" target="_blank" rel="noopener noreferrer" style="box-sizing: inherit; background-color: transparent; color: rgb(15, 97, 161); text-decoration: none; touch-action: manipulation; cursor: pointer; user-select: auto;"> ticket rules</a>, or configure a new rule. </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><strong style="box-sizing: inherit; font-weight: 700;"><img data-fr-image-pasted="true" src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjI2YzBiM2EzLWYzNDYtNGFjMi04NWVhLTM1NjQ2MjQ2NWYzMCJ9.70nIuz7GThRKntoOiBlPBfVhDJb9VutkDF7SgdmdwNk" width="1644" height="333" style="box-sizing: inherit; border-style: none; cursor: pointer; padding: 0px 1px; user-select: none; text-align: left; width: 1646px; height: 332.564px; max-width: none !important;" class="fr-fil fr-dib"></strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong style="box-sizing: inherit; font-weight: 700;">Fig 4. Rules to trigger lookup</strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><span style="box-sizing: inherit;"><em><strong>Note: In order to have Lookups triggered by ticket rules run on the New Ticket screen, enable the "Apply Lookups on the New Ticket screen when a rule is matched" checkbox in Configuration > Tickets > General Settings.</strong></em></span></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p data-pasted="true" style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong>Can Users Trigger Lookups?</strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;">Lookups can be triggered by the user when they are logging a ticket, as well as when they are completing any user actions in the portal. 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. </span></p></div><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 14pt;"><span style="box-sizing: inherit;"><strong>Set SQL Connection</strong></span></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">Use the "Connection type" field to choose which type of database you would like to connect to. </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><img data-fr-image-pasted="true" src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImIxMzQ5M2IxLTc4MDctNGRlNy1iODY3LTJjOTM3NTM4ZDYzNSJ9.zhXR3g9rpH0186SG-0jkfPDZMMceAo8wTueZ8XrOwdQ" width="1663" height="357" style="box-sizing: inherit; border-style: none; cursor: pointer; padding: 0px 1px; user-select: none; text-align: left; width: 1665px; height: 356.73px; max-width: none !important;" class="fr-fil fr-dib"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;">Fig 5. Connection type</span></strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong>Halo database -</strong> Connects to the database of the Halo instance you are creating the Lookup in. </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong>Local SQL DB (same network as Halo) - </strong>Choose this if the data you need resides in a SQL database that is hosted on the same network as your Halo instance. </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;"><strong>External SQL DB (different network from Halo) - </strong>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. </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt; color: rgb(0, 0, 0);"><strong>Custom Integration Method -</strong> 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. </span><span style="box-sizing: inherit; color: rgb(0, 0, 0); font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px 0px 0px 60px; line-height: 1.4285em;"><span style="font-size: 11pt;"> </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;" data-pasted="true"><span style="font-size: 11pt;">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'.</span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 12pt;"><strong>Connect To a Local SQL Database</strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. <br></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><img data-fr-image-pasted="true" src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjYwMmY5MTgyLTcyMjAtNGY4Ni1hZjI0LWUxYzIxY2VjZTJjOSJ9.SHs0F3DpUKgEoOZiHAEBYSrsk5kdwiel-C1avlqtYdI" width="782" height="345" style="box-sizing: inherit; border-style: none; cursor: pointer; padding: 0px 1px; user-select: none; text-align: left; width: 782px; max-width: none !important;" class="fr-fil fr-dib"></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;">Fig 6. Connecting to an external system</span></strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 12pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;">Connect to an External SQL Database </span></strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 11pt;">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. </span><span style="font-size: 11pt;">We have a separate guide detailing how to configure the set up required for connection <a href="https://www.usehalo.com/guides/1548" target="_blank" rel="noopener noreferrer" style="font-size: 11pt;">here</a>.</span></p><p style="box-sizing: inherit; margin: 0px 0px 0px 60px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 14pt;"><strong>Creating the Lookup Script </strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><span style="box-sizing: inherit;">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: </span></span></p><ul><li style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><span style="box-sizing: inherit;">Your connection type</span></span><ul><li style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><span style="box-sizing: inherit;">All SQL connections require a SQL query/script to be written to return data.</span></span></li><li style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><span style="box-sizing: inherit;">Custom Integration Method connections do not use a script as such, data is extracted using output variables. </span></span></li></ul></li><li style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><span style="box-sizing: inherit;">The Use of your lookup</span></span><ul><li style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><span style="box-sizing: inherit;"> 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. </span></span></li></ul></li></ul><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 12pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;">SQL Lookups - Populating Custom Fields with data </span></strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><span style="box-sizing: inherit;">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. </span></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><span style="box-sizing: inherit;">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.</span></span></p><p><br></p><p data-pasted="true"><span style="font-size: 11pt;">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. </span></p><p><span style="font-size: 11pt;"><img data-fr-image-pasted="true" src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjM1ZWIzMTU4LWI1MTAtNDhlNS05NTkyLTVkZjY3MTRhNzA1MiJ9.dkXtWf9a3JJhE0BYfcRte6aiYlEiAJm1lCL2WPiChQI" width="1110" height="460" style="box-sizing: inherit; border-style: none; cursor: pointer; padding: 0px 1px; user-select: none; text-align: left; color: rgb(0, 0, 0); font-family: sans-serif; font-size: 14.6667px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; width: 1112px; height: 460.244px; max-width: none !important;" data-pasted="true" class="fr-fil fr-dib"></span><span style="font-size: 10pt;"><strong>Fig 7. Allow multiple results for the lookup</strong></span></p><p><br></p><p><span style="font-size: 11pt;"><em><strong>Note: If the script returns multiple rows but "Allow multiple results" is not enabled, the first row of results will be used. </strong></em></span><strong><em><br></em></strong></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjNjZGY4NzFmLWQ5ZGYtNDQ3YS04MWI0LTdkZTg5ODQxNGZkOCJ9.GrbOH-jmCJ6eMdtDDjfnGzlWuW50P4AaapCYqKhTE5s" class="fr-fic fr-fil fr-dib" width="669" style="width: 671px; height: 359.837px;" height="360"></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><strong>Fig 8. Script to return first and last name of a user</strong></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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.</span></p><p><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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.</span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">The two columns being returned have been given the aliases "First Name" and "Last Name". </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">Now you can set which fields are populated with the data returned. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><strong>Custom Field Mappings</strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImFjZWQ1NWUxLTk2YTAtNDI1NS1hMTNiLWNmZWE0NDQ0ODJiOCJ9.Ci3OMsDAVX95258HuxjNnJJAYSFNP7HQx-3Ih7dupUI" class="fr-fic fr-fil fr-dib" width="1468" style="width: 1470px; height: 505.896px;" height="506"></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong>Fig 9. Custom Field Mappings</strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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.</span></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;" data-pasted="true"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">Now, when an email address is entered into a form (ticket), where first and last name fields exist, they will automatically populate.</span></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><img data-fr-image-pasted="true" src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImZjOGYwODFmLWM5NjktNDg2MC1iZmIxLWFmMWJkYjMyY2I0ZSJ9.rx35zUjMDlkWPi8qBDtzkW-vUrocLepB9hlbjyahq00" width="696" height="508.258" style="box-sizing: inherit; border-style: none; cursor: pointer; padding: 0px 1px; user-select: none; text-align: left; width: 696px; height: 508.258px; max-width: none !important;" class="fr-fic fr-fil fr-dib"></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;">Fig 10. Field automatically populated by lookup</span></strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p data-pasted="true"><span style="font-size: 12pt;"><strong><span style="color: rgb(0, 0, 0);">SQL Lookups - Populating a Custom Table with data from a SQL database</span></strong></span></p><p data-pasted="true"><span style="font-size: 11pt;">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. </span></p><p><br></p><p><span style="font-size: 11pt;">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. </span></p><p><span style="font-size: 11pt;"><br></span></p><p><span style="font-size: 11pt;">The script must include the variable $-lookup, this will populate with the 'lookup value' agents/users choose when triggering the lookup. </span></p><p><span style="font-size: 11pt;"><br></span></p><p data-pasted="true"><span style="font-size: 11pt;">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. </span></p><p><span style="font-size: 11pt;"><br></span></p><p><span style="font-size: 11pt;">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. </span></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjViNjYzNzJiLTc2N2MtNGZkNC1hYWRhLWIwYWY3NzMzOTU2ZSJ9.6fDYIA1pOkFF-0ImatW5MuwMxJfL0VmI5pibYBvhTto" class="fr-fic fr-fil fr-dib" width="775" style="width: 777px; height: 345.433px;" height="345"></p><p><strong><span style="font-size: 10pt;">Fig 11. Script to return the names and count of each software filtered by user</span></strong></p><p><br></p><p><span style="font-size: 11pt;">Then, choose the "Lookup Custom Field" you would like to base the lookup on, the value chosen in this field will populate $-lookup. </span></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6Ijc4OGFhMGQxLThhNjQtNGI5MC1iYzRhLTMyZmU3ZDM1ZjQ4MCJ9.yiandkvcxrt-B4Jg4G_xloek6A-Qp15SPdAZoq8p5nA" class="fr-fic fr-fil fr-dib" width="669" style="width: 671px; height: 382.064px;" height="382"></p><p><strong>Fig 12. Lookup Custom Field</strong></p><p><br></p><p><span style="font-size: 11pt;">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. </span></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImY2ZGRhNjM4LWQxYWEtNDdiYi05YTUxLTNkYzhhMjRlMjNhMyJ9.8jbuxzlIUUkKSI9EiW8hGouuNdQR5iL58CjPnYVPIGI" class="fr-fic fr-fil fr-dib" width="521" style="width: 523px; height: 585.065px;" height="585"></p><p><strong>Fig 14. Custom field to return all active users</strong></p><p><br></p><p><span style="font-size: 11pt;">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. </span></p><p><br></p><p><span style="font-size: 11pt;">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. </span></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjBkNjVlMDdiLTI1ZGQtNDk1Ny1iMTAyLTljOGY1NmZlZjg1NSJ9.U-hkaGEWp8wFfNKSdil6OKSyfX3T_bjLg3big_e27H8" class="fr-fic fr-fil fr-dib" width="1066" style="width: 1068px; height: 263.034px;" height="263"></p><p><strong>Fig 15. Populate multiple table rows when the query returns multiple rows</strong></p><p><br></p><p><span style="font-size: 11pt;">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). </span></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjYyYTJhYjRkLWQwOTctNGQ0Ny04YzA3LTdlNDEwNDQ1NjcwZSJ9.EjPhmVtC150NlOA5L-gVUDKyvIzi4xCHgLVCqaCbqaY" class="fr-fil fr-dib" width="1157" style="width: 1159px; height: 575.878px;" height="576"></p><p><strong>Fig 16. Custom Table Lookup in Action</strong></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><strong><span style="font-size: 14pt;">SQL Lookups - Agent Matching </span></strong></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjJkNjdmYTkyLTFlZGItNGQwMS1iMTAxLWQ2OWY5NDExMjk3ZSJ9.yggeKG0WsxQi_gq6acb774GP8fhsE7PXn7oXyWGnJuc" class="fr-fic fr-fil fr-dib" style="width: 816px; height: 353.442px;" width="1030" height="447"></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong>Fig 17. Script to determine which agent is assigned the ticket based on their status</strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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 <span style="color: rgb(0, 0, 0);">users</span>/agents to choose which of the 'matched' agents to assign the ticket to by enabling "Allow multiple results" for the lookup. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjM1ZWIzMTU4LWI1MTAtNDhlNS05NTkyLTVkZjY3MTRhNzA1MiJ9.dkXtWf9a3JJhE0BYfcRte6aiYlEiAJm1lCL2WPiChQI" class="fr-fic fr-fil fr-dib" width="1110" style="width: 1112px; height: 460.244px;" height="460"></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong>Fig 18. Allow multiple results for a lookup</strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">When enabled, should multiple rows be returned, a pop up will appear when the lookup is triggered, as shown in Figure 19. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjNhZWJlNWMxLTUzNWMtNDdmNS1iMDNmLWZmZDUxM2I5MTVhNyJ9.bEZUKtU3dI1C5CA2x8bQcF5gFuwG__Z_5tewAEmhHAQ" class="fr-fic fr-fil fr-dib" width="2016" style="width: 2018px; height: 292.569px;" height="293"></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong>Fig 19. Pop-up to choose which agent to assign to</strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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.</span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjYwZjFiNjg2LWFjOGUtNDg1NC1hM2RhLWE1ZWVlYjVlOTdjNCJ9.HKdpp09Ubq7EHv6VT8aAnghaSKfERZXB8R_g1FIsVJw" class="fr-fic fr-fil fr-dib" width="1305" style="width: 1307px; height: 565.068px;" height="565"></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong>Fig 20. Field for Agent matching</strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><strong>Agent Matching</strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjcxNzYzMzRkLTAzZGYtNDgzOS05MmU2LWQ2M2Q3YWNiYzM1MSJ9.DQ7Q63iltQAs1LsXxb7y9vhrqLDfcpObFUzEWuVAeEw" class="fr-fic fr-fil fr-dib" width="1880" style="width: 1882px; height: 383.562px;" height="384"></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong>Fig 21. Agent Matching Mappings</strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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'. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">Custom Field mappings can also be configured if you would like any custom fields to be populated with data returned by your script. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 12pt;"><strong>Custom Integration Method Lookups - Population of Custom Fields</strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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 <a href="https://www.usehalo.com/guides/2660" target="_blank" rel="noopener noreferrer">here</a>. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">In this example we have created a custom integration method to obtain information about the agents in another Halo instance. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImEwMDhkMWY3LTJjMTktNDJkNC05NTNkLTkwZTM2OWEwYzk5NiJ9.X2-My2dF5xAApI8IFywdfSS1uXy3pYOUnXtrtFjfdBQ" class="fr-fic fr-fil fr-dib" style="width: 1821px; height: 594.059px;" width="2484" height="811"></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong>Fig 22. Custom Integration method</strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjQzYWJlNzg1LWU5NDctNDI2ZC05NWQ2LTJkNjQ2YTFiNzU4YyJ9.3bebgaZKxGjO8evpnM95QcHipjlCfE5KVtFATvnT2Ok" class="fr-fic fr-fil fr-dib" width="1465" style="width: 1467px; height: 529.208px;" height="529"></span></strong></span><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;">Fig 23. Custom Integration Output Value</span></strong></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><span style="box-sizing: inherit;">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. </span></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br></span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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.</span></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6Ijg0NzIyOWU2LTI1ZTAtNDUwOS1iZjViLWU3OTNiMDgzY2Q2OCJ9.twWNWBgJGbbVNlRZQycz_qx5cxS7Y5PWTzOzCqrzQII" class="fr-fic fr-fil fr-dib" width="560" style="width: 562px; height: 689.26px;" height="689"></span></p><p><strong>Fig 24. Mapping Output Values</strong></p><p><br></p><p><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;" data-pasted="true"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 14pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;">Filtering results with variables</span></strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">Some common variables available to use include (when using variables do not include the hyphen):</span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">$-ticketid= Returns the ID of the ticket.</span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">$-agentid = Returns the ID of the agent.</span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">$-userid = Returns the ID of the user.</span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">$-deviceid = Returns the ID of the asset.</span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">$-invoiceid = Returns the ID of the invoice. </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">$-clientid = Returns the ID of the client (customer).</span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">$-siteid = Returns the ID of the site.</span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">$-loggedinuserid = Returns the ID of the User logging a ticket.</span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;">Filtering results when Allowing a Tickets Customer and Site to be different to the End-Users Customer and Site</span></strong></span></p><p data-pasted="true" style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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.</span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><img data-fr-image-pasted="true" src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImU4YjU1MTEyLTdmMjYtNDA0Yy04OTRjLWZhZDZhMDBmZmNhNyJ9.bDl3DrMoLzQZR39p_HCjbV29r61BBIoL7JHf6bDmrXw" width="689" height="313" style="box-sizing: inherit; border-style: none; cursor: pointer; padding: 0px 1px; user-select: none; text-align: left; max-width: none !important;" class="fr-fic fr-dii"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 10pt;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit;">Fig 25. Allow a Tickets Customer and Site to be different to the End-Users Customer and Site</span></strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br style="box-sizing: inherit;"></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="font-size: 11pt;">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. </span></p><p><br></p><p><span style="font-size: 14pt;"><strong>Lookup Result Options</strong></span></p><p><span style="font-size: 11pt;">Result options can be configured to control UI behaviours when running the lookup. </span></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjNkNTgxNGE5LTZmNjEtNDY3YS1iYjZiLTc1ODcwY2E3ZmYwYSJ9.XT9nu_ayrstQPNeuURC57Edy9LKgZa7f7jK95QqXhjw" class="fr-fic fr-fil fr-dib" style="width: 1493px; height: 413.74px;" width="1491" height="414"></p><p><strong>Fig 26. Lookup Result Options</strong></p><p><br></p><p><span style="font-size: 11pt;"><strong>Message when lookup has results -</strong> 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. </span></p><p data-pasted="true"><span style="font-size: 11pt;"><strong>Message when lookup has no result - </strong>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. </span></p><p><span style="font-size: 11pt;"><strong>Allow multiple results - </strong>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".</span></p><p data-pasted="true"><span style="font-size: 11pt;"><strong>Popup Message -</strong> 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. </span></p><p><span style="font-size: 11pt;"><strong>Run lookup every time the Ticket details screen is opened or an action is submitted -</strong> 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</span><span style="font-size: 11pt;"><a href="https://www.usehalo.com/guides/2364" target="_blank" rel="noopener noreferrer"> audit log tab</a></span><span style="font-size: 11pt;"> of the ticket. </span></p><p><br></p><p><span style="font-size: 11pt;"><em><strong>Note: Lookups will still run if the trigger field is updated on an action without this setting being enabled. </strong></em></span><strong><em><br></em></strong></p><p><br></p><p data-pasted="true"><span style="font-size: 11pt;"><strong>Field to map success/failure of Lookup to - </strong>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". </span></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><span style="font-size: 11pt;"><br style="box-sizing: inherit; color: rgb(0, 0, 0); font-size: 14px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; white-space: normal; text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; font-family: Roboto; text-align: start; background-color: rgb(255, 255, 255);"></span></p></div><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><br style="box-sizing: inherit; color: rgb(0, 0, 0); font-family: Roboto; font-size: 14px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial;"></p>
