multiple dbjoin dropdown elements in a form

[qqwwong]

My application has two forms. The first form, named person, has three elements: id, surname, givenname (among other elements). The second form, named team, has the following (among other elements):

1) an element named leader which is a databasejoin to the person table rendered as dropdown.
2) an element named member which is also a databasejoin to the person form table rendered as dropdown.

Both of these has the id as the value and a concat label. The label for the first element is
person.surname,', ',person.givenname. If I put the same concat label in the second element, I got an error:

Unknown column 'person.surname' in 'field list' SQL=SELECT DISTINCT(`person_0`.`id`) AS value, CONCAT(person.surname,', ',person.givenname) AS text FROM `person` AS `person_0` ORDER BY text ASC

It works if I put the concat label without the table name like this:
surname,', ',givenname

With this setting, I can fill in and submit the team form. But after having submitted a few copies of the form. I wanted to look at the data. Then, I got error saying surname in ... is ambiguous.

Can anyone help to fix this? Thank you in advance.

Wai

 

[Jaanus]

Use {thistable} instead of table name, this is recommended:
{thistable}.surname,', ',{thistable}.givenname