Duplication of records in Database Join used for watch field in Cascading Dropdown

[bggann]

I'm using Database Join and Cascading dropdown to select values from a table based on another field value.
A good analog would be having a user pick a state - then pick from a list of cities in the state.

The "source" table for both the databasejoin and the cascading dropdown is the same - in my example the table would be

id | State | City
1 | CO | Denver
2| CO |Rifle
3 | CO| Silt
4 | WY | Casper
5 | WY | Cheyenne
6 | WY | Douglas

In the form being filled out, the databasejoin filed is used to pick the State.
Then that value is used as the "Watch value" in a cascading dropdown to pick the cities from that state.

The problem is that if I use
id [recommended]
for the Value of the database join field and "State" for the Label
what is presented to the users is
Please Select:
CO
CO
CO
WY
WY
WY

That's not nice.

I really just the unique values from the State Column to show up
e.g.
Please Select:
CO
WY

I can achieve that by using "State" as both the Value and Label in my databasejoin element.
When I do that, only the unique states are rendered in the databasejoin element and works in the cascadingdropdown.

But I'm a little leary of that from a database standpoint because it's not unique.

Any problems with doing this?

I can't think of any reason that would cause problems, but is seems I've had problems not using id[recommended] in other databasejoin elements.

Otherwise I have to make a "State" table for the databasejoing and a "State - City" table for the cascading dropdown, but that is duplicative work and they need to be in sync. I could also hard code the States into a "Dropdown" element - but again - extra work and synchronization.

For clarity - I'm not using State and City in my system, that was just an easy example. It is more "Action" and "Task" where the tasks available to the user are related the action. I can anticipate adding more Tasks and Actions - so I don't want to have multiple tables to synchronize.
-bob

 

[bggann]

Okay - No responses here - but I think it don't work.
I implemented it and when I display the list - I get multiple repeated rows as if it cannot decide what row to use.
If I look at the actual database - there is only one record - so it is a problem with how Fabrik displays the list.
What a pain -
Any suggestions - can I use a query in php to pre-populate a dropdown instead?

 

[troester]

The "clean" way would be to have a state table/list + a city table/list with a databasejoin element to state.

You can enable "Frontend add" in the dbjoin, so no need to "synchronize". If a new state is needed it's added via the city form.

 

[bggann]

Thanks Troster. I'd thought about that - but the state/city example is a simple version of what I'm trying to do. My table structure is a bit more complex so I'll end up adding multiple tables - which I was wanting to avoid. Also, I don't want users to be able to add values - that needs to be centrally controlled.

Specifically - what I have is a structure like this.

Unit | Regions | Resources.

The Unit is the "State" in my example. For each Unit - there are multiple Regions. For each Region there are multiple Resources.
So - what I want(ed) to do was
- use DBjoin to pick a Unit
- Use that value to create Cascading dropdown of Regions in the Unit
- Use that Region value to create Cascading dropdown of Resources in the unit.

So - doing what you suggest makes my table structure a bit more complex.
Table of Units
Table of Units -> Regions
Table of Regions -> Resources.

With this complexity - it is a bit more difficult.

Another possibility may be to use php to populate a dropdown - I'm going to look at that.

Other suggestions are welcome.

 

[bggann]

Well - using php to populate a dropdown didn't work either.
I can make the "Unit" part work, but not the "Region"
What I did was make "Unit" a dropdown and populated its values with php

$options[] = JHTML::_('select.option', '0', 'Please select' );
$db = JFactory::getDbo();
$db->setQuery("SELECT DISTINCT unit  FROM dfpc_resources");
$rows = $db -> loadObjectList();
foreach ($rows as $row) {
    $options[] = JHTML::_('select.option', $row->unit, $row->unit);
}
return $options;

Note the SELECT DISTINCT.
Even though the table has multiple rows for each value of "Unit" - I got only a list of distinct Units.

Then I tried to use the same approach on the "Region"
To whit

$options[] = JHTML::_('select.option', '0', 'Please select' );
//$dfpc_unit = '{dfpc_assist___dfpc_unit}';
$dfpc_unit = $this->getFormModel()->data['dfpc_assist___dfpc_unit'];
$query = "SELECT DISTINCT region FROM dfpc_resources WHERE unit = '".$dfpc_unit."'";
$db = JFactory::getDbo();
$db->setQuery($query);
$rows = $db -> loadObjectList();
foreach ($rows as $row) {
    $options[] = JHTML::_('select.option', $row->region, $row->region);
}
return $options;

As you can see, I tried both accessing the placeholder for Unit and the Form Data for Unit.

--> The problem is that this PHP for the region dropdown is run only on load of the form. So changing "unit" does not trigger "region" to be re-populated. Region's dropdown list is based on the status of "Unit" when I loaded, the record, not the current value of Unit in the form.

-> If I'm adding a new record to the list "Unit" is empty so when the "Region" code is run - the form data for "dfpc_unit" is blank - so the "Region" code returns nothing (no records match)

-> If I'm editing an existing record - then "dfpc_unit" is populated and the "resource" dropdown is populated based upon the value of "dfpc_unit" that existed when I loaded the record.
But changing the "unit" does not trigger the php code for "region" to run.

------
Moral of the story, I'll have to make multiple lists to drive the cascading dropdown.
-bob