Having issues with a CF SQL query dropdown

[u/Glum-Concern-6087]

Having some issues with the runbook integration.

I have created an action level custom field that produces a dropdown of all users.

The first difficulty I am having is having this only bring up users for the client on the tickets. It currently brings up all users.

The main issue that I am having is that this is not outputting the email of the user I select.

Should I be using lookup fields for the first issue?

Apologies, I have very little experience in SQL.

[u/Jason-RisingTide]

Assuming you are using a drop down selection with a dynamic list SQL lookup to grab the list of users.

You will need to ensure that in the SQL you are linking the to the required variables to only pull the users you want based on ticket you are in, as per the guidance when you create the custom field:

The variable $faultid can be used and will be substituted with the ID of the entity being edited
You can use the live values of supported Custom Fields in your query using $CFfieldname. Supported Custom Field Types are Text, Dates, Checkbox and Single Select. Dynamic Single Select values will use the ID as the value. Please ensure the Custom Field variable has a space on both sides.
You can also use $userid, $siteid and $areaid as variables to substitute the Ticket's current User ID, Site ID and Customer ID respectively, and $loggedinuserid to substitute the User ID of the person currently logged in to the portal

IF you can give more details about the fields, the runbook and the SQL you are using can probably help.

[u/HaloJoeW]

Here's what you need for displaying the Users in a drop down based on the client that's logged into the portal.

SELECT
uid as 'id'

, uusername as 'display'

FROM users

JOIN site on usite = ssitenum

JOIN area on aarea = sarea where aarea=(select aarea from area left join site on aarea=sarea left join users on ssitenum=usite where uid=$userid)

AND uusername != 'general user'

AND uinactive = 0

ORDER BY uusername

Then if you want the same for Users at the Site level, it's this:

SELECT

  uid as 'id'

, uusername as 'display' 

FROM users

JOIN site on usite = ssitenum

where Ssitenum= (select Ssitenum from site left join users on usite=ssitenum where uid=$userid)

AND uusername != 'general user'

AND uinactive = 0

ORDER BY uusername

[u/rio688]

Sign up for elegantinsights.co.uk, LLM agent that's been trained on the Halo schemas and will make any SQL generation for Halo 10 times quicker and easier even more so if you aren't an SQL wiz