Solved

Master/Detail on multiple fields

Posted on 1998-09-09
13
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 1

Expert Comment

by:Greedy
ID: 1339189
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
ID: 1339190
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
ID: 1339191
Do you have a compound index of the three fields or
only three seperate indexes ?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:millerw
ID: 1339192
Both.
0
 
LVL 1

Expert Comment

by:Greedy
ID: 1339193
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
ID: 1339194
Damn it I said and 3 TDataSets...they aren't DataSets they are TDataSource
0
 
LVL 2

Expert Comment

by:333
ID: 1339195
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
ID: 1339196
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
ID: 1339197
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
ID: 1339198
You can put my code also in OnDataChange
0
 
LVL 1

Author Comment

by:millerw
ID: 1339199
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
ID: 1339200
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
ID: 1339201
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…

733 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