Cascading Dropdown shows value and not label

Status
Not open for further replies.
jeanl

jeanl

Member
Hi

I have an element in a table, which is a dropdown with 17 values and their corresponding labels.

On a second table, I use a CDD which should return the label of the dropdown element, but it only shows the value. Is there any way to change this so that the CDD returns the label?

Jean
 
Hello

I thought I could get this working, by adding the following in the CONCAT label field so that the label is displayed and not the value:

(SELECT unit FROM stock_control WHERE stock_control___id = {purchases}.purchases_58_repeat___unit_of_measure)

However, I get this error, and hope someone could assist me with this error?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '}.purchases_58_repeat___unit_of_measure)) AS `purchases_58_repeat___unit_of_meas' at line 20 SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `purchases`.`id` AS `purchases___id`,
`purchases`.`id` AS `purchases___id_raw`,
`purchases`.`date_time` AS `purchases___date_time`,
`purchases`.`date_time` AS `purchases___date_time_raw`,
`purchases`.`order_no` AS `purchases___order_no`,
`purchases`.`order_no` AS `purchases___order_no_raw`,
`purchases`.`supplier` AS `purchases___supplier_raw`,
`suppliers`.`supplier_name` AS `purchases___supplier`,
`purchases`.`supplier_invoice_no` AS `purchases___supplier_invoice_no`,
`purchases`.`supplier_invoice_no` AS `purchases___supplier_invoice_no_raw`,
`purchases_58_repeat`.`parent_id` AS `purchases_58_repeat___parent_id`,
`purchases_58_repeat`.`parent_id` AS `purchases_58_repeat___parent_id_raw`,
`purchases_58_repeat`.`id` AS `purchases_58_repeat___id`,
`purchases_58_repeat`.`id` AS `purchases_58_repeat___id_raw`,
`purchases_58_repeat`.`product` AS `purchases_58_repeat___product_raw`,
`stock_control`.`product` AS `purchases_58_repeat___product`,
`purchases_58_repeat`.`code` AS `purchases_58_repeat___code_raw`,
`stock_control_0`.`product_code` AS `purchases_58_repeat___code`,
`purchases_58_repeat`.`unit_of_measure` AS `purchases_58_repeat___unit_of_measure_raw`,
CONCAT_WS('', (SELECT unit FROM stock_control WHERE stock_control___id = {purchases}.purchases_58_repeat___unit_of_measure)) AS `purchases_58_repeat___unit_of_measure`,
`purchases_58_repeat`.`order_qty` AS `purchases_58_repeat___order_qty`,
`purchases_58_repeat`.`order_qty` AS `purchases_58_repeat___order_qty_raw`,
`purchases_58_repeat`.`cost` AS `purchases_58_repeat___cost`,
`purchases_58_repeat`.`cost` AS `purchases_58_repeat___cost_raw`,
`purchases`.`id` AS slug
, `purchases`.`id` AS `__pk_val`

FROM `purchases`
LEFT JOIN `suppliers` AS `suppliers` ON `suppliers`.`id` = `purchases`.`supplier`
LEFT JOIN `purchases_58_repeat` AS `purchases_58_repeat` ON `purchases_58_repeat`.`parent_id` = `purchases`.`id`
LEFT JOIN `stock_control` AS `stock_control` ON `stock_control`.`id` = `purchases_58_repeat`.`product`
LEFT JOIN `stock_control` AS `stock_control_0` ON `stock_control_0`.`id` = `purchases_58_repeat`.`code`
LEFT JOIN `stock_control` AS `stock_control_1` ON `stock_control_1`.`id` = `purchases_58_repeat`.`unit_of_measure` LIMIT 0, 10
 
Use {thistable}, literally, don't replace it with anything.

This is a special placeholder used in join WHERE and CONCAT's, which we replace with the table alias we are using for the join.

-- hugh
 
Hugh

Thank you. I changed the entry to this:

(SELECT unit FROM stock_control WHERE stock_control___id = {thistable}.purchases_58_repeat___unit_of_measure)

But I still get this error:

Unknown column 'stock_control___id' in 'where clause' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `purchases`.`id` AS `purchases___id`,
`purchases`.`id` AS `purchases___id_raw`,
`purchases`.`date_time` AS `purchases___date_time`,
`purchases`.`date_time` AS `purchases___date_time_raw`,
`purchases`.`order_no` AS `purchases___order_no`,
`purchases`.`order_no` AS `purchases___order_no_raw`,
`purchases`.`supplier` AS `purchases___supplier_raw`,
`suppliers`.`supplier_name` AS `purchases___supplier`,
`purchases`.`supplier_invoice_no` AS `purchases___supplier_invoice_no`,
`purchases`.`supplier_invoice_no` AS `purchases___supplier_invoice_no_raw`,
`purchases_58_repeat`.`parent_id` AS `purchases_58_repeat___parent_id`,
`purchases_58_repeat`.`parent_id` AS `purchases_58_repeat___parent_id_raw`,
`purchases_58_repeat`.`id` AS `purchases_58_repeat___id`,
`purchases_58_repeat`.`id` AS `purchases_58_repeat___id_raw`,
`purchases_58_repeat`.`product` AS `purchases_58_repeat___product_raw`,
`stock_control`.`product` AS `purchases_58_repeat___product`,
`purchases_58_repeat`.`code` AS `purchases_58_repeat___code_raw`,
`stock_control_0`.`product_code` AS `purchases_58_repeat___code`,
`purchases_58_repeat`.`unit_of_measure` AS `purchases_58_repeat___unit_of_measure_raw`,
CONCAT_WS('', (SELECT unit FROM stock_control WHERE stock_control___id = stock_control_1.purchases_58_repeat___unit_of_measure)) AS `purchases_58_repeat___unit_of_measure`,
`purchases_58_repeat`.`order_qty` AS `purchases_58_repeat___order_qty`,
`purchases_58_repeat`.`order_qty` AS `purchases_58_repeat___order_qty_raw`,
`purchases_58_repeat`.`cost` AS `purchases_58_repeat___cost`,
`purchases_58_repeat`.`cost` AS `purchases_58_repeat___cost_raw`,
`purchases`.`id` AS slug
, `purchases`.`id` AS `__pk_val`

FROM `purchases`
LEFT JOIN `suppliers` AS `suppliers` ON `suppliers`.`id` = `purchases`.`supplier`
LEFT JOIN `purchases_58_repeat` AS `purchases_58_repeat` ON `purchases_58_repeat`.`parent_id` = `purchases`.`id`
LEFT JOIN `stock_control` AS `stock_control` ON `stock_control`.`id` = `purchases_58_repeat`.`product`
LEFT JOIN `stock_control` AS `stock_control_0` ON `stock_control_0`.`id` = `purchases_58_repeat`.`code`
LEFT JOIN `stock_control` AS `stock_control_1` ON `stock_control_1`.`id` = `purchases_58_repeat`.`unit_of_measure` LIMIT 0, 10
 
"stock_control___id" is a Fabrik full element name.
The concat is SQL syntax, you have to use column names (i.e. only "id" in this case)
 
Troester

Thank you. I will just admit that I am not too familiar with exactly how this works. Maybe a detailed explanation will help.

I have a table with these elements:

upload_2016-9-19_18-29-20.png

I also have a second table with these elements:

upload_2016-9-19_18-30-4.png

So what I need, is that in the form for this table, when I enter a product, the unit of measure is filled in as CDD from the 1st table. The CDD works, but it shows the value and not the label.

So I changed the code now to this:
(SELECT unit FROM stock_control WHERE id = {thistable}.purchases_58_repeat___unit_of_measure)

And I get this when I open the form:

Unknown column 'stock_control_1.purchases_58_repeat___unit_of_measure' in 'where clause' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `purchases`.`id` AS `purchases___id`,
`purchases`.`id` AS `purchases___id_raw`,
`purchases`.`date_time` AS `purchases___date_time`,
`purchases`.`date_time` AS `purchases___date_time_raw`,
`purchases`.`order_no` AS `purchases___order_no`,
`purchases`.`order_no` AS `purchases___order_no_raw`,
`purchases`.`supplier` AS `purchases___supplier_raw`,
`suppliers`.`supplier_name` AS `purchases___supplier`,
`purchases`.`supplier_invoice_no` AS `purchases___supplier_invoice_no`,
`purchases`.`supplier_invoice_no` AS `purchases___supplier_invoice_no_raw`,
`purchases_58_repeat`.`parent_id` AS `purchases_58_repeat___parent_id`,
`purchases_58_repeat`.`parent_id` AS `purchases_58_repeat___parent_id_raw`,
`purchases_58_repeat`.`id` AS `purchases_58_repeat___id`,
`purchases_58_repeat`.`id` AS `purchases_58_repeat___id_raw`,
`purchases_58_repeat`.`product` AS `purchases_58_repeat___product_raw`,
`stock_control`.`product` AS `purchases_58_repeat___product`,
`purchases_58_repeat`.`code` AS `purchases_58_repeat___code_raw`,
`stock_control_0`.`product_code` AS `purchases_58_repeat___code`,
`purchases_58_repeat`.`unit_of_measure` AS `purchases_58_repeat___unit_of_measure_raw`,
CONCAT_WS('', (SELECT unit FROM stock_control WHERE id = stock_control_1.purchases_58_repeat___unit_of_measure)) AS `purchases_58_repeat___unit_of_measure`,
`purchases_58_repeat`.`order_qty` AS `purchases_58_repeat___order_qty`,
`purchases_58_repeat`.`order_qty` AS `purchases_58_repeat___order_qty_raw`,
`purchases_58_repeat`.`cost` AS `purchases_58_repeat___cost`,
`purchases_58_repeat`.`cost` AS `purchases_58_repeat___cost_raw`,
`purchases`.`id` AS slug
, `purchases`.`id` AS `__pk_val`

FROM `purchases`
LEFT JOIN `suppliers` AS `suppliers` ON `suppliers`.`id` = `purchases`.`supplier`
LEFT JOIN `purchases_58_repeat` AS `purchases_58_repeat` ON `purchases_58_repeat`.`parent_id` = `purchases`.`id`
LEFT JOIN `stock_control` AS `stock_control` ON `stock_control`.`id` = `purchases_58_repeat`.`product`
LEFT JOIN `stock_control` AS `stock_control_0` ON `stock_control_0`.`id` = `purchases_58_repeat`.`code`
LEFT JOIN `stock_control` AS `stock_control_1` ON `stock_control_1`.`id` = `purchases_58_repeat`.`unit_of_measure` LIMIT 0, 10
 
(SELECT unit FROM stock_control WHERE id = {thistable}.unit_of_measure)
Click to expand...

In Fabrik, "full" element names are the table field name, prepended with the table name, so tablename___fieldname. That's so we don't get name clashes when joining rows from other tables. Those fill names are what you use in "placeholders".

When you are creating queries and specifying table fields, don't use the tablename___ part. Just the fieldname. So in this case, 'unit_of_measure'.

-- hugh
 
Hugh

Thank you. I used this code:

(SELECT unit FROM stock_control WHERE id = {thistable}.unit_of_measure)

And still get this error message:

Unknown column 'stock_control_1.unit_of_measure' in 'where clause' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `purchases`.`id` AS `purchases___id`,
`purchases`.`id` AS `purchases___id_raw`,
`purchases`.`date_time` AS `purchases___date_time`,
`purchases`.`date_time` AS `purchases___date_time_raw`,
`purchases`.`order_no` AS `purchases___order_no`,
`purchases`.`order_no` AS `purchases___order_no_raw`,
`purchases`.`supplier` AS `purchases___supplier_raw`,
`suppliers`.`supplier_name` AS `purchases___supplier`,
`purchases`.`supplier_invoice_no` AS `purchases___supplier_invoice_no`,
`purchases`.`supplier_invoice_no` AS `purchases___supplier_invoice_no_raw`,
`purchases_58_repeat`.`parent_id` AS `purchases_58_repeat___parent_id`,
`purchases_58_repeat`.`parent_id` AS `purchases_58_repeat___parent_id_raw`,
`purchases_58_repeat`.`id` AS `purchases_58_repeat___id`,
`purchases_58_repeat`.`id` AS `purchases_58_repeat___id_raw`,
`purchases_58_repeat`.`product` AS `purchases_58_repeat___product_raw`,
`stock_control`.`product` AS `purchases_58_repeat___product`,
`purchases_58_repeat`.`code` AS `purchases_58_repeat___code_raw`,
`stock_control_0`.`product_code` AS `purchases_58_repeat___code`,
`purchases_58_repeat`.`unit_of_measure` AS `purchases_58_repeat___unit_of_measure_raw`,
CONCAT_WS('', (SELECT unit FROM stock_control WHERE id = stock_control_1.unit_of_measure)) AS `purchases_58_repeat___unit_of_measure`,
`purchases_58_repeat`.`order_qty` AS `purchases_58_repeat___order_qty`,
`purchases_58_repeat`.`order_qty` AS `purchases_58_repeat___order_qty_raw`,
`purchases_58_repeat`.`cost` AS `purchases_58_repeat___cost`,
`purchases_58_repeat`.`cost` AS `purchases_58_repeat___cost_raw`,
`purchases`.`id` AS slug
, `purchases`.`id` AS `__pk_val`

FROM `purchases`
LEFT JOIN `suppliers` AS `suppliers` ON `suppliers`.`id` = `purchases`.`supplier`
LEFT JOIN `purchases_58_repeat` AS `purchases_58_repeat` ON `purchases_58_repeat`.`parent_id` = `purchases`.`id`
LEFT JOIN `stock_control` AS `stock_control` ON `stock_control`.`id` = `purchases_58_repeat`.`product`
LEFT JOIN `stock_control` AS `stock_control_0` ON `stock_control_0`.`id` = `purchases_58_repeat`.`code`
LEFT JOIN `stock_control` AS `stock_control_1` ON `stock_control_1`.`id` = `purchases_58_repeat`.`unit_of_measure` LIMIT 0, 10

Not sure about this though:
Unknown column 'stock_control_1.unit_of_measure' in 'where clause' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `purchases`.`id` AS
 
I don't get your setup, there should be a 3rd table.

Your dbjoin is which element, using which table, value, label?
Your CDD is which element (full element name)? using which list, id, "label"?
 
Troester

I only have two tables:

stock_control
purchases

In purchases one of the elements is unit_of_measure, which is a CDD that shows the unit of measure (e.g. each/set/litre) from the stock_control list, depending on the product you choose.
 
Troester, here are the elements in each table:

upload_2016-9-19_21-26-34.png upload_2016-9-19_21-27-9.png

So when I select a supplier on the purchase order, I get a list of all products from said supplier, and it then also populates the correct unit of measure, but I see the value and not the label. I am trying to change this so I see the actual unit of measure's label, so not "1" or "2", but "each" or "litre".
 
Hugh

Here is the code:
upload_2016-9-19_22-34-58.png

And here is the result:
upload_2016-9-19_22-37-52.png
 

Attachments

  • upload_2016-9-19_22-35-24.png
    upload_2016-9-19_22-35-24.png
    19.4 KB · Views: 118
Oh, I just noticed your 'unit' on the stock_control table is a dropdown, not a join. I was thinking it was a join.

Yeah, I don't think that's going to work. Dropdown labels aren't stored anywhere you can get at them. They are only stored in the params for the element in the #__fabrik_elements table.

In order to make this work you'd have to store the units in a separate table, like units_of_measure.

I just logged on to your site and see that you do have a units_of_measure table ... ?

-- hugh
 
Hugh. Thank you. Yes I have a table for units which ai pick from when creating a stock item in stock_control. So I suppose I should have some sort of reference that comes from the Unit of Measure table?


Sent from my iPhone using Tapatalk
 
Last edited:
But the 'unit' element on your stock control table is a dropdown, not a join. It's not using that units_of_measure table at all.

The 'unit' element needs to be a database join element, which joins to the units_of_measurement table.

Do the values in your dropdown match the id's of the corresponding rows in the units_of_measurement table? If so, you can just change the 'unit' element type and set it up as a join, and it'll have the same measurement. If not, you'll need to modify any existing data you have to change the unit value to the correct entry in the table.

Once you've done that, you then be able to use the "CONCAT label" technique to get the label in your other table.

-- hugh
 
Hugh

OK, the unit of measure in stock_control is now a dbjoin from unit_of_measure table, and in stock_control my entries are still correct.

upload_2016-9-20_20-56-2.png

Now in "purchase_order" I added this to the CONCAT label:

(SELECT unit FROM stock_control WHERE id = {thistable}.unit_of_measure)

And I get this error:

Unknown column 'stock_control_1.purchases_58_repeat___unit_of_measure' in 'where clause' SQL=SELECT SQL_CALC_FOUND_ROWS DISTINCT `purchases`.`id` AS `purchases___id`,
`purchases`.`id` AS `purchases___id_raw`,
`purchases`.`date_time` AS `purchases___date_time`,
`purchases`.`date_time` AS `purchases___date_time_raw`,
`purchases`.`order_no` AS `purchases___order_no`,
`purchases`.`order_no` AS `purchases___order_no_raw`,
`purchases`.`supplier` AS `purchases___supplier_raw`,
`suppliers`.`supplier_name` AS `purchases___supplier`,
`purchases`.`supplier_invoice_no` AS `purchases___supplier_invoice_no`,
`purchases`.`supplier_invoice_no` AS `purchases___supplier_invoice_no_raw`,
`purchases_58_repeat`.`parent_id` AS `purchases_58_repeat___parent_id`,
`purchases_58_repeat`.`parent_id` AS `purchases_58_repeat___parent_id_raw`,
`purchases_58_repeat`.`id` AS `purchases_58_repeat___id`,
`purchases_58_repeat`.`id` AS `purchases_58_repeat___id_raw`,
`purchases_58_repeat`.`product` AS `purchases_58_repeat___product_raw`,
`stock_control`.`product` AS `purchases_58_repeat___product`,
`purchases_58_repeat`.`code` AS `purchases_58_repeat___code_raw`,
`stock_control_0`.`product_code` AS `purchases_58_repeat___code`,
`purchases_58_repeat`.`unit_of_measure` AS `purchases_58_repeat___unit_of_measure_raw`,
CONCAT_WS('', (SELECT unit FROM stock_control WHERE id = stock_control_1.purchases_58_repeat___unit_of_measure)) AS `purchases_58_repeat___unit_of_measure`,
`purchases_58_repeat`.`order_qty` AS `purchases_58_repeat___order_qty`,
`purchases_58_repeat`.`order_qty` AS `purchases_58_repeat___order_qty_raw`,
`purchases_58_repeat`.`cost` AS `purchases_58_repeat___cost`,
`purchases_58_repeat`.`cost` AS `purchases_58_repeat___cost_raw`,
`purchases`.`id` AS slug
, `purchases`.`id` AS `__pk_val`

FROM `purchases`
LEFT JOIN `suppliers` AS `suppliers` ON `suppliers`.`id` = `purchases`.`supplier`
LEFT JOIN `purchases_58_repeat` AS `purchases_58_repeat` ON `purchases_58_repeat`.`parent_id` = `purchases`.`id`
LEFT JOIN `stock_control` AS `stock_control` ON `stock_control`.`id` = `purchases_58_repeat`.`product`
LEFT JOIN `stock_control` AS `stock_control_0` ON `stock_control_0`.`id` = `purchases_58_repeat`.`code`
LEFT JOIN `stock_control` AS `stock_control_1` ON `stock_control_1`.`id` = `purchases_58_repeat`.`unit_of_measure` LIMIT 0, 10
 
Hugh

Thank you very much for pointing out my error.

To summarise what you pointed out:

First issue was the Foreign Key wasn't set on the "Watch" tab for the CDD. And the CONCAT needed to be:
(SELECT unit_of_measure FROM unit_of_measure WHERE id = {thistable}.unit)
So {thistable}.unit is the 'unit' join on the stock control table.

Thank you for your assistance.

Jean
 
Status
Not open for further replies.

Useful links

  • Create a Fabrik Account (Downloads)
  • Create a Forum Account
  • Fabrik Wiki
  • Fabrik 4 Changelog /Install /Upgrade
    • We are in need of some funding.
    More details.

    Thank you.

    Members online

    No members online now.
    Total: 194 (members: 0, guests: 194)
    Back
    Top