Page 1 of 1
Is there a way to set details inside OnFilterRecord
Posted: Mon 20 Aug 2012 20:26
by 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
Re: Is there a way to set details inside OnFilterRecord
Posted: Tue 21 Aug 2012 11:15
by 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.
Re: Is there a way to set details inside OnFilterRecord
Posted: Fri 24 Aug 2012 15:54
by 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
Re: Is there a way to set details inside OnFilterRecord
Posted: Mon 27 Aug 2012 10:47
by 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;
Re: Is there a way to set details inside OnFilterRecord
Posted: Tue 28 Aug 2012 22:49
by 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
Re: Is there a way to set details inside OnFilterRecord
Posted: Wed 29 Aug 2012 11:49
by 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:
Re: Is there a way to set details inside OnFilterRecord
Posted: Wed 29 Aug 2012 17:39
by 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
Re: Is there a way to set details inside OnFilterRecord
Posted: Wed 29 Aug 2012 19:12
by 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
Re: Is there a way to set details inside OnFilterRecord
Posted: Fri 31 Aug 2012 06:53
by AndreyZ
It's good to see that you've found a solution. If any other questions come up, please contact us.