updating database with data from multiplier field

As per the FAQ i have a form with 2 databases, One for the main form data and one for the array data from a multiplier element

I can fill in all the fields on the main form, add multiple rows in the multiplier, fill them with data and submit the form, this saves all the data to the 2 databases

I added the following custom code

<?php
foreach  ($form->data['extra'] as $k => $v ) {
$form->data['extra'][$k]['ot_id'] = $form->data['id'];
}
?>

This saves the value of the id field in the first database to a field called ot_id in the second database

After ensuring i had dummy data in the databases I added a DB Read element to the form for the 2 databases to the on load section and loaded the form and as expected the form loaded with saved data on both the main form and in the multiplier.

next i added a hidden field on the main form called Main[id] to load the ID so that when the form is submitted an update occurs to the database instead of an insert. (This works)

With the multiplier i added a text field for the id called extra[##][id] and a text field for ot_id called extra[##][ot_id]. I did this so the individual entries in the multiplier would be updated if an edit was required.

when the form loads the correct values are shown in these boxes

However when submitting the form the value in extra[##][ot_id] which is displayed correctly when the form loads is stripped on submit.

    [extra] => Array
        (
            [0] => Array
                (
                    [Date_OT] => 02-12-2015
                    [WorkType_OT] => Overtime
                    [StartTime_OT] => 08:30
                    [FinishTime_OT] => 12:00
                    [MealStart_OT] => 12:30
                    [MealFinish_OT] => 16:00
                    [Hours_OT] => 345
                    [Site_OT] => LUC
                    [Remarks_OT] => 
                    [id] => 57    (ID has a correct value)
                    [ot_id] =>   (should have a value here)
                )

            [1] => Array
                (
                    [Date_OT] => 12/12/2015
                    [WorkType_OT] => Overtime
                    [StartTime_OT] => 08:30
                    [FinishTime_OT] => 15:30
                    [MealStart_OT] => 12:30
                    [MealFinish_OT] => 16:00
                    [Hours_OT] => 127
                    [Site_OT] => SJG
                    [Remarks_OT] => 
                    [id] => 58     (ID has a correct value)
                    [ot_id] =>    (should have a value here)
                )

            [id] => 58
        )

the sql is as follows

    [12] => Array
        (
            [DB Save] => Array
                (
                    [Queries] => Array
                        (
                            [0] => UPDATE `jos_chronoengine_chronoforms_overtime` AS `extra` SET `Date_OT` = '02-12-2015', `WorkType_OT` = 'Overtime', `StartTime_OT` = '08:30', `FinishTime_OT` = '12:00', `MealStart_OT` = '12:30', `MealFinish_OT` = '16:00', `Hours_OT` = '345', `Site_OT` = 'LUC', `Remarks_OT` = '', `ot_id` = '', `modified` = '2016-01-04 23:50:04' WHERE `id` = '57';
                            [1] => UPDATE `jos_chronoengine_chronoforms_overtime` AS `extra` SET `Date_OT` = '12/12/2015', `WorkType_OT` = 'Overtime', `StartTime_OT` = '08:30', `FinishTime_OT` = '15:30', `MealStart_OT` = '12:30', `MealFinish_OT` = '16:00', `Hours_OT` = '127', `Site_OT` = 'SJG', `Remarks_OT` = '', `ot_id` = '', `modified` = '2016-01-04 23:50:04' WHERE `id` = '58';
                        )

                )

        )

)

as you can see no data is passed

On troubleshooting i have found it is because of the custom code element described earlier because if i remove it the data for ot_id is not stripped.

What is the best way to get around this and have the ot_id value updated on submital of the form.

Thanks in advance

Hi ramakunga,

I think that what is happening is that the custom code is over-writing the ot_id value. Try changing it like this:

<?php
foreach  ($form->data['extra'] as $k => $v ) {
  if ( empty($form->data['extra'][$k]['ot_id']) ) {
    $form->data['extra'][$k]['ot_id'] = $form->data['id'];
  }
}
?>

That should keep any existing values.

Bob

Hi Bob

This works for existing records but if a new record is created in the multiplier this also needs to have the value of ot_id set to the same as the other records in the multiplier

thanks for your efforts so far

Hi ramakunga,

I can't work out the logic in my head but I expect that as you have the values you need you can find a way to identify the new record and add the value where it is needed.

Bob