• Registered: 2020-06-26
  • Posts: 302

Topic: Manipulate Date Fields

Hi all,
Moving right along with my project, I need to manipulate some Dates.  I've attached a sample PDF file showing the tables that I'm working with.
Here's what I want to do:
- When I create a new Service record, I want to :
- Replace the LastSvcDate with SvcDate (the Date of this new Service)
- Then I want to change the NextSvcDate to be the NEW LastSvcDate PLUS the svcInterval from tbl_Svc_Interval.
.
Then eventually I'll create some reports showing the Equipment that needs to be serviced. And I will use the NextSvcDate to show what services need to be performed.
.
I found a reference to Inc(mydate, +14) which looks like it might be used to increase a date by a number of days.
.
Also, in my Svc Interval table the numbers are days, which I'm thinking might be used to add to NextSvcDate.  I'm not sure how to go forward from here.
.
Any guidance to point me in the right direction will be appreciated, as always.
Thanks, Frank

Post's attachments

Attachment icon Service Interval.pdf 99.21 kb, 183 downloads since 2021-08-10 

2 Reply by derek (edited by derek 2021-08-10 20:14:11)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,687

Re: Manipulate Date Fields

Hi Frank,
For what it's worth, I don't think I'd structure your data quite as you've laid out in your .pdf.  I think it would work, but I just don't think it's optimal.
My main questions are why you hold the last service date and next service date on the service record?  Wouldn't someone ask the question "When is the next service due on equipment 'x'? rather than "When is the next service due after the most recent service that's just been done?  As such,  I think that the 'last service date' and the 'next service date' should be at the 'equipment' table rather than held on the 'service' table? 
Having these fields at the equipment table level means you can easily see when something is next due for servicing.  I believe it would also make any reports you intend to write more straightforward.
The other point I'd make is whether you should even be storing the last service date and next service date?  This information can be calculated simply from looking at the service dates and from the servicing interval.  I'd use calculated fields to avoid the data redundancy.
Please have a look at the attachment and hopefully it helps.
Derek.

Post's attachments

Attachment icon fcintervals.zip 343.49 kb, 189 downloads since 2021-08-10 

  • Registered: 2020-06-26
  • Posts: 302

Re: Manipulate Date Fields

Derek,
Thanks for checking my project and providing the sample program.
Yes, I agree completely, it makes more sense to keep the Last and Next service dates in the Equipment table.  I'll work on making that change.  And calculated fields do make sense also.
.
Thank you for the Next Service Date calculated field code.  That was one that I didn't know how to create.
.
You do realize you've made more work for me though.  Anytime I make a change to the structure in a table, it's amazing how many things don't work and need to be fixed sad
But that's OK, the more I do the more I learn smile
.
Thanks
Frank

  • Registered: 2020-06-26
  • Posts: 302

Re: Manipulate Date Fields

Derek,
It's going OK implementing your suggestions so far.
.
However I have a question.  The Calculated Date fields are coming up in the European format: LastSvcDate is 2001-01-01 00:00:00.000 and NextSvcDate is 2001-03-04.  I need to have the dates in the US format - 1/1/2001.
.
And the LastSvcDate is in the long date format, whereas it needs to be in the short date format.
.
I'll keep looking but I'm not finding how to change the date format.
Thanks
Frank

  • Registered: 2014-08-02
  • Posts: 917

Re: Manipulate Date Fields

Hello papafranck, hello Derek

To get a date in american format, you have to go in the Object Inspector of the DateTimePicker.

In the Kind property, you choose: date and in the Format property,
you enter the format that suits you, in your case: yy / MM / dd

JB

  • Registered: 2020-06-26
  • Posts: 302

Re: Manipulate Date Fields

Jean,
Thanks, I didn't know that about the DateTimePicker.
However, in my case I have a Calculated field that contains the date that is coming out different.
This calculated field is being displayed as a Text Box. So I don't have the Kind property.
.
Maybe there should be some kind of formatting when creating the calculated field?
.
Thanks
Frank

7 Reply by derek (edited by derek 2021-08-11 19:54:18)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,687

Re: Manipulate Date Fields

Hi Frank, Salut Jean,
Unless you explicitly reformat a date in MVD, my understanding is that any field defined as a 'date' type will display in the date format specified on your PC (as set in the 'regional and language options').
Since the calculated fields in my previous attachment (cflastservice and cfnextservice) are both defined as 'date' types, they should therefore adopt the date format specified on the PC.
To confirm this, I ran my previous attachment ('fcintervals') on my PC;  the calculated field dates show as dd/mm/yyyy (UK format).
I then changed my 'regional and language options' to be US and re-ran the program;  the calculated field dates show as mm/dd/yyyy (US format).
So I'm not sure why it's not showing correctly for you.
That said, you could explicitly reformat the calculated fields (cflastservice and cfnextservice) using the 'strftime' command (see attachment).  However, to do this, you need to change the calculated field type from 'date' to 'text' and that will then mess up your sort sequence (but it's easily fixed with a small script).
Maybe something else is going on.   If you attach your project, people can have a look and see if they can spot anything.
Derek.

Post's attachments

Attachment icon fcintervals2.zip 510.57 kb, 195 downloads since 2021-08-11 

  • Registered: 2020-06-26
  • Posts: 302

Re: Manipulate Date Fields

Derek,
Sorry I didn't respond sooner.  I've been making the changes you suggested and as I mentioned earlier I've had to make changes to get everything to work correctly.  I believe I'm pretty close now.  I've fixed the relationship issues and the date calculation appears to be functioning correctly.
.
However, the NextSvcDate is coming out as 2021-09-08 and I'd like it to read 09/08/2021.
The date appears to be correct, it's just the formatting that I'd like to change.
.
Also, in my Next Svc Date table grid, if you scroll up and down it shows the service dates of all of the services for this equipment.  I'd like this table grid to show only the latest service date.
.
I've been doing a bunch of trial & error stuff and I believe it is the date(julianday... part of the calculated field that is returning it in this format - 2021-09-08.
.
I've attached a screenshot of my Equipment form along with the calculated field formulas that I'm using.  My calculated formula should be the same as Dereks, just with different table names.
.
Thanks
Frank

Post's attachments

Attachment icon Calculated Next Service Date.pdf 114.45 kb, 145 downloads since 2021-08-12 

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,687

Re: Manipulate Date Fields

Hi Frank,
As I wrote in my last message, if the calculated field dates are formatted incorrectly (for whatever reason), then change the calculated field type from 'date' to 'text' and then use the strftime function.
So, in the case of your next service date calculation, if you want it in mm/dd/yyyy format, it would read:
strftime('%m/'%d/%Y',date(julianday((select max(SvcDate) from tbl_Service where tbl_Service.id_tbl_Equip = tbl_Equip.id)) + (select SvcInterval from tbl_Svc_Interval where tbl_Equip.id_tbl_Svc_Interval = tbl_Svc_Interval.id)))
I'd just make sure that ALL my dates take the same format otherwise it can be confusing to the user.
But I'm a bit confused by your comment  "... in my next svc date table grid, if you scroll up and down it shows the service dates of all the services for this equipment".  I thought that you were calculating the next svc date at the tbl_equip level in which there is only one next svc date.  And if there's only one next svc date, then why use a tablegrid - can't you just use an edit field?
Derek.

10 Reply by papafrankc (edited by papafrankc 2021-08-13 03:12:41)

  • Registered: 2020-06-26
  • Posts: 302

Re: Manipulate Date Fields

Derek,
I used that table grid for Next Svc Date when I was doing trial and error stuff.  I had tried a Text field and got the date as 2021-9-8, so I tried a table Grid to see if it made any difference, which it didn't.
.
So now I'm using a Text field which is so much simpler.
.
And you nailed it with the strftime function.  At first I got an error, but it was because of an extra ' apostrophe. But it was just a typo.  Now the date is showing as 9/8/2021. Perfect smile
.
What's strange is that in the fcintervals example program you provided earlier, the dates show up in the 9/8/2021 US format.  But when I used that same Calculated field formula in my program the date was 2021-9-8.  I think it's probably Aliens, they seem to like to mess with my code smile
.
And using the Text field now I see only the Next Svc Date, not all the others.
.
Question: because the Calculated Next Svc Date is not stored anywhere, will I have to use the Calculated field formula when I start doing queries and reports for items that need servicing and when they need to be done?
Note I was thinking that when I said the Calculated field is not stored anywhere, I was wrong.  It should be stored in the calculated field in the Equipment table.  So I should be able to use it. 
.
Many thanks, again
Frank

  • Registered: 2020-06-26
  • Posts: 302

Re: Manipulate Date Fields

Derek,
It appears when I made my earlier statement asking about my Calculated field not being stored I was correct.
Later when I said that it was stored in the Equip table, it seems like I was wrong.
.
I looked at the data in the Equip table using SQLite and there is no stored Calculated field.
.
So I'm back to my original question, that is how to use the NextSvcDate to do a query or a report about upcoming items that need servicing.
.
Thanks
Frank