Advice: a cfField V.S. an SQLExecute on forms (Page 1 of 2)

Hi Joshua,
I've changed your calculated field to fix the problem (see attachment).
As the calculated field is held against the employee table and the fields used in the calculation are also on the employee table, you don't need to do a 'select' nor specify the table.
Derek.

Greetings derek and sparrow,
.
Thank you both for these quick responses, and especially for the added explanations!
.
I believe I was heading in the direction that sparrow provided, but as he put it: derek has a much nicer solution for this problem.

I'm going back to the drawing board on this design because I'm having trouble deciding which fields should be stored and which only need to be calculated...
.
My thinking is that since these values are reset annually- they don't need be stored, but this is where I'm confused.  Should this be a deciding factor?
.
I guess the values could be (just as easily) reset at the beginning of each year.
.
How would you more experienced folks handle this?

Hey Derek,
.
I was afraid that was an open, subjective question there.  But your response has given me some confidence in what I have going so far.  The project I have is working fine, but noticed those last two (unaddressed) items you mentioned.  This may have started my second guessing.
.
Thanks a lot for giving those different examples too!
.
Going back to this secondary project example, I added the month/year filter similar to the one from my other post.  I cannot get the dateTimePicker to start auto-checked in this project.
.
I have set and unset both checked and default checked properties, but this doesn't seem to do anything.  So, I tried writing it in a script to check it.  When I do this, the box is checked, but the tablegrid isn't filtered. (it is followed with searchButton.click too)
.
Not sure what's causing this, because it's working fine in my other main project so this is kinda minor...
.
But it leads to another (more important) request I have: while the dateTime filter is checked, when a new record is created, it removes the filter and shows all the records (even though it is still checked).
.
How can I keep the filter in place and prevent this from happening?  You can observe it with this project.

Hey sparrow,
.
This works nicely, and I appreciate the example.  There are so many tricks- I have so much to learn with MVD!
.
Thanks again to you and derek.

Hi Joshua,
Had a very quick look and there are a couple of things.
1.  I'm not sure why you are building a tstringlist of employee ids.  Why don't you simply loop through the ids in frmmain.tablegrid1 as they are already there?  Please see the attachment.
2.  I don't know how many updates you'll be doing when your program is live, but if there are a lot, then you might want to use 'beginsqlitetransaction / commitsqlitetransaction' as part of your update procedure as it will be appreciably faster.
3.  I probably wouldn't bother with a separate form for the updates - it's basically the rows in frmmain.tablegrid that drives it and it would keep your application that bit tighter - but it's marginal.
Derek.

Okay, so moving forward.  I'm starting to build multiple save buttons depending on where the frmAtt (is called) originated from.  What's the best way to handle this scenario?
.
I found this post doing a similar thing with labels, but I'm stepping into unfamiliar territory here...
.
Depending on where the form is called, I want to hide certain controls because they're not applicable.  The project I've attached is a simple example using ShowMessages to demonstrate my idea.
.
Currently, I have two different places where the button is called, and I need to hide (or do something special) for each case.  The Apply To Group button is one instance. When the new record button is called from the frmEmp it needs to do something different.
.
To me, this seems like I may be over-complicating things, and I feel like there might be a better way to approach this.  Would someone offer extra advice here?  I would appreciate it.  Any thoughts or ideas?

Hi Joshua, Hi Sparrow,
If you want REALLY simple (which I always do - LOL!), the easiest way is just to use two forms (see the attachment).
So in your example, frmmain.button5 calls frmatt-1 and frmemp.button2 calls frmatt-2.(or whatever you want to call them).
It's very 'clean' and means that you don't have to switch certain options on or off, make certain fields / buttons visible or not etc (so no script required).  It also means that you don't have to remember to 're-set' everything when you leave either.
Creating a 'duplicate' form is pretty easy and takes about 20 seconds.  I usually:
1.  note the height and width of the 'source' form
2.  right-click and 'select all' and then 'copy'
3.  create a new, empty form
4.  set the height and width equal to the size of the 'source' form
5.  right-click and select paste.
n.b:  if you do it this way, it will also copy procedures (if any) associated with objects on the 'source' form - so be sure to remove them from the new form.
Derek.

Okay- I may be back-stepping a little bit here...
.
So I'm wondering if it would be more simple, since I'm already using a DateTime field to represent both Date (and optional Time) together, using a single tableGrid column...  In a previous post, I was trying to separate these two.  And it is working perfectly, BTW.
.
The new column format is set to:

TNxdateColumn(frmEmp.TableGrid1.Columns[0]).FormatMask := 'mm/dd/yyyy HH:MM';

.
I don't care about seconds (which shows by default) so I would like to eliminate that part.  Is there a way to conditionally format this further to only include a range of times?  I'm only interested in showing the times within a window. Specifically between 06:00 and 18:00, otherwise I only wish to show the date (and exclude the time portion if possible) from the column.  Which in the case for this project, the time is set to 00:00 when it's not needed.
.
Example:
Date  /  Time   Column
11/02/2021
11/05/2021 06:12
11/10/2021 13:00
11/13/2021
11/16/2021 15:00
11/20/2021
.
Is there an easy way to achieve this, or should I just keep them separated?

Hi derek,
.
Just to recap on my latest post in here.  I'm not loosing my mind (yet).  I had this figured out already.  But now my users are asking for date and time ranges, so I had to revisit some past posts here too.  That's the reason for the seemingly revisit to this issue (again).
.
I experimented with your solution some, and it is possible to use a cfField to do what I needed (thanks to your example). However, one thing I noticed with my second requirement mentioned below; you cannot share form controls being used for the Date/Time field when the date and time are being used separately.
.
So I come to this conclusion (guideline):

• When there will always be a date with an optional time, it can be done using a single date/time database field.

• When both date and time are optional, this requires the date field and time field separately in the database.

• There probably are some other cases to be included here too...

.
I wasn't aware of these, and it always bugs me when I open the database externally and see a perfectly good date/time combination using any of the three MVD fields: date/time, date, or time.  I'll just have to mentally ignore my OCD here.
.
Anyways, my apologies for stating the obvious to those more experienced.  And as always derek, I appreciate your examples and advice.

Hi Derek, joshuA

If you are talking about a pop-up window then:
ShowHint (Form2.Edit1, 'This is a required field');
or
ShowHint(Form2.Edit1,'This is a required field',-1,-1,5000) -where 5000 Hide after time
And you can do the field check yourself. )))

Hi all,
.
I wasn't planning to check for duplicates on the groups, but I wanted to see how far I could go... not very far
.
The other duplicate checking examples that I found were dealing with a single table.  And this takes it a bit further, or either I'm over-complicating it.
.
I started the code, but this may not be the ideal approach.
.
Thanks in advance for having a look.

Hi sparrow,
.
Thanks for responding.  Sorry for not being more specific in my last post.
.
There are two cases for creating attendance records:
1. A single employee with one or multiple dates
2. A group of employees with a single date (filtered by Department)
.
Everything is working fine so far.  But I'm attempting to check for duplicates prior to creating any records when it applies to multiple employees.
.
This is declaration and field checking portion:

procedure frmAtt_Button2_OnClick (Sender: TObject; var Cancel: boolean);
var
    i, j : integer;
    isDuplicate : boolean;
begin                                                     
    // Check that the attendance code has been selected
    if frmAtt.ComboBox1.Text = '' then
    begin
        ShowHint(frmAtt.ComboBox1,'This field is required. Please enter a value for the field.');
        Exit;
    end;

Looping through each employee that has been filtered (in the tableGrid)

    
    for i := 0 to frmmain.tablegrid1.rowcount -1 do
    begin
        isDuplicate := False;

       
Now I'm attempting to check all attendance records for each employee's for duplicate fields

        for j := 0 to SQLExecute('SELECT count(*) FROM attendance WHERE id_employee = '+inttostr(frmmain.tablegrid1.dbindextoid(i))) do
        begin

       
This is where I get lost because I'm not sure how to construct a query that will pull out the pieces for each attendance record.  Because any query that I can come up with returns all the results at once.  I don't know how to control this.  And the ID's don't match the loop counter either, so it's hard to specify which record to use for testing.  And I don't know how to control the flow from SQL.

            
            // This doesn't work, but it's an example of the pieces that I need to test against on the entry form.

            //if (SQLExecute('SELECT aDate, id_attCode FROM attendance WHERE id_employee = '+inttostr(frmmain.tablegrid1.dbindextoid(i))) = frmAtt.dtpDate.sqlDate) then

            // How to check for duplicates on frmAtt.dtpDate, frmAtt.tbAttCode
            // IF THEY EXIST, SIMPLY SKIP THEM...
            // isDuplicate := True;

        end;

After testing for duplicates, then insert the new fields ONLY if none of them were duplicated

        if not isDuplicate then
           //SQLExecute('INSERT INTO attendance (aDate, aTime, aTime2, attNote, id_attCode, id_employee) VALUES('+frmAtt.dtpDate.sqlDate+', '+frmAtt.dtpTime.sqlTime+', '+frmAtt.dtpTime2.sqlTime+', "'+frmAtt.Memo1.Text+'", "'+frmAtt.tbAttCode.Text+'", "'+inttostr(frmmain.tablegrid1.dbindextoid(i))+'")');
    end;
    frmAtt.Close;
    frmEmp.TableGrid1.dbUpdate;
end;

.
I've created a loop inside a loop to filter (and test) each attendance record for each employee in the group (tableGrid list).  At least that's the idea.  It might not be the ideal way for doing this in MVD.  So this is where I'm getting stuck.
.
This is a bit fragmented, but it's hard to highlight code sections unless I just rely on comments.
.
Many thanks!