You are not logged in. Please login or register.
My Visual Database → General → Find the latest date record
Hi All,
I have a table and a table grid with records containing various dates. Using script, I would like to be able to choose the one with the latest date.
.
Here's the line of code that I'm using:
frmEquip.EqNextSvcDate.Text := DateToStr(frmService.cboSvcDate.DateTime + strToInt(frmEquip.cboSvcInterval.Text )) ;
.
This code picks up the last entered record, even if the Date is not the latest date.
.
Thanks
Frank
I haven't seen your project. From what I can see the line of code you showed only displays info from the current record you are editing on the form. If you want to display the latest date (highest date) from a database table you can use the following line of code as an example.
Form1.edLatestDate.Text := DateToStr(SQLDateTimeToDateTime(SqlExecute('Select Max(TransDate) From Test')));ehwagner,
That's perfect, it does exactly what I wanted. But, there's always a but... I believe I need a tweak to the code.
.
Here's what I have:
- Equipment table with lots of entries
- Service table with multiple entries for each Equipment entry.
.
So when I grab the latest date from the Service table, I need it to be for ONLY the Equipment that the Service is related to.
.
I've attached a PDF file showing my 2 tables and your code that I have implemented. I'm thinking that the code needs something added like WHERE the Service record ID is related to the Equipment ID. But I don't know how to add that to your code.
.
Thanks for your help
Frank
Next Service Date.pdf 182.77 kb, 188 downloads since 2021-08-25
without seeing your project in ehwagner code
Form1.edLatestDate.Text := DateToStr(SQLDateTimeToDateTime(SqlExecute('Select Max(TransDate) From Test')));add WHERE.
... DateToStr(SQLDateTimeToDateTime(SqlExecute('Select Max(SvcDate) From tbl_Service WHERE id_tbl_Equip = your Equipment ID from frmService ')));Frank,
Along the same lines as Sparrow indicated see attached for an example that may help you.
Equip Service.zip 338.99 kb, 204 downloads since 2021-08-26
ehwagner,
I opened up your sample application and it doesn't look like it does much. I can add Equipment and Service entries into the forms but they don't seem to be related. It's just 2 tables with some entries.
.
Also I noticed that there is no script file. I see a Script folder but when I open it using Lazarus it is empty.
.
Maybe something happened when the files were being saved?
---------------
QUESTION: I noticed that you have a relationship in the Equipment table pointing to the Service table. I was under the impression that the relationship should be in the MANY table pointing back to the PARENT table - Equipment. In this case I would envision that there would be ONE Equipment record with many Service records.
Thanks
Frank
Frank,
I am not familiar with Lazarus. You should run this project thru MVD not Lazarus. The script file is present. What I was trying to show you is how to show the last service date and the next service date on the form and in the Equipment tablegrid. The two tables are most definitely related. This is a typical Master-Detail type relationship. The Equipment table is the parent and the Service table is the child. In other words for each piece of equipment there can be many services performed.
I would suggest re-downloading the project and run again.
ehwagner,
I downloaded your project again and now it works. I don't know what happened yesterday because basically all I saw was the forms and no script file. So of course, without the script file, nothing worked as it should.
.
So, in my first download, I did see the script files in the SCRIPT folder so I tried to open them and that's when they opened in Lazarus. Lazarus is a free Pascal development system that I have on my computer but I don't use. So apparently when I tried to open the 'script.pas' file, it was opened by Lazarus by default. And yesterday it was empty.
.
So, something happened to my first download that scrambled a number of things, including the script file.
.
I did, and do, believe that Equipment is the parent file and Service is the child file. But in my first download, it had the relationship entry in the Equipment file which confused me.
.
So apparently the discrepancies and my confusion were all caused by the faulty download.
But I have a good working copy now and will be going through it to see how I can implement your suggestions.
.
Thanks for your patience and your help
Frank
Hi Frank, Hi EHW,
Rather than us trying to answer specific questions (which may be correct in isolation but then turn out to be less valid in the overall context of your project), I thought I'd try and knock up a 'total' solution based on the information so far provided and then you pick out the relevant pieces as a way to move things forward yourself.
The approach I took was to simplify things as much as possible but without losing functionality - see attachment.
So now there is just one form where you view all of the equipment records (and summary information such as number of services, next service due etc) in a tablegrid, but also add, edit or delete service records; there is then a second form for your reference tables.
This will hopefully get over a couple of problem you were having, namely:
1). The 'next service date' that was in different formats on different forms, now only appears in the table grid and it's format can be easily adjusted in the script (there is an example of the syntax in the script, currently commented out).
2). More importantly, it is defined as a date so that it can be correctly sorted on; forcing your dates into text fields will normally mean that you lose sorting functionality (unless displayed as yyyy/mm/dd).
3). The 'next service due date' and the 'due in days' are calculated fields. As both are calculated fields,
3.1). no database updates are required,
3.2). a new service record (or change to or deletion of an existing service record's date) will automatically recalculate.
3.3). changes to the servicing interval (via the formlookups screen) will automatically recalculate the 'next service due date'.
This is the whole point of calculated fields, as well as avoiding data redundancy.
From what I've seen of your attached .pdf's in other posts, I don't know of anything that hasn't been basically covered.
Likewise, the main screen in my attachment (form1) could be easily re-arranged to more suit the layout of your .pdf with no changes to the underlying logic or script required.
I hope this manages to progress things.
Derek.
fc one form.zip 366.77 kb, 196 downloads since 2021-08-27
Derek, EHW
Thank you both for your sample programs. I've got a lot to go through and try to understand.
.
Derek, I have a comment and a couple of questions:
- First, your sample application is too big for my screen. I'm using an HP Laptop with a 15" diagonal screen. I also have a smaller laptop that I do some testing on. I've found that a screen size of 1075 x 540 works for my smaller PC. So that's what I use for developing. Some time ago I was asking the forum about how to make it so that my program would automatically fit any size screen. I finally would up inserting some code to resize the screens based on their sizes:
if screen.width = 1920 then
begin
vscale := 170;
end and I have a few other sizes with different numbers.
But, of course I don't know the size of whatever computer the program will be used on so, so far this is all I use. If you know of another way to resize an application please let me know.
.
As far as your program goes, when it opens up on my PC, it is so big that I have to grab the bar at the top and drag it so I can get to the X to close the form.
.
So to fix this I have resized each of the elements in your program so I can use it on my PC. A little bit of work but it was doable.
.
On another issue, I have a question. I was looking at your code for the calculated fields and noticed this ...tblserviceinterval.id))) * -1 I also noticed that there was a + sign after one of the lines of code. I'm curious as to what are they for?
.
One more question: Is there any way to 'break' a line in the script file? Some of the code lines are really long and I find them easier to understand if I can see them with crlf in the middle of the line. But if I put a crlf in the middle of the line, MVD doesn't like it.
.
Lastly, since I now have the app resized, I can start to see how it works and see how I can implement it into my program
.
Thanks again for your effort.
Frank
Hi,
1. The *-1 is just to throw the days that are overdue to negative
2. The +1 is just to add a day - some people count 'up to' and some count 'up to and including'
3. To break a long line in a script (you can break it anywhere but some places are more logical than others!), insert a single quote and a plus sign ('+) where you want the line to break and then start the next line with a single quote.
For example,
Form1.DateTimePicker4.Time := SQLDateTimeToDateTime(sqlexecute('select x from y where id='+Form1.TableGrid1.sqlValue));
can be broken like this:
Form1.DateTimePicker4.Time := SQLDateTimeToDateTime(sqlexecute('select x from y '+
'where id='+Form1.TableGrid1.sqlValue));
Derek.
Derek,
Thanks for the info. I looked & looked on the web to see if I could find out how to break a long line of code. With no success. Now this should help me to better understand those long lines of code.
Thanks
Frank
My Visual Database → General → Find the latest date record
Powered by PunBB, supported by Informer Technologies, Inc.
Theme Hydrogen by Kushi