Child created/updated upon creation/update of the parent

carrarachristophe

Hello,

I have 2 tables:
Transaction
*id
*date
*amount
*flowid

Flow
*id
*date
*amount

My datatables works fine for the 1st one.
But what I am trying to do is:
1. when creating the transaction, it creates as well the flow at the same time
2. when updating the transaction, it updates the flow (example: amount or date, based on the amount or date of the transaction) at the same time

It is not exactly a parent child editor because I want the flow to be created automatically.

Did anyone manage to implement something similar?

rf1234

Add this to your php Editor for "transaction"

Editor::inst( $db, 'transaction' )
....
->on( 'writeCreate', function ( $editor, $id, $values ) use ( $db ) {
    $db->insert( 'flow', array(
        'transaction_id' => $id,
        'date'           => $values['yourDate'],
        'amount'         => $values['yourAmount']
    ) );
} )
->on( 'writeEdit', function ( $editor, $id, $values ) use ( $db ) {
     $db->update( 'flow', array( 
         'date'        => $values['yourDate'],
         'amount'      => $values['yourAmount']
     ), array( 'transaction_id' => $id) );
} )

when updating the transaction, it updates the flow (example: amount or date, based on the amount or date of the transaction) at the same time

I would not recommend to do the UPDATE without having a foreign key of "transaction" in "flow" because the update might go wrong if you only do the matching based on the previous amount and date of the transaction. Hence my code requires the implementation of "transaction_id" as foreign key in table "flow".

Flow
*id
*transaction_id
*date
*amount

If you are not reading back the changed "flow" entries with this Editor, you can also use "postCreate" and "postEdit". Then you can also use "$row" to use the values read back from table "transaction".

You can also use your own db-handler in the above events with "global".

carrarachristophe

Thank you rf1234.
As a matter of fact, although I did not mention it, I had a transaction_id equivalent field.
I followed your code but am getting the following message: Undefined array key "date".
I will look into it and keek you posted

rf1234

If you want more flexibility, you can also use Editor's "raw" method with real SQL like this

$db->raw()
   ->bind( ':transaction_id', $id ) 
   ->bind( ':date', $values['yourDate'] )
   ->bind( ':amount', $values['yourAmount'] )
   ->exec( 'UPDATE flow
               SET `date`          = :date,
                   `amount`        = :amount
             WHERE transaction_id  = :transaction_id' );

carrarachristophe

Hi rf1234,
Thanks, but this method leads to the same result:

Undefined array key "tms_ft_financialtransaction.TransactionDate" in ... on line 166
Undefined array key "tms_ft_financialtransaction.TransactionAmount" in ... on line 167

    ->on( 'writeEdit', function ( $editor, $id, $values ) use ( $db ) {
        $db->raw()
           ->bind( ':FinancialTransaction', $id )
           ->bind( ':TransactionDate', $values['tms_ft_financialtransaction.TransactionDate'] )
           ->bind( ':FlowAmount', $values['tms_ft_financialtransaction.TransactionAmount'] )
           ->exec( 'UPDATE tms_fl_flow SET
                    `TransactionDate            = :TransactionDate,
                    `FlowAmount`                = :FlowAmount
                    WHERE FinancialTransaction  = :FinancialTransaction' );
    } )

The transaction_id works fine but the debug highlights a null value returned for date and amount

"UPDATE tms_fl_flow SET\r\n\t\t\t\t\t`TransactionDate`\t\t\t= :TransactionDate,\r\n\t\t\t\t\t`FlowAmount`\t\t\t\t= :TransactionAmount\r\n\t\t\t\t\tWHERE FinancialTransaction\t= :FinancialTransaction","bindings":[{
"name":":FinancialTransaction","value":"5","type":null},
{"name":":TransactionDate","value":null,"type":null},
{"name":":TransactionAmount","value":null,"type":null}]}

            {
                label: "Date de transaction:",
                name: "tms_ft_financialtransaction.TransactionDate",
                type: "datetime",
                format: "DD\/MM\/YYYY"  
            },
            {
                "label": "Montant 1:",
                "name": "tms_ft_financialtransaction.TransactionAmount"
            }

Any idea of where it can come?

rf1234

I think you need to check the $values array in your debugger.

Does $values['tms_ft_financialtransaction.TransactionDate'] exist at all? If so what does it contain? Same applies to $values['tms_ft_financialtransaction.TransactionAmount'].

I am pretty sure both don't exist.

You should probably use this:
$values['tms_ft_financialtransaction']['TransactionDate'] and
$values['tms_ft_financialtransaction']['TransactionAmount']

I don't see any set formatting. Do you really return the date and the amount from the client in database format? I usually return those values as formatted values depending on the user language
e.g. "1.000.000,99" and "23.11.2024" or "1,000,000.99" and "23/11/2024"

Then I use set formatters to get 1000000.99 and "2024-11-23 00:00;00"

Here is something I use for dates and amounts

function setFormatterDate($val) {  
    //Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: 
    //if the separator is a slash (/), then the American m/d/y is assumed; 
    //whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed. 
    //If, however, the year is given in a two digit format and the separator is a dash (-, the date string is parsed as y-m-d.
    if ( is_null($val) || $val <= '' ) {
        return null;
    }
    $val = str_replace('/', '-', $val);
    $val = str_replace('.', '-', $val);
    
    $dateTime = new DateTime($val);
    return $dateTime->format('Y-m-d H:i:s');
}

function setFormatterAmount($val) {
    if ( $val <= "" || $val === "-" || $val === '.' || $val === ',' ) {
        return 0;
    }    
    //explode to get rid of the 1,000 separators
    //works also for rates!!
    if ($_SESSION['lang'] === 'de') {     
        $numberArray = explode('.', $val);
    } else {
        $numberArray = explode(',', $val);
    }
    //implode without delimiter to join the pieces again
    $numberString = implode($numberArray);
    //replace the German decimal comma with a period
    if ($_SESSION['lang'] === 'de') {   
        $numberString = str_replace(',', '.', $numberString);
    }
    return $numberString;
}

carrarachristophe

Thanks rf1234!
Looks like I indeed had to use

$values['tms_ft_financialtransaction']['TransactionDate']
$values['tms_ft_financialtransaction']['TransactionAmount']

instead of

$values['tms_ft_financialtransaction.TransactionDate']
$values['tms_ft_financialtransaction.TransactionAmount']

I still doesn't update the child, but I have no more error message and I will keep working on it.