databasejoin just shows ID in form

[uwesch]

Hi,

I'd like to achieve the following

A user registers via the normal Joomla registration. Now he ist in status Registered. this works already
By filling in more details in a Fabric form he can become a "Requestor".
To become a Requestor the user needs to choose a so called "Sponsor" from a dropdown list.

All users can become a Sponsor but this is done with a manual assignment by the admin via the "sponsors" form.

I have a list/form called "sponsors" with an element (sponsor_name) using databasejoin to take a full_name from a table of existing "Requestors". In total this list only has three elements - date_time, id and sponsor_name (Value = ID , label = full_name).

Another list/form which makes the user a "Requestor" is also using databasejoin and is supposed to show the list of full_names in this form as a dropdown list. Here I used Value = ID and label = sponsor_name

Now the problem is that always only the ID shows up in the dropdown list and i cant show the sponsor_name.

Where is the problem in your eyes?

 

[troester]

Another list/form which makes the user a "Requestor" is also using databasejoin

To which list/table? "sponsors" or "Requestors"?

Database join element

The database join element is an extremely powerful element. It allows you to look up data in another database table and to pre-populate a radio list or drop down list with the data returned from the look up. For example, you could easily make a drop-down list containing all of your Joomla...

fabrikar.com

 

[uwesch]

To the sponsors

 

[troester]

So you have to use the concat label to fetch the original label, see the WIKI link.

 

[uwesch]

just tried concat and used the example form the wiki

'test-',{thistable}.sponsor_name

the result in the drop down list is "test-430", "test-431" etc. where 430 and 431 are the ID's

 

[troester]

Database join element

The database join element is an extremely powerful element. It allows you to look up data in another database table and to pre-populate a radio list or drop down list with the data returned from the look up. For example, you could easily make a drop-down list containing all of your Joomla...

fabrikar.com

So
(SELECT s.sponsor_name FROM sponsors s WHERE s.id = {thistable}.sponsor_name)

 

[uwesch]

thanks for the code. it now shows no ID's anymore in the drop down list but blank lines.

 

[troester]

I'm not sure about your setup.
Which elements of which type in which form are exactly pointing to where?
Do you have multiple Fabrik connections?
You can't crossjoin.

 

[uwesch]

table Requestors (user_profile) has elements of following types
jdate
internalid
field
password
calc which combines first_name and last_name from the same table to full_name
yesno to block user
yesno for forced password reset
date
birthday
databasejoin to a table that contains nationalities
databasejoin to joomla_users value = ID , label = ID
databasejoin to sponsors element = sponsor; value = ID ; concat label "(SELECT s.sponsor_name FROM sponsors s WHERE s.id = {thistable}.sponsor_name)"

table sponsors (sponsors) has elements of following type
jdate
internalid
field
databasejoin to Requestors (user_profile) element = sponsor_name; value = ID , Label = full_name


now that I type this I realized that it could be a crossjoin indeed if user_profile has a database join to sponsors and sponsors a databasejoin to user_profile ... Do you think this is the reason for blank fields?