1 Topic by AD1408 (edited by AD1408 2017-04-08 15:05:53)

  • Registered: 2016-07-28
  • Posts: 1,028

Topic: System dialog message

I like to replace the following Error dialog message with custom info dialog. Is it possible, if so how please?


https://s21.postimg.org/jlw53kyo7/zzzzz_Temp17.png


Situation I'm trying to find solution for as follows:


1. I have customer records on customers tGrid.


2. When customer has an sale invoice record (Sale invoice table is linked to customer table, id_Customer), clicking on delete button on customers tGrid returns the above error message after standard delete confirmation dialog. It's fine  in respect of database integrity which prevents user having invoice record without a customer, but not a user friendly one. I wanted to replace foreign key constraint error dialog with a friendly info dialog box.


Other solution I thought adding cascade delete on sale invoice table but it doesn't seems an attractive in this case. User may delete a customer by mistake which may cause considerable problem for the user, especially if deleted customer had many invoices etc.


Please see attached sample project. First 3 customer has invoice, clicking on delete for any of those customer with invoice brings the above unfriendly foreign key error dialog

Post's attachments

Attachment icon ForeignKey Constraint 2.zip 5.95 kb, 378 downloads since 2017-04-08 

Adam
God... please help me become the person my dog thinks I am.
  • Registered: 2015-02-24
  • Posts: 446

Re: System dialog message

Hello Adam,


I have first to say that, in an accounting software, I would not allow deleting clients and invoices once validated. You could allow archiving them for example (add a boolean field archived for example to the client table).


Now, if you really want to delete records from clients, the normal MVD system will not let you if he finds foreign key constraints.


To my knowledge, the only way to customize the message would be to add an OnClick Event on the delete button with something like :

procedure Form1_Button6_OnClick (Sender: string; var Cancel: boolean);
var
    nb_invoice, client_id : Integer;
begin
    client_id := Form1.tgMainCustomers.dbItemID; //WHAT IS THE CLIENT ID ?
        if client_id < 1 then //IF CLIENT ID < 1 THEN NO CLIENT SELECTED, CANCEL THE DELETE ACTION
            begin
                Cancel := True;
                messageDLG('You need to select a customer if you want to perform a delete action',mtWarning,mbOK,0);
            end
        else if client_id >= 1 then //A CLIENT HAS BEEN SELECTED
            begin
                nb_invoice := SQLExecute('SELECT COUNT(id) FROM Invoice WHERE id_customer = "'+IntTOStr(client_id)+'"'); //HOW MANY INVOICES FOR THIS CLIENT ?
                    if nb_invoice = 1 then //ONLY ONE INVOICE, SINGULAR WARNING
                        begin
                            Cancel := True;
                            messageDLG('You can not delete this customer because he has an invoice attached to his account'+#13#10#13#10+'You first need to delete his invoice.',mtError,mbOK,0);
                        end
                    else if nb_invoice > 1 then //MORE THAN ONE INVOICE, PLURIAL WARNING
                        begin
                            Cancel := True;
                            messageDLG('You can not delete this customer because he has invoices attached to his account'+#13#10#13#10+'You first need to delete his invoices.',mtError,mbOK,0);
                        end;
            end;
end;

If none of the condition tested is met, then the delete action is performed.


Wait for the others to answer, but this is how I do it.


Hope this helps



Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house
Zaza Gabor
  • Registered: 2014-02-14
  • Posts: 10,294

Re: System dialog message

Using this example
http://myvisualdatabase.com/forum/misc. … download=1


you can catch exceptions from a database and replace system message.

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

Re: System dialog message

I think it is forbidden by law to delete accounting information, you'd better tag them for archiving. You might be in big trouble should you have an audit... I would replace the ugly error message with something like " Operation not permitted" or remove the button "Delete" anyway.

  • Registered: 2016-07-28
  • Posts: 1,028

Re: System dialog message

Hi Guys,


Thanks a lot for your help and advise......
I'm not trying to delete records having dependent records... In any case they shouldn't be deleted for the sake of db integrity beside anything else.
I just want to replace "FOREIGN KEY constraint failed..." system message dialog with a custom one.


Dmitry,
I tried the following with sample project on post #1 but couldn't catch it:

function OnSQLException(Sender: TObject; Msg: string; SQL: string): boolean;
begin
    // exception from functions SQLExecute or SQLQuery
    if Sender=nil  then
    begin
        result := true; // to prevent system message
        ShowMessage(Msg); // your message
        MessageBox('Selected record contains...', 'Error', MB_OK+MB_ICONWARNING);
    end;
end;
Adam
God... please help me become the person my dog thinks I am.
  • Registered: 2014-02-14
  • Posts: 10,294

Re: System dialog message

AD1408
Check it out

// global event for all exceptions related database
function OnSQLException(Sender: TObject; Msg: string; SQL: string): boolean;
begin
    // exception from button Form1.Button6
    if Sender = Form1.Button6 then
    begin
        if Pos('FOREIGN KEY constraint failed', Msg)=1 then
        begin
            result := True; // to prevent system message
            MessageBox('Selected record contains...', 'Error', MB_OK+MB_ICONWARNING);
        end;
    end;
end;

Project:

Post's attachments

Attachment icon ForeignKey Constraint 2_fixed.zip 6.85 kb, 431 downloads since 2017-04-10 

Dmitry.
  • Registered: 2016-07-28
  • Posts: 1,028

Re: System dialog message

Thank you very much Dmitry.........................

Adam
God... please help me become the person my dog thinks I am.

8 Reply by AD1408 (edited by AD1408 2017-05-04 22:04:59)

  • Registered: 2016-07-28
  • Posts: 1,028

Re: System dialog message

I couldn't apply foreign key constraint script to multiple delete buttons on same form.


I have lookups form contains about 15 tables with 15 delete buttons on tab pages. How can I apply one  foreign key constraint script to multiple delete buttons on a same form rather than repeating the script 15 times?


function OnSQLException(Sender: TObject; Msg: string; SQL: string): boolean;
begin
    if Sender = frmLookups.btnGenTaxRateDelete + frmLookups.btnGenCountryDelete + frmLookups.btnGenRetReasonDelete then
    begin
    if Pos('FOREIGN KEY constraint failed', Msg)=1 then
        begin
            result := True; // to prevent system message
            MessageBox('Record cannot be deleted....',
            'Error', MB_OK+MB_ICONWARNING);
        end;
    end;
end;
Adam
God... please help me become the person my dog thinks I am.
  • Registered: 2015-11-13
  • Posts: 652

Re: System dialog message

Adam,  It's only one function. You just need to trap which button caused the constraint. You could do something like the following.

function OnSQLException(Sender: TObject; Msg: string; SQL: string): boolean;
begin
    if Pos('FOREIGN KEY constraint failed', Msg)=1 then
        begin
            result := True; // to prevent system message
            case sender of    // Each of the following messages should be customized for the appropriate delete
                 frmLookups.btnGenTaxRateDelete : MessageBox('Record cannot be deleted....', 'Error', MB_OK+MB_ICONWARNING);
                 frmLookups.btnGenCountryDelete : MessageBox('Record cannot be deleted....', 'Error', MB_OK+MB_ICONWARNING);
                 frmLookups.btnGenRetReasonDelete : MessageBox('Record cannot be deleted....', 'Error', MB_OK+MB_ICONWARNING);
            end;
        end;
end;
  • Registered: 2016-07-28
  • Posts: 1,028

Re: System dialog message

Hi EHW,


Thank you so much.................................

Adam
God... please help me become the person my dog thinks I am.