Is there a way to set details inside OnFilterRecord

[JGordon]

Hi,

I need to look at the details of a master inside the OnFilterRecord to see if it should match. Is there a way to do a RefreshDetail inside the OnFilterRecord event?

Thanks,
JGordon

[AndreyZ]

Hello,

If you want to establish the master/detail relationship, please read the "Master/Detail Relationships" articles of the SDAC documentation. It describes two ways of establishing master/detail relationships: using parameters and using the MasterSource, MasterFields, and DetailFields properties. In this case, you don't need to use the OnFilterRecord event.
If that is not what you want to achieve, please describe in more details the functionality you need.

[JGordon]

Hi,

I have a table Family and a table Children. Family is the master and Children is the detail. Now, I have a complicated Delphi calculation that determines if the Child in the Children table is to be included. The calculation can't be represented as a sql statement. I would like to have a grid with the Families that have any items in the Children table that are included.

The easiest way that I can come up with is to use the OnFilterRecord and scan through the Children records in there, but I do not seem to be on the correct Family record. I assume it's in a different buffer that is not the regular one since we are in the middle of loading and have not yet decided if this record is accepted. I am not sure how to access the Family data for the record I am filtering from inside the OnFilterRecord

***Update: I did get further. I thought the dataset.fieldbyname('xyz').value inside the onFilterRecord was incorrect but I think it is right and my problem is elsewhere.

Thanks,
JGordon

[AndreyZ]

You can use the following code:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  MSQueryFamily.Open;
  MSQueryChildren.Filtered := True;
  MSQueryChildren.Open;
end;

procedure TForm1.MSQueryChildrenFilterRecord(DataSet: TDataSet;
  var Accept: Boolean);
begin
  Accept := MSQueryFamily.FieldByName('ID').AsInteger = MSQueryChildren.FieldByName('FamilyID').AsInteger; // here you can implement your complicated logic of including records from the Children table to a grid
end;

// whenever the active record in the MSQueryFamily dataset is changed, you should refresh the filter for the MSQueryDetail dataset
procedure TForm1.MSQueryMasterAfterScroll(DataSet: TDataSet);
begin
  MSQueryDetail.Filtered := False;
  MSQueryDetail.Filtered := True;
end;

[JGordon]

That didn't quite work. Let me start this way. I have 2 tables

Code: Select all

FamTbl
   FamRec:  integer
   FamName: string

MemTbl
   FamRec:  integer
   MemRec:  integer
   MemName: string

There is a primary index on MemTbl of (FamRec;MemRec) and we have defined the MemTbl to have a master of FamTbl connecting on FamRec

Now, if I do:

Code: Select all

   FamTbl.First;
   While not FamTbl.Eof do
     begin
       ...
       FamTbl.Next; 
     end;

This will go through all the FamTbl entries even if there are no MemTbl entries. What I would like to do is put an OnFilterRecord on the FamTbl that can look at the MemTbl Something like:

Code: Select all

   procedure Form.FamOnFilterRecord(DataSet: TMSTable; var Accept: boolean);
   var
     r:  integer;
   begin
     r:=DataSet.FieldByName('FamRec').AsInteger;
     ... do something to set the MemTbl to use the r for the Master/Detail Link
     Accept:=not MemTbl.IsEmpty;
   end;

Now, I know I can create a query inside the OnFilterRecord to determine if the is any memtbl record that match the FamRec and that match the condition in the MemTbl.Filter, but it would be easier if I could just use the MemTbl since it can have filters on it.

Thanks,
JGordon

[AndreyZ]

If you want the master table to contain only the records, for which there are detail records, you should query only these master records from the server. For example, the master query:

Code: Select all

SELECT * FROM FamTbl
WHERE FamRec in (SELECT FamRec FROM MemTbl)

, and the detail query:

Code: Select all

SELECT * FROM MemTbl

[JGordon]

I do understand that, but there are filters (and Calculations) on the MemTbl too. So, I cannot make a SQL statement to get just the families I need. I must to use the OnFilterRecord and inside the OnFilterRecord I need to access the detail tables that would be attached to the record being tested. I was hopeing that I could set the parameters and call something like SetMasterParams to force the link to use the 'temporary' master.

Thanks,
Joseph Gordon

[JGordon]

I think I have solved it. Since the FieldByName is using the temporary buffer at this point, if I simply do a Active:=false; and Active:=true; on the detail, it is reset to the record being tested. I could instead surface the MDPropertiesChanged procedure and call it.

Thanks,
Joseph Gordon

[AndreyZ]

It's good to see that you've found a solution. If any other questions come up, please contact us.