• Registered: 2014-06-25
  • Posts: 861

Topic: Check duplicates before saving with combobox

Hi MVDB fans,

I am looking for the proper syntax to check if IDs are already in use when selecting them from Combobox and before clicking on "save" to make sense.

Current working code to check when clicking on save:


 //##################################################################################################
 //#
 //# Check duplicate function
 //#
 //##################################################################################################

function CheckDuplicate (Action, sTable, sField, sValue: string; id: integer;): boolean;
var
   s: string;
begin
     result := False;
     if Action = 'NewRecord' then
     begin
          s := SQLExecute ('SELECT Count(*) FROM '+sTable+' WHERE '+sField+' LIKE "' + sValue + '"');
          if StrToInt(s) > 0 then result := True; //check if value exists
     end;

     if Action = 'ShowRecord' then
     begin
          s := SQLExecute ('SELECT Count(*) FROM '+sTable+' WHERE ('+sField+' LIKE "' + sValue + '") AND (id <> '+ IntToStr(id) +')');
          if StrToInt(s) > 0 then result := True;
     end;
end;

// Check if SIMNr id already used in Users table
procedure Details_ButtonSaveDet_OnClick (Sender: string; var Cancel: boolean);
begin
          if CheckDuplicate(Details.dbAction, 'Users', 'id_SIM', Details.ComboSimNr.SQLValue, Details.ButtonSaveDet.dbGeneralTableId)

then
     begin
          MessageDlg('This SIM number is already in use.', mtError, mbOk, 0); // Displays a red 'X', header is: Error
          Cancel := True;
end;
end;
  • Registered: 2014-02-14
  • Posts: 10,294

Re: Check duplicates before saving with combobox

Hello,


This code does not work?

Dmitry.
  • Registered: 2014-06-25
  • Posts: 861

Re: Check duplicates before saving with combobox

Hi Dmitry,

the code works yes, but I would like to display the "duplicate message" when I select an item in the combobox OR find a way to not display already assigned items in the combobox which is even more tricky.

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Check duplicates before saving with combobox

Try to use event OnCloseUp

procedure Details_ComboSimNr_OnCloseUp (Sender: string);
begin
   if CheckDuplicate(Details.dbAction, 'Users', 'id_SIM', Details.ComboSimNr.SQLValue, Details.ButtonSaveDet.dbGeneralTableId) then
  begin
     MessageDlg('This SIM number is already in use.', mtError, mbOk, 0); // Displays a red 'X', header is: Error
     Details.ComboSimNr.dbItemID := -1;
   end;
end;


also you can create calculate field to display if record is used

(CASE WHEN (SELECT COUNT(*) FROM person WHERE person.id_groups=groups.id)>0 THEN '(used)' ELSE '' END)

in the combobox you can show two field, Fieldname = {fieldname}   {calc_fieldname}

Dmitry.
  • Registered: 2014-06-25
  • Posts: 861

Re: Check duplicates before saving with combobox

Thanks a lot Dmitry, the calculated field is a brilliant idea actually. I will test that.

  • Registered: 2014-06-25
  • Posts: 861

Re: Check duplicates before saving with combobox

Ok, and how do you filter this calculated field using combobox properties? I want to discard 'used' from the results so only free items would be displayed.

I tried different things in filter properties but it ended up with an error and the application stuck in task manager with CPU at 100%

http://myvisualdatabase.com/forum/misc.php?action=pun_attachment&amp;item=1493

Post's attachments

Attachment icon combo_calculatedfield_filter_error.png 35.22 kb, 340 downloads since 2015-11-26 

  • Registered: 2014-06-25
  • Posts: 861

Re: Check duplicates before saving with combobox

Ah, and it is even more complex, I need to display also the number which is actually used by the user...

  • Registered: 2014-02-14
  • Posts: 10,294

Re: Check duplicates before saving with combobox

tcoton
please attach your project.

Dmitry.
  • Registered: 2014-06-25
  • Posts: 861

Re: Check duplicates before saving with combobox

I will send you something privately, the current database contains corporate data I have to anonymize before.

  • Registered: 2014-06-25
  • Posts: 861

Re: Check duplicates before saving with combobox

I sent you my project, thanks in advance.

  • Registered: 2014-06-25
  • Posts: 861

Re: Check duplicates before saving with combobox

Issue has been fixed in http://myvisualdatabase.com/forum/viewtopic.php?id=1912