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.

allan

Yeah - it uses nested arrays rather than having the full name with dot delimitation in it.

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

Possibly something to do with the id? If you add ->debug(true) before the ->process(...) call, the JSON response from the server will include the SQL that is being executed, which might be useful. Feel free to post the full JSON here if you like.

Allan

carrarachristophe

Thanks both,
I fixed it thanks to your comments and I can now create/update/delete the chid upon the creation/update/deletion of the parent, but I still have 3 things to fix before getting what I exactly need.
Allow me to start with the first one.
As you can see below, the description of the child is a concatenation of several fields of the parent:

    ->on( 'writeCreate', function ( $editor, $id, $values ) use ( $db ) {
        $db->insert( 'tms_fl_flow', array(
            'FinancialTransaction'  => $id,
            'AccountId'             => $values['tms_ft_financialtransaction']['TransactionAccount'],
            'FlowCodeId'            => '1',
            'BudgetCodeId'          => '1',
            'FlowStatusId'          => $values['tms_ft_financialtransaction']['Statut'],
            'TransactionDate'       => $values['tms_ft_financialtransaction']['TransactionDate'],
            'ValueDate'             => $values['tms_ft_financialtransaction']['ValueDate'],
            'FlowCurrencyId'        => $values['tms_ft_financialtransaction']['TransactionCurrency'],
            'FlowAmount'            => $values['tms_ft_financialtransaction']['TransactionAmount'],
            'FlowDescription'       => 'Test ' .$id.' '.$values['tms_ft_financialtransaction']['TransactionCurrency'].' '.$values['tms_ft_financialtransaction']['TransactionAmount']
        ) );
    } )

What I need is, instead of quoting the value of the currency select field (meaning $values['tms_ft_financialtransaction']['TransactionCurrency']), quoting the code of the currency ( 'TransactionCurrency.devise_code' below)

        Field::inst( 'tms_ft_financialtransaction.TransactionCurrency' )
            ->options( Options::inst()
                ->table( 'devises' )
                ->value( 'devise_id' )
                ->label( array('devise_code', 'devise_en') )
                ->render( function ( $row ) {
                    return $row['devise_code'].' ('.$row['devise_en'].')';
                } )
            )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Requis' )))
            ->validator( Validate::dbValues() ),
        Field::inst( 'TransactionCurrency.devise_en' ),
        Field::inst( 'TransactionCurrency.devise_code' ),

Is it possible to do that?

rf1234

Yes, that's possible:

if you have the other field "left joined" (don't know whether that's possible for you or not) you can just refer to it like to the other field.

$values['TransactionCurrency']['devise_code'];

If you can't do the left join with Editor you can still SELECT the suitable value and insert it then.