Dynamic list from multi columns

[azaleas]

Is there a way to create a Dynamic list from multiple columns of DB table? i.e ID+Name? Just as a Display value?

[dvanhussel]

Hi azaleas,

You can do this with a custom list. For example, something like this.
This assumes that the user data is fetched into a table called USERS:

Code: Select all

var userTable = tables.USERS;
var users = fields.USERDROPDOWN.createCustomList();

userTable.fetchTable();

var rows = userTable.getRows();
while(rows.next()){
	var usernameDisplay = userTable.ID.value + ' - '+userTable.NAME.value	;
	users.add(usernameDisplay, userTable.ID.value);
}

The list will contain the ID as fieldvalue and a concatenation of the ID and USERNAME as displayvalue. The list is associated with the field USERDROPDOWN.

Regards,

David

[Jon]

Or you could do it by concatenating fields in the select statement of the Dynamic List e.g.

Code: Select all

select concat(name, id) as displaycol

Contatenation is DB specific so you would need to check the exact syntax for your DB.

[azaleas]

Jon, I'm using MySQL and I can't use CONCAT function in Dynamic List. I've tried both Assisted and Native SQL in SQL editor of Dynamic List window. Any ideas? Right now, I'm using the solution with custom list. But it requires additional coding.

[Jon]

MySQL supports the concat function. With assisted SQL, you should have something like this in the Select columns box:

Code: Select all

concat(customer_id, ' ', name) as CUSTOMER_NAME

where customer_id and name are the columns you want to concatenate. Then add a list field named CUSTOMER_NAME.