Setting field (but reading from DB)

I would like to know which is the better way to implement the following item:
I have a double drop-down linked field - Customer - address
when I choose in the 1° field a Customer, in the 2° dropdown I see only the addresses of that Customer.
All is working well with 2 DataBase (Customers - Addresses)

But I have to set a third field (plant) reading the plant's number from a 3rd DB with the index:
id_rag={customer} AND addr={address}
where {customer} and {address} are the choices in the double dropdown

Is correct to create an Events:
Load Dynamics Options - On Change value of the 2nd dropdown (the address)
Set Options of field "plant-number" AJAX Event "ajax_plant"
and in SETUP - ajax_plant put a Custom Code:

<?php
$sql = "SELECT id_imp FROM fli_chronoengine_chronoforms_datatable_Impianti WHERE id_rag={customer} AND addr={address};
      $res = $db->query($sql);
      $f = $res->fetch();
      @extract($f);
$nimp= $id_imp;
echo $form->data[$nimp] ;
?>

Thanks in advance
Gigi

Hi Gigi,

I don't think that the built-in AJAX call will work here as you need to send two variables - customer and address - back to make the query work. You can write a custom JavaScript Ajax query and add that to a Load JavaScript event to get this to work.

You'll also need to check your database query.

+ You can't use {customer} in PHP - use $form->data['customer'] instead.

+ I don't think that the PHP you have here will work at all, I suggest that you use the Joomla! database methods; or a ChronoForms DB Read action.

+ The data you return will depend on what you want to do with it. Presumably your script will put it into an existing <dIv> in your form HTML?

Bob

Hi Bob,
you wrote: The data you return will depend on what you want to do with it. Presumably your script will put it into an existing <dIv> in your form HTML?
I want to put in the field "plant_number", so I can save it when the user press the SUBMIT BUTTON.
After your reply, I did the following steps:

1) In Designer Events: Load Dynamics Options - On Change value of the address (com)
Set value of "gf_imp" with the AJAX event "ajax_impianto"

2) In Setup - New Event: On ajax_impianto
DB Read (as You suggest) with
Multiread: NO
Model ID: ajax_impianto
Fields: id_imp
Conditions: <?php return array('id_rag'=>$form->data['clienti'] , 'com'=>$form->data['com']); ?>
so I read the DB with the 2 keys: Customer + address; the result is only 1 RECORD and I need the value of the field "id_imp"

3) In Setup - New Event: On ajax_impianto
Custom Code:

<?php
$options = array();
if ( !$form->data['ajax_impianto'] || count($form->data['ajax_impianto']) < 1 ) {
  // no result was found
  $options[] = "No Impianto";
} else {
  foreach ( $form->data['ajax_impianto'] as $d ) {
    $options[$d['gf_imp']] =  $d['gf_imp'];
  }
}
$d2 = str_replace('"', '', $d);
echo json_encode($d2);
?>

*********************************
The result gives me the problem that I receive the value PLUS the DOUBLE QUOTE "".
If you like to go to see the site:
http://www.gascarugate.it/zprova/index.php?option=com_chronoforms5&chronoform=GruFri_aggiungi
Choose: Ragione Sociale--> Lucisano Walter
AND : Comune --> Villasanta
in the field GF_Impianto you see: "27" where I would like to receive ONLY 27, that is the id_imp for the record of that plant;
if you choose Ragione Sociale--> Lucisano Walter
AND : Comune --> Monza
in the field GF_impianto I receive "" where I'd like to receive No Impianto
************************************
next week I'll order the 3 validation keys for CF.
Thanks in advance
Gigi

Hi Gigi,

How are you sending the two values to the AJAX event? I don't see that here.

Also you don't need all of the 'Options' code if you are only returning a single value here.

Bob

Bob,
do you work on Sunday ? 😲
I appreciate your quickly answer !
I am sending the two values via Conditions in DB Read.
***************************
My problem is to fill a field (gf_imp - plant number) depending on the choice of two other fields, BEFORE TO PRESS the Submit botton.
So:
the user selects (with a double dynamic dropdown) id_rag (Customer) and com (city)
In Designer - Events: Set Dynamic value - On click of com (city) - Set value of GF_imp - ajax_impanto
In Setup - On ajax_impianto : DB Read + Custom Code
------------------------
DB Read:
Table name: xxxxxxxxxx-plants
Multi read: NO
Enable Model ID: yes
Model ID: ajax_impianto
Fields: id_imp (THIS IS THE FIELD that contains the value I need to set the gf_imp in the form)
Conditions: <?php return array('id_rag'=>$form->data['clienti'] , 'com'=>$form->data['com']); ?>
(HERE THE 2 VALUES id_rag + com to read the DB to find the value)
------------------------
Custom Code:
<?php
$options = array();
if ( !$form->data['ajax_impianto'] || count($form->data['ajax_impianto']) < 1 ) {
// no result was found
$options[] = "No Impianto";
} else {
foreach ( $form->data['ajax_impianto'] as $d ) {
$options[$d['gf_imp']] = $d['gf_imp'];
}
}
$d2 = str_replace('"', '', $d);
echo json_encode($d2);
?>
**********************
I understand that I don't need to use Options Code, because I receive ONLY 1 VALUE, but the question is: how to refer to this value and to set the field gf_imp?
Thanks
Gigi

Hi Gigi,

The question was how are you sending them from the form in the browser so that they are available in the Ajax event?

Bob

Hi Bob,
in the meanwhile I ordered the 3 validation keys - 25 USD (order number 105572407845).
Here attached the backup of my form and a winword-file describing the problem.
Answering your question, I send the 2 parameters in the DB Read of ajax_impianto
in the Conditions:

<?php return array('id_rag'=>$form->data['clienti'] , 'com'=>$form->data['com']); ?>

In the custom code, I wrote:

<?php
$options = array();
$options[] = "No Impianto";
    foreach ( $form->data['ajax_impianto'] as $d ) {
    $options[$d['gf_imp']] =  $d['gf_imp'];
    }
echo $options[$d['gf_imp']];
?>

but I receive ONLY THE FIRST CHARACTER,
thai is: 2 if the value is 2
but only 3 if the value is 37
Gigi

Hi Gigi,

I can't test the backup form with out the tables :-(

The DB Read has Multi read set to No so you will only get one record returned. and I think that the echo statement in your PHP will only return the last option created.

I can’t see anything that will shorten the value of the id though.

Bob

Bob,
excuse me but how can I attach the DB export ?
CSV and SQL are not accepted.
Do you prefer to receive credentials to do login on my web site?
Gigi

Hi Gigi,

You can attach a file by zipping it, but please don't send me your db tables, It takes too much time to re-install and reconnect them.

By all means PM me the site URL, the form name, and a SuperAdmin login and I'll take a quick look.

Bob

Hi Gigi,

I changed the code in the On ajax_impianto event to this and it now appears to work

<?php
if ( empty ($form->data['ajax_impianto']['id_imp']) ) {
  echo "No Impianto";
} else {
  echo $form->data['ajax_impianto']['id_imp'];
}
?>

You could simplify it a bit more by removing the Model ID from the DB Read

<?php
if ( empty ($form->data['id_imp']) ) {
  echo "No Impianto";
} else {
  echo $form->data['id_imp'];
}
?>

Note that the DB Read is set up to give you only one result but there may be more than one in the database table.

Bob

PS I installed MijoSQL on your site to test the DB queries, you can uninstall it when you are done.

Bob,
mille grazie !!! (in italian language)
Thanks a lot !!!
You are fantastic
Gigi