Set style in cxGrid based on data from all rows

Question

I have a few grids where users enters from-to dates, and would like to set background colour on rows with date-interval that overlap other rows date-interval.

Now I'm using oncalc event on the dataset to set an overlap-flag, but it is a slow solution, and I like to know if it's solvable in cxGrid instead.

Ove B-)

You can probably have a better algorithm to calculate the flag but I don't think the grid (or any visual component) will handle that for you. — Uli Gerhardt, Apr 26 '16 at 13:05
My point is that I have the data, so instead of another trip to the database in an onCalc event it would be better to check for overlap using local data. Tried to be clever and traverse the dataset in the onCalc event, but soon learned that messing with the dataset in the onCalc event was a bad thing to do:) I may end up with triggers in the database. — Ove Halseth, Apr 26 '16 at 13:38
I went down a rabbithole of events(OnCalc, OnDataChange, OnGetContentStyle) trying to manage coloring of cxGrid. I took a step back and found an easier way. FireDAC lets you update the underlying table even if the query it self is not updateable. So I calculated the fields in sql and got rid of OnCalc all together:) — Ove Halseth, Apr 29 '16 at 14:02

score 2 · Answer 1 · answered Apr 26 '16 at 17:27

I think you should be able to do this in a fairly straightfoward manner. The fact that the cxGrid can do grouping of data rows and can be switched into and out of grouping mode shows that once the grid has loaded the dataset, it has all the data necessary available to group, ungroup and sort the rows in the grid without having to re-traverse the related dataset. The data is accessible via the Values property of the DBTableView's DataController.

You can access the data rows in a DBTableView in the grid via the Values property of its associated DataController (see other answer or the cxGrid's Online Help) as if it were a two-dimensional array or matrix - the first dimension is the rows of the grid and the second, the index of one of the column values of the grid.

How I would attempt your task would be to

Add an fkInternalCalc flag field to the dataset to indicate how the row should be colored. There is on need to set its value in the OnCalcFields event, nor do anything else in OnCalcFields. The reason for including this calculated field is just that it's an easy way of having a Values[] value for it without getting involved in "unbound" grid columns.
Once the data is loaded into the grid, process it as a 2D array to work out which rows need to be specially colored and set the Values[entry] for the calculated field
Use the DBTableView's OnCustomDrawCell event to color the cells as you require.

This may require a bit of fine-tuning of its implementation details but I think that in principle it should work.

NB: This will only work if the DataController's IsGridMode property is set to False. If it is set to True, only a sub-set of the dataset's rows are loaded at any one time, so the grid will not group and you will not be able to process the whole of the dataset using its 2D representation in the the DataController's Values property.

score 1 · Accepted Answer · answered Apr 26 '16 at 14:03

I don't know in which moment would you like to do this verification, but you can iterate through the grid dataset to check if there is any record that overlaps and maybe use a invisible unbound column as a flag.

var
I, ARecordIndex: Integer;
begin
    for I := 0 to cxGrid1DBTableView1.DataController.FilteredRecordCount - 1 do
    begin
        ARecordIndex := cxGrid1DBTableView1.DataController.FilteredRecordIndex[I];
        if (Pos('ame2', cxGrid1DBTableView1.DataController.Values[ARecordIndex, cxGrid1DBTableView1Name.Index]) <> 0) then
        cxGrid1DBTableView1.DataController.FocusedRecordIndex := ARecordIndex;
    end;
end;

Thanks a lot for the code:) I was looking for a way to traverse rows and cells. Unfortunately it kept me on the wrong track longer. But that's the price for asking such a specific question. Found out that FireDAC lets you update the underlying table even if the query it self is not updateable. So I calculated the fields in sql and got rid of OnCalc all together:) — Ove Halseth, Apr 29 '16 at 14:07

score 0 · Answer 3 · answered Apr 29 '16 at 14:14

I came closest to a solution with @Rigotti's code. But I found no way to manage OnCalc, OnDataChange and OnGetContentStyle. It triggered way to much.

We switched to FireDAC not long ago, and I was not aware that it let you update the underlying table even if the query it self is not updateable. So I calculated the fields in sql and did not need the OnCalc.

Set style in cxGrid based on data from all rows

3 Answers3