1 Topic by dbk (edited by dbk 2019-06-06 20:50:13)

  • dbk
  • Member
  • Offline
  • Registered: 2016-04-26
  • Posts: 119

Topic: ComboBox values from SQL "if"

Hello friends,


Here i am again smile sorry


I'm trying to fill a combobox with values from a table called "clients", however, i would like this combobox to show the value of "name" IF the client type is "private" but if the client type is "company" the value listed in the combobox should be "companyname".


I tried to set the combobox properties to {name} {companyname} so they show up both, but it's a bit messy and it would look much nicer if i got it working.


So, in sort of "psuedo code" i am trying something in the lines of the following:


frmAddInfo.ComboBox2.ItemIndex := SQLExecute('SELECT name FROM client WHERE id_client.type = "private''');

But obviously this isn't working, i'm at the wrong end again. If someone could help or guide me in the right direction it would be a great help!
Thanks!

2 Reply by derek (edited by derek 2019-06-06 23:19:24)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,677

Re: ComboBox values from SQL "if"

Hi DBK,
I'd try it using a calculated field (see attached).
I've added a (C) or (P) to indicate whether the name in the combobox is Company or Private - this also means that you can easily sort the drop-down list by client type, if required.
Is there a need to have 2 separate fields for 'private name' and 'company name' since you already know from the 'type' which it is? - just a thought.
Hope this helps,
Derek.

Post's attachments

Attachment icon dbkcombobox.zip 337.33 kb, 323 downloads since 2019-06-07 

  • dbk
  • Member
  • Offline
  • Registered: 2016-04-26
  • Posts: 119

Re: ComboBox values from SQL "if"

derek wrote:

Hi DBK,
I'd try it using a calculated field (see attached).
I've added a (C) or (P) to indicate whether the name in the combobox is Company or Private - this also means that you can easily sort the drop-down list by client type, if required.
Is there a need to have 2 separate fields for 'private name' and 'company name' since you already know from the 'type' which it is? - just a thought.
Hope this helps,
Derek.

Hi Derek!

Thank you so much, again!! This is exactly what i was trying to achieve. Again, very clever using the calculated field! However i couldn't possibly come up with this syntax smile

4 Reply by derek (edited by derek 2019-06-07 13:43:11)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,677

Re: ComboBox values from SQL "if"

Hi DBK,
The calculated field in your example is, at its core, like this:
(case
when id_type = 1 then name
when id_type = 2 then company     
end)   
The other elements are simply to add the text literals '(C) ' and '(P) ' and the || is the way in which you join two or more fields (or a text literal and a field etc etc) together when using calculated fields.
I guess it's like any syntax rules ........ you simply get used to them after a while!
Derek.