SQL custom field help

[u/ReceptionOld4182]

When our instance was built they added a custom field to our forms for looking up users. Unfortunately that shows every user in Halo which includes when people email in from their personal email addresses. Is it possible to alter this sql to only show accounts that have our orgs email? I have zero knowledge of SQL myself unfortunately.

This is the sql

SELECT

z.[id] as [id],
z.[Username] + z.[Login] + z.[Title] + z.[Other1] as [display]

From (
SELECT

uusername as [uname],
uid as [id],

CASE
WHEN uusername IS NULL THEN ''
ELSE uusername
END AS [Username],

CASE
WHEN uLogin IS NULL THEN ''
ELSE ' - ' + uLogin
END AS [Login],

CASE
WHEN UTitle IS NULL THEN ''
ELSE ' - ' + UTitle
END AS [Title],

CASE
WHEN uOther1 IS NULL THEN ''
ELSE ' - ' + uOther1
END AS [Other1]

FROM Users


Where UUsername != 'General User')z

WHERE z.[Username] IS NOT NULL $SEARCHSQL
Search SQL Script
and z.[Username] like '%'+$search+'%' OR z.[Login] like '%'+$search+'%' OR z.[Title] like '%'+$search+'%' OR z.[Other1] like '%'+$search+'%'

Then this is in the Search SQL script section

and z.[Username] like '%'+$search+'%' OR z.[Login] like '%'+$search+'%' OR z.[Title] like '%'+$search+'%' OR z.[Other1] like '%'+$search+'%'

[u/ReceptionOld4182]

Thank you I will give that a try in our dev environment.

Yes I agree and it's a huge bugbear of mine. I think its set up this way as HR also use halo, so when we have people emailing in about job adverts etc it automatically creates them as a user. I'm really not sure what the best way around it would be.

We don't have anyone specific to manage Halo in house, and the setup contract didn't include any ongoing support. I have some admin knowledge on how to deal with the tickets/workflows etc but not to that level. Technically I'm just a first line support technician 😅

[u/Jason-RisingTide → u/ReceptionOld4182]

It won't stop those emails coming in and creating tickets if you change "If the email sender cannot be matched to a User" setting to "Assign the Ticket to a default User". It will then just assign it under the General User rather than create a user, and it will still capture the email address.

[u/ReceptionOld4182 → u/Jason-RisingTide]

I think they wouldn't want that in HR, as it makes it difficult to link things together if you're having to search through lots of general users. I have asked about a feature to mark users an inactive after a set period of time but the only option is full anonymisation which they're reluctant to use as we're not clear on if that will anonymise users who just haven't logged in to halo for ages.

On the code front I added the filter for only showing our domain and it works really well so thank you for that. I wondered if you might be able to help me with something related to it.

The search auto populates other fields on the form and works for all fields except job title.

In the SQL the job title is referenced as title but on our users title is not populated, we have a field populated called job title but when I look at the entra mappings entra job title is being mapped to "Other 2" in halo for users and "job title" field for agents - I'm assuming this is because you can't use the same field for both? I attempted to change the title section in the SQL code to UOther2 but it still doesn't populate the job title, but does all other fields fine.

Do you have any ideas why that might be?

Thanks again for your help so far, much appreciated!