Get different values from same database join in different fields

[witchypaws]

Hi guys

I have a database join dropdown field xfco_introductions___intro_specialist_name which gets the name of a person from the table and field xfco_specialists___spec_name

That is returning the value I want in the form

In the same form, I would like to have a calc field that returns the email of the same person the email is found in the table and field xfco_specialists___email

How would I do this please, I don't want them to have to select the email from another dropdown, I want them to choose the person, then the next field just populates the email address for that person but grabs the data from the other table.

Thanks

 

[lcollong]

Sounds like it's in the same table ? Two different columns on the same record from the same table ?
You can use the "Concat part" of the dbjoin elt to show both the name and the email in the dropdown
You can use the advanced tab of the dbjoin elt to add email in the description field so the email will display just under the dropdown each time you change it
You can use a cascading dropdown to show the email in a specific field
You can use another dbjoin element with ajax update to do the same thing
You can use a calc element, to grab the value from the database using the placeholder of the name to catch the email (there are plenty of examples in the wiki and in the forum).

 

[Nagtegaal]

Hi,

You should create a join between the two lists. Lists -> Data -> Join tab. If you do not have it, create an element in the xfco_introductions list named like specialists_id, which you can use in the join.

Then for the element specialists_id use the database join. Choose xfco_specialists for the table, id for the value and spec_name as the label. After saving all elements of the xfco_specialists appear in the xfco_introductions list.

Good luck.

 

[witchypaws]

Hi Natlegal

thanks for the help but it didn't work as expected

If I do this as a database join where you said, then it adds introductions as an extra form to specialists and I don't want that

What I need is

Table 1 - Specialists
Fields id, date, name, business name, email, phone

Table 2 - Introductions
A whole heap of fields plus the fields specialist name, specialist business name, specialist email, specialist phone

However, once they populate the specialist name field from the dropdown from table 1, I would like the other three fields to automatically update with the the values matching the specialist they chose in the specialist name field

Each of these fields has to be standalone so it can be used in an email plugin on the form to send the info to various people and using each field independently in the email body and to/from fields

Can this be done

Any further help would be appreciated

 

[witchypaws]

Thanks Apchea

Could you perhaps point me to an example of this

You can use a calc element, to grab the value from the database using the placeholder of the name to catch the email (there are plenty of examples in the wiki and in the forum).

I think that's what I need but don't know where to start

It basically has to get a value in table 2 from table 1 for the database join field
Then I want calc fields to get the id value from the database join field but return a value from a different column in table 1 but with a different column value

i.e. join field is specialist_id
next three field are specialist_name, number, phone where all that data is stored in the table 1

Thanks

Jacqui

 

[Nagtegaal]

Hi Witchypaws,

Ok, So you want to populate/copy the Introduction table with data from the Specialist table. What I would do is use an update statement in the database as described in the wiki PHP common tasks. Here you find also an example of the update statement.

You can trigger the event with the PHP Events list plugin. I would use the onPreloadData.

So with the database join you populate your Introductions table with the id from the Specialist table, using the field/element Specialist_id. Then with the update statement you can populate the other fields.

Hope this helps.

 

[witchypaws]

HI Natlegal

that helps enormously

I'll try to do it and come back if I have any issues

thanks so much for your help

 

[troester]

You may also have a look at the "autofill" form plugin.
http://fabrikar.com/forums/index.php?wiki/autofill-form-plugin/

 

[witchypaws]

Hi Troester

I think this is exactly what I need but I am having an issue with it

I have a list Client to specialist with a databasejoin element called Specialist Business Name xfco_introductions___intro_specialist_id and the join element is getting it's data from another table bxofc_cck_store_fprm_xfc_specialists

I am using the fields from this table to get the data id as the value and ppbusinessname as the label

That is successfully pulling in the business name of the person to the form and storing the business name id as the value in the database

Now is the part I can't get right

I have created another list called specialist lookup which is attached to the table
bxofc_cck_store_fprm_xfc_specialists and returns all the data from that table in a listing correctly

I have then gone back to my other list I have put in another fabrik element called xfco_introductions___intro_specialist_email as an ordinary field

On the form I have then gone and added a plugin using autofill (please see screenshot)

I have told it to lookup the list specialist lookup which gets its data from bxofc_cck_store_fprm_xfc_specialists

I have put the lookup field as the id field

then I have put field to observe as Specialist Business Details, the one with the specialist id as the value being stored in the database

Then I have mapped the data and used autofill on load

{"xfco_introductions___intro_specialist_name","form_plugin_autofill___xfco_introductions___intro_specialist_email"}

What I want to do is when someone comes to the form, they select the Specialist Business Name in the first field and the second field xfco_introductions___intro_specialist_email automatically grabs the email address from the specialist table bxofc_cck_store_fprm_xfc_specialists or list Specialist Lookup which is based on that table

I need it to fill immediately (I turned on autofill confirmation) so that it has the email address in place when it saves as there is another form email plugin that sends and email to that email address

I can't get it to ask me to autofill and I don't know if I have the mapping wrong or need to use the trigger function and if so, what the trigger would be

Any help would be appreciated, I am still relatively new to Fabrik and loving it but have a bit of difficultly following the tutorials and examples on occasion

Thanks

Jacqui

 

[troester]

Please have an exact look at the WIKI: the syntax is {"from1":"to1","from2":"to2" etc}

 

[witchypaws]

Hi Troester

I have had a look at the wiki about 100 times now and I still can't get this to work

I have a Retrospective Introduction based on the list Restrospective introductions which puts data in the table xfco_introductions - the form works as expected till I try to get autofill to work

The form has one element xfco_introductions___intro_specialist_id which is a database join field and looks up the table bxofc_cck_store_fprm_xfc_specialists and gets label ppbusinessname using the ID column (recommended) - please see screenshot.

The form has another element xfco_introductions___intro_specialist_email which is the one I want to autofill from the element element xfco_introductions___intro_specialist_id.
I have it as a standard field with no plugins? Is this correct? (see screenshot 2)

Then I have added the autofill plugin to FORM as per this screenshot

It is pulling the data from the list Specialist Lookup which comes from the same table as the databasejoin field bxofc_cck_store_fprm_xfc_specialists (please see screenshots 3 and 4 for the setup)

I have changed the syntax as per your assistance above to {"bxofc_cck_store_form_xfc_specialists___ppenquiryemail":"xfco_introductions___intro_specialist_email"}

I am tearing my hair out and the client is getting very antsy. I'm not sure if I have missed a setting elsewhere that is not mentioned in the wiki

I have tried this on the standard protostar template as it is not working

The menu item is https://xfactorcollective.com/testing and I have made it public for now in the hidden menu on the backend if you want to view the page

Can you please assist, I am happy to send backend login details for you to review and happy to pay for support but cannot find a way to get paid support any more

Thanks

Jacqui

 

[witchypaws]

Hi Troester

So I installed a Test joomla site with latest fabrik and autofill plugin and some testing data and the autofill was working fine so I know my coding is working for the autofill

I had a post earlier in the year where I was having issues with popups http://fabrikar.com/forums/index.ph...for-database-join-element-in-list-view.49308/

I did try the solution offered there of testing on protostar and found it didn't change anything

I have to do some updates on the live site to make sure all software is up to date over the weekend and try again but would like some guidance on how to trouble shoot this

It seems to be that javascript and/or ajax is not firing within fabrik for all elements/plugins

What would you suggest is the best way to test against other components/extensions to see what is causing the issue and is there a debug tool I can use on the front end form itself that may help you help me to diagnose the problem

Thanks

Jacqui

 

[troester]

Your link https://xfactorcollective.com/testing is showing "Uncaught SyntaxError: Unexpected token 'var'"
Which exact Fabrik and Joomla version?
I can't see any Fabrik JS loaded at all.
Do you have 3rd party stuff with e.g. JS compression?

 

[witchypaws]

Hi Troester

Thanks for all your help

I updated everything on the site, and reviewed all my settings for each thing against the testing container I made and now it is all working

I have no idea what was blocking it but it seems to have resolved itself after installing the official release of 3.9.2 using the joomla installer instead of the github method (I had updated from github in march 2020)

I am closing this thread now