1 (edited by leprince2007 2020-02-12 16:10:19)

Topic: Get the last date for the task

Hello Everybody,
Could you help me in my question,please?
I created a program for my work tasks.Every checkbox is a task and has a column in my database..
If I finished a task ,I click on its checkbox and it returns 1 in the database(If I click save button).
My question:
-There is a label next to every task.
First :I want the labels to show the last date in which I did this task (last "1" value).
Second:If the date in each label is yesterday,It`s font color changes to green.and if the date in each label is older than yesterday,It`s font color changes to red.
I want all this happens when the Form loads.
I`am sorry I`am a beginner.
Thanks in advance

Post's attachments

Attachment icon MyTasks.rar 295.73 kb, 271 downloads since 2020-02-12 

2 (edited by derek 2020-02-13 13:44:49)

Re: Get the last date for the task

Hello Leprince2007,
In order to achieve what you want, you need to change your table structure.  You currently use checkboxes to show if a task has been completed or not but nowhere do you save the date of completion.  If you don't hold this, then how do you know what date to put into your labels? (you can't derive it from a checkbox because it only holds a value of 0 or 1).  You would need to add a date field for every checkbox that you have (eg excavators checkbox, excavators date etc etc).
Personally, rather than use checkboxes AND dates, I would just use dates for each of your tasks because it tells you all you need to know (if the date has not been checked, the task has not been completed and if it has been checked, you know that it's been both completed AND the date on which it was completed).  Then you just change the label associated with the date fields to show whether the task was completed on time or not.  It's just my opinion but it seems much more straightforward.
Please see the attached suggestion which is a scaled down alternative to your project to see how the label colour coding works in conjunction with the date fields for each task (no colour = on time, green = 1 day late, red = 2 or more day late, yellow = not completed).
Also, your project didn't have any way of retrieving saved records (no tablegrid or combobox to selected a record from and no show record button) so I've added these.
I hope this gives you some ideas to move forward,
Derek.

Post's attachments

Attachment icon leprincex.zip 338.25 kb, 275 downloads since 2020-02-13 

Re: Get the last date for the task

leprince2007 and Derek,
I was trying to get my hands around leprince2007's design for some of the same reasons you gave Derek. I made some assumptions which sometimes one has to do on some forum requests. Since he only wants to know the last date the task was performed and not necessarily wanting a history of the tasks and that one or more of these tasks are done daily, I made the assumption that the same task record is to be used. Using leprince2007's form design (and you are right Derek that a date needs to be defined for each checkbox and requires extra coding), I revised his project to show the last date the task was performed. Each day when the project is opened the checkboxes are all cleared for that day, but the dates remain until the task is performed (checked) again at which time the date is updated for that task. It's an option and not at all meant to be an end-all solution. Maybe the combination of the two is also a solution. At least it should give leprince2007 some ideas.

Post's attachments

Attachment icon MyTasks Revised.zip 349.91 kb, 264 downloads since 2020-02-13 

4 (edited by derek 2020-02-13 21:59:36)

Re: Get the last date for the task

Hi EHW,
I think your interpretation is much closer to the requirement than mine (I'd assumed every day would be a new 'task list').
But it makes more sense now I've looked at your attachment - in effect, it's a single row table that updates each of the tasks with 'today' when any of the tasks is clicked and then colour-codes them depending on how many days ago that specific task was last ticked.
By the way, really like your
    ShowRecord(Form1, 'Mytasks', 1); 
Never seen it done like that before - I've always used a hidden tablegrid and hidden 'show record' button but it always struck me as being rather crude.
Regards,
Derek.

5 (edited by leprince2007 2020-02-13 23:33:12)

Re: Get the last date for the task

ehwagner wrote:

leprince2007 and Derek,
I was trying to get my hands around leprince2007's design for some of the same reasons you gave Derek. I made some assumptions which sometimes one has to do on some forum requests. Since he only wants to know the last date the task was performed and not necessarily wanting a history of the tasks and that one or more of these tasks are done daily,.

Thank you Mr.ehwagner and Mr.derek for your replies but I want my app design like it is.
You did things in my app that I don`t want:
1-The app saves my last marked check-boxes
2-the app overwrites data in the database
3-a lot of datepickers are created
4-You hid my datepicker "Dttoday"
I will explain again what my app does:
1-I choose a date from the data picker in the top "Dttoday".Note I can choose any date
2-I tick on check-boxes when I complete the tasks
3-I click save button to save the data (date and check-boxes ) to the database
I want to show the last date of the task in the label next to the task
I hope you understand me and help me
See my database in the picture

Post's attachments

Attachment icon Untitled.jpg 181.3 kb, 117 downloads since 2020-02-14 

Re: Get the last date for the task

leprince2007,
My apology for not fully understanding your requirements. It sounds like Derek's solution may be closer to what you want then. Try it out and see.

7 (edited by leprince2007 2020-02-14 11:59:07)

Re: Get the last date for the task

ehwagner wrote:

leprince2007,
My apology for not fully understanding your requirements. It sounds like Derek's solution may be closer to what you want then. Try it out and see.

Mr Derek changed completely the design of my app and this isn't what I want.
I need from you to work on my project in the first post.
This what I want but in vba script (vba is used in office apps) :

Datacolumn=checkbox.Caption    'column name in database
For row=Datacolumn.Rows.Count to 0 step - 1.   ' search from the last record to the first record 
If cells(row, Datacolumn) >1 then.    Look for the last value >1
Label1. Caption=cells(row, datecolumn).Value
Exit for
End if
Next

Could you convert it to work in myvisual database , please?

Re: Get the last date for the task

leprince2007,

Are you aiming for a Daily 'Todo' or 'Checklist'  and you then save completion or not to the database?

You can make DTToday visible again by just ticking the property box.

Could you explain what you want to happen if you select an earlier date than 'today' from your date time picker?

I thought ehwagner had got pretty close to what you needed.

To convert your visual basic script you want something like this:

1. to make it easier you should have a hidden tablegrid, then you can address cells directly

var
    row, column: integer;
    rowcnt, colcnt: integer;

 for row := 0 to rowcnt do
       for column := 0 to colcnt do
       begin

          if form1.tablegrid1.cells[column, row] > 1 then
             label1.caption := form1.tablegrid1.cell[column, row].AsDateTime
        end;
end;

I haven't tried this out, but it is close to your VB script.

If you want the label to change colour then add in

form1.label1.font.color := clGreen;

On a clear disk you can seek forever

9 (edited by leprince2007 2020-02-15 14:20:56)

Re: Get the last date for the task

Thank you mr. CDB for your reply
I will answer your questions:
1-I want the databse to store the completion of my tasks. Everyday is stored in a single row like in my file in first thread.
2-If I choose an older date then I click save, all data is saved to this date
The script:I have a question for you, please
-I want it to get the data from the databse not from a new table grid, please
-Then apply the final script to my project in first thread and post it here.

This script- that I want - will help me so much in creating all my future programs.
Thanks in advance for your great help

10 (edited by ehwagner 2020-02-15 19:27:34)

Re: Get the last date for the task

leprince2007, we will get you through this, but there a few things you should know about MVD and a few things we need to understand about your project. First, in the normal and elementary progression of MVD you would have a form with a tablegrid of your database records. This is what CDB was alluding to. For the sake of discussion we will call it Form1, but it can be any name you want. Then you would have a subsequent form to be able to add records and change records. We will call this Form2, but it can be any name you want.This form needs to know if it is in ShowRecord mode or NewRecord mode. Typically you would have a new button on the tablegrid form (Form1) which would open Form2 to enter a new record. MVD puts Form2 in NewRecord mode. Also in Form1 you would have buttons to edit and/or delete records in the tablegrid of database records. The edit button would open Form2 to change the record info. MVD puts Form2 in ShowRecord mode.This is the process in its elementary state. This is not an absolute way you have to do it, but it will require a little bit of scripting, which is not a big deal.

Question 1 - In your VB project did you have a tablegrid form to navigate to the update form you presented in your project in your post. If not, that's ok. We can still work with it.

Question 2 - If you did not have a tablegrid form and only used the one form to do all your adding and updating, then how did you retrieve the record you wanted? Did you use the date field at the top of the form to search for the corresponding date record?

Question 3 - What is the purpose of storing all your daily records? Is it to only retrieve the last date a task was performed? If that's the case, then eventually (after each task has been performed twice) you will have dead records in the database and will have no value to the application and wasting space.

Question 4 - If there is another reason for storing past records, then when you select a record , say a record from 6 months ago, what should show in each of the respective task date label displayed on the form. Is it the last date prior to the date displayed at the top? I'm not exactly sure what benefit this would have in your application to know this, but it's your app and it that's the way you want it, then so be it.


Let's start with these questions and see what we can do. Now having said that, I cannot guarantee you my time commitment to this. This is not my full time job, but I will try to help as much as I can. If Derek or CDB or anybody else would like to step in at any time, by all means do so.

11 (edited by CDB 2020-02-15 22:00:01)

Re: Get the last date for the task

I think I now understand what you are looking for.


1. A daily checklist of items. Some are performed weekly and others monthly. Checkboxes are used to tick off the tasks that have been completed that day/week/month.


2. Each day's checklist results are saved to the database. Saved to database as ROW_x Date, checkbox1, checkbox2, checkbox....... (1= done, 0 = not done) next day ROW_x +1 has the new row of the above data layout.


3. By selecting previous dates from the date time picker you can bring up previous checklist results. As you select previous dates, the data for the ROW corresponding to the date selected is read from the database and the boolean results for each checkbox column (in the database) are shown against each checkbox with a label changing colour, depending on the date taken from the Date column in the database..


4. The label changes colour depending on the difference between (today's date - date selected in the date column/row of the database). IF the difference between today and date selected is 1 THEN the label or font :=  coloured green,  ELSE IF the difference is >1 the label or font := coloured red.


If this checklist was a piece of paper on a clipboard, each day would have a new sheet of paper placed on top of yesterdays sheet of paper on the clipboard.  Each sheet of paper has the date it was actioned. By sheaving through the paper forms on the clipboard you would be able to see by date what had or had not been checked.


Is that what you are hoping to achieve?

On a clear disk you can seek forever

12 (edited by leprince2007 2020-02-16 06:59:28)

Re: Get the last date for the task

CDB wrote:

3. By selecting previous dates from the date time picker you can bring up previous checklist results. As you select previous dates, the data for the ROW corresponding to the date selected is read from the database and the boolean results for each checkbox column (in the database) are shown against each checkbox with a label changing colour, depending on the date taken from the Date column in the database..

Is that what you are hoping to achieve?

You are right but no3 is wrong:
-I want the labels to show only last date of the task .And I don`t want to create datetimepicker next to each checkbox.
-By selecting an old date-like new date-I can add tasks it it .
See the picture in post no.5,this is my database.I want it as it is with no change.
Like I said before,I want this vb script to use in my current project and future ones also.
Thanks in davance

Re: Get the last date for the task

leprince2007,
I reworked your project and hopefully I am a little closer to what you want. Since I did not have answers to my questions, I had to make some assumptions especially as it relates to Question 4 when selecting a prior date.


There is only one form to do all updating of date records. It required a little scripting because it is a little out of the ordinary for MVD, but it shows the power of MVD to be able to do most anything you want. The script will automatically place the form in the mode it needs to either ADD or SHOW a record based on the date selected at the top of the form. The one thing that may be questionable is what label dates to show when selecting a prior date record. What the script does is show the most recent dates of the tasks based on the selected date at the top of the form. If that is not what you want, it is easy enough to change. You just have to let me know which way you want.


No FOR LOOP is necessary as in your VB project. A simple SQL SELECT can directly access the most recent date record. Also, I'm not sure how you built the database structure. You must have done this outside of MVD because MVD requires the "id" autoincrement key field and it was missing in your database. I added it to your structure.

Post's attachments

Attachment icon MyTasks Revision.zip 353.37 kb, 271 downloads since 2020-02-17 

14 (edited by leprince2007 2020-02-18 09:18:11)

Re: Get the last date for the task

ehwagner wrote:

No FOR LOOP is necessary as in your VB project. A simple SQL SELECT can directly access the most recent date record. Also, I'm not sure how you built the database structure. You must have done this outside of MVD because MVD requires the "id" autoincrement key field and it was missing in your database. I added it to your structure.

The app doesnot work,it shoes error in line 31
I`am sorry I didn`t see your post.
I wan My app to do the following:
Add mode: by choosing the date (datetimepicker) then tick the checkboxes then press save button
Show mode:the labels should always show the most recent date of the task .The labels should not depend on datetimepicker or any other thing..
Note:datetimepicker is used only in add mode
Thanks in advance

15

Re: Get the last date for the task

leprince2007 wrote:

The app doesnot work,it shoes error in line 31


leprince2007,  do you mean when you try to run the program you get an error?

I've just run the program with no error.  Try downloading the zip file again and run.

What build of MVD are you using? ehwagner is probably using v6.2 or possibly beta 6.3.

Just trying to work out why you get an error showing.

On a clear disk you can seek forever

Re: Get the last date for the task

Thanks Mr. CDB .I updated the app and it works now
But I need one last modification:
the labels should always show the most recent date of the task .The labels should depend on today`s date only not datetimepicker.
Thanks in advance

Re: Get the last date for the task

Project revised. Date labels only show the most recent date of the task.

Post's attachments

Attachment icon MyTasks Revision2.zip 353.02 kb, 318 downloads since 2020-02-18 

Re: Get the last date for the task

Well done Mr.ehwagner .thank you
Thank you all for helping me.This forum members is very helpful.