Solved

Master/Detail on multiple fields

Posted on 1998-09-09
13
250 Views
Last Modified: 2010-04-06
I have two DBase files that I use and need to link them on MONTH_NO, JRNL_TYPE, and MODE columns.  I've tried everything I can think of.  MONTH_NO;JRNL_TYPE;MODE doesn't work like the help file states.  Can anyone help?
0
Comment
Question by:millerw
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 1

Expert Comment

by:Greedy
Comment Utility
Do you have the second table (detail) indexed on the three fields?

click on the (...) button in the object inspector and see if you can do it there.

what is the error that you are getting?

0
 
LVL 1

Author Comment

by:millerw
Comment Utility
Both tables are indexed on all three fields (as well as some others--I removed all but those three for a test and it didn't work either).  

The error is as follows: "Field Index out of range"

Another I got was "'Table' has no index for fields "MONTH_NO;JRNL_TYPE;MODE"'.

I even tried an expression index on all three, but that didn't work either.

Hope that helps you,
Scott
0
 
LVL 1

Expert Comment

by:EmmDieh
Comment Utility
Do you have a compound index of the three fields or
only three seperate indexes ?
0
 
LVL 1

Author Comment

by:millerw
Comment Utility
Both.
0
 
LVL 1

Expert Comment

by:Greedy
Comment Utility
Got some bad news for you... take  a look at
http://www.inprise.com/devsupport/delphi/ti_list/TI2838.html

Now for some good news after about an hour of dicking with this POS I got this to work.  This is the whole unit I'm using two TTables two DBGrids and a TQuery oh and 3 TDatasets...talk about a pain in the ass but it's fast...and with a few tricks could be even faster.

I did a test with 12,000 master records and 471,826 detail records and it takes about 7 seconds on my P200 the problem was that it had to go into swap because I only have 64 megs.  with 1200 and 14630 it is instant when the master table scrols...not too bad unless you are doing 500,000 records :)  play with this and I might come up with a better solution tommarow - like move this to Paradox :) - give me your email if you can't figure this out and want me to send it to you instead.  The power in all in that one query.

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Grids, DBGrids, Db, DBTables, StdCtrls, Buttons;

type
  TForm1 = class(TForm)
    DataSource1: TDataSource;
    Table1: TTable;
    DataSource2: TDataSource;
    Table2: TTable;
    DBGrid1: TDBGrid;
    DBGrid2: TDBGrid;
    Label1: TLabel;
    Label2: TLabel;
    Query1: TQuery;
    DataSource3: TDataSource;
    procedure FormCreate(Sender: TObject);
    procedure Table1AfterScroll(DataSet: TDataSet);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.DFM}

procedure TForm1.FormCreate(Sender: TObject);
var
  MyTypeInt, MyModeInt, MyMonth, MasterCounter, DetailCounter: Integer;
  MyTypeString, MyModeString: string;
  Running: Boolean;

begin
  Randomize;
  Table1.TableName := 'MTable.DBF';
  Table1.FieldDefs.Clear;
  Table1.FieldDefs.Add('MONTHNO', ftString, 5, False);
  Table1.FieldDefs.Add('JRNLTYPE', ftString, 20, False);
  Table1.FieldDefs.Add('MODE', ftString, 20, False);
  Table1.IndexDefs.Clear;
  Table1.CreateTable;

  Table2.TableName := 'DTable.DBF';
  Table2.FieldDefs.Clear;
  Table2.FieldDefs.Add('MONTHNO', ftString, 5, False);
  Table2.FieldDefs.Add('JRNLTYPE', ftString, 20, False);
  Table2.FieldDefs.Add('MODE', ftString, 20, False);
  Table2.FieldDefs.Add('RECNO', ftInteger, 0, False);
  Table2.IndexDefs.Clear;
  Table2.CreateTable;

  Table1.Active := True;
  Table2.Active := True;

  Table1.DisableControls;
  Table2.DisableControls;
  For MyMonth := 1 to 12 do
  begin
    For MasterCounter := 1 to 100 do //# of master records * 12
    begin
      MyTypeInt := Random(5);
      MyModeInt := Random(5);
      case MyTypeInt of
        1 : MyTypeString := 'Type One';
        2 : MyTypeString := 'Type Two';
        3 : MyTypeString := 'Type Three';
        4 : MyTypeString := 'Type Four';
        5 : MyTypeString := 'Type Five';
      else
        MyTypeString := 'Type Other';
      end;
      case MyModeInt of
        1 : MyModeString := 'Mode One';
        2 : MyModeString := 'Mode Two';
        3 : MyModeString := 'Mode Three';
        4 : MyModeString := 'Mode Four';
        5 : MyModeString := 'Mode Five';
      else
        MyModeString := 'Mode Other';
      end;
      Table1.InsertRecord([IntToStr(MyMonth), MyTypeString, MyModeString]);
      Running := True;
      DetailCounter := 1;
      while running do
      begin
        Table2.InsertRecord([IntToStr(MyMonth), MyTypeString, MyModeString, DetailCounter]);
        if DetailCounter > Random(100) then //# of detail recs
          running := False
        else
          inc(DetailCounter);
      end;
    end;
  end;
  Table1.EnableControls;
  Table2.EnableControls;
  Label1.Caption := 'Master: ' + IntToStr(Table1.RecordCount) + ' Records';
  Label2.Caption := 'Detail: ' + IntToStr(Table2.RecordCount) + ' Records';
  Table1.Next;
end;

procedure TForm1.Table1AfterScroll(DataSet: TDataSet);
begin
  Query1.Sql.Clear;
  Query1.SQL.Add('select * from DTable where MONTHNO=''' + Table1.FieldByName('MONTHNO').AsString + ''' and JRNLTYPE=''' + Table1.FieldByName('JRNLTYPE').AsString + ''' and MODE=''' + Table1.FieldByName('MODE').AsString + ''' order by MONTHNO,JRNLTYPE,MODE,RECNO');
  Query1.Active := True;
end;

end.

0
 
LVL 1

Expert Comment

by:Greedy
Comment Utility
Damn it I said and 3 TDataSets...they aren't DataSets they are TDataSource
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 2

Expert Comment

by:333
Comment Utility
Hi,
there is another way. Let's say, you have Table1 (master) and Table2 (detail). Make index on Table2 field i.e. MODE. Then, in AfterScroll event write:

procedure TForm1.Table1AfterScroll(DataSet: TDataSet);
begin
   Table2.Locate('MODE', Table1.FieldByName('MODE').AsString, [] );
   while Table2.FieldByName('MODE').AsString=Table1.FieldByName('MODE').AsString do begin
      if (Table2.FieldByName('JRNLTYPE').AsString=Table1.FieldByName('JRNLTYPE').AsString) and (Table2.FieldByName('MONTHNO').AsString=Table1.FieldByName('MONTHNO').AsString) then
      break;
   Table2.Next;
  end;
end;

This method is good if there are not much fields with the same values. So indexed field must be field, with fewest same values.

A.
0
 
LVL 1

Author Comment

by:millerw
Comment Utility
Ok, I have a solution and I want to see what you guys think.  I've never used the AfterScroll event.  Normally, I use the good ol' OnDataChange event of the TDatasource to do my job.  Here is what I have got:

procedure TfrmJournalPosting.dsrJournalInfoDataChange(Sender: TObject;
  Field: TField);
begin
     If Assigned(TempJournalFilter) then
        TempJournalFilter.Free;
     With FStrategyYear.tblTempJournalInfo do
          TempJournalFilter := TTableFilter.Create(
                               FStrategyYear.tblTempJournalTrans,
                               '(MONTH_NO  = '''+
                               IntToStr(FieldByName('MONTH_NO').AsInteger)+
                               ''') AND (JRNL_TYPE  = '''+
                               FieldByName('JRNL_TYPE').AsString+
                               ''') AND (MODE = '''+
                               FieldByName('MODE').AsString+''')');
end;

TempJournalFilter is an object I've created to activate a filter and when it is freed, it restores the previous filter that was active.  That is what is taking place here.  

Comments?  Should I use the AfterScroll event or leave this?  The problem is that the tables are in a DataModule and it is "bad OOP technique" to access those tables directly from my form(s) since it is used by two at any one time.  One handles the "Account Tree" and the other is a Setup form or a Journal Posting form (mattering on what the log file says the year's mode it).  The datasources are always on the form in question, while the tables are all in the DataModule (guess that breaks the above rule itself :-)

Anyway, why not just filter it based on the three values?  If master detail won't work, then just do the job it won't--filter it manually.  Right?  or am I way off base?

Scott
0
 
LVL 1

Expert Comment

by:Greedy
Comment Utility
useing the filter works well too...about as fast as the Query thing.
The OnDataChange Event is the best place to put this code(I was just useing OnScroll for demonstration)
I just tested the filter with my little program and got these results 12000 master with 471701 details gave a 9 to 10 second delay when moving to a new record...I tried my query code on the same database and got 7 to 8 sec.  If there was a way to use an index for the filter it would be cool...but the thing is the MDX file that was build was almost as big as the batabase (25 meg) oh well, if the database is small I guess the user will never see any problems.

0
 
LVL 2

Expert Comment

by:333
Comment Utility
You can put my code also in OnDataChange
0
 
LVL 1

Author Comment

by:millerw
Comment Utility
Normally, each master record will have from 2 to 25 detail records.  Not more than that (at least that is what they tell me :-)  Also, I have indexed on all three fields and I have been brought to the understanding that if an index exists, the filtering mechanism will use it to speed up the filtering process.

The way this works, is that the master record holds pertinate data to the entire journal transaction while the detail records are the individual transaction line items.  Under normal circumstances (the Journals files), I will be using the VOUCHER_NO field to link to two tables in a master-detail relationship.  However, as a user is entering a new transation (into the TempJournals files), the VOUCHER_NO field is blank for some of the process.  Thus, I have to find the detail records based on what month they are in, what journal they have selected to post to, as well as if they are in View, Add, Edit, Delete, or Undelete modes (View and Delete don't have a "mode" in the TempJournals files since they operate on the Journals files--mode is only applicable to the TempJournals).

BTW, VOUCHER_NO is unique to each transation and every transaction must have one specified.

All that to say, I think filtering will be fast as needed--especially since I'M the one doing the master record changing.  The user normally doesn't have direct access to the master/detail tables (only by doing searches are they doing this and searches are causing problems and will most likely be formed into a query).  

Greedy, if you want the points answer.  I'll give you the points for all your work and helping me out as much as you did.

Thanks,
Scott
0
 
LVL 1

Accepted Solution

by:
Greedy earned 50 total points
Comment Utility
OK...are you sure you don't want to just port this to Paradox?  Think of that one person that does 1000 entries a day by the time they die they could have 21,000,000 records in there :) ...So just to test what Paradox can do I rewote the little program and MasterSourced it...It has much better performance.  I was testing it on a small 240 master 1,230,471 detail and it would retrieve the 5000+ matching detail record sets fast as I would hit the key...the database is smaller too but I don't know why.  Oh well, glad to help...Damn now I have to go back to doing my work :(
0
 
LVL 1

Author Comment

by:millerw
Comment Utility
Yeah, I'm sure.  The DBase files have the Soft delete feature that we use to help our users out.  I'll keep PDox in mind though.

Thanks again for your help,
Scott
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now