How to update multiple table field based on user selection?

Hi,
I have made a simple form in CC Front List > Actions > Edit.
Based on the selections made by the user, I want to update multiple table fields. How can I do that in CC v5?

Johan

Hi JvdStel,

What exactly do you mean by 'multiple table fields'?

Bob

The form defined in the Front List > Actions tab allows the user to select a "name", displayed from a database query. See code below:

<select class="inputbox validate-selection" id="select_3" size="1" title=""  name="SVS[Nummer]">
    <option value="">make a choice</option>
       <?php
      foreach ($rijen as $rij) {
               echo "<option value='$rij->Nummer'>CKC $rij->Team: $rij->Naam</option>";
      }
       ?>
</select>

For the selected "name" from our membership table he can then further on in the form add some attributes. After pressing submit, these attributes together with the selected "name" (model SVS, field Nummer) get inserted into a table. What I would like is to insert not only the selected field (Nummer), but also two other fields from the same table (Team and Naam, which you can already see mentioned in the PHP echo statement).

Obviously the inputbox only allows me to select one field. My question is, how can I capture more fields and make sure they get selected and inserted in the same row as the selected field?

Hi Johan,

There are two ways to do this.

a. You use the value of $form->data['SVS']['Nummer'] to look up the other values from the table after the form submits. A DB Read action will let you do that.

Or

b. You add those values to the value and 'un-pack' them after the form submits.

So your code changes to

<select class="inputbox validate-selection" id="select_3" size="1" title=""  name="SVS[Nummer]" >
  <option value='' >make a choice</option>
<?php
foreach ( $rijen as $rij ) {
  echo "<option value='{$rij->Nummer}#{$rij->Team}#{$rij->Naam}' >CKC $rij->Team: $rij->Naam</option>";
}
?>
</select>

And then On Submit add a Custom Code action to unpack the value:

<?php
$rij = explode('#', $form->data['SVS']['Nummer']);
$form->data['SVS']['Nummer'] = $rij[0];
$form->data['SVS']['Team'] = $rij[1];
$form->data['SVS']['Naam'] = $rij[2];
?>

!! Not tested - may need debugging !!

Bob

Hi Bob,
I created a DB_READ action collecting the missing fields from my membership table based on "$form->data" as you suggested. I assume this action is executed after my form submits (how do I know??). What I then want is add the data to my next table. How do I add this data into the table? Should I code the SQL table update myself?

Johan

Hi Johan,

Use a Debugger action after the DB Read to see what has been added. A normal DB Save action will work to re-add the data* provided that it is appropriately saved in the $form->data[''] array - this is, if your DB Save uses a Model ID then the same ID needs to be used for all the data to be saved.

Bob

* It is generally considered bad database design to save the same information in several tables. Better to save the record id alone.

Bob,
So far I have used the same pre-defined EDIT action for EDIT updates as well as NEW inserts. This action results in DB inserts/updates based on the FORM input by the user. These inserts are handled by CC5, transparent to me. I have created a new action DB READ based on your previous comment, but it is not clear to me how this would result in a DB update.
When you refer to "DB save", do you refer to the predefined SAVE action I can see in CC5? - How would that result in a DB update?

Thanks, Johan
ps: And thanks for the "generally considered bad database design" advise ;-)

Hi Johan,

Sorry, we got at Cross-Purposes here, I'm assuming that you are linking from CC to a ChronoForms form event to do the work. The DB Read is a ChronoForms action, not a CC one.

Bob

Thanks Bob. Indeed I had missed the CF bid.

So I have now created my form in CF5. In my form code I have included these fields that I want to be filled "on submit" based on user-input:

<input value="" id="hidden_4" name="SVS[Naam]" type="hidden" />
<input value="" id="hidden_5" name="SVS[Team]" type="hidden" />

I have created a setup as follows:
[attachment=0]CFsetup.jpg[/attachment]

On submit it should execute my DB Read. I have defined a model "Data3" to select fields "Data3.Team_of_Commissie" and "Data3.Naam" from my "Membership" table, based on condition set at:

< ?php 
$Lidnummer =$form->data['SVS']['Lidnummer'];
return array('Relatienummer' => '$Lidnummer'); 
?>

After that it should execute my Custom Code action labeled "fill_form_data":

<?php
$form->data['SVS']['Naam'] = $row['Data3']['Naam'];
$form->data['SVS']['Team'] = $row['Data3']['Team_of_Commissie'];
?>

And finally I want it to execute a Connection Action, calling the "save" action in CC5.

This setup does enable me to create new records and edit existing records, but just does NOT add the 2 fields as I would like. Even worse, when they are filled in the table, they are blanked out after an edit.

Is there anything wrong in my logic as outlined above? - Is the coding wrong?

Hi JvdStel,

The general answer here is to add Debugger actions (or Custom Code actions that display specific values) at different points in the On Submit event so that you can see what is happening.

The specific answer is that the data you want will be returned in he $form->data[] array, not in $row which is only used in CC.

Bob