<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;
}
.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><p><strong>In this guide we will cover:</strong></p><p><strong>- Creating Dynamic Fields using a SQL Lookup </strong></p><p><strong>- Worked Example -SQL</strong></p><p><strong>- Using Variables in Dynamic SQL Lookup Queries</strong></p><p><strong>- Formatting as a Tree Dropdown</strong></p><p><strong>- Default to the First Returned Value</strong></p><p><strong>- Creating Dynamic Fields using a Custom Integration Method</strong></p><p><br></p><p><br></p><p>Custom fields of type "Single Selection" or "Multiple Selection" can be configured to dynamically return options in the field based on existing data, rather than you having to input fixed values. The data can be dynamically brought into a field, can either be pulled from your Halo database, or from a third party tool. This is done by either running a SQL query on your database, or by running a custom integration method to obtain data from another tool.</p><p><br></p><p>Dynamic custom fields are configured differently depending on if you are using the SQL lookup method or the custom integration lookup method. </p><p><br></p><p><strong><span style="font-size: 14pt;">Creating Dynamic Fields using a SQL Lookup </span></strong></p><p><span style="font-size: 11pt;">SQL lookups are used when you would like to populate the options in a field using data in your Halo database. </span></p><p><br></p><p>In this example, we will look at creating a single select custom field, to return the names of users at the same site as the individual raising the Ticket. </p><p><br></p><p>Firstly, head to Configuration > Custom Objects > Custom Fields, and ensure the entity selected is "Ticket".</p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImNlZTgwOWNhLTg4ZDItNDM5Ni05YmU4LTA4ZjFmMTkxODNjZiJ9.FJOY1BaisbcLKGPNR3kwpfE685ixzJuhGRIcpxzs6QY" class="fr-fic fr-fil fr-dib" width="509" height="296"></p><p><strong><span style="font-size: 10pt;">Fig 1. Entity Selection Against Custom Fields</span></strong></p><p><br></p><p>Create a new Custom Field with the following configuration:</p><p><br></p><ul><li><strong>Type of Field -</strong> Single Selection</li><li><strong>Lookup Type - </strong>Dynamic List</li><li><strong>Lookup Method -</strong> SQL</li><li><strong>Connection Type -</strong> Halo Database (if using another SQL database setup is the same but you will be required to enter your connection details)</li></ul><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImY0OWJjZTQ0LTJmZDUtNDdmNS05ZmNiLTliYjg1OWYyNzgyMiJ9.GBVFqeJv3iKlMXFCrv8nJvYIDa3qHUZE2kdpsZYIVWA" class="fr-fic fr-fil fr-dib" width="915" style="width: 917px; height: 701.03px;" height="701"></p><p><strong><span style="font-size: 10pt;">Fig 2. Configuration of New Custom Field</span></strong></p><p><br></p><p><span style="font-size: 11pt;">Now, you will need to create and enter a SQL script that returns the values from your database that you would like to be available to choose from in the field. </span></p><p><br></p><p>The SQL query must return two columns: ID and Display.</p><p><br></p><ul><li>The ID column should refer to the ID of the entity you're returning. In this case, as we're concerned with returning the names of users', our ID will be '<strong>uid</strong>' (the ID of the user in question, from the users table).</li><li>The Display column is whatever is returned in the dropdown. For our example, as we want to return the names of users, our display column will be '<strong>uusername</strong>' (the name of the user in question, from the users table).</li></ul><p>Finally, we want to ensure we are not returning <em>all</em> users, only users at the same site as the end-user of the Ticket being raised. For this, we will need to add a "where" clause.</p><p><br></p><p><em><strong>where usite=(select usite from users where uid=$userid)</strong></em></p><p><br></p><p>In this example usite is the ID of the site & $userid is the end-user of the Ticket.</p><p><br></p><p>So, putting this all together, we would want our SQL query to look like:</p><p><br></p><p><em><strong>select uid [ID], uusername [Display] from users where usite=(select usite from users where uid=$userid)</strong></em></p><p><br></p><p>If your SQL query returns less than 10,000 rows ensure "Data Load Type" is set to "Load all rows and filter in browser (<10,000 rows)". This will allow all results will be returned in the field for agents/users to choose from. </p><p><br></p><p id="isPasted">If your SQL query returns more than 10,000 rows ensure "Data Load Type" is set to "Load only the top 100 rows and filter on the server (>10,000 rows)". When this is selected agents/users will need to search the field before results are returned (to limit the amount of data being returned). In order to search successfully you will need to adjust your existing SQL query and create a "Search SQL Script". See the section "Creating and Using a Search SQL Script" for information on how to do this. </p><p><br></p><p><span style="color: rgb(0, 0, 0); font-family: sans-serif; font-size: 14px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; 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; display: inline !important; float: none;" id="isPasted">Once you have created your field, click "Save" and add your newly created custom field to the relevant Ticket Type(s). You should find it returns a list of usernames, where those users are located at the same site as the end-user of the Ticket being logged</span></p><p><br></p><p><strong><span style="font-size: 12pt;">Creating and Using a Search SQL Script</span></strong></p><p>A Search SQL Script will need to be configured if your SQL query returns more than 10,000 rows of data. </p><p><br></p><p>You will need to adjust your existing SQL query. In this example we are using a query that returns all items (products) in the instance. The field will be used to select an item (product) from all items currently in my instance. </p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjMyMWMyY2IwLWFhMjQtNGRhMC1iM2FhLTFlNzgzODFlNWFiZSJ9.WW8WgHVPP1cOOFUx7YWMOwlMey_As4qtFqlhtbINvB0" class="fr-fic fr-fil fr-dib" width="1172" height="307"></p><p><strong><span style="font-size: 10pt;">Fig 3. Modified SQL Query to Allow for Search</span></strong></p><p><br></p><p><span style="color: rgb(226, 80, 65);">$topsql </span> - This is added after the select command. This ensures only the top 100 values are returned and available to chose from, after the results have been filtered by the search. This is required to prevent too many values being returned. </p><p><span style="color: rgb(251, 160, 38);">where 1=1 </span>- This is added after the from clause. This acts a base condition and ensures all relevant results are returned even after filters are applied. </p><p><span style="color: rgb(44, 130, 201);">$searchsql</span> - This will pull through the search SQL script, configured in the field below (figure 4).</p><p><br></p><p>Now, you will need to add another SQL script to allow results to be filtered by the search accordingly. </p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImU0NzhmOTZiLTk4YzYtNDZmYS05Mzc0LTVjZDYyMjMyYzY3YyJ9.Uk4-012LmvlH8F6XGQj4PEMFzW6vGJx3R5eqtx4_aEE" class="fr-fic fr-fil fr-dib" width="1055" style="width: 1057px; height: 331.23px;" height="331"></p><p><strong><span style="font-size: 10pt;">Fig 4. Search SQL Script</span></strong></p><p><br></p><p>The script here extends the where clause of the existing query (filtering the results). Results are filtered so that only items that have an item description (idesc) containing the string entered in the search will be returned.</p><p><br></p><p><span style="color: rgb(147, 101, 184);">idesc </span>- The name of the column being used as the 'Display' value. </p><p><span style="color: rgb(65, 168, 95);">'%'+$search+'%' </span>- Pulls in the string entered in the search field. </p><p><br></p><p>Once you have created your field, click 'Save' (top LHS) & add your newly created custom field to the relevant Ticket Type(s)/actions.</p><p><br></p><p>Now, when this field is used agents/users will be able to search the field before results are loaded.</p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImQ1ZWYxY2M0LTgwMjMtNGY2MC05ZmQ2LTNiZDczNDZkYjI5YyJ9.r2IKSkF99pAdPBIka_c6v3NarqxIvQqLB3gu7O8SD2E" class="fr-fic fr-fil fr-dib" width="911" style="width: 913px; height: 175.915px;" height="176"></p><p><strong><span style="font-size: 10pt;">Fig 4. Searching in Custom Field</span></strong></p><p><br></p><p>From v2.238+, $-SEARCH can be used in the SQL Script and the Search SQL Script.</p><p><br></p><p><strong><span style="font-size: 14pt;">Worked Example - SQL</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 dynamic SQL fields and <a data-fr-linked="true" href="https://usehalo.com/haloitsm/guides/1238" id="isPasted" target="_blank" rel="noopener noreferrer">database lookups</a>. 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><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><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><br></p><p><strong><span style="font-size: 14pt;">Using Variables in Dynamic SQL Lookup Queries</span></strong></p><p>Variables can be used in your SQL query to have the results returned in the field change based on the ticket/user/customer the field relates to. Using the above example, the variable $-userid is used to filter results based on the user of the ticket. </p><p><br></p><p>Some common variables available to use include (when using variables do not include the hyphen):</p><p>$-faultid = Returns the ID of the ticket.</p><p>$-agentid = Returns the ID of the agent doing the action.</p><p>$-userid = Returns the ID of the user assigned the ticket.</p><p>$-deviceid = Returns the ID of the asset.</p><p>$-invoiceid = Returns the ID of the invoice. </p><p>$-areaid (available from v2.234+) - Returns the ID of the customer.</p><p>$-siteid (available from v2.234+) - Returns the ID of the site.</p><p> $-loggedinuserid (available from v2.234+) - Returns the ID of the logged in user.</p><p><br></p><p><strong><em>Note: $-clientid can be used to filter other SQL queries but not in dynamic SQL lookup queries, therefore to filter on the client use the following SQL 'where aarea = (select aarea from users left join site on usite=ssitenum left join area on sarea=aarea where uid=$userid)' </em></strong></p><p><br></p><p><strong><span style="font-size: 14pt;">Formatting as a Tree Dropdown</span></strong></p><p data-pasted="true">If a SQL query includes '>', Halo handles this character and formats the results in a tree dropdown format.</p><p><br></p><p><strong>Using this example: </strong></p><p><br></p><p data-pasted="true">select uid as [ID], aareadesc + '>' + uusername as [Display] from area</p><p>join users on aarea=uarea</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImRjYzc3OTg1LTgxYzctNDk5Ny05MDM4LWZkZGVlZDBlNTVmNSJ9.i7XxrHrTZrOtB975lb4iUxUWy5pGVHdufCpOyMZPsJU" class="fr-fic fr-fil fr-dib" width="360" height="308"></p><p><strong><span style="font-size: 10pt;">Fig 5. Tree dropdown from SQL.</span></strong></p><p><br></p><p><strong><span style="font-size: 14pt;">Default to the First Returned Value</span></strong></p><p>You can have dynamic SQL lookup fields be populated with default data. As the options within this field change dynamically you cannot choose a set default like you can with static fields. Instead you can set the default to be the first value that is returned by the SQL query used for the field. </p><p><br></p><p>To do this enable the option 'Default to first value' against the dynamic SQL field. </p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjJmYTlhZDY4LWIxZWYtNDExZi04YmI5LTJmMDAwNGVhMWZmYiJ9.TUNzzpFnvcjfeeN7IWHDjUmGaMKgnxxv3uGSUMTP4dE" class="fr-fic fr-fil fr-dib" width="1205" style="width: 1207px; height: 609.989px;" height="610"></p><p><strong><span style="font-size: 10pt;">Fig 6. Default Field Value to the First Value Returned by the Query</span></strong></p><p><br></p><p data-pasted="true"><strong><span style="font-size: 14pt;">Creating Dynamic Fields using a </span></strong><span style="font-size: 14pt;"><strong>Custom Integration Method</strong></span></p><p><span style="font-size: 11pt;">Custom integration methods are used when you would like the options available in a field to be based on data in another tool (outside of Halo). Using this method requires a base understanding of runbooks/custom integrations.<br></span></p><p><br></p><p><span style="font-size: 11pt;">The <a href="https://usehalo.com/haloitsm/guides/2660">custom integration method</a> is used when the data required is not stored in the Database but is accessible in a Custom Integration. </span></p><p><br></p><p><span style="font-size: 11pt;">In this example, we will create a field that returns a list of tickets stored in another tool.</span></p><p><br></p><p data-pasted="true">Firstly, head to Configuration > Custom Objects > Custom Fields, and choose the entity you would like the custom field to be available on.</p><p><br></p><p><strong><em>Note: Custom table fields can use custom integration lookups.</em></strong></p><p><br></p><p>Configure the custom field with the following values:</p><p data-pasted="true"><strong>Type of Field - </strong>Single Selection/Multiple Selection</p><p><strong>Lookup Type </strong>- Dynamic List</p><p><strong>Lookup Method </strong>- Custom Integration Method</p><p><br></p><p><strong><span style="font-size: 11pt;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImU4YTNhN2ZkLTA3NmUtNGYxNy1hODM5LWJmMWMyYWNkODg4MyJ9.-TH5MJvEfg148VX0WBTeq4mje58CorJtfm1o9O7Pvgs" class="fr-fic fr-fil fr-dib" width="1084" style="width: 1086px; height: 326.786px;" height="327"></span></strong></p><p data-pasted="true"><strong><span style="font-size: 10pt;">Fig 7. Custom Integration Method</span></strong></p><p><br></p><p>You are now able to choose which of your custom integration methods to use for this lookup, and the desired output variable. The custom integration method must obtain the data you would like to appear in the field. </p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjU5Y2U4OGZkLWI5MGMtNDg3Ny1hYzI5LWVmODY2ZTZhZjFkNSJ9.gUY7fgue8eKk0Mw9npbUeaDuZ1UELZf286CfgFUug2c" class="fr-fic fr-fil fr-dib" width="1081" style="width: 1083px; height: 390.615px;" height="391"></p><p data-pasted="true"><strong><span style="font-size: 10pt;">Fig 8. Custom Integrations</span></strong></p><p><br></p><p><span style="font-size: 11pt;">The output value must the the name of an output variable outputted by your chosen custom integration method. This variable must produce an array of data. This array will be used as the options available in the custom field. In this example, the output variable 'TicketsList' contains a list (array) of tickets. Each ticket returned in this list will be an option to choose from in the field. If you do not have an existing variable that meets these</span><span style="font-size: 11pt;"> conditions you will need to create a new variable. <br></span></p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjExMDdmZjlmLTU2MWQtNDY0MS1iMDk5LTQ1ZWQ0ZWFmNmU4ZSJ9.M67axa6V8W0tlKrQgKyGdS28sVrmki9N6gLi86YRarM" class="fr-fic fr-fil fr-dib" width="1084" style="width: 1086px; height: 206.19px;" height="206"></p><p data-pasted="true"><strong><span style="font-size: 10pt;">Fig 9. Output variables </span></strong></p><p><br></p><p>Finally, you can set the ID property and Display property. These properties must be child properties of each element returned in the array, and will be used to populate the custom field. </p><p><br></p><p>The ID property must be an integer, this must be the unique identifier for the element in the array. The Display should be the property that you would like to display to agents/users in the custom field, this should be unique and can be the same as the ID property. This can be chosen form the objects returned within your array. </p><p><br></p><p>In the figure 10 example the id of each ticket returned in the array is used as the ID property and the summary of each ticket is used as the display property.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6Ijk1NTRmYzY0LTA1NTktNDY3MC05MjA1LWZlMDdjMDk5NjgwZSJ9.LEdwGOyGaX6mNyuNVJqVF3opD84arZIV1w90NVRcIc4" class="fr-fic fr-fil fr-dib" width="1083" style="width: 1085px; height: 578.533px;" height="579"></p><p data-pasted="true"><span style="font-size: 10pt;"><strong>Fig 10. Setting properties</strong></span></p><p style="box-sizing: inherit; margin: 0px; line-height: 1.4285em; color: rgb(0, 0, 0); font-family: sans-serif; font-size: 14px; font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; 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;"><strong style="box-sizing: inherit; font-weight: 700;"><span style="box-sizing: inherit; font-size: 10pt;"> </span></strong></p><p>Figure 11 shows the properties within an element of the array used as the ID and display properties for the field. </p><p><br></p><p><img data-fr-image-pasted="true" src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjhlMzZkY2MxLTdlYjUtNGYwOS1iZTU5LThkNDU1OWE5ZDFlMCJ9.P1P_aKgyVzMSNsQLKZdhm2ZpRIh4sRIA-8utcOncocw" width="1020" height="186" 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: 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; background-color: rgb(255, 255, 255); text-decoration-thickness: initial; text-decoration-style: initial; text-decoration-color: initial; max-width: none !important;" data-pasted="true" class="fr-fil fr-dib"></p><p data-pasted="true"><strong><span style="font-size: 10pt;">Fig 11. Example array</span></strong></p><p style="margin: 0px;"><br></p><p style="margin: 0px;" data-pasted="true"><strong><em>Note: Non-integer IDs can be identified for multi-select custom fields.</em></strong><strong><em> </em></strong></p><p style="margin: 0px;"><br></p><p style="margin: 0px;">Once the ID and display properties are set save the field. </p><p style="margin: 0px;"><br></p><p style="margin: 0px;">The custom integration method will run every time the drop-down for the field is clicked, retrieving the current values from your custom integration. </p>
