[SOLVED] Insert into form gridview returns autonumeric id = zero

[Intercore]

I am on the latest version 9.10.021 (2), in this version when a line insert is performed in a detail form (gridview), the ID returned is zero.

If we do a sc_redir itself and reload the detail form once inserted, specifically in onAfterInsert after sc_commit_trans(), it will collect the ID that has been recorded in the Table, it is seen that it has been incremented and inserted correctly, but the user is seeing Unnecessary ‘reloads’ of the form every time you insert a row.

Also, the problem comes when you need to use this ID in onAfterInsert, for example to write it to another Table,
1st we will have to do a “SELECT LAST_INSERT_ID()” to collect the newly recorded key,
2nd carry out the processes with the recovered ID.
3rd then execute sc_commit_trans().
and 4th sc_redir to reload the form, because if the user wants to modify the row again, it has to have its ID, not a zero.

Let’s look at the following:
If the user wants to update the row, after having inserted it, and in our processes we need to use the ID of the row that is being modified, in an onValidate or onBeforeUpdate, this ID field will be at ZERO, we will not be able to search even in its Table, nor in external tables with a sql of the style: “SELECT field FROM Table WHERE ID = {ID}”, unless we had done everything indicated in the previous section (LAST_INSERT_ID()…,sc_commit_trans()…and sc_redir itself…).

Why doesn’t scriptcase do all this, simply reloading the ID field, with its new value, once inserted? The onAfterInsert event is very descriptive ‘After Insert’, well, if it has already been inserted, it means that a sc_commit_trans has already been processed or if there had been errors, we would not be allowed to be executing processes in onAfterInsert, the internal sc_rollback_trans of scripcase would have prevented us from reaching this event. I repeat again, what’s wrong with loading the ID field with the new key, once it has been successfully inserted?

Greetings

[Intercore]

Sorry, I think the following will clarify something important, regarding the versions.

Code that worked in version 9.10.020 in the onAfterInsert event

/*
– KARDEX.PHP LIBRARY –
We use the function to extract the latest balances and the Weighted Average Price.
*/

$v_saldos = fi_kardex_saldos({codresiduo}, [s_codcentro], {codalmacen});
$v_saldo_pmp = $v_saldos[2];
$v_cantidad_traspaso = {cantidad_traspaso} * -1;

/*
– KARDEX.PHP LIBRARY –
We updated the Warehouse kardex. We use the fi_kardex_insert Function to
create a new Warehouse Goods Output record.
We send the parameters:
“I” = Transaction Type (insert),
{codmovimiento_cen_env} = FK of the Master.
{codlinea} = PK of this Detail row.
{codalmacen} = Warehouse FK.
{codresiduo} = FK of the Residue.
$v_cantidad_traspaso = Amount that will be recorded in the kardex.
$v_saldo_pmp = Waste Inventory Cost Value (Weighted Average Price).
*/

fi_kardex_insert(“I”,
[g_codserie],
{codmovimiento_cen_env},
{codlinea},
{codalmacen},
{codresiduo},
$v_cantidad_traspaso,
$v_saldo_pmp);

sc_commit_trans();

---

Code that was incorporated in 9.10.021, in the onAfterInsert event, so that it can continue working:

/*
We extract the last inserted ID.
*/
sc_lookup(rl_codlinea, “SELECT LAST_INSERT_ID()”);
$v_codlinea = {rl_codlinea[0][0]};

/*
– KARDEX.PHP LIBRARY –
We use the function to extract the latest balances and the Weighted Average Price.
*/

$v_saldos = fi_kardex_saldos({codresiduo}, [s_codcentro], {codalmacen});
$v_saldo_pmp = $v_saldos[2];
$v_cantidad_traspaso = {cantidad_traspaso} * -1;

/*
– KARDEX.PHP LIBRARY –
We updated the Warehouse kardex. We use the fi_kardex_insert Function to
create a new Warehouse Goods Output record.
We send the parameters:
“I” = Transaction Type (insert),
{codmovimiento_cen_env} = FK of the Master.
$v_codlinea = PK of this Detail row.
{codalmacen} = Warehouse FK.
{codresiduo} = FK of the Residue.
$v_cantidad_traspaso = Amount that will be recorded in the kardex.
$v_saldo_pmp = Waste Inventory Cost Value (Weighted Average Price).
*/

fi_kardex_insert(“I”,
[g_codserie],
{codmovimiento_cen_env},
$v_codlinea,
{codalmacen},
{codresiduo},
$v_cantidad_traspaso,
$v_saldo_pmp);

sc_commit_trans();

/*
We reload the detail form.
*/
sc_redir (form_D3_regularizaciones_lin, ‘’, ‘_self’);

As seen in the code in v9.10.020, it was not necessary to use LAST_INSERT_ID(), the system returned the PK in the {codlinea} field and we could use it in onAfterInsert. We also didn’t need to do sc_redir.

It is proven that in addition to appearing in the “codlinea” field of the detail form, we make transactions that are recorded in other inventory tables (kardex) and that they save this detail ID and the Master form ID correctly.

Greetings

[Danilo_Lima]

Dear @Intercore,

The problem has already been identified and corrected in the version that is about to be released (9.10.022).

As soon as it is released, we will be providing feedback through this thread.

Best regards!

[Danilo_Lima]

Dear @Intercore,

The fix was released in version 9.10.022, which is now available for download/update.

Scriptcase Changelog | Form Builder | Buy now

Best regards!

[Intercore]

Checked, correct returns the ID, but…

Due to the problem, we had already created a New Function in our libraries, which ensures a more exhaustive monitoring of the autoincrements, avoiding ‘jumps’ in the numbering, it is the case that the user deletes the last record created (for having made a mistake in its content), if for example it was ID = 15, the DB will return ID = 16 when creating a new one, not the deleted 15, this is the normal operation of autoincrement.

We have proceeded to the following, in onBeforeInsert:

/*
We make sure that auto_increment is incremented correctly.
To do this, we prepare the PK before the Insert is performed in onAfterInsert.
*/
{codlinea} = fi_regenerate_PK(‘alm_movimientos_cen_env_lin’, ‘codlinea’);

The Function in the library:

function fi_regenerate_PK($p_table, $p_PK)
{
/*
Function to regenerate the PK declared auto_increment in a Table.
*/
$v_last_number_sql = "SELECT " . $p_PK . " FROM " . $p_table . " ORDER BY “. $p_PK . " DESC LIMIT 1”;
sc_lookup(rl_last_number, $v_last_number_sql);
if(isset({rl_last_number[0][0]}))
{
$v_next_PK = {rl_last_number[0][0]} + 1;
}else{
$v_next_PK = 1;
}

sc_exec_sql(“ALTER TABLE " . $p_table . " AUTO_INCREMENT = " . $v_next_PK);
sc_lookup(rl_PK, “SELECT AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = '” . $p_table .”’
AND TABLE_SCHEMA = ‘" . [s_database] . "’");
return {rl_PK[0][0]};
}

This is very useful, for example, when you are in a development DB and you manually delete records in the test table, the PK will always start again at 1, you will not have skipped increments, or if you delete all the records, when you create a new one, you will not you will find an ID of ‘44’ for example.

I have checked it and it does not interfere with your internal process, we replace the ID field with our calculation and we do not use the return that you provide, since you have made the insert query prior to the onBeforeInsert event, we ‘crush’ the content of the field.

We can use the return of the function, or use yours, the ALTER TABLE is already done.

{codlinea} = fi_regenerate_PK(‘alm_movimientos_cen_env_lin’, ‘codlinea’);
either
fi_regenerate_PK(‘alm_movimientos_cen_env_lin’, ‘codlinea’);

Greetings