Page 1 of 1

Check Duplicate with two field

Posted: Thu Jan 11, 2018 6:13 pm
by nadimsia

Hi...

In PHPMAKER there have a "check duplicate" on Edit Tag but it only for individual field. How about, If I want to set when two field already exist, its cannot be duplicate/copy.

Example.
Data already exits.
Field A = Toyota
Field B = Altis

When insert new data, field A = Toyota, field B = Vois. So data can be added.
But when insert new data, field A = Toyota, field B = Altis, So data cannot be added.

Its mean when data field A and field B are present, then it cannot be add again.
Only one of the field already exists, then it can be added again.


Re: Check Duplicate with two field

Posted: Thu Jan 11, 2018 6:28 pm
by kirondedshem

Phpmaker has only support for checking duplicate on one, so if you want a composite key kind of check then.
There is no such a thing but you can easily do it in row_inserting and row_updating events like this.

// Row Inserting event
function Row_Inserting($rsold, &$rsnew) {
	// Enter your code here
	// To cancel, set return value to FALSE


	$if_exists  = ew_ExecuteScalar("SELECT count(*) FROM my_table where field_a = '".$rsnew["field_a"]."' and field_b = '".$rsnew["field_b"]."' ");    

	if($target_exists > 0)
	  {
		$this->CancelMessage = "This Combination of Field A and Field B already exists";
		 return FALSE;
	  }
	  else
	  {
	  
	  return TRUE;
	  
	  }
	
}

NOTE: If you allow editing and a user can change eithier of the fields then you need to add an axtra check to ensure that when editing a record which already had a given combination, it does not report itself as already exists, like this.

// Row Updating event
function Row_Updating($rsold, &$rsnew) {
	// Enter your code here
	// To cancel, set return value to FALSE

	$target_exists  = ew_ExecuteScalar("SELECT count(*) FROM field_a where field_a = '".$rsnew["field_a"]."' and field_b = '".$rsnew["field_b"]."' and id != '".$rsold["id"]."'");    
	

	if($target_exists > 0)
	  {
		$this->CancelMessage = "This Region Monthly Target Already Exists";
		 return FALSE;
	  }
	  else
	  {
	  
	  return TRUE;
	  
	  }
	  
}

Re: Check Duplicate with two field

Posted: Thu Jan 11, 2018 9:23 pm
by sangnandar

If you do composite key within db, phpmaker will acknowledge it.


Re: Check Duplicate with two field

Posted: Fri Jan 12, 2018 5:36 pm
by nadimsia

Thank you,

I tried using your scripts, but I can still enter data even though the data on both already exists.


Re: Check Duplicate with two field

Posted: Fri Jan 12, 2018 7:06 pm
by kirondedshem

All you have to do is to make an sql check if there exists a record with had some value for both fields A and B.

paste how you implimented your code with an example of your table schema any maybe some sample data, am sure your missing something small


Re: Check Duplicate with two field

Posted: Thu Mar 07, 2024 6:50 pm
by alivepro

How can we make a yes or no popup screen instead of a cancel message?


Re: Check Duplicate with two field

Posted: Thu Mar 07, 2024 6:56 pm
by arbei

Note that you have already submitted the form data to the server side, you can't have yes/no popup message on the client side. To have such dialog, you need to check the field values on the client side before submitting the form, you may Create Your Own API Action to check.