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.

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;