Dynamic SQL Lookup Fields - Customer Specific Lookup

In this guide we will cover:

- How to complete custom specific lookups for dynamic fields

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 $-clientid variable works in customer reporting.

The following script will return the site of the user, this can be entered into the SQL script field against the custom field:

select distinct uid as [id],
uusername as [display]
from users
left join site on usite = ssitenum
left join area on sarea = aarea
where aarea = (
select aarea
from users
left join site on usite = ssitenum
left join area on sarea = aarea
where uid = $userid) and uusername not like '%general user%' and uinactive=0

Note: The section in bold works in the same way as $-CLIENTID.

Fig 1. Field dynamically populated with user's site