insert records from a subform into a table

[cuadradoja]

I have a form called 'fent' and in it, a subform called 'snom' with several records. The problem is that I don't know how to insert into another table called 'finven' only those records from 'snom' that have the 'entry' checkbox activated as the only condition.
If you can help me, I would be very grateful...

[kev1n]

Hi,

You can use PHP in the BS (Before Save) event to achieve that.

The following sample code loops through the rows of a subform object named 'snom' and insert data into a database table named 'finven' based on certain conditions.

The code begins by creating a reference to the 'snom' subform object and retrieving the rows, fields, and deleted values from that object.

Next, the code searches for the column indexes of specific fields in the 'snom' subform object using the array_search function. The values to search for are hard-coded strings ('entry', 'other1', 'other2') and should be replaced with the ids of your objects/columns.

The code then loops through each row of the 'snom' subform object and checks if the delete checkbox is not ticked. If the checkbox is not ticked, the code checks if the entry checkbox is checked. If the entry checkbox is checked, the code prepares an SQL insert statement and executes it using the nuRunQuery function.

Code: Select all

$sfObj = nuSubformObject('snom');
$rows = $sfObj->rows;
$fields = $sfObj->fields;
$deleted = $sfObj->deleted;

// Retrieve column index of object ids
$entryCol = array_search('entry', $fields);
$other1Col = array_search('other1', $fields); // <-- replace other1 with your id
$other2Col = array_search('other2', $fields); // <-- replace other1 with your id
// add more ....

// Loop through subform rows
for ($row = 0; $row < count($rows); $row++) {

    $delete = $deleted[$row];
    if ($deleted === 0) {
        // Delete checkbox is not ticked

        $entryChecked = $rows[$row][$entryCol];
        if ($entryChecked == '1') {
            // entry checkbox checked

            // Insert statement
            $insert = "
        		INSERT INTO `finven`(
        				`finven_id`,
        				`other_1`, -- replace with your columns
        				`other_2` -- replace with your columns
        				)
        		VALUES(
        				:id,
        				:other_1,
        				:other_2
        		)
			";

            // Insert parameters
            $params = array(
                "id" => $rows[$row][0],
                "other_1" => $rows[$row][$other1Col], -- replace with your columns
                "other_2" => $rows[$row][$other2Col], -- replace with your columns
            );

            // Run insert query
            $result = nuRunQuery($insert, $params, true);
            if ($result == 0) {
                // nuDebug('Insert sucessful!');

            }

        }

    }

}