Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General Discussion » View Thread |
Messages 1 to 2 of 2 total |
Writing a roster based system - Need help on where to start. |
Tue, May 11 2010 12:45 AM | Permanent Link |
Adam H. | Hi Guys,
I've just had an enquiry about writing a new customised roster based system. From a quick look it would appear as though they are going to want a grid / input screen where the names are down the left side in rows, and the dates are across the top. (Showing a fortnight at a time). Data wise, I can see that we just need two keyed fields to do this (NameID and Date), with a 3rd field for the value tagging if someone is on duty or not. What I was wondering is some ideas on how should I best do this? Should I pre-populate the database with a list of available dates, and then use some sort of pivot query to create the result. Is it possible to then update the database from within a pivot query, or would this be a memory table that I would need to scan, and create any alter/insert statements required? I haven't done much work in this sort of area before and was wondering if anyone has some recommendations on how to handle it, or where I should start? Cheers Adam. |
Tue, May 18 2010 3:22 AM | Permanent Link |
Adam H. | I guess it's not an easy question.
I've been working on this and have come up with the following. Wanted to run it by you guys to see if you can see a potential flaw: 1) Find the Minimum and Maxmimum Date Ranges to Display. 2) Create a Memory Table and Dynamically create Fields to it. The first field would be the NameID field. 3) Create additional fields for each date. I could label the fieldname according to the integer value of the date. At this stage I would end up with a table with fields such as: NameID D40314 //effectively 2010-05-17 D40315 D40316 D40317 D40318 D40319 D40320 D40321 D40322 D40323 D40324 D40325 D40326 D40327 //effectively 2010-05-30 This would give me a fortnight worth of fields. Since I dynamically create them I can go on for as long as I want. 4) Now run through my source table, locating the NameID for each record and assigning a value to the relevant "date field" that I want. Then the user can alter the Memory table, and after done, I do the reverse. (Clear any entries within the underlying dataset that fits within the date range specified, and then reinsert any values as required). I envisage something like the code below. Just wondering on anyone's thoughts regarding this. Are there any negatives to handling a roster based system like this? Cheers Adam. ----------------- var MINDATE: TDate; MAXDATE: TDate; Field: TField; idate: Tdate; begin //First obtain the minimum and maximum date ranges SourceDS.first; MinDate := SourceDSDate.value; MaxDate := SourceDSDate.value; while not SourceDS.eof do begin if MinDate > SourceDSDate.value then MinDate := SourceDSDate.value; if MaxDate < SourceDSDate.value then MaxDate := SourceDSDate.value; SourceDS.next; end; RosterM.Fields.Clear; //Create the First 'linking' field within the memory dataset. Field := TStringField.Create(RosterM); Field.Name := 'RosterMName'; Field.FieldName := 'NameID'; Field.FieldKind := fkData; Field.Size := 50; Field.DisplayWidth := 15; Field.DataSet := RosterM; //RosterM.Fields.Add(Field); idate := MinDate; //Create each additional field 'date fields' within the memory dataset. while idate < MaxDate + 1 do begin Field := TStringField.Create(RosterM); Field.Name := 'RosterM' + IntTostr(trunc(idate)); Field.FieldName := IntToStr(trunc(idate)); Field.DisplayLabel := datetostr(idate); Field.FieldKind := fkData; Field.Size := 5; Field.DataSet := RosterM; idate := idate + 1; end; //We should now have the columns and rows configured. Time to populate the data from the dataset. RosterM.open; SourceDS.First; while not SourceDS.eof do begin if not RosterM.Locate('Name', SourceDSName.value, []) then RosterM.insert else RosterM.edit; RosterM.findfield('NameID').Value := SourceDSName.value; RosterM.FindField(IntToStr(trunc(SourceDSDate.Value))).Value := SourceDSShift.Value; RosterM.Post; SourceDS.next; end; end; |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |