Cascading dropdown changing source data

[PvN94]

Say I have a cascading dropdown, first I have a dropdown "brand", with brand 1, brand 2 etc. Then a cascading dropdown "model", with model 1, model 2, etc, each model record has a field "brand" to use in the cascading option.
When I change model 1 to be from brand 2 instead of brand 1, and I want to search it in the list of orders (where the brand and model are selected in the dropdowns) it doesn't show up after selecting either brand 1 or brand 2 in the filter, i'm guessing because in the "order" record the saved value is brand 1 -> model 1, which is invalid now.
Is there a way to make it so that these "order" records are updated automatically in this case, or am I going to have to write some php to do the update when a model is assigned to a different brand?

 

[cheesegrits]

Yes, you guessed right. Once you change the FK (foreign key) of a row in the CDD table (in your case changing the brand a model belongs to), that invalidates any existing data in forms that have that model (with the original brand) selected.

You'll need to write a little form submission script for your 'models' form, running 'onAfterProcess' (for edit) which modifies all existing orders to change the 'brand' selection to the new one. Something like ...

// get the model ID
$modelId = $formModel->formData['models___id_raw'];
// get the brand id (and check if it's an array, p'cos it's a join element)
$brandId = $formModel->formData['models___brand_id_raw'];
$brandId = is_array($brandId) ? $brandId[0] : $brandId;
// get db and query objects
$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);
// execute query to change brand on all orders with matching model and non-matching brand
$myQuery->update('orders')
   ->set('brand_id = ' . $db->quote($brandId))
   ->where('model_id = ' . $myDb->quote($modelId))
   ->where('brand_id != ' . $myDb->quote($brandId));
$myDb->setQuery($myQuery);
$myDb->execute();

Obviously change your element / table / field names to suit. Leave the _raw on the end of the element names though.

However ... personally, I wouldn't change brands on existing models. I would create a new model, with the correct brand. But that's obviously down to your "process". I just prefer to maintain order records as a historical record. If the customer ordered an Acme widget, I want that order to show that's what they ordered, if it was ordered prior to becoming an American Anvils widget.

-- hugh

 

[PvN94]

Yeah, the brands and models is just what I was using for testing the possibilities. The eventual idea would be to have persons belonging to groups, and using the cascading dropdowns to create dynamic list filters, so when I select group1 as a filter, the persons filter is restricted to those in group1.

Anyway, thanks for the answer and example