Cascading Dropdown + CONCAT label

[jo-ka]

Hello. 6 months later i'm returning to work with Fabrik...

So, i have 3 tables, (examples)

table1
id | number | firstname | lastname

table2
id | number | role

table3
id | person | function

Note: First le me say that i know that i can put t1.firstname and t1.lastname fields in table2, but for reasons that doesnt matter right now i need to be like this.

In table2, the number field is a databasejoin with t1.number

OK, now the "Problem".

I have a form that is inserting data in table3. The form3.person field is a cascading dropdown element, that reads t2.number and is watching another field in the form (also doesn't matter righ now)

Everything is working almost good. The value i get from t2.number is ok but i need in the form3 to get a label from t1.lastname + t1.firstname.

So in the end, i need in the form to get the value from t2.number but the name and lastname from table1 just for display, not to insert in the table.

Is is possible do have a INNERJOIN in CONCAT Label field for example? What shall i do?

BR

 

[myfatebiz]

Why not using sql query on calc element.

On table3, create 1 new calc element named Full Name :

$db = JFactory::getDBO();
$db->setQuery("SELECT `firstname` + ' ' + `lastname` FROM `table1` WHERE person = '{table1___number}'");
return $db->loadResult();

or (that must match your database join value)

$db = JFactory::getDBO();
$db->setQuery("SELECT `firstname` + ' ' + `lastname` FROM `table1` WHERE person = '{table1___id}'");
return $db->loadResult();


Visit at http://webdatabasesystem.com/e-resource and see the sample there because mostly my report i using sql query (1 form = multiple report from multiple database). All data no need to key in again for multiple report because i using sql query to display information what i need.

 

[jo-ka]

Dear myfatebiz, thank you for your reply...

But, in that way i'll loose the feature of "filtering" the input on the cascading dropdown element... This is, now the results displayed on the combo box are only those that come filetered from the field beeing "watched" by this element...

Was i clear? I'm sorry if i don't made myself clear...

 

[myfatebiz]

If u say concat label at ccd, maybe it should

{thistable}.firstname, ', ', {thistable}.lastname

on concat label area

 

[jo-ka]

Well, let me try to explain better:

table1 - joomla_comprofiler table, from where i will get user data (id, firstname, lastname,)

table2
id | person | function | - t2.person is a databasejoin element with joomla_comprofiler.id (the community builder component table) from where i get the joomla user id to input in t1.person field. Then, i use the CONCAT Label option with joomla_comprofiler.lastname, ',', joomla_comprofiler.firstname, so the combobox shows SMITH, john in place of Joomla user id.

t2.function field is a databasejoin from joomla_usergroups table from where i will get the user group id, that i will input in the t2.function field.

So in the end i have something like this:

id | person | function |
11 | 54 | 23 | - record id (11) | joomla user id ( 54) | joomla user group id (23)

This is working good.

After this i have a new table and form (a form where someone will input an evaluation to a role played by another person):

table3
id | person | function | evaluation

In this case i use a cascadingdropdown element, joining t2.person and t3.person but this element is watching t3.function filed. So, everytime user select a funcition, the person dropdown field will only display the person that belongs to that function (joomla group)

This is also working good, but it is showing the Joomla user id (54) in place of user name SMITH, John, that i would like to be shown in place of the user id.

That's why i thought that i could use a CONCAT Label, but i also thought i coul use an INNER JOIN in the label, so i could cross both tables and get the user name.

Probably the best way is tou use an option you've suggested, create a calc element in table 2, like t2.person_name so it can be used after with table3.

Was i more clear? I hope so...

 

[myfatebiz]

Oh i see. It happen to me also when i want get the label from 2nd database(have database join from 1st database) which is store by value id. Databasejoin or CCD only read what value store inside the column in database unless fabrikar developer put features at databasejoin or ccd be able to read label from databasejoin element.

And yes if me, i need to create new calc element at table2 to get fullname from table1. So it can be use on table3. If you using this options i recommend you using mysql query (see at my 1st post) instead using this return "{table2___person}";

 

[jo-ka]

Thanks again for your help... i've tried your suggestion but something is failing... I've just tested with lastname field for now.

$db = JFactory::getDBO();
$db->setQuery("SELECT `lastname` FROM `c629i_comprofiler` WHERE {table2___person} = '{c629i_comprofiler___user_id}' ");
return $db->loadResult();

What can possibly be wrong?

Maybe because the query can't find the {table2___person} that is being written in the form...

 

[myfatebiz]

Wrong query though . {table2___person} to person

Note : person is column from c629i_comprofiler

Correct is

$db = JFactory::getDBO();
$db->setQuery("SELECT `lastname` FROM `c629i_comprofiler` WHERE person = '{c629i_comprofiler___user_id}' ");
return $db->loadResult();

-----------------------------------

If still not working try change {c629i_comprofiler___user_id} to {c629i_comprofiler___user_id_raw}

-----------------------------------

I look again at your query it like something wrong because :

---> FROM `c629i_comprofiler` <--- This is table where value you want to look
---> WHERE {c629i_comprofiler___user_id} <--- This is current table

The red one can't be same.

---------------------------------------

 

[jo-ka]

OK, this is the way i solved it, after talking with a friend and following some of your code...

$db = JFactory::getDBO();
$sql="SELECT concat(lastname,', ',firstname) FROM c629i_comprofiler";
$sql.=" WHERE user_id = ";
$sql.={fab_nominations___person_raw};
$db->setQuery($sql);
return $db->loadResult();

This is now working... Thank's a lot for your support!