Create a Questionaire Form

[stevelis]

I have created a form #7 Expressions with 3 Groups
eoi_details
eoi_chreg
eoi_tmreg
The eoi_details group contains several field elements that request personal details, i.e. name, phone number, email etc. Currently the data for these fields are manually entered.
What I would like to achieve with the fields in the eoi_details group being auto-filled based on the logged in users details, i.e. {$my->id}
The data for the eoi_details comes from the Steward list, and when the form is submitted this information needs to be saved in the Expression list, where the two other groups of data are saved.
I have no success with dbasejoin elements, auto-fill form plugin displaying the logged in users details for the eoi_details group
Any suggestion greatly appreciated
Steve

 

[mediaateam]

Steve,

I suggest on the individual fields you put a script in the default area that will fetch the data you want it to auto populate with. For instance, on the name element on your questionnaire, do something like this (replacing ELEMENTNAME and TABLE with your own):

$myUserid = JFactory::getUser()->get('id');
$myDb = FabrikWorker::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->select('ELEMENTNAME')->from('TABLE')->where('userid = ' . $myDb->quote($myUserid));
$myDb->setQuery($myQuery);

return $myDb->loadResult();

And, don't forget to set Eval to Yes under the default script area.

-Robbie

 

[mediaateam]

Steve,

Note, that 'userid = ' in my sample refers to the element on the questionnaire that has the user id in it. You may need to change the name of that if your element is named differently. (I'm a creature of habit and always name it userid on the different tables that need it.)

-Robbie

 

[stevelis]

Thanks for your response Robbie and I am nearly there but not quite
The List with the data is called stewards with these elements
plugin Name Label Full Element Name
internalid id id steward___id
field Licence Username steward___Licence
field J_id Jid steward___J_id
field Location Suburb steward___Location

The steward___licence element value is the username that a steward logs in with.
The steward___J_id element is the link between the Fabrik steward List and the JUsers table
The steward___location contains the value I need to be auto populate in the questionnaire
The elelement in the questionnaire form is eoi___Location which is where I need to place the script provided above.

$myUserid = JFactory::getUser()->get(steward___Licence);
$myDb = FabrikWorker::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->select(steward__Location)->from(steward)->where(steward___Licence = ' . $myDb->quote($myUserid));
$myDb->setQuery($myQuery);

return $myDb->loadResult();

Any suggestions welcomed as the above script returns an error
Steve

 

[mediaateam]

Steve,

Ok, so what is the element name of the userid on your current table? That will need to be placed in this script:

$myUserid = JFactory::getUser()->get('id');
$myDb = FabrikWorker::getDbo();
$myQuery = $myDb->getQuery(true);
$myQuery->select('Location')->from('steward')->where('ELEMENTofUSERidONcurrentTABLE  = ' . $myDb->quote($myUserid));
$myDb->setQuery($myQuery);

return $myDb->loadResult();

You can direct message me the element name you are trying this on and I'll take a look if this doesn't work.

-Robbie

 

[stevelis]

Hi Robbie
Element name in the eoi table (current) is stewid #1160 being a dbjoin element
The group is eoi_details

Thanks
Steve

 

[mediaateam]

@stevelis Can you test the Location element (#71) on the expressions table now and see if it's working? It has to be done with a steward login. If that is working for you, you can take a look at the code and modify it for any other fields you need to pull data from the stewards table for.

 

[stevelis]

Hi Robbie
This is the error message displayed at the top of the questionnaire form when login in
Unknown column 'stewid' in 'where clause' SQL=SELECT Location FROM steward WHERE stewid = '1059'
have added a steward login details in my site if it helps
Menu is EOI Registrations > 2017 National .........

 

[cheesegrits]

I think stewid actually needs to be the user id element on the steward table.

-- hugh

 

[stevelis]

Hi Hugh / Robbie
I am not sure if I have the correct setup for the code.
List #6 called Stewards (tbl name steward) with location, email, phone field elements etc. along with a fabrik internal id, username, and a J_id
List #7 called Expressions (tbl name eoi) with Yes/No elements and other field elements.
Theses elements are grouped into 3 Groups (eoi_details, eoi_chreg & eoi_tmreg) for Form #7 called Expressions.
This Form #7 Expressions is accessed via a menu option once a user from (List #6 Stewards) is logged in.
I am trying to auto populate some of the Group eoi_details elements based on the logged in 'user' stored in the steward table.
Tried adding a dbasejoin element (eoi___stewid), Group (eoi_details) to the Expressions List as follow
Name: stewid
Label: Stewardid
with these Data settings

I am not sure if I have got the setup totally correct for the code provided so far.

 

[mediaateam]

@stevelis - your problem was that stewid, it needed to be element type user and set to the logged-in user's id. The second change in the code I made was your Joomla user id element on the Stewards table was name J_id, so this line was updated in the code:

$myQuery->select('Location')->from('steward')->where('J_id  = ' . $myDb->quote($myUserid));

I made a test steward login with the name Fabrik to test, and it autopopulated the Location from the steward table no problem. Please try on your end. And, you can delete the Fabrik steward now, it was just for testing.

 

[stevelis]

Hi Robbie
Thank you very much, I have added addition elements with your coding and it's working like a treat.
With the rush to recover the site and finish this little exercise by tonight, I was over-looking the basics or just plain forgot them this time
Again, appreciate your time and advice with the coding help.

Steve
Adelaide

 

[mediaateam]

Glad to hear it! And, I would love it if you would post about your project in the Showcase forum when you get a chance!
Thanks,
Robbie