Is it possible to get the Field-Value from TTMSFNCDataGridDatabaseAdapter in the TTMSFNCDataGrid.OnGetCellLayout
This would be really handy for defining formatting conditions externally based on database values instead of displayed values.
Hi, you can use the following code to achieve the behavior your looking for. The code is based on the BioLife.xml (TClientDataSet)
procedure TForm1.TMSFNCDataGrid1GetCellLayout(Sender: TObject;
ACell: TTMSFNCDataGridCell);
var
o: Integer;
begin
if not Assigned(TMSFNCDataGridDatabaseAdapter1) or not TMSFNCDataGridDatabaseAdapter1.CheckDataSet then
Exit;
o := TMSFNCDataGridDatabaseAdapter1.DataLink.ActiveRecord;
try
if TMSFNCDataGridDatabaseAdapter1.SetActiveRecord(ACell.Row) then
begin
if TMSFNCDataGridDatabaseAdapter1.ColumnAtField['Category'].Field.AsString = 'Shark' then
ACell.Layout.Font.Color := gcRed;
end;
finally
TMSFNCDataGridDatabaseAdapter1.DataLink.ActiveRecord := o;
end;
end;
This works great if you are using the field values within the dataset, However, if I need to check the qty from inventory and if qty are not there make that row red, I try to use the following code but it has a performance issue.
procedure TBOMForm.WOITEMSDATAGRIDGetCellLayout(Sender: TObject;
ACell: TTMSFNCDataGridCell);
var
ar, c, r: Integer;
TOTON : DOUBLE;
begin
if ACELL.row < WOITEMSDATAGRID.FixedRowCOUNT then
begin
Acell.Layout.TextAlign := TTMSFNCGraphicsTextAlign.gtaCenter;
Acell.layout.Font.Style := [tfontstyle.fsUnderline,tfontstyle.fsBold];
Acell.layout.Font.Size := 15;
end
else
begin
Acell.layout.Font.Style := [tfontstyle.fsBold];
Acell.layout.Font.Size := 11;
end;
R := 0;
if not Assigned(WOITEMSDATAGridDatabaseAdapter) or not WOITEMSDATAGridDatabaseAdapter.CheckDataSet then
Exit;
r := WOITEMSDATAGridDatabaseAdapter.DataLink.ActiveRecord;
try
if WOITEMSDATAGridDatabaseAdapter.SetActiveRecord(ACell.Row) then
begin
if (WOITEMSDATAGridDatabaseAdapter.ColumnAtField['TYPE'].Field.AsString = 'Raw Good') THEN
BEGIN //FDQUERY4 IS USING A STORED PROCEDURE TO GET QTY ON HAND
FDQUERY4.Close;
FDQUERY4.ParamByName('LG').AsString := 'Vicksburg';
FDQUERY4.ParamByName('PID').AsInteger := WOITEMSDATAGridDatabaseAdapter.ColumnAtField['PARTID'].Field.AsInteger;
FDQUERY4.Open;
if fdquery4.RecordCount > 0 then
TOTON := FDQUERY4.FieldByName('QTYONHAND').AsFloat
else
TOTON := 0;
FDQUERY4.Close;
IF (WOITEMSDATAGridDatabaseAdapter.ColumnAtField['QTYTARGET'].Field.ASFLOAT > TOTON) then
ACELL.Layout.Fill.Color := tAlphaColorRec.Red
ELSE
ACELL.Layout.Fill.Color := tAlphaColorRec.lightgreen;
END
else
exit;
end;
finally
WOITEMSDATAGridDatabaseAdapter.DataLink.ActiveRecord := r;
end;
.
Thanks still learning Datagrid
Garnett
The performance issue comes from fetching the columns (ColumnAtField), you might want to determine the column once after the dataset is set active, and then use that column to retrieve the field. Or use the fields from the dataset directly.
procedure TBOMForm.CheckInvClick(Sender: TObject);
VAR
TOTON,TOT : DOUBLE;
CNT : INTEGER;
flg : boolean;
begin
CNT := 0;
flg := False;
FDTbl9D.FIRST;
while NOT FDTbl9D.Eof do
BEGIN
CNT := CNT + 1;
if FDTbl9D.FieldByName('TYPE').AsSTRING = 'Raw Good' then
BEGIN
FDQUERY4.Close;
FDQUERY4.ParamByName('LG').AsString := FDTBL9BA.FieldByName('LOCATIONGROUP').AsString;
FDQUERY4.ParamByName('PID').AsINTEGER := FDTbl9D.FieldByName('PARTID').AsINTEGER;
FDQUERY4.Open;
if fdquery4.RecordCount > 0 then
TOTON := FDQUERY4.FieldByName('QTYONHAND').AsFloat
else
TOTON := 0;
FDQUERY4.Close;
WOITEMSDATAGrid.BeginUpdate;
TOT := FDTbl9D.FieldByName('QTYTARGET').AsFloat;
if (TOTON >= TOT) AND (FDTbl9BA.FieldByName('STATUS').AsSTRING = 'Entered') then
WOITEMSDATAGrid.Layouts[6,cnt].Fill.Color := tAlphaColorRec.Lightgreen
ELSE
begin
if (TOTON < TOT) AND (FDTbl9BA.FieldByName('STATUS').AsSTRING = 'Entered') then
begin
WOITEMSDATAGrid.Layouts[6,cnt].Fill.Color := tAlphaColorRec.Rosybrown;
flg := True;
end;
end;
WOITEMSDATAGrid.EndUpdate;
END;
FDTbl9D.Next;
END;
FDTbl9D.FIRST;
if flg then
CreateWOTraveler.Enabled := False
else
CreateWOTraveler.Enabled := True;
end;
This is the way I set the row column[6] fill color base on inventory qty and did work in fncgrid but it will not set the color here and I cannot seem to get it to work in the
layout procedure WOITEMSDATAGRIDGetCellLayout
thanks
Garnett
Your previous code, coloring the cells dynamically was the correct approach. You mentioned that you had performance issues, most likely this is due to the retrieval of columns each time a cell layout is dynamically built up. You would need to change the code to:
procedure TForm7.FormCreate(Sender: TObject);
begin
//after activate adapter / dataset
FTypeColumn := WOITEMSDATAGridDatabaseAdapter.ColumnAtField['TYPE'];
FPartIDColumn := WOITEMSDATAGridDatabaseAdapter.ColumnAtField['PARTID'];
FQtyTargetColumn := WOITEMSDATAGridDatabaseAdapter.ColumnAtField['QTYTARGET'];
end;
procedure TBOMForm.WOITEMSDATAGRIDGetCellLayout(Sender: TObject;
ACell: TTMSFNCDataGridCell);
var
ar, c, r: Integer;
TOTON : DOUBLE;
begin
if ACELL.row < WOITEMSDATAGRID.FixedRowCOUNT then
begin
Acell.Layout.TextAlign := TTMSFNCGraphicsTextAlign.gtaCenter;
Acell.layout.Font.Style := [tfontstyle.fsUnderline,tfontstyle.fsBold];
Acell.layout.Font.Size := 15;
end
else
begin
Acell.layout.Font.Style := [tfontstyle.fsBold];
Acell.layout.Font.Size := 11;
end;
R := 0;
if not Assigned(WOITEMSDATAGridDatabaseAdapter) or not WOITEMSDATAGridDatabaseAdapter.CheckDataSet then
Exit;
if not Assigned(FTypeColumn) or not Assigned(FPartIDColumn) or not Assigned(FQtyTargetColumn) then
Exit;
r := WOITEMSDATAGridDatabaseAdapter.DataLink.ActiveRecord;
try
if WOITEMSDATAGridDatabaseAdapter.SetActiveRecord(ACell.Row) then
begin
if (FTypeColumn.Field.AsString = 'Raw Good') THEN
BEGIN //FDQUERY4 IS USING A STORED PROCEDURE TO GET QTY ON HAND
FDQUERY4.Close;
FDQUERY4.ParamByName('LG').AsString := 'Vicksburg';
FDQUERY4.ParamByName('PID').AsInteger := FPartIDColumn.Field.AsInteger;
FDQUERY4.Open;
if fdquery4.RecordCount > 0 then
TOTON := FDQUERY4.FieldByName('QTYONHAND').AsFloat
else
TOTON := 0;
FDQUERY4.Close;
IF (FQtyTargetColumn.Field.ASFLOAT > TOTON) then
ACELL.Layout.Fill.Color := tAlphaColorRec.Red
ELSE
ACELL.Layout.Fill.Color := tAlphaColorRec.lightgreen;
END
else
exit;
end;
finally
WOITEMSDATAGridDatabaseAdapter.DataLink.ActiveRecord := r;
end;
end;
ColumnAtField loops through the columns which can be done once, after the dataset is activated.
Still having an issue with performance, however I decided to pickup inventory qty on hand value with a fdtable calculated field which as part of the dataset: code below works
procedure TBOMForm.WOITEMSDATAGRIDGetCellLayout(Sender: TObject;
ACell: TTMSFNCDataGridCell);
var
r: Integer;
begin
if ACell.row < WOITEMSDATAGRID.FixedRowCOUNT then
begin
ACell.Layout.TextAlign := TTMSFNCGraphicsTextAlign.gtaCenter;
ACell.Layout.Font.Style := [tfontstyle.fsUnderline, tfontstyle.fsBold];
ACell.Layout.Font.Size := 15;
endelsebeginAcell.Layout.Font.Style := [tfontstyle.fsBold];
ACell.Layout.Font.Size := 11;
end;
if not Assigned(WOITEMSDATAGridDatabaseAdapter) or not WOITEMSDATAGridDatabaseAdapter.CheckDataSet then
Exit;
r := WOITEMSDATAGridDatabaseAdapter.DataLink.ActiveRecord;
try
if WOITEMSDATAGridDatabaseAdapter.SetActiveRecord(ACell.row) then
begin
if WOITEMSDATAGridDatabaseAdapter.ColumnAtField['TYPE'].Field.AsString = 'Raw Good'
then
if WOITEMSDATAGridDatabaseAdapter.ColumnAtField['QTYTARGET']
.Field.AsFloat > WOITEMSDATAGridDatabaseAdapter.ColumnAtField['TOTON']
.Field.AsFloat THEN
ACell.Layout.FILL.Color := tAlphaColorRec.Yellowgreen
ELSE
ACell.Layout.FILL.Color := tAlphaColorRec.green;
end;
finally
WOITEMSDATAGridDatabaseAdapter.DataLink.ActiveRecord := r;
end;
Thanks Pieter
Garnett
Hi,
Does this provide better results in terms of performance?
PS: I edited and formatted the code snippets for readability
Yes, average time before 20-40 seconds to 3 to 5 seconds.
P.S. The other issue with filtering datagrid using firedac fdtable tables. Is there a flag(s) or setting within the datagrid or datagriddatabaseadapter that would not allow filtering or could it be that master/detail datagrids has an issue with filtering?
Thanks
Garnett