check and avoid duplicate fields and show custom error message

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

check and avoid duplicate fields and show custom error message

Post by fgazza » 2019-10-12 08:39

Hello to all
I have a "staff_progetto" table.
In the table there are a field "nome" (= name), a field "cognome" (= surname) and a field "cognome_nome".
The "cognome_nome" field automatically concatenate (on change) the "cognome" field and the "nome" field.
I need to prevent that in my database there are duplicates of the COUPLE "cognome" and "nome" fields (for this I created the "cognome_nome" field and I thought I would create a code that prevents the presence of two lines with the same "cognome_nome" text in the database).
I also need to make sure that if someone enters a "nome" and "cognome" so to the concatenated "cognome_nome" field result already present in my table a custom error message appears and the saving of the record with duplicate "cognome_nome" is inhibited.

To do all this, thanks to another user of the forum that I contacted privately, I have a code, but it does not work: no error message appears in the case of a surname / duplicate name and, if I do not set the "unique" feature directly in appgini the field this field is saved normally also if duplicated. If instead I set the "unique" value in the fields the field is not saved but the normal appgini error message appears.

I insert below the code present in my files:
- HOOK -> staff_progetto.php
- HOOK -> staff_progetto-dv.js
- ROOT -> full_name_check.php

I hope someone can help me! Thanks!

Fabiano

1) code in HOOK -> staff_progetto.php ###################################

<?php
// For help on using hooks, please refer to https://bigprof.com/appgini/help/workin ... tion/hooks

function staff_progetto_init(&$options, $memberInfo, &$args){

/* ATTENZIONE: ho tolto questa istruzione perchè consentiva l'inserimento di una concatenazione cognome_nome bypassando la definizione "unique" del campo e generando la successiva inaccessibilità della tabella a causa di un errore

sql("UPDATE staff_progetto SET cognome_nome = CONCAT_WS('', cognome, ' ', nome)", $eo); */



return TRUE;
}

function staff_progetto_header($contentType, $memberInfo, &$args){
$header='';

switch($contentType){
case 'tableview':
$header='';
break;

case 'detailview':
$header='';
break;

case 'tableview+detailview':
$header='';
break;

case 'print-tableview':
$header='';
break;

case 'print-detailview':
$header='';
break;

case 'filters':
$header='';
break;
}

return $header;
}

function staff_progetto_footer($contentType, $memberInfo, &$args){
$footer='';

switch($contentType){
case 'tableview':
$footer='';
break;

case 'detailview':
$footer='';
break;

case 'tableview+detailview':
$footer='';
break;

case 'print-tableview':
$footer='';
break;

case 'print-detailview':
$footer='';
break;

case 'filters':
$footer='';
break;
}

return $footer;
}

function staff_progetto_before_insert(&$data, $memberInfo, &$args){
$data["cognome_nome"] = $data["cognome"] . " " . $data["nome"];
return TRUE;
}

function staff_progetto_after_insert($data, $memberInfo, &$args){

update_staff_progetto_list();

return TRUE;
}

function staff_progetto_before_update(&$data, $memberInfo, &$args){
$data["cognome_nome"] = $data["cognome"] . " " . $data["nome"];
return TRUE;
}

function staff_progetto_after_update($data, $memberInfo, &$args){

update_staff_progetto_list();

return TRUE;
}

function staff_progetto_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){

return TRUE;
}

function staff_progetto_after_delete($selectedID, $memberInfo, &$args){

update_staff_progetto_list();

return TRUE;

}

function staff_progetto_dv($selectedID, $memberInfo, &$html, &$args){

if(!in_array($memberInfo['group'], ['Admins'])) {
ob_start();
?>
<script>
$j(function() {
if($j('[name=SelectedID]').val().length) {
$j('#nome').prop('readonly', true);
}
})
</script>
<?php
$html .= ob_get_clean();
}


if(!in_array($memberInfo['group'], ['Admins'])) {
ob_start();
?>
<script>
$j(function() {
if($j('[name=SelectedID]').val().length) {
$j('#cognome').prop('readonly', true);
}
})
</script>
<?php
$html .= ob_get_clean();
}


}

function staff_progetto_csv($query, $memberInfo, &$args){

return $query;
}

function staff_progetto_batch_actions(&$args){

return array();
}
function update_staff_progetto_list() {
// retrieve existing cognome and nome
$staff_progetto = array();
$res = sql("select * from staff_progetto order by cognome", $eo);
while($row = db_fetch_assoc($res))
$staff_progetto[] = "{$row['cognome']} {$row['nome']}";

// save the tags to the options list file
$list_file = dirname(__FILE__) . '/consuntivo_eventi_incontri.enti_operatori_coinvolti.csv';
@file_put_contents($list_file, implode(';;', $staff_progetto));
}

2) code in staff_progetto-dv.js ###############################################

$j(document).ready(function(){
$j('#cognome_nome').attr('type','hidden');
var cogname_val=$j('#cognome_nome').val();
$j('#cognome_nome').before('<span id="cognome_nome_label">'+cogname_val+'</span>');
function updateName()
{
var nome = $j( '#nome' ).val();
var cognome = $j( '#cognome' ).val();
$j( '#cognome_nome_label' ).text(cognome+' '+nome);
$j( '#cognome_nome' ).val(cognome+' '+nome);

var SelectedID=$j('[name=SelectedID]').val();

$j.ajax({
url:'full_name_check.php',
type:'post',
dataType:'json',
data:{'full_name':cognome+' '+nome,'SelectedID':SelectedID},
success:function(resp)
{
if(resp['availability']==1)
{
// alert('Il nome completo esiste già');
$j('#cognome_nome-uniqueness-note').show();
setTimeout(function(){
$j('#cognome_nome-uniqueness-note').hide('slow');
},1500);
}
},
error:function(err)
{
console.log(err);
}
});
}

$j('#cognome,#nome').on('change', function() {
updateName();
});
});


3) code in ROOT -> full_name_check.php ###################################

<?php
include('lib.php');
$full_name=makesafe($_POST['full_name']);
$SelectedID=makesafe($_POST['SelectedID']);

$q="SELECT COUNT(*) FROM staff_progetto WHERE LOWER(cognome_nome)='".strtolower($full_name)."'";
if(!empty($SelectedID) && is_numeric($SelectedID))
$q.=" AND pri_key!='".$SelectedID."'";

$res_count=sqlValue($q);
if($res_count>0)
$data['availability']=1;
else
$data['availability']=2;

echo json_encode($data);
?>

THANK YOU TO ALL FOR HELP!

Fabiano

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: check and avoid duplicate fields and show custom error message

Post by pbottcher » 2019-10-12 19:40

Hi,

can you explain what is not working? The code itself seem clean.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

Re: check and avoid duplicate fields and show custom error message

Post by fgazza » 2019-10-13 00:09

Hello. An example of what happen: In the table there is already a record with the nome_cognome field containing the text "Altman John". Now I create a new record where I insert john in the nome field and insert Altman in the cognome field. The cognome_nome field is automatically filled with the contents of the cognome and nome fields.

If before to export my app from appgini i have selected the option "unique" for the field cognome_nome, filling the new record for a few seconds a message appears warning you that the cognome_nome field is duplicated (but it is the standard message of appgini and not my personalized message I would like) and when I save the record the record is not saved and the standar appgini error message appear.

If before to export my app from appgini i have NOT selected the option "unique" for the field cognome_nome, filling the new record with duplicated data in cognome_nome field, the record is saved without errors and with duplicated cognome_nome field.

so it seems that even if the code is clean and no code error messages appear, the code does not do its job which should be:
if cognome_nome already exists a custom error message should appear "il nome completo esiste già'" and it should be IMPOSSIBLE to save the record with surname_ duplicate name

I hope for your help

Thank you very much!

Fabiano

fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

Re: check and avoid duplicate fields and show custom error message

Post by fgazza » 2019-10-13 09:25

Excuse me,
looking at the code I realized the error message was commented and therefore didn't work.
Now the error message appears (see screenshot).
This would be fine if after clicking "OK" the contents of the first and last name were deleted but if the "cognome_nome" field is not set as "unique" in appgini I can still save the record even if there is already a "cognome_nome" equal in the table.
custom error.png
custom error.png (6.51 KiB) Viewed 4387 times
If instead in APPGINI the cognome_nome field is set to "unique" I cannot save the record but when I give the "save record" command the standard error message of APPGINI appears and not a personalized message.

Furthermore, the APPGINI standard error message appears on a blank page (see screenshot) and not on a page formatted with the graphics of the other pages. It's normal?
duplicated_cognome_nome.png
duplicated_cognome_nome.png (10.2 KiB) Viewed 4387 times
Thanks!

Fabiano

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: check and avoid duplicate fields and show custom error message

Post by pbottcher » 2019-10-13 10:18

Hi,

well, just clear the fields and let the user re-enter the data.

$j.ajax({
url:'full_name_check.php',
type:'post',
dataType:'json',
data:{'full_name':cognome+' '+nome,'SelectedID':SelectedID},
success:function(resp)
{
if(resp['availability']==1)
{
alert('Il nome completo esiste già');
$j('#cognome_nome-uniqueness-note').show();
setTimeout(function(){
$j('#cognome_nome-uniqueness-note').hide('slow');
},1500);
$j('#cognome, #nome, #cognome_nome').val('');
$j( '#cognome_nome_label' ).text('');
}
},
error:function(err)
{
console.log(err);
}
});

Or you could disable the save button until a correct combination is entered and only activate it then.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

Re: check and avoid duplicate fields and show custom error message

Post by fgazza » 2019-10-13 15:50

Hi pböttcher!
It works fine.
Thank you so much!
A special thanks to Uday Vattury for the original code that also works like a charm!
Ciao!
Fabiano

fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

Re: check and avoid duplicate fields and show custom error message

Post by fgazza » 2019-10-14 23:09

Sorry but now I'm trying to do the exact same thing with a table named anagrafica_persone where the check is done on three fields:
nome
cognome
codice_fiscale

the tree fields automatically concatenate in the field nome_cognome_cf

Everything works perfectly until the error message appears, if the "nome_cognome_cf" field is duplicated but when I clic on OK in the error mesage the nome, cognome, codice_fiscale and cognome_nome_cf fields are not cleaned how I wish (fields remain filled).

I think I have correctly reproduced the code that worked with the other table (staff_progetto) but obviously I'm neglecting something.

Thanks for your help!

Fabiano

Here is my code:

$j(document).ready(function(){
$j('#cognome_nome_cf').attr('type','hidden');
var cogname_val=$j('#cognome_nome_cf').val();
$j('#cognome_nome_cf').before('<span id="cognome_nome_cf_label">'+cogname_val+'</span>');
function updateName()
{
var nome = $j( '#nome' ).val();
var cognome = $j( '#cognome' ).val();
var codice_fiscale = $j( '#codice_fiscale' ).val();
$j( '#cognome_nome_cf_label' ).text(cognome+' '+nome+' '+codice_fiscale);
$j( '#cognome_nome_cf' ).val(cognome+' '+nome+' '+codice_fiscale);

var SelectedID=$j('[name=SelectedID]').val();

$j.ajax({
url:'full_name_cf_check.php',
type:'post',
dataType:'json',
data:{'full_name_cf':cognome+' '+nome+' '+codice_fiscale,'SelectedID':SelectedID},
success:function(resp)
{
if(resp['availability']==1)
{
alert('ATTENZIONE: : è già presente un record con questo nome, cognome e codice fiscale. Non è possibile inserire due volte lo stesso nominativo.').show();
setTimeout(function(){
$j('#cognome_nome_cf-uniqueness-note').hide('slow');
},1500);
$j('#cognome, #nome, #codice_fiscale, #cognome_nome_cf').val('');
$j( '#cognome_nome_cf_label' ).text('');
}
},
error:function(err)
{
console.log(err);
}
});
}

$j('#cognome,#nome,#codice_fiscale').on('change', function() {
updateName();
});
});

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: check and avoid duplicate fields and show custom error message

Post by pbottcher » 2019-10-15 06:37

Hi,

your syntax at the alert is wrong (you have combined two statements).
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

fgazza
Veteran Member
Posts: 205
Joined: 2019-04-30 17:37

Re: check and avoid duplicate fields and show custom error message

Post by fgazza » 2019-10-15 14:47

Ok. I found my mistake and I corrected it!
Now everything works perfectly!
Thanks! Fabiano

pfrumkin
Veteran Member
Posts: 157
Joined: 2020-02-18 17:58
Location: Albuquerque, New Mexico USA

Re: check and avoid duplicate fields and show custom error message

Post by pfrumkin » 2020-03-24 17:24

Thank you and kudos to both of you. This is great work. I am inexperienced at jquery so it still took me a bit of time to apply this to my app, but now it works like a champ!

We have one set of pages that required catching changes on a lookup dropdown, so we have to watch each container for change separately, while on another set of pages we can group all the controls into a single on.change watch. For us we did not have to modify the hook at all - just create the -dv.js and the .php to query the database.

~Paul

Post Reply