Error when sorting a Detail from Master Detail Relationship

[Felipe Garrido]

Hello!

I have a Master Detail Relationship with two RDBGrids.

When I sort a column at Datil Grid show error at DataSet:

[FireDac][Comp][DS]-200. Bookmark is not found for dataset [FDQueryDetail]

What I miss?

Any clue?

[Tomas Rosinsky]

Hello,

standard Master-Detail (MD) relationship in RAD studio uses index for filtering of detail dataset and therefore custom sorting of detail dataset is not supported.

Anyway using of standard MD is not good practice in real appl due to following disadvantages:

• all detail records must be loaded even you need to show only few of them (issue for huge datasets)
  
• detail dataset is filtered and grid is refreshed after each master dataset scroll which significantly slow down master dataset scrolling
  
• detail custom sorting and filtering is not supported
  

So I'd suggest to implement own MD functions using Timer and reloading of detail dataset by code in following way:

• master afterscroll event close detail dataset and restart timer (approx 800 ms is optimum delay)
  
• timer event stop timer and open detail dataset with sql conditions according to active master record
  

This ensures that detail records are loaded only if needed (user stop scrolling for defined time), only needed detail records is loaded and detail dataset is independent with no limitation for sorting and filtering.

You can also use another thread to load details but is it more complex task - let me know if you need more details.

I hope it helps

Tomas

[Felipe Garrido]

Tks again for your assistance Tomas...

When I make an MD relationship I do this (for example):

- Master Dataset (FDQuery1)
- Destail Dataset (FDQuery2)

FDQuery1 SQL statement something like "SELECT * FROM master"

FDQuery2 SQL statement something like "SELECT * FROM detail LEFT JOIN master ON master.id = detail.id_master WHERE detail.id_master = :master.id"

Then in FDQuery2 I set:

- MasterSource as DataSource from FDQuery1 and MasterField as id

- IndexFieldNames as id_master

- DetailFields as id_master

Following your suggestions, could you give me a sample how can I do a MD relationship with events handling?

If you can't, don't worry, I understand.

Tks again!

[Felipe Garrido]

Hello Tomas!

At FDQueryMaster.AfterScroll event I did:

  FDQueryDetail.Close;

  FDQueryDetail.Open;

Without a sleep, because it's a tiny dataset.

Also I removed FDQueryDetail.IndexFieldNames, then sorting works perfectly.

This MD Relationship that I tryed it's read only, but in case of MD with CRUD operations do you know if I need to do something else? Or just AfterScroll event is suficient to do this job?

[Tomas Rosinsky]

Hello,

you do not need to join detail and master table because in detail grid you probably do not want repeat fields from master record, it is shown in master Grid.

So I would use following SQL only:

"SELECT * FROM detail WHERE detail.id_master = :master.id"

and only set parameter master.id to selected value from master dataset before opening of detail dataset.

Tomas

[Tomas Rosinsky]

Detail dataset can be used for CRUD without any limitation. You only have to set correct Id_master for new records.

Tomas