Jim,
You could add a field like dateTimeCompleted. Then
create a view that only sees records where dateTimeCompleted
IS NULL. If your jobs all relate to the information in the
header table, put the completion datetime in the header.
Otherwise it can go in the detail table.
If you only want to see 'open items' put the field
in both. Then you can look at the open items for open jobs.
Putting in a datetime of completion for a job could also
automatically 'close' the detail related to that job.
An equivalent view for 'completed' jobs or detail
items could be used for billing or production reports. It
would simply list all jobs or detail items where
dateTimeCompleted IS NOT NULL.
I am uncomfortable deleting data for several
reasons. One is the opportunity for deleting something that
isn't really finished. Another is the lost opportunity for
historical reports. If you append completed items to a
jobHistory table, that might be another approach.
Using a dateTimeCompleted field to define a view
just seems more automatic and less susceptible to
irrecoverable data.
Randy Peterson
PS. Triggers might automate the events you want to happen
everytime you enter or remove something.
----- Original Message -----
From: "Jim Limburg" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[EMAIL PROTECTED]>
Sent: Thursday, March 20, 2003 7:24 AM
Subject: [RBASE-L] - Re: Brain mush - thought needed on a
view
> Ben
>
> I have considered having a way to insert a row into this
TimeTable table
> when new "Jobs" are entered into the Header table, and I
think this is what
> you're saying. Wow, I'm starting to be able to think like
some of you gurus.
> I did say starting here... ha,ha.
>
> Correct me if I'm wrong.. when you say orphans you are
meaning basically
> any row that would be in the TimeTable that for some
reason the parent got
> deleted. I know we have had a few headaches when our plant
manager, and stock
> control person have complained about not having a good way
to tell if when
> looking up certain parts as to whether a job is started or
not.. This is my
> ultimate goal in all this. This leads me to believe that
It would be better
> to create a routine for removing/deleting jobs that would
get rid of the info
> in both tables-- as it should be. I do wonder, how would
one go about keeping
> someone from deleting a row in the parent thru the form?
Thinking...ouch
>
> Please bear in mind, I did not design this db.. My Boss
did, and overall it
> has served the business well, it's just needs to be
tweeked to serve some
> more specific and detailed needs. Most of our db's are DOS
6.5++ including this one.
>
> Jim Limburg
>
> Ben Petersen wrote:
> > No. Wouldn't bomb, just wouldn't be included in the
view. Maybe that's not
> > an issue? I could imagine routines failing if they
expected a correlation and
> > didn't find it. Although you started wanting to know if
there were any orphans.
> >
> > It's more of a user friendly issue. If you use
multi-table forms you have to
> > train users to access the second table before exiting,
if you use "enter
> > using", to satisfy a PK/FK constraint (the other way to
avoid orphans). You
> > also have to decide if the same form is satisfactory to
both edit and enter...
> > or maintain two forms.
> >
> > If you code so as to always have keyed, empty rows
available, and to
> > present "left-over" empty rows when the user wants a new
record, everything
> > becomes very predictable and more easily managed.
> >
> > I use the same logic to force completion... if the user
wants to "add new"
> > they keep getting the last incomplete record until the
entry is properly
> > completed or deleted. User friendly, fewer rules, fewer
forms, less training...
> >
> > Ben Petersen
> >
> >
> >
> > On 19 Mar 2003, at 16:18, Jim Limburg wrote:
> >
> >
> >>Hey Ben
> >>
> >>That sound feasible, but... I was just thinking.. the
hours
> >>are not recorded until there are hours, so what would
happen
> >>in this if there is no mponums in the TimeTable table?
> >>It wouldn't bomb would it?
> >>
> >>Attempting to come to life
> >>Jim Limburg
> >>
> >>Ben Petersen wrote:
> >>
> >>>Hi Jim,
> >>>
> >>>>From the view, or either table, you could could test
for null in one or more
> >>>columns:
> >>>
> >>>Sel * from HdrTable t1, TimeTable t2 +
> >>> whe t1.MpoNum = t2.MpoNum and +
> >>> t2.WorkHrs is Null and t1.ItemNum is Null
> >>>
> >>>Ben Petersen
> >>>
> >>>
> >>>
> >>>On 19 Mar 2003, at 15:51, Jim Limburg wrote:
> >>>
> >>>
> >>>
> >>>>G-Day all
> >>>>
> >>>>I would like some advice.
> >>>>
> >>>>We have one table which is basically a header table
for MPO to track
> >>>>jobs in the plant.. then another table that gets data
from timeclocks
> >>>>for each Mpo number and related data..
> >>>>
> >>>>I want a view and ultimatley a report that I would run
on this view to
> >>>>show all the mpos that do not have time put onto them.
In other words
> >>>>Mpos entered into the system, but not yet started on..
> >>>>
> >>>>General info in the header table I would like to
collect would be
> >>>>Mponum, Itemnum, ShrtDesc,QtyOrd,Location,shopordr
> >>>>
> >>>>and then the table of time tracking we have
> >>>>Mponum,WrkDate,WorkHrs,OTHrs,DTHrs,ClockNo
> >>>>
> >>>>Can someone give some suggestions to break this fog
I'm in..
> >>>>
> >>>>I know this is so simple it's going to make me kick my
can when I see it, but
> >>>>I've had one of those head in the cloud days..
> >>>>
> >>>>Thanks for input
> >>>>Jim Limburg
