<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;}</style><p><strong>In this guide we will cover:</strong></p><p><strong>- How to complete custom specific lookups for dynamic fields</strong></p><p style='margin:0cm;font-size:15px;font-family:"Calibri",sans-serif;'><br></p><p style='margin:0cm;font-size:15px;font-family:"Calibri",sans-serif;'><br></p><p id="isPasted">If you are looking to create customer specific lookups (such as a lookup of a customer’s sites) you can use the $-userid field in the dynamic SQL query. If you structure the query in a certain way this will then work in the same way the <strong>$-clientid</strong> variable works in customer reporting.</p><p style='margin:0cm;font-size:15px;font-family:"Calibri",sans-serif;'><span style="font-size: 11pt; font-family: Arial, Helvetica, sans-serif;"> </span></p><p>The following script will return the site of the user, this can be entered into the SQL script field against the custom field:</p><p style='margin:0cm;font-size:15px;font-family:"Calibri",sans-serif;'><br></p><p><em>select distinct uid as [id],<br style="box-sizing: inherit; color: rgb(0, 0, 0); font-family: Poppins, sans-serif, Roboto; font-size: 13px; 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;">uusername as [display]<br style="box-sizing: inherit; color: rgb(0, 0, 0); font-family: Poppins, sans-serif, Roboto; font-size: 13px; 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;">from users<br style="box-sizing: inherit; color: rgb(0, 0, 0); font-family: Poppins, sans-serif, Roboto; font-size: 13px; 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;">left join site on usite = ssitenum<br style="box-sizing: inherit; color: rgb(0, 0, 0); font-family: Poppins, sans-serif, Roboto; font-size: 13px; 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;">left join area on sarea = aarea<br style="box-sizing: inherit; color: rgb(0, 0, 0); font-family: Poppins, sans-serif, Roboto; font-size: 13px; 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;"></em><strong><em>where aarea = (<br style="box-sizing: inherit; color: rgb(0, 0, 0); font-family: Poppins, sans-serif, Roboto; font-size: 13px; 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;">select aarea<br style="box-sizing: inherit; color: rgb(0, 0, 0); font-family: Poppins, sans-serif, Roboto; font-size: 13px; 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;">from users<br style="box-sizing: inherit; color: rgb(0, 0, 0); font-family: Poppins, sans-serif, Roboto; font-size: 13px; 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;">left join site on usite = ssitenum<br style="box-sizing: inherit; color: rgb(0, 0, 0); font-family: Poppins, sans-serif, Roboto; font-size: 13px; 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;">left join area on sarea = aarea<br style="box-sizing: inherit; color: rgb(0, 0, 0); font-family: Poppins, sans-serif, Roboto; font-size: 13px; 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;">where uid = $userid) and uusername not like '%general user%' and uinactive=0</em></strong></p><p style='margin:0cm;font-size:15px;font-family:"Calibri",sans-serif;'><br></p><p><em><strong>Note: The section in bold works in the same way as $-CLIENTID.</strong></em></p><p style='margin:0cm;font-size:15px;font-family:"Calibri",sans-serif;'><span style="font-size: 11pt; font-family: Arial, Helvetica, sans-serif;"><br></span></p><p style='margin:0cm;font-size:15px;font-family:"Calibri",sans-serif;'><span style="font-size: 11pt; font-family: Arial, Helvetica, sans-serif;"><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6Ijk3MzlkNTVjLWFkMjQtNDdhZS04ZWY0LTUzNjMzZDRlODQ3OCJ9.Nm0URQjWM4v7JA415Qzm5xQZKqRYtdLHnUMz97SeZg4" class="fr-fic fr-fil fr-dib" width="1122" height="874"></span></p><p><strong><span style="font-size: 10pt;">Fig 1. Field dynamically populated with user's site<br></span></strong></p><p><br></p>
