<style>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: 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: 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: 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;
}
</style><div id="isPasted"><p><span style="font-size: 11pt;"><strong>In this guide we will cover:</strong></span></p><p><span style="font-size: 11pt;"><strong>- What is a lookup?</strong></span></p><p><span style="font-size: 11pt;"><strong>- How to create a lookup</strong></span></p><p><span style="font-size: 11pt;"><strong>- Worked Example</strong></span></p><p><span style="font-size: 11pt;"><strong>- Filtering results with variables</strong></span></p><p><br></p><p style="text-align: left;"><br></p><p style="text-align: left;"><br></p><p><strong><span style="font-size: 14pt;">What are Lookups?</span></strong></p><p><span style="font-size: 11pt;">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.</span></p><p><br></p><p><strong><span style="font-size: 14pt;">Create a New Lookup</span></strong></p><ol style="box-sizing: inherit; margin-bottom: 0cm; margin-top: 0px; list-style-type: decimal;"><li style="font-size: 11pt;">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’.</li><li style="box-sizing: inherit; font-size: 11pt;"> Create a name for your lookup, and select the Use:</li></ol><p><br><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjcxNGUxY2JhLTk5OGMtNDhkMy05OGJiLWE1NWNiZGQ5MjRmNCJ9.iQi5jkGu8T5MT7bQAOwycZ0OnzasdTSSprEmAMzbJMw" class="fr-fic fr-fil fr-dib" width="1071" style="width: 1073px; height: 514.546px;" height="515"></p></div><p><strong><span style="font-size: 10pt;">Fig 1. New Lookup profile<br></span></strong></p><p style="box-sizing: inherit; margin: 0cm 0cm 8pt 36pt; line-height: 16.05px; font-size: 15px; font-family: Calibri, sans-serif;"><br></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><span style="box-sizing: inherit; font-size: 15px; line-height: 16.05px; font-family: Calibri, sans-serif;"> </span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><br style="box-sizing: inherit;"></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;">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:</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjE3NTkwODc0LWZmZmEtNGIwNy1iMmM5LTYyMjZlYTdmMWRlMCJ9.1kq-nraq-DguipOZJBufwBaqFwLeC-4M_zOipVBqdks" class="fr-fic fr-fil fr-dib" width="1504" height="245"><strong><span style="font-size: 10pt;">Fig 2. Trigger fields for lookup<br></span></strong></p><p><br></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;">Checkbox fields can be used as the trigger for a lookup.</p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;">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. </p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;">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.</p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;">4. The next step will be to set up your connection. You can connect directly to your own Halo database by selecting the following: </p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjMwMDgwM2E5LTY3NDEtNDAzNS04N2VjLTY0ZGY2MTk1ZjRjNSJ9.78P4CEqZkmVU7n0XWDeaFNxuZUp-rE70_rU-SgIVkww" class="fr-fic fr-fil fr-dib" width="863" style="width: 863px; height: 122.5px;" height="122.5"></p><p style="box-sizing: inherit; margin: 0px 0px 0px 60px; line-height: 1.4285em;"><strong><span style="font-size: 10pt;">Fig 3. Connection type<br></span></strong></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><span style="background-color: transparent; font-size: 13px;">This can also be used to connect to external systems. If you choose this option it will look something like this:</span></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><br style="box-sizing: inherit;"></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjYwMmY5MTgyLTcyMjAtNGY4Ni1hZjI0LWUxYzIxY2VjZTJjOSJ9.SHs0F3DpUKgEoOZiHAEBYSrsk5kdwiel-C1avlqtYdI" width="782" height="345" style="box-sizing: inherit; border-style: none; text-align: left; width: 782px;" class="fr-fil fr-dib"></p><p style="box-sizing: inherit; margin: 0px 0px 0px 60px; line-height: 1.4285em;"><strong><span style="font-size: 10pt;">Fig 4. Connecting to an external system<br></span></strong></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><br style="box-sizing: inherit;"></p><p style="margin-left: 20px;">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:</p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;"><br style="box-sizing: inherit;"></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImMyMDY3OTMzLThmODYtNDcxMS1iNWIwLTVlMTE4YTlkYmI0MyJ9.l0-H20p83EqXUFA2MKHcxKEF0QAbg2p3esZahCZtzUg" width="582" height="195" style="box-sizing: inherit; border-style: none; text-align: left; width: 582px;" class="fr-fil fr-dib"></p><p style="box-sizing: inherit; margin: 0px 0px 0px 60px; line-height: 1.4285em;"><strong><span style="font-size: 10pt;">Fig 5. SQL script for lookup<br></span></strong></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><br style="box-sizing: inherit;"></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em; text-align: left;">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:</p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><br style="box-sizing: inherit;"></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjhiMzI2MTlmLWU5YWQtNGQ5MC1iY2M4LWRmYjZiMzAwNTdiMyJ9.fh4RgXBARC_0OorKQcv63hwNwctBVEK7JnoyHcfKPNs" width="721" height="206" style="box-sizing: inherit; border-style: none; text-align: left; width: 721px;" class="fr-fil fr-dib"></p><p style="box-sizing: inherit; margin: 0px 0px 0px 60px; line-height: 1.4285em;"><strong><span style="font-size: 10pt;">Fig 6. Field mappings<br></span></strong></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><br style="box-sizing: inherit;"></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;">Hit save. Now you have finished creating your lookup code! </p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><br style="box-sizing: inherit;"></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;">Now, when an email address is entered to a form, where first and last name fields exist, they will automatically populate:</p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin: 0px 0px 0px 20px; line-height: 1.4285em;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImZjOGYwODFmLWM5NjktNDg2MC1iZmIxLWFmMWJkYjMyY2I0ZSJ9.rx35zUjMDlkWPi8qBDtzkW-vUrocLepB9hlbjyahq00" class="fr-fic fr-fil fr-dib" width="696" style="width: 696px; height: 508.258px;" height="508.258"></p><p style="box-sizing: inherit; margin: 0px 0px 0px 60px; line-height: 1.4285em;"><strong><span style="font-size: 10pt;">Fig 7. Field automatically populated by lookup</span></strong></p><p style="box-sizing: inherit; margin: 0px 0px 0px 60px; line-height: 1.4285em;"><br></p><p style="box-sizing: inherit; margin: 0px 0px 0px 60px; 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 Tables</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;">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.</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;"><strong><span style="font-size: 10pt;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjBhMzY5YjkxLTUzYTgtNDUyOC1iODMxLTM4MjZkYjVlMGJkMiJ9.dLlOpXJ0PGkHyjbNa1hVsLWcVKP1DtK9E2AxM6MtcHg" class="fr-fic fr-fil fr-dib" width="433" height="598">Fig 8. Populating a custom table</span></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: 0px; line-height: 1.4285em;" id="isPasted">This will then allow you to select the custom table you would like to populate using a single select drop down. </p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em;">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.</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;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImE4NWQxOGI3LWNlZGQtNGM4NS05ZDEyLTgyYTYwZGM0MjJhZCJ9.SOiKYsTNfC0xGdUjHv-Zi7C2qaXoYp-UlAqz7Q4Dn0o" class="fr-fic fr-fil fr-dib" width="442" style="width: 444px; height: 603.712px;" height="604"></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;" data-pasted="true"><strong><span style="font-size: 10pt;">Fig 9. Using SQL to relate values to custom fields</span></strong></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p>You can then set the field you would like to trigger the lookup using the "Lookup Custom Field" drop down. </p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p><strong><span style="font-size: 14pt;">Worked Example</span></strong></p><p>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 <a data-fr-linked="true" href="https://usehalo.com/haloitsm/guides/2041" id="isPasted" target="_blank" rel="noopener noreferrer">dynamic SQL fields</a> and database lookups. Video Link for Portal Users: <a href="https://www.youtube.com/watch?v=2IBMwuvYhWM" rel="noopener noreferrer" target="_blank">Database Lookups</a></p><p><br></p><p><iframe width="560" height="315" src="https://www.youtube.com/embed/2IBMwuvYhWM?si=_75GIAnDSr1eSYVZ" title="YouTube video player" frameborder="0" allowfullscreen=""><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span><span class="fr-mk" style="display: none;"> </span></iframe></p><p style="box-sizing: inherit; margin: 0px 0px 0px 60px; line-height: 1.4285em;"><br></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;">Filtering results with variables</span></strong></p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;">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. </p><p style="box-sizing: inherit; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; line-height: 1.4285em;"><br></p><p id="isPasted">Some common variables available to use include (when using variables do not include the hyphen):</p><p>$-ticketid= Returns the ID of the ticket.</p><p>$-agentid = Returns the ID of the agent.</p><p>$-userid = Returns the ID of the user.</p><p>$-deviceid = Returns the ID of the asset.</p><p>$-invoiceid = Returns the ID of the invoice. </p><p>$-clientid = Returns the ID of the client (customer).</p><p>$-siteid = Returns the ID of the site.</p><p>$-loggedinuserid = Returns the ID of the User logging a ticket.</p><p><br></p><p><strong><span style="font-size: 12pt;">Filtering results when Allowing a Tickets Customer and Site to be different to the End-Users Customer and Site</span></strong></p><p data-pasted="true">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.</p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImU4YjU1MTEyLTdmMjYtNDA0Yy04OTRjLWZhZDZhMDBmZmNhNyJ9.bDl3DrMoLzQZR39p_HCjbV29r61BBIoL7JHf6bDmrXw" class="fr-fic fr-fil fr-dib" width="689" height="313"></p><p>Fig 10. Allow a Tickets Customer and Site to be different to the End-Users Customer and Site</p><p><br></p><p>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. </p><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>
