Search options (Page 6 of 16)

Sometimes, when working with bunch of record/row in my database, complex calculated field make my application slow, even hang sometimes especially for the users with a low-spec computers. But then I use it(calculated) in some cases.

Trigger is a piece of code inside your database. you have to code it manually,
I'm putting some of the business logic inside the database using trigger., think of it as an auto-query triggered before/after insert, update and delete. with values NEW.[field] and OLD.[filed] which you can use to update/insert/delete other record from other table of the database.

You can search for "trigger in sqlite" for that.

Just be careful for updating fields on a table using MVD will delete the trigger or sometimes prevent updating it. My way of doing trigger is to have a file and save all triggers there. Here's a sample of my trigger to give you an idea

DROP TRIGGER IF EXISTS "main"."debit_post";
CREATE TRIGGER "main"."debit_post"
AFTER UPDATE OF "dbt_is_posted"
ON "debit"
WHEN ((OLD.dbt_is_posted=0) AND (NEW.dbt_is_posted=1))
BEGIN        
    DELETE FROM commitment_payment WHERE id_commitment in (SELECT DISTINCT(id_commitment) FROM debit_commitment WHERE id_debit=NEW.id);

    INSERT INTO commitment_payment(
        id_commitment, id_account, id_fms_budget, amt, dtime, id_debit    
    )
    SELECT 
        debit_commitment.id_commitment,
        fms_budget.id_account,
        fms_budget.id,
        CASE WHEN (debit_commitment.fms IS NOT NULL ) THEN 
            SUM(debit_commitment.dbtc_amt)
        ELSE 
            debit.dbt_amt
        END
        as amt,
        debit.dbt_dtime AS dtime,
        debit.id as dvid
    FROM debit_commitment
    LEFT JOIN debit ON debit.id=debit_commitment.id_debit
    LEFT JOIN fms_budget ON fms_budget.id = 
        CASE
            WHEN (debit_commitment.id_fms_budget IS NULL ) THEN debit_commitment.fms
            ELSE debit_commitment.id_fms_budget
        END
    WHERE id_debit=NEW.id
    GROUP BY CASE
        WHEN (debit_commitment.id_fms_budget IS NULL ) THEN debit_commitment.fms
        ELSE debit_commitment.id_fms_budget
    END, fms_budget.id_account;
    UPDATE bdcm SET dbt_is_posted = 1 WHERE id_debit = NEW.id;
END;

Then before updating field using MVD, I am deleting all triggers created then re-run it(copy-paste). I'm using Database manager(Navicat) when creating and updating triggers. Save so much time. There are free software too like DBeaver, DB Browser, etc. I think some of then can create trigger too with just a few clicks. But knowing how to construct it manually is a great advantage.

You can put aside trigger for now and try to study it first.

I rarely use calculated fields on the applications I am working on. I rather use trigger or save-it-in-database. This might be a workaround on what you are looking for.

I also updated the filter date, since entering a record that will fall in the last day of the month will not be displayed on the search.
I just added 0.99999 (less than a millisecond/second for a day)

Try changing this line too

frmDetail.a_ampm.Text := FormatDateTime('AM/PM',frmDetail.dtpDate.DateTime);

to

frmDetail.a_ampm.Text := FormatDateTime('hh:nn AM/PM',frmDetail.dtpDate.DateTime);

It might be the one you are looking for. I just go with what your calculated fields do.

PS: I didn't change the report. just the saving part. You might want to just update it.

if you are using this code

If SqlExecute('Select Count(Id) From MyTable Where Col1 = "' + Form2.Edit_Col1.Text + '"') > 0 then
      Begin
         MessageBox('Duplicate Record Exists - Cannot Save','Error',MB_OK+MB_ICONERROR);
         Cancel := True;
         Form2.Edit_Col1.SetFocus;
         Exit;
      End;

Then, you might want to change it to

procedure Save_Button_OnCLick();
var
   _chk: String;
begin
   _chk := SQLExecute('SELECT COALESCE(Col1, '''') FROM MyTable WHERE Col1 LIKE "' + Form2.Edit_Col1.Text + '" LIMIT 1');
   if CheckExactString(Form2.Edit_Col1.Text, _chk) then
   begin
         Cancel := True;
         Form2.Edit_Col1.SetFocus;
         Exit;
   end;
end;

This might help you.

You're welcome.

I got here a procedure focusing the textbox. It's out of the topic but can help you save some time and have a better User Experience for your application

{
    Focus and select all text of a textbox
}
procedure FocusTBox(tb: TdbEdit);
begin
    tb.SelectAll;
    tb.SetFocus;
end;

Add it on your script, then change

Form2.Edit_Col1.SetFocus;

to

FocusTBox(Form2.Edit_Col1);

Frank,

Glad that you have fixed it. Well, MVD's really great in creating that kind of report for you. I've been trough a lot of SQL coding before(Web Development) just to create such reports and MVD simplified it all.

To fully understand what MVD's doing, you must learn a little bit of SQL Queries. You really must select from tbl_Service since that table handles the record(Master data) you are trying to display. You just have to LEFT JOIN it(Service) to tbl_Equp table to get the equip information.

MVD automatically creates that left join when you add equip field in your report. MVD will look on the reference for that table using the main table you select(tbl_Service in this case). MVD will find that reference(id_tbl_Equip) and join it to the table (tbl_Equip) to get equip details you ask MVD to fetch. Then if you add another table(in my example tbl_technician, it will look for id_tbl_technician and LEFT JOIN it to tbl_technician), and so on...

To sum it up, MVD creates SQL something like this behind that report button

SELECT
   tbl_Equip.eqName AS "tbl_Equip_eqName", 
   tbl_Service.s_date, 
   tbl_technician.t_name
FROM tbl_Service
LEFT JOIN tbl_Equip ON tbl_Equip.id = tbl_Service.id_tbl_Equp
LEFT JOIN tbl_technician ON tbl_technician.id = tbl_Service.id_tbl_technician 
WHERE tbl_Equip.eqNAME = "[Text_from_eqName_on_Form]"

Well, MVD is assigning each fields an alias using "AS" like I done in the first field.

Note: Just be careful with that kind of filter you used (eQName), Having 2 or more equipment with the same Name will ruin your report. It will display all services for all that equipment.

In deployment, there's more likely no equipment with the same name thou but if your application doesn't check it upon saving, then your application can have 2 or more equipment with the same(exact) name

It's hard to find the problem here Frank. I suggest you put all the SQL query here and maybe someone can point it out.

Here, I made an example. I hope you can get an idea from it.

On frmEquip, there are 2 Report buttons. First button will not work(not really, it just wont display equipment details) when no service is added to an equipment.

Report 2, on the other hand will display equipment even without service is added but will require additional script(well you can use that script later on especially when working with multi-table to display in a report)

PS: By the way, when you said tbl_SvcProc is a child, you mean in a service record you will have one or more procedure/s? I only make it as a connection in my sample

I'm not sure to where you want the default settings to appear, when creating new record? it seems your button on Form1 is just a Show Form.

Anyway, for further samples, you can have a global variable which will be available to all your functions/procedures within your script that will handle the defaults.

In Database,
Blue(Filled) = NULL
Hollow(Unchecked) = 0
Checked = 1

You can use the that filled when you are having a filter,
marking it as filled(blue) will give you both yes and no,
yes, for all checked,
no for all no or unchecked.

Yes there is. Just Uncheck the "Close the current form after saving". Then to make sure that it won't add a record(which happens to me sometimes), set dbDontResetID of the frmEquip.btnSave to True from the onClick of the save button

procedure frmEquip_btnSave_OnClick (Sender: TObject; var Cancel: boolean);
begin
    frmEquip.btnSave.dbDontResetID := True;
end;

For Option 1, try putting your code in the OnAfterClick event of frmService.btnSave

Don't copy my sample query, you have to get the query from your calculated field you are trying to get. Can you go to your database and put here the calculated query (calcNextSvcDate) from the table tbl_Equip you are trying to access