Database Join element of another Database Join element

[Bren]

Hello all.
I'm putting together another site and I added a Database Join element that pointed to another Database Join element; but the label only shows the "value" (ID) of the database join and not the "label" I have in the settings. Any suggestions?
Thanks in advance.

 

[troester]

http://fabrikar.com/forums/index.php?wiki/database-join-element/#or-concat-label-examples

 

[Bren]

Thank you, Troester; but I had no problem using the "Or Concat label" on the 1st Database Join element. It's the 2nd Database Join element the references the 1st Database Join element that only lists the ID. Any other suggestions? Thank in advance.

 

[troester]

It's for the 2. dbjoin element

If the label of your dbjoin is a dbjoin value itself get it's label with
Code (Text):
(SELECT name FROM table2 WHERE table2.id = {thistable}.element)

 

[Bren]

Ah, yes. My bad. I didn't notice that. I'll try it. Thanks Troester.

 

[Bren]

I've been trying this and only receiving errors. Maybe I'm misunderstanding what to exactly input into the "Or Concat label"...
(SELECT customer FROM bw_vehicles WHERE bw_vehicles.id = {thistable}.element)
"customer" is the element / table field name of the child DBjoin that this parent DBjoin element is referencing. The "bw_vehicles" list / table where the child DBjoin is a part of. Am I making a mistake here that's causing the errors?
Thanks in advance.

 

[Bren]

Friendly bump
Thanks in advance

 

[cheesegrits]

Replace 'element' with the field name you are joining to, which contains the vehicle ID you need to look up.

-- hugh

 

[Bren]

Hi Hugh.
So, I tried your suggestion with the following.
(SELECT customer FROM bw_vehicles WHERE bw_vehicles.id = {thistable}.id)
Unfortunately, when I test it out I still only see the id in the drop down and not the Customer name (see the attached). Any suggestions?
Thanks in advance.

 

[cheesegrits]

No, not 'id'. The element on that table that has the foreign key.

Sent from my HTC6545LVW using Tapatalk

 

[Bren]

Hi Hugh.
I didn't actually manually add a foreign key to this table, yet. Should I?
I thought that I was trying to get a DBjoin element to just list the in it's drop down list combined data from another DBjoin drop down list...
Thanks in advance.

 

[cheesegrits]

The field which is join element on the other table you are trying to get a label for.

Sent from my HTC6545LVW using Tapatalk

 

[Bren]

Hi Hugh.
OK. I think I understand what your saying. I decided to update the elements field name to make it a bit easier to understand as well. I then updated the "Or Concat label" to the following.
(SELECT customer_first_last FROM bw_vehicles WHERE bw_vehicles.vehicle_id = {thistable}.customer_first_last)
Unfortunately, I still have the same attached result of just seeing the related ID number in the dropdown list. Also attached is a snippet of the DBjoins data settings. Maybe I should share that the customer_first_last DBjoin also has a "Or Concat label" of the following; which displays its label properly on its dropdown.
bw_customers.first_name,' ',bw_customers.last_name
I don't know if this really a reason or not for why the Customers Vehicle DBjoin is only displaying the customer_first_last ID... Any other suggestions?
Thanks in advance.

 

[Bren]

Friendly bump
Thanks in advance

 

[cheesegrits]

I've lost track of your exact setup, but here';s the generic answer:

Table 1:
customer: join to table2.id

Table 2:
id
t2name: join to table3.id

Table3:
id:
t3name


So in table1, in the 'customer' join, in the "Or CONCAT":

SELECT t3name FROM table3 WHERE id = '{thistable}.t2name'

-- hugh

 

[Bren]

Hi Hugh.
So, are quotes ' ' around everything after equals = required?
What about parentheses ( ) around everything; as the Wiki shows?
Thanks in advance.

 

[cheesegrits]

I can't recall offhand if you have to put parens round it - I think we'll do that if you don't, if you select 'query' for type.

Actually, my example is wrong, it shouldn't have quotes around the {thistable}.t2name. That's table and field names, so either leave them unquoted, or do backticks around both parts, `{thistable}`.`t2name`

In MySQL, table and field names are either unquoted, or use `backticks`, data is either unquoted (numbers) or 'single' or "double" quoted (strings).

-- hugh

 

[Bren]

Success! I finally both wrapped my brain around this and succeeded in the drop down listing what I expected.
Thanks again for all your help, Hugh.

 

[cheesegrits]

Yay! Glad you got it working.

It's not an easy thing to explain.

-- hugh