8/30/2017 - 11:30 PM

Create a SQL based dropdown EDF

Create a SQL based dropdown EDF

[4:36 PM] 
create a computer edf

set type to dropdown

set to not editable

and paste this into the fill...
```~Select Convert(Concat('Use Location Setting',CHAR(0),0) Using UTF8) UNION Select Convert(Concat(passwords.Title,CHAR(0),passwords.PasswordID) Using UTF8) From passwords WHERE passwords.ClientID IN (0,'%clientid%')|Select user for domain join and automatic software actions|0```

play with that...

that includes static entries populates via union/select, as well as a real sql lookup, including the %clientid%
A word about the values: `Convert(Concat(passwords.Title,CHAR(0),passwords.PasswordID) Using UTF8)` and `Convert(Concat('Use Location Setting',CHAR(0),0) Using UTF8)` demonstrate that each row (value) is a string with some visible text, a NULL, and then a number.

the number is what would be stored in the DB.

If you set the default to `0`, and then, whatever result you returned, if it was 1 row of text with a null char and then a 0, THAT would be the selected/displayed result.

otherwise the EDF would already have to be holding the string that you want displayed, which defeats the purpose because you don't know what to set the EDF to. ;)

instead of returning several rows, just make sure that you return 1, and that it's "value" (after the null) is a predictable value, so that you can set the default to that value.
now, if you change the query to return only 1 row, as long as it has `concat('STRING YOU WANT TO SEE',CHAR(0),0)` then your string=="0", so it will be selected.

and obviously, 'STRING YOU WANT TO SEE' can be a column from a table in your select statement... Like....... A Client EDF value....

Make a query that returns the EDF value for Client EDF 'XYZ', with a hardcoded client id

That query, using `%clientid%`, should be usable.

make sure that the query handles the Client edf not being set (NULL match), and only returns 1 row.

do this in sqlyog

ensure it returns, always and only, 1 row.

wrap the CONVERT(CONCAT( stuff onto it, only when you have the above working right.

and when you have that working right, replace the hardcoded clientid with `%clientid%`

Make sure that you use "0" for the record id (  `concat(RESULT,char(0),0)`  ), even for the case when no result was returned... (Use a left join , and IFNULL)... Then, you need to go to dashboard->config->edf defaults, and set the default to 0.... (Here, since clientid will not have a valid value, your placeholder text should be displayed).

if you don't set the default correctly to 0, then it won't display correctly when you open for the first time on an agent.

(Blank will be selected in the dropdown in that case, which is no good...)