Cascade dropdown - has a repeat table in between from a multi-select DB Join

[Bren]

Hello all.
I'm trying to add a cascading dropdown that has a Foreign Key, on the Watch tab, that somehow needs to pass through a repeat table, in between, from a multi-select DB Join. Man, this ones a hard one to explain...
Maybe it's easier to explain the application. I have a Condo Unit # list and a Users list. Only certain Users have access to Condo Unit # records / rows that are assigned to them. The assigning is handled with a multi-select DBjoin. I have a form where a condo unit is selected and then on the same form I need a cascading dropdown that only displays users that have been assigned to the selected condo. How do I make the cascading dropdown work with the hidden repeat list, that connects the 2 lists ids, in between?
Thanks in advance.

 

[Bren]

Friendly bump.
Thanks in advance.

 

[Bren]

I was hoping someone had an idea on this one. I’m right at the finish of a project and this one has me stumped. Any suggestions would be greatly appreciated.
Thanks in advance.

 

[juuser]

Hi, I think I have bumped into similar issue, but I'm not sure anymore how I solved it. Unfortunately I don't have similar test case to investigate in more detail.
One idea is to use "Eval options" in the cascadingdropdown to remove the options you do not want to be available. Here's a sample script from regular dropdown:

$mydb = JFactory::getDbo();
//select the options you want to be available
$mydb->setQuery("SELECT GROUP_CONCAT(a.id) FROM ...";
$myresult = $mydb->loadResult();

$myoptions = explode(',',$myresult);
if (!in_array($opt->value, $myoptions)) {
//if value not within mysql query result, then remove
return false;
}

P.S. just an idea and not tested.

 

[Bren]

Thanks for the suggestion juuser. I appreciate it. I see how this could work. I'm a bit stuck on the SQL statement, as I'm wondering how the foreign key on the watch tab would work with this. I think I need to sleep on this and come back to it tomorrow.
Thanks, again.

 

[Bren]

Ok, I slept on it and I’m still stuck on the SQL statement. I keep thinking that I need to include the watch element value into the SQL statement, as I’m trying to filter down which user has access to the selected condo in the 1st dbjoin dropdown. Is it possible to include the watch element value within the SQL statement? Or, maybe with a placeholder (not sure if that would work on a new record that hasn’t been submitted, yet)?
Thanks in advance.

 

[Bren]

Well, it took a lot of experimentation, but I finally figured out a work around that made this work in actually a much simpler way. I added the the hidden repeat list to the Fabrik lists (with a note to not alter this table). Next I adjusted the CCD Data tab > List to this, now available to select, repeat list and selected the id & label to both be the parent id. Then I added a CONCAT label with (SELECT `name` FROM Users WHERE `id` = {thistable}.parent_id). Finally on the CCD Watch tab I left the Condo Unit as the Watch element and select the condo unit as the Foreign key. After some testing it now works perfectly.
Thanks for the suggestions guys. I took the scenic route on this one, but learned some things along the way, too.

 

[juuser]

Glad you sorted it out. Neat solution, thanks for sharing