1 Topic by timlitw (edited by timlitw 2016-02-02 17:37:51)

  • Registered: 2014-09-04
  • Posts: 98

Topic: database design problem - help me understand

I am working on a project to keep track of truck inspections due dates. 

I made a list of the trucks and the mechanics and a form to record the inspection date.  and I made a table grid to show the inspections, but once I had it built I realized that I can enter the same truck multiple times in the table, I do need to be able to do that but if there is an older inspection on that truck I rather need to edit it than add a new one.

My current design does not do that.   

So to all you Database gurus - here is my project,  what do I need to add so that I can limit the tablegrid to 1 inspection per truck? 

do I add an inspection date in the vehicle table?

ps I have around 50 trucks to track the inspections on.

Post's attachments

Attachment icon DOTTracker.zip 28.52 kb, 498 downloads since 2016-02-02 

  • Registered: 2014-09-04
  • Posts: 98

Re: database design problem - help me understand

I have a vehicle table with details about the vehicles and an inspections table with a relationship field back to the vehicles, and to the mechanics table

in the tablegrid in question I show the inspection table and details of the vehicle inspected from the vehicle table.   

I don't know how this will work but would it work better to add a inspected date field in the vehicle table and then just show that table in the grid.  so then the only thing I need is the vehicle table and I don't even need an inspections table.

I guess I'll try that. . .    unless I hear/have a better idea

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

Re: database design problem - help me understand

Hello,


I made some changes.
Now you can see list on trucks and its new date for inspection, also you can press "Edit Vehicle" I see list of  all inspections.

Post's attachments

Attachment icon DOTTracker_fixed.zip 16.21 kb, 528 downloads since 2016-02-02 

Dmitry.
  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,763

Re: database design problem - help me understand

Hi Timlitw,
I've done something similar before - I've attached a copy of it for you to have a look at.
There's none of your original code but it's just a case of copy and pasting it in.
I've just added a calculated field to get the maximum next due date so that can display in tablegrid 1 and I guess the colour coding would run off that.
Hope this helps.
Derek.

Post's attachments

Attachment icon timlitw.zip 342 kb, 528 downloads since 2016-02-02 

  • Registered: 2016-01-17
  • Posts: 151

Re: database design problem - help me understand

Hi
in the "InspectionForm" of your project you defined an script on the combobox "Vehicle" OnChange.
I need something similar to your script.
Is it possible that instead of the combobox, we define a textbox and a button. when inserting numbers on the textbox and then press the button, the script is triggered based on the data in the textbox and then shows the data of other textboxes in that form???
thanks
By the way I'm using version 1.45 sad

  • Registered: 2014-09-04
  • Posts: 98

Re: database design problem - help me understand

Thanks for those examples!  Looking at them now,

  • Registered: 2014-09-04
  • Posts: 98

Re: database design problem - help me understand

Drivesoft, 2 things with the way your change works,

I wanted the form1 tablegrid to show the oldest inspections at the top - when filled with data the list will be longer than 1 screen.
and
I want a report that shows a list of vehicles who's inspections are 305 days or older.  And a list of vehicles who do not have an inspection.

I don't know how to work with the calculated date field to make either of these work.

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

Re: database design problem - help me understand

timlitw wrote:

Drivesoft, 2 things with the way your change works,

I wanted the form1 tablegrid to show the oldest inspections at the top - when filled with data the list will be longer than 1 screen.
and
I want a report that shows a list of vehicles who's inspections are 305 days or older.  And a list of vehicles who do not have an inspection.

I don't know how to work with the calculated date field to make either of these work.

I can help you only with specific questions.

Dmitry.
  • Registered: 2014-09-04
  • Posts: 98

Re: database design problem - help me understand

In the fixed program you sent back to me with the calculated inspection date.
How can I sort the tablegrid on form1 with the oldest inspections at the top.

  • Registered: 2016-01-17
  • Posts: 151

Re: database design problem - help me understand

Please help me with this
in the "InspectionForm" of this project , there is an script on the combobox "Vehicle"  (OnChange).
Is it possible that instead of the combobox, we use a textbox???
For example upon filling this textbox,  based on the data in the textbox, an script shows the data in other textboxes in that form.
Do you get what I mean?
thanks

  • Registered: 2014-09-04
  • Posts: 98

Re: database design problem - help me understand

identity wrote:

Please help me with this
in the "InspectionForm" of this project , there is an script on the combobox "Vehicle"  (OnChange).
Is it possible that instead of the combobox, we use a textbox???
For example upon filling this textbox,  based on the data in the textbox, an script shows the data in other textboxes in that form.
Do you get what I mean?
thanks

I am not sure what part of it you are asking about, 
Is it the filling in the rest of the database row information you are asking about? 

I think you could use the incremental search feature to do that with a text box similar to the way the tablegrid works on form1

  • Registered: 2016-01-17
  • Posts: 151

Re: database design problem - help me understand

no that did not do what I wanted
Please download "DOTTracker.zip 28.52 kb" above
go to the "InspectionForm", there you can see a combobox and in front of it says : Vehicle
when you run the project, as soon as you choose something from that combobox, other information are shown in the textboxes below that combobox.
what I like to do is: instead of the combobox, I use a textbox that does the same.
it should not be incremental search because  incremental search only shows the records on a TableGrid and I don't want the records to be shown in TableGrid. I like that records are shown in textboxes
thanks

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

Re: database design problem - help me understand

timlitw
Please download latest beta version:
https://www.dropbox.com/s/ro1hs53q8rdv5 … a.zip?dl=0


then change this event:

procedure Form1_TableGrid1_OnChange (Sender: string);
var
   i,c: integer;
   s: string;
   dt: TDate;
begin
     c := Form1.TableGrid1.RowCount - 1;
     for i := 0 to c do
     begin
         s := Form1.TableGrid1.Cells[0,i];
         if s = '' then continue;

         dt := SQLDateTimeToDateTime(s);
         Form1.TableGrid1.Cells[0,i] := DateToStr(dt);
         s := Form1.TableGrid1.Cells[0,i];

         if ValidDate(s) then
         begin
             if (StrToDate(s) + 330) <= now then Form1.TableGrid1.Cell[0,i].Color := clRed;
             if (StrToDate(s) + 329) >= now then Form1.TableGrid1.Cell[0,i].Color := $0000CCFF;
             if (StrToDate(s) + 260) >= now then Form1.TableGrid1.Cell[0,i].Color := $0000CC00;
         end;
     end;

     Form1.TableGrid1.Columns[0].SortType := stDate;
     Form1.TableGrid1.Columns[0].Sorted := true;
end;
Dmitry.
  • Registered: 2014-09-04
  • Posts: 98

Re: database design problem - help me understand

Thanks that looks very nice.   If I understand the change, you made the table grid sort column0 by the date.

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

Re: database design problem - help me understand

timlitw wrote:

Thanks that looks very nice.   If I understand the change, you made the table grid sort column0 by the date.

Yes.

Dmitry.
  • Registered: 2014-09-04
  • Posts: 98

Re: database design problem - help me understand

I also wanted a report that only shows non inspected and those with inspections due in the next 30 days or actually inspections 335 days old or older, but for now I just export the grid to excel

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

Re: database design problem - help me understand

timlitw wrote:

I also wanted a report that only shows non inspected and those with inspections due in the next 30 days or actually inspections 335 days old or older, but for now I just export the grid to excel

Sorry for delay. Done:

Post's attachments

Attachment icon DOTTracker_fixed2.zip 18.5 kb, 522 downloads since 2016-02-07 

Dmitry.