Dynamic Valuelists, more than 0ne Column selected

[sym]

I would like to combine 2 tables:

table1: [EMPLOYEES_BUSINESS]
EMPLOYEE_ID ; EMPLOYEE_FIRSTNAME ; EMPLOYEE_MIDDLENAME ; EMPLOYEE_LASTNAME ; EMPLOYEE_INITIALS


table2: [EMPLOYEES_GROUPS]
EMPLOYEE_ID ; GROUP_ID


In valuelists.ini of [EMPLOYEES_GROUPS] i tried:

[EMPLOYEES_BUSINESS]
__sql__ = "SELECT EMPLOYEE_ID, EMPLOYEE_LASTNAME, EMPLOYEE_MIDDLENAME, EMPLOYEE_FIRSTNAME FROM EMPLOYEES_BUSINESS ORDER BY EMPLOYEE_LASTNAME"

I see that only the first parameter (EMPLOYEE_LASTNAME) in the table displayed.
Is it possible to show more then 1 column (or 1 column with the data of more then 1 columns) ?

[njw]

This post earlier may help - http://framework.weblite.ca/forum/dataface-users/35

[shannah]

Thanks for digging up that post, Neil. The key here is to do the combination in MySQL rather than in dataface. Valuelists expect the first column to be the id and the 2nd column to be the value displayed. It ignores the third, fourth, etc.. columns. However, MySQL has a nifty CONCAT function the can concatenate multiple columns together into one...
so:

[EMPLOYEES_BUSINESS]
__sql__ = "SELECT EMPLOYEE_ID, CONCAT(EMPLOYEE_LASTNAME, ',',EMPLOYEE_MIDDLENAME,',', EMPLOYEE_FIRSTNAME) as FullName FROM EMPLOYEES_BUSINESS ORDER BY EMPLOYEE_LASTNAME"

This should get the desired results.

[sym]

Steve,

Thanks for the perfect answer!

I tried to make a web-based program to update my database, last week.
Normaly i programmed in PHP/javascript/mysql a user interface to show/update the tables.

Dataface is a universel toolbox to do this in a minimum of time, and whithout real programming and Dataface is more flexible: changing the database and the application is also updated.

You made a wonderfool tool!
Thanks you so much.

Greetings,
Nico Sijm

[shannah]

Hi Nico,

Glad you found it to be helpful. Thanks for the glowing review

-Steve