![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General Discussion » View Thread |
Messages 1 to 2 of 2 total |
![]() |
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 Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |