26 Reply by ehwagner (edited by ehwagner 2017-04-06 23:47:02)

  • Registered: 2015-11-13
  • Posts: 652

Re: Recurring expenses

Adam,
Your number 2 in your post was put in the last project. I added the feature to insert the next recurring expense when saving. Since you use dates mostly in the dd/mm//yyyy, for the sake of testing the new feature use the attached project. You were getting errors in Recurring expenses 5.zip probably because it was converting dates to mm/dd/yyyy.


Dimitry,

For Adam (Ad1408) number 1 in his last post, is there a way to extract dates from a dataset "AsDate"? I had to extract the date AsString to retrieve a date and then manipulate into a format where I could add days, months, etc. However, I always convert to the format mm/dd/yyyy so it's not an issue for me, but others may use the format of dd/mm/yyyy. The attached project should work for him because he uses the dd/mm/yyyy format.  Is there a way to write the following (line 120 in the script of the attached project) so that it goes directly into a date variable so that the date can be incremented.

String_Date := ExpResults.FieldByName('Date(recurDate)').AsString;
Post's attachments

Attachment icon Recurring Expenses 6.zip 601.61 kb, 402 downloads since 2017-04-07 

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

Re: Recurring expenses

AD1408

I made some changes.


In the components there is no hard coded date format. Components take date format from the user OS.

Post's attachments

Attachment icon Recurring Expenses 4EHW_fixed.zip 18.7 kb, 383 downloads since 2017-04-07 

Dmitry.
  • Registered: 2015-11-13
  • Posts: 652

Re: Recurring expenses

Thank you Dimitry. That was quite obvious. I wasn't thinking along those lines for converting the date. Sometimes one can't see the forest for the trees :-)


Adam, the attached project has Dimitry's date fix and I put in what I think you want on a Save event. So  when saving a recurring event, the next recurring event will get populated and thus show on the tablegrid. From that point on the other process will keep up with recording future recurring expenses.

Post's attachments

Attachment icon Recurring Expenses 7.zip 601.28 kb, 375 downloads since 2017-04-07 

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

Re: Recurring expenses

Hi Dmitry,


Thank you very much for the fix...............
Now it runs without any issue that I could notice.
I added backdated monthly recurring expense and it saved and displayed on the tGrid.
I then added daily recurring expense yesterday and it didn't show it on the tGrid today? Please see image below:
https://s3.postimg.org/bqlfer48j/zzzzz_Temp14.png


____________________________


Hi EHW,

Thank you very much for the update v7.........
However, I still couldn't get it working. When adding new recurring expense produces an foreign key restrain error:
https://s17.postimg.org/nm4zn2trz/zzzzz_Temp13.png
I deleted db file and run again to start with clear db file but it didn't run. It crashes.

Adam
God... please help me become the person my dog thinks I am.
  • Registered: 2015-11-13
  • Posts: 652

Re: Recurring expenses

Alright Adam, we are going to get you through this yet. The reason for the foreign constraint was that your category and group comboboxes were empty. I updated the project so you shouldn't get the error now. The database in the attached is empty so you can start fresh. Try it again.

Post's attachments

Attachment icon Recurring Expenses 8.zip 601.29 kb, 370 downloads since 2017-04-08 

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

Re: Recurring expenses

ehwagner wrote:

Alright Adam, we are going to get you through this yet. The reason for the foreign constraint was that your category and group comboboxes were empty. I updated the project so you shouldn't get the error now. The database in the attached is empty so you can start fresh. Try it again.


Thank you very much EHW...................


1. Yes, it runs fine now, as long as I don't delete the db file you have supplied with v8. Once it's deleted, compiling and running fails then crashes instead of creating new db file and run. It seems some of the sql script db specific, I may be wrong tho.
https://s1.postimg.org/i8ztz21rz/zzzzz_Temp16.png


2. I tried an update button to show recurred items but didn't work:

procedure Form1_Button2_OnClick (Sender: string; var Cancel: boolean);
begin
   Form1.tgExpenses.dbUpdate
end;

I came a cross Dmitry's script below, perhaps something like that may be used so that recurred items can be saved and displayed on date and time, but it's beyond me currently in this case:

SQLExecute('update entries set date = "'+FormatDateTime('yyyy-MM-DD hh:nn:ss.000', form1.datetimepicker.datetime)+'"' );

3. Expenses tGrid data display would be more clearer as on the image 2 below:
https://s15.postimg.org/dbsly9cij/zzzzz_Temp15.png

Original record marked and saved as recurring needs to be always first record with recurring recur every.. columns filled, i.e. Yes | Month
Recurred items recurring column needs to empty but recurring every column filled with the period selected on first record.
Non-recurring, one of items recurring column needs to displays No
This way user can differentiate better recurring, recurred and non recurring items.

Adam
God... please help me become the person my dog thinks I am.
  • Registered: 2015-11-13
  • Posts: 652

Re: Recurring expenses

I updated the project so you won't get the table not found on startup. Plus the save button will now catchup all recurring expenses for past dated recurring expenses and display them on the grid. I no longer empty your Recurring Boolean value and Recurring Every value. I let them alone so you can see that they are recurring expenses. However, I had to create another Boolean field in the Expenses table so I can check that field to see if a recurring record was created and turn it on if a recurring was created. The app, upon startup, will still check and create new future recurring expenses as their date arrives. I tested the app, but not necessarily an exhaustive test for time sake. If you start messing around with changing dates on past recurring expenses, there may be issues in the recurring process and possibly create duplicates. Therefore I disabled the expense date on past recurring expenses. I put some controls in place to try to prevent other problems. For instance, I do not allow a user to change the recurring date any longer. That date is controlled by the Recurring Every value and shouldn't be messed with. Try it out and give it a whirl.

Post's attachments

Attachment icon Recurring Expenses 9.zip 598.17 kb, 381 downloads since 2017-04-10 

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

Re: Recurring expenses

Hi EHW,


Looks like you got there.
Thank you very much for your truly appreciated help..................................................


Only item left out is to show recurred items without restarting the app. For this I'm trying the following:

procedure Form1_btnRefresh_OnClick (Sender: string; var Cancel: boolean);
begin
  Form1.tgExpenses.dbUpdate;
end;

I'll have to wait till end of the day to see if refresh button will work or not.

Adam
God... please help me become the person my dog thinks I am.
  • Registered: 2015-11-13
  • Posts: 652

Re: Recurring expenses

You do not need to restart the app. They should show after you do a save. Are you saying that they are not showing when you do a save? They do on my end. But if you want to place a dbUpdate in the script, don't create a button for that. Just place it inside the  "frmExpenses_Button_Save_OnAfterClick" procedure after the "If" statement.

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

Re: Recurring expenses

Perhaps shortcoming at my end with explanation... What I meant is:
1. I add a daily recurring expense today.
2. Added recurring daily expense with todays date shows on tGrid as normal and doesn't show recurred one as it it's not due till tomorrow as it should be since recurrd expenses only need to be displayed on their recur date and thereafter.
3. Left app running. Tomorrow (after midnight) it doesn't show recurred expense. Still showing only recurring expense. However, if I restart the  app it shows recurred one too..

Adam
God... please help me become the person my dog thinks I am.
  • Registered: 2015-11-13
  • Posts: 652

Re: Recurring expenses

Oh I see. No it's not going to run the process if you leave the app running over midnight. Do you really anticipate that happening from your users?

37 Reply by ehwagner (edited by ehwagner 2017-04-11 04:37:08)

  • Registered: 2015-11-13
  • Posts: 652

Re: Recurring expenses

Adam, I went ahead and put in a timer so if the app is still running over midnight, then the recurring process will run and you'll see the new recurring expenses populated in the tablegrid. The timer checks every minute so if you are watching and it does not populate right at midnight, wait a minute and it will.

Post's attachments

Attachment icon Recurring Expenses 10.zip 599.09 kb, 405 downloads since 2017-04-11 

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

Re: Recurring expenses

ehwagner wrote:

Adam, I went ahead and put in a timer so if the app is still running over midnight, then the recurring process will run and you'll see the new recurring expenses populated in the tablegrid. The timer checks every minute so if you are watching and it does not populate right at midnight, wait a minute and it will.


Thank you very much.... Truly appreciated.......

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