jerrym
Posts: 52
|
| Posted: 08/04/2008, 9:38 PM |
|
i'm hoping someone would give me a few pointers on how i can achieve/implement the following both from a database /tables and CCS perspective.
My issue involves an Asset register and Maintenance application where i'm not real sure on how to handle recurring events like maintenance of a motor car. the record form can easily capture information such as Frequency, StartDate, EndDate, etc
But how do i report on this data say in a list format or even using the calendar view? Do i need to cretae another table to log the recurring events row by row via some procedure?
any help would be greatly appreciated.
regards
jerry
|
 |
 |
wkempees
Posts: 1679
|
| Posted: 08/05/2008, 4:26 AM |
|
http://forums.codecharge.com/posts.php?post_id=97812&s_keyword=recurr
http://forums.codecharge.com/posts.php?post_id=97419&s_keyword=recurr
http://forums.codecharge.com/posts.php?post_id=97355&s_keyword=recurr
http://forums.codecharge.com/posts.php?post_id=95561&s_keyword=recurr
Some interesting reading material.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
jerrym
Posts: 52
|
| Posted: 08/06/2008, 1:33 AM |
|
hi walter
thanks for the links. been thru it and totally agree with the concept. however i think my situation needs more assistance from the experts like yourself.
the solution above suits when there is no requirement for data input/update FOR EACH row of the events say a service of a motor car.
i'm posting my table structure here so you may perhaps give me some pointers.
table - Equipment
fields - equipid, equipdescription, make, model, serial, MaintStartDate, MaintEndDate, Frequency (integer), PeriodType (lookup table: days, week, month, year), NoOfRecurrances (integer)
table - MaintenanceDetails (child table to Equipment)
fields - MaintID, EquipID, ScheduleDate (calculated field), MaintDescription, Priority, ActualCost, CompletedFlag
the solution provided by the links above is for viewing only, i need data to be populated as you can see by the Maintenance table structure. Question is how do i approach this?
for example; if i had a motor car with maintenance parameters in the Equipment table as:
EquipID = 66
MaintStartDate = 08/06/2008
Frequency = 2
PeriodType = Annually
NoOfRecurrances = 8 (being 2 services a year for next 4 years)
how do i now use this info to populate the MaintenanceDetails table with 8 rows of data? i can prob do a trigger of some sort but how do i calculate the dates for the ScheduleDate column?
|
|
|
|
Oper
Posts: 1195
|
| Posted: 08/06/2008, 7:54 AM |
|
we do somethign similar what we do is let said for one customer we use the simple way.
maybe it will work for you the easy one.
Easy Way:
when the event is created you have to create all the entry (real Entry) ex:
ID----------DATE---------------OtherInfo whatever you want here---------ChildRecordID
1..............08/06/2008............!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!...................... 0
2..............02/06/2009............!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!...................... 1
3..............08/06/2009............!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!...................... 1
etc
ID is a Incremental record
Childrecord is a another Incremental for everyMasterEvent Same Event have same CHild
masterEvent have CHildRecordID = 0 all other CHildRecordID are the ID of the Master.
The Important Part is if you try to modify the Event (since is not only one)
you will need to ask if the Person need to chaneg:
1) This Particular event
2) All Events
3) All Future event (+ Current)
(will be easy to change since all Past,presnet and future event have same childRecordID
(work perfect)
we use similar approach for all the Maintenance of the Equpiment/part of Few Gym
Since we use a number of recurrance and look like you too tehre is no proplem thinking on a huge amount of record create in advanced,
(But this part is easy to solve too)
_________________
____________________________
http://www.7bz.com (Free CMS,CRM Developed in CCS)
http://www.PremiumWebTemplate.com
Affiliation Web Site Templates
Please do backup first |
|
|
|
Waspman
Posts: 948
|
| Posted: 08/06/2008, 1:07 PM |
|
I've done this a few times on the fleet managements systems I've done.
I do some simple maths to calculate the 8 dates (number of days etc) at the time you initiate the maintenance program. So your frequency would be number of days between each service. Before you insert the from you would add 90(or whatever you enter) days to the current date then save it to a field then add 90 days to that and save it to another field and so on.
The service intervals could populate fixed fields in your main table or a seperate service schedule table, which would be the table you complete when you do the service.
Then how you implement the service record thingy is up to you.
I would have some kind of table showing upcoming services and as they are done a service schedule form is completed which is connected to a seperate table(the one I mentioned) from the main vehcile table, but related by id etc.
It's more of a logic problem than a coding one.
Hope I've been of some help
T
_________________
http://www.waspmedia.co.uk |
|
|
|
jerrym
Posts: 52
|
| Posted: 08/07/2008, 2:55 PM |
|
sup guys thanks for the responses.
Waspman > been thinking about your method as it closely resembles my situation.
when you say "Before you insert the from you would add 90(or whatever you enter) days to the current date then save it to a field then add 90 days to that and save it to another field and so on."
how do you determine how many fields you're going to require? and do you create the fields on the database on the fly?
i was attempting to test a 'looping method in the after insert event to do the insert in the Child table' for example;
For i=1 To intRecurr
Insert Into MaintenanceDetails (ScheduleDate, MaintDescription)
VALUES (dteMaintStartDate, strMaintDescription)
Next
but i stopped here as the variable dteMaintStartDate needs to add 90 days for each time the loop runs. could i get more help here folks. thanks in advance.
|
|
|
|
jerrym
Posts: 52
|
| Posted: 08/07/2008, 3:34 PM |
|
hey guys
i've amended the loop statement above to now read as below, havent tested it but would ike to get some opinion from you whether there is a better way to achieve what i need.
code:
===
For i=0 To intRecurr-1
Dim intFrequency
Dim dteMaintStartDate
intFrequency = intFrequency * i
dteMaintStartDate = DateAdd(d, intFrequency, dteMaintStartDate)
Insert Into MaintenanceDetails (ScheduleDate, MaintDescription)
VALUES (dteMaintStartDate, strMaintDescription)
Next
===
|
|
|
|
Waspman
Posts: 948
|
| Posted: 08/07/2008, 11:34 PM |
|
Nothing so complicated..(Im a simple man )
I have 2 tables one that describes the vehicle and one that's a maintenance table and contains all possible fields required. Service date - Vehicle ID - Owner ID - Service type engineer ID, etc...
When you add a new record there are 2 fields on it - "services intervals" and "how many times" (determine this how every you want). The second sets the parameters for the loop, so "before insert" you loop through the script that inserts new records into your maintenance tables until the variable set from the "how many times" is 0.
Then I have 4, 6 or 8 service records ready to be completed for the vehicle. We do it this cos different vehicles have different intervals and life expectancy.
It goes something like...
…
Do Until mFreq = 0
(put your insert script here)
mfreq = mfreq - 1
Loop
…
I'll get the proper thing if I've time.
Declare the mFreq variable before the routine and set it to zero when the page or record opens.
Hope this helps...
T
_________________
http://www.waspmedia.co.uk |
|
|
|
|
