• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Help for cascading dropdown

rodeokid

Member
Hi Fabrik guru, i am pretty sure this is easy as hell but maybe i am too tired, i just can't figured how to do this easy thing:

i have 1 table with 2 column that i would like to have as choice in my cascading dropdown element.

So element to check is called codeproduit, i use id for identification, and column1 for label. All is fine
When element codeproduit changes, it looks for the value of column1, and give it to me in the dropdown... but i would like to have column2 too in this dropdown...
So can someone explain to me how to format the query in the where section to be able to add column2 as antoher choice in the dropdown menu, so in frontend, the user will be given the choice between column1 and column2 in the dropdown ?

Thanks in advance !

rodeokid
 
I'm not really sure what you mean.

Do you just want to display both column1 and column2 in the label?

You can't really have column2 as "another choice in the dropdown", such that you have two options referring to the same row.

-- hugh
 
Ok i'll try to explain better: i have a form where we have to enter different options for a SKU... so let's say bottle A can have a cap Option1 or a cap Option2... so in my listesku table, this skucode will have a column option1 with the definition of this option, and a column option2 with the definition of this option...
Now in the form that employees will fill when they produce bottles, they have to choose in a dropdown which option from 1 or 2 will be used for their batch. So that's why i need to be able to have column option1 and column option2 in my dropdown.

Thanks for your help...

rodeokid
 
OK, then no ... you can't.

When you join to another table, you do so using the PK (primary key) of the table. So your sku table might be ...

id, sku, description
1, ABC123, A bottle with a cap

In the form where you link to this, you record the 'id' (1), in a join element called (say) sku_id, uniquely identifying the row for SKU ABC123 (A bottle with a cap)

What you seem to be describing is a SKU table that looks like ...

id, sku, description, option1, option2
1, ABC123, A bottle with a cap, red cap, bue cap

... which won't work, because there's no way of differentiating the option for id 1.

What a SKU table would usually look like (kinda) would be more like ...

id, sku, description, option
1, ABC123, A bottle with a cap, red cap
2, ABC124, A bottle with a cap, blue cap

So id 1 (SKU ABC123) is bottle with red cap, and id 2 (ABC124) is bottle with blue cap.

Although usually you wou'd have a separate 'products' table ...

id, description
1, bottle
2, red cap
3, blue cap

... and SKU table that links to it ...

id, SKU product_id, option_id
1, ABC123, 1, 2
2, ABC124, 1, 3

... or maybe a separate 'options' table, depending how your product inventory works.

Without knowing a lot more about your work flow and inventory handling, I can't really tell you exactly how to do it.

-- hugh
 
Ok problem solved, here's what i did... i use the normal dropdown element, and in advanced tab i use this code that gives me exactly what i need:

$options[] = JHTML::_('select.option', '0', 'Veuillez choisir' );
$db = JFactory::getDbo();
$skuid='{rapport_production_salles_blanches___codeproduit}';
$db->setQuery("SELECT bouchonsoption1, bouchonsoption2 FROM listesku WHERE listesku.skucode='$skuid'");
$rows = $db -> loadObjectList();
foreach ($rows as $row) {
$options[] = JHTML::_('select.option', $row->bouchonsoption1, $row->bouchonsoption1);
$options[] = JHTML::_('select.option', $row->bouchonsoption2, $row->bouchonsoption2);
}
return $options;


The only problem is that there are no ajax option on dropdown, so employee have to press the Apply button to have the right dropdown choices, but this is what i needed. If you have a way to get Ajax call on this dropdown element, i would be very happy !

Thanks.

rodeokid
 
Last edited:
There isn't a way.

Honestly, you are setting yourself up for problems trying to do it this way, without normalizing your data.

Are you having to deal with legacy data that has this multiple options in the SKU table? It seems to violate the entire purposes of having SKU's, which by definition are supposed to uniquely identify different configurations of a product, so "bottle + green cap" should be a different SKU to "bottle + blue cap".

And by not storing the unique FK of the selected option (and using the text of an option instead) you are pretty much guarranteed to run into problems with your data in the future. For example, if someone edits the SKU table to change a spelling mistake in an option name, you will no longer be able to resolve existing uses of that option to a SKU.

-- hugh
 
There isn't a way.

Honestly, you are setting yourself up for problems trying to do it this way, without normalizing your data.

Are you having to deal with legacy data that has this multiple options in the SKU table? It seems to violate the entire purposes of having SKU's, which by definition are supposed to uniquely identify different configurations of a product, so "bottle + green cap" should be a different SKU to "bottle + blue cap".

And by not storing the unique FK of the selected option (and using the text of an option instead) you are pretty much guarranteed to run into problems with your data in the future. For example, if someone edits the SKU table to change a spelling mistake in an option name, you will no longer be able to resolve existing uses of that option to a SKU.

-- hugh


I know what is a SKU, thanks... and each product we sell have it's own SKU, but we use raw materials from different suppliers, so let's say bottle 123 could be made with capA coming from supplierA, or it could be made with capB coming from supplierB... we will not make a new SKU for each configuration, because all of our bottles could have a lot of possibilities. But supplierA and supplierB provides the same cap, we just buy from differents suppliers based on actual resin price or other reasons.
So when employees actually produce some bottles, they have to tell us which cap they use, because in the eventuality of a recall from 1 supplier, i need to know which production has been made with this cap.

So we have a raw materials list for each unique items we buy... and in our SKU list, for each product, you can have option1 coming from supplierA or option2 coming from supplierB, but those are exactly the same cap, same color, same shape... so my bottle 123 with the capA coming from supplierA will have the same SKU as bottle 123 with capB coming from supplierB... you understand ? It's just not possible to have different SKU for the same product just because we change the cap's supplier...
And for spelling mistake that could be change in future, i can live with that easily... the raw materials list is locked from any change of any kind for any staff members. So to make even a little typo change, they will have to send a request to do so, and we will then change it and run a search and replace string command on database to change all the references to it.

So now... any idea how to change structure to achieve this easily ? Or any idea how can i have an ajax call on this dropown i did ?

Thanks

rodeokid
 
OK so now i did change the structure to have the id of SKU, so now no matter how many changes are made to the name of the sku, everythings still works like a charm... this is the code i use so far in the Advanced tab of a dropdown element...

$codeproduit='{rapport_production_salles_blanches___codeproduit_raw}';

$db = JFactory::getDbo();
$db->setQuery("SELECT skucode FROM listesku WHERE listesku.id='$codeproduit'");
$skuid = $db->loadResult();

$db = JFactory::getDbo();
$db->setQuery("SELECT bouchonsoption1 FROM listesku WHERE listesku.skucode='$skuid'");
$rows1 = $db->loadResult();

$db = JFactory::getDbo();
$db->setQuery("SELECT bouchonsoption2 FROM listesku WHERE listesku.skucode='$skuid'");
$rows2 = $db->loadResult();

$db = JFactory::getDbo();
$db->setQuery("SELECT CONCAT(codempbouchons, ' ',description) FROM liste_mp_bouchons WHERE id='$rows1'");
$name1 = $db->loadResult();


$db = JFactory::getDbo();
$db->setQuery("SELECT CONCAT(codempbouchons, ' ',description) FROM liste_mp_bouchons WHERE id='$rows2'");
$name2 = $db->loadResult();

$options[] = JHTML::_('select.option', '0', 'Veuillez choisir' );
$options[] = JHTML::_('select.option', '1', $name1);
$options[] = JHTML::_('select.option', '2', $name2);

return $options;


i know this code can be optimized to remove a couple of lines, if you have suggestion.... but it's working.... so the only thing missing is the Ajax call to change when employee choose the codeproduit element... so when i load the form, i have option 0, 1, 2...when they hit Apply to save the form once, i have the right options like i want them, and they now reflects the real value in my listsku table... so is their a way to turn them into an Ajax driven options ?

rodeokid
 
OK so now i did change the structure to have the id of SKU, so now no matter how many changes are made to the name of the sku, everythings still works like a charm... this is the code i use so far in the Advanced tab of a dropdown element...

$codeproduit='{rapport_production_salles_blanches___codeproduit_raw}';

$db = JFactory::getDbo();
$db->setQuery("SELECT skucode FROM listesku WHERE listesku.id='$codeproduit'");
$skuid = $db->loadResult();

$db = JFactory::getDbo();
$db->setQuery("SELECT bouchonsoption1 FROM listesku WHERE listesku.skucode='$skuid'");
$rows1 = $db->loadResult();

$db = JFactory::getDbo();
$db->setQuery("SELECT bouchonsoption2 FROM listesku WHERE listesku.skucode='$skuid'");
$rows2 = $db->loadResult();

$db = JFactory::getDbo();
$db->setQuery("SELECT CONCAT(codempbouchons, ' ',description) FROM liste_mp_bouchons WHERE id='$rows1'");
$name1 = $db->loadResult();


$db = JFactory::getDbo();
$db->setQuery("SELECT CONCAT(codempbouchons, ' ',description) FROM liste_mp_bouchons WHERE id='$rows2'");
$name2 = $db->loadResult();

$options[] = JHTML::_('select.option', '0', 'Veuillez choisir' );
$options[] = JHTML::_('select.option', '1', $name1);
$options[] = JHTML::_('select.option', '2', $name2);

return $options;


i know this code can be optimized to remove a couple of lines, if you have suggestion.... but it's working.... so the only thing missing is the Ajax call to change when employee choose the codeproduit element... so when i load the form, i have option 0, 1, 2...when they hit Apply to save the form once, i have the right options like i want them, and they now reflects the real value in my listsku table... so is their a way to turn them into an Ajax driven options ?

rodeokid
Without writing your own custom AJAX handling, there isn't a way.

I'm trying to help you avoid a mistake in database design which I can absolutely guarantee will come back to bite you on your ass in the future. You obviously don't have to take my advice, but keep in mind I've been building databases for people for several decades.

If you don't want different SKUs, then add two extra tables, something like 'options' and 'package', where 'package' defines the product SKU + option.

(note that I'm adding supplier_sku in these examples, you haven't mentioned it, but I assume you have them in you product tables)

suppliers
id, name
1, Acme Bottles
2, Bottle Tops R Us
3, Global Bottle Tops, Inc

products:
id, sku, description, supplier_id, supplier_sku
1, sku123, small bottle, 1, ab1
2, sku124, big bottle, 1, ab2

options
id, name, supplier_id, supplier_sku
1, big bottle top, 2, btru1
2, big bottle top, 3, gbt1
3, small bottle top, 2, btru2
4, small bottle top, 3, gbtr2

packages
id, product_id, option_id
1, 1, 3
2, 1, 4
3, 2, 1
4, 2, 2

So "package" 2 is a "small bottle" from "Acme Bottles", their SKU ab1) with "small bottle top" from "Global Bottle Tops, Inc" (their SKU gbtr2).

So on your form, you have a "Product" join, and a "Package" (or call it "Product Options" or whatever) CDD. They select the product, the package CDD then shows the options for that product (two different tops). You'll need a CONCAT label to grab the option name from the options table.

This is called "normalizing" your data, and is an absolutely essential part of database design.

In your example of needing to do a product recall, a simple query will find all "packages" that use the affected option_id, given a supplier SKU.

It is also future proofed, so if in the future you add more options, it's just adding rows to tables, not modifying PHP code.

Hugh


Sent from my HTC6545LVW using Tapatalk
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top