How to update multiple table field based on user selection?

JvdStel 17 May, 2015
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
GreyHead 18 May, 2015
Hi JvdStel,

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

Bob
JvdStel 19 May, 2015
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?
GreyHead 19 May, 2015
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
JvdStel 19 May, 2015
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
GreyHead 20 May, 2015
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.
JvdStel 20 May, 2015
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 ;-)
GreyHead 25 May, 2015
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
JvdStel 06 Jun, 2015
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?
GreyHead 06 Jun, 2015
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
This topic is locked and no more replies can be posted.