HaloPSA

HaloPSA Guides

Documentation to assist with the setup and configuration of the HaloPSA platform

Guides > Dynamic SQL - Custom Fields

Dynamic SQL - Custom Fields

How It Works

Dynamic SQL custom fields, unlike static lists, generate data from the database and auto-populates the selection choices in each field. 


These SQL scripts require an 'ID' and a 'Display' field. Every entity within your Halo instance will have two values in the database. Generally speaking, this is a numeric value which is used as the ID and the display, an alphabetical or alphanumeric column value. 



The alphanumeric value tends to be a 'friendly name' within the database, which is easily identifiable as the column you require to be input. The numeric value, can be considered as an ID number, which are automatically generated within the database in order to keep entities created in a separate column.


For example: 

The 5th user ever generated on your Halo will have an ID of '5'.

Your 67th custom field ever created will have an ID of '67'.


Although this might sound daunting, we have created a visual schema to look at that may help with creating the script. You can also use the "All Columns & Tables" report that can be found in the Online Repository.


Working Example


As a working example, I will show you how a dynamic SQL custom field for user/contact selection is created.

Referencing the schema above, we would like to select each username within the database. If we take a look at the users table, we are presented with the following options:



Next to each of the column names, you are able to see the type of column they are: with UID being an int (integer/numeri) and nvarchar meaning variable characters - or alphanumeric. From this, we can start to distinguish which columns may be useful for the script. 


Since we would like the username, we will use 'UID' as the ID and 'uusername' as the 'Display' - as it is the value we recognise and is 'friendly' to us.


We have to now input the SQL script into the custom field:


The 'SELECT' indicates we want to select the following values, with a comma separating each column name, and 'FROM' indicating from which table we'd like to take this information. In this case, 'users'.


When the field is in use upon saving, it will generate a complete list of all of your users in your Halo database and have them available for selection on the field.


A typical issue when using a dynamic SQL field for users is that your unknown users, or your 'general users', will also appear in this list, as they exist in your database. 


If you're wanting to exclude these entities, you can add a condition to your script. This is often called a 'WHERE' clause. 



The script above shows that the WHERE clause is excluding any values that are similar or equal to 'General User'. 



Popular Guides