Hi,
Using method from this my previous post using @AndrewSimard method, I have loaded a TXDataWebDataSet with around 100 lines of data. Using a TWebDataSource we linked it with a TWebDBGrid.
Unfortunately only first visible rows from the TWebDBGrid contains data, all the rest, generates rows, but when you scroll down, are all empty.
Is there a limit of visible rows in this grid?
What should I do to show all the rows in the dataset in the grid, not only first ~50 ones?
Hmmm.. Well that's not good. I fiddled around with that example and was able to use a JSON string that had 15,000 records in it that seemed to work OK.
I'd suggest checking that the JSON string you're passing around is a WideString and not a String, but then again if it got clipped it is unlikely the JSON parsing would still work. It is super-finicky about that kind of thing. Doesn't even like leading zeroes in integers. So maybe double-check that the JSON has what you're expecting right before it is loaded into the dataset?
Thank you @AndrewSimard for your reply.
After looking inside Chrome debug window I have noticed the error that compromises the grid loading.
It was related with a calculated field for TDateTime that issued an conversion error at loading.
After solving the Calcfield issue the grid loads normally.
So it is not an Grid issue.
But the TWebDBGrid has no filtering options and now I struggle to use a TFNCGrid with a FNCGridDatabaseAdapter. It shurely loads but after searching on internet (very poor documentation TMS!!!) I discovered that no filter/sort/group works in Dataset mode for FNCGrid. The solution FNCGridDatabaseAdapter.LoadAllDataAndDisconnect provided by TMS is not working because we use static columns and when disconecting it scrambles all the grid.
So I was wondering if I can use FNCGrid.LoadFromJSONStream instead of using a dataset like my current approach, so I can use the full filtering/ordering faetures from FNCGrid.
So @AndrewSimard maybe you know how can I switch
JRowList := TJSArray(JTableList['RowList']) used previously to load in the dataset to a TJSONStream compatible with FNCGrid.
Thanks for understanding,
Dor
Hi,
We have looked at this here. In the grid database adapter demo, we can set the columns with the following code:
TMSFNCGridDatabaseAdapter1.Columns.Add.FieldName := '_Species_No';
TMSFNCGridDatabaseAdapter1.Columns.Add.FieldName := '_Category';
TMSFNCGridDatabaseAdapter1.Columns.Add.FieldName := '_Common_Name';
Then this will load the data for only those columns. Then using TMSFNCGridDatabaseAdapter1.LoadAllDataAndDisconnect will store the data from those columns only, and then you will be able to apply filtering and sorting. Alternatively, if you want to keep connection, you can apply filtering and sorting on dataset level instead. You can also use OnCanSortColumn, capture the sort click interaction, and then sort the dataset. Using the filter will require you to use the OnNeedFilterDropDownData, where you can provide a list of filter values coming from the dataset. After click in the filter list, the OnFilterSelect event is triggered with the chosen value. This will allow you to filter the grid on dataset level, which will then reload the grid with the new data.
Here is my code for loading the grid:
procedure TFLicenses.RefreshLicensesByOrganisation(aSecretKey: String);
var
Response : TXDataClientResponse;
JSObjectResponse : JS.TJSObject;
JFDBS: TJSObject;
JManager: TJSObject;
JTableList: TJSObject;
JColumnList: TJSArray;
JRowList: TJSArray;
i : Integer;
StringFields : Array of TStringField;
IntegerFields : Array of TIntegerField;
FloatFields : Array of TFloatField;
DateFields : Array of TDateField;
DateTimeFields : Array of TDateTimeField;
Fld_ACQUISITION_CONTRACT_DATE : TStringField;
Fld_ACQUISITION_INVOICE_DATE_CALC : TStringField;
Fld_LICENSE_EXPIRE_DATE_CALC : TStringField;
Fld_LAST_ACTIVATION_DATE_CALC : TStringField;
Fld_LAST_VALIDATION_DATE_CALC : TStringField;
Fld_SUBSCRIPTION_EVENT_DATE_CALC : TStringField;
begin
if Assigned(tbLicenses) then begin
FreeAndNil(tbLicenses);
end;
if Assigned(dsLicenses) then begin
FreeAndNil(dsLicenses);
end;
tbLicenses := TXDataWebDataSet.Create(Self);
dsLicenses := TWebDataSource.Create(Self);
tbLicenses.Close;
XDataWebClient.Connection := XDataWebConnection;
Response := await(XDataWebClient.RawInvokeAsync('IMagisterLicenseService.GetOrganizationLicenses', [aSecretKey, 19, -1]));
JFDBS := TJSObject(TJSObject(TJSJson.Parse(string(Response.Result)))['FDBS']);
JManager := TJSObject(JFDBS['Manager']);
JTableList := TJSObject(TJSArray(JManager['TableList'])[0]);
JColumnList := TJSArray(JTableList['ColumnList']);
JRowList := TJSArray(JTableList['RowList']);
for i := 0 to JRowList.Length - 1 do begin
JRowList.Elements[i] := TJSObject(JRowList.Elements[i])['Original'];
end;
for i := 0 to JColumnList.Length-1 do begin
if (String(TJSObject(JColumnList.Elements[i])['DataType']) = 'AnsiString') then
begin
SetLength(StringFields, Length(StringFields) + 1);
StringFields[Length(StringFields)-1] := TStringField.Create(tbLicenses);
StringFields[Length(StringFields)-1].FieldName := String(TJSObject(JColumnList.Elements[i])['Name']);
StringFields[Length(StringFields)-1].Size := Integer(TJSObject(JColumnList.Elements[i])['Size']);
StringFields[Length(StringFields)-1].Dataset := tbLicenses;
end
else if (String(TJSObject(JColumnList.Elements[i])['DataType']) = 'Int32') then
begin
SetLength(IntegerFields, Length(IntegerFields) + 1);
IntegerFields[Length(IntegerFields)-1] := TIntegerField.Create(tbLicenses);
IntegerFields[Length(IntegerFields)-1].FieldName := String(TJSObject(JColumnList.Elements[i])['Name']);
IntegerFields[Length(IntegerFields)-1].Dataset := tbLicenses;
end
else if (String(TJSObject(JColumnList.Elements[i])['DataType']) = 'WideString') then
begin
SetLength(StringFields, Length(StringFields) + 1);
StringFields[Length(StringFields)-1] := TStringField.Create(tbLicenses);
StringFields[Length(StringFields)-1].FieldName := String(TJSObject(JColumnList.Elements[i])['Name']);
StringFields[Length(StringFields)-1].Size := Integer(TJSObject(JColumnList.Elements[i])['Size']);
StringFields[Length(StringFields)-1].Dataset := tbLicenses;
end
else if (String(TJSObject(JColumnList.Elements[i])['DataType']) = 'FmtBCD') then
begin
SetLength(FloatFields, Length(FloatFields) + 1);
FloatFields[Length(FloatFields)-1] := TFloatField.Create(tbLicenses);
FloatFields[Length(FloatFields)-1].FieldName := String(TJSObject(JColumnList.Elements[i])['Name']);
FloatFields[Length(FloatFields)-1].Dataset := tbLicenses;
end
else if (String(TJSObject(JColumnList.Elements[i])['DataType']) = 'Currency') then
begin
SetLength(FloatFields, Length(FloatFields) + 1);
FloatFields[Length(FloatFields)-1] := TFloatField.Create(tbLicenses);
FloatFields[Length(FloatFields)-1].FieldName := String(TJSObject(JColumnList.Elements[i])['Name']);
FloatFields[Length(FloatFields)-1].Dataset := tbLicenses;
end
else if (String(TJSObject(JColumnList.Elements[i])['DataType']) = 'Date') then
begin
SetLength(StringFields, Length(StringFields) + 1);
StringFields[Length(StringFields)-1] := TStringField.Create(tbLicenses);
StringFields[Length(StringFields)-1].FieldName := String(TJSObject(JColumnList.Elements[i])['Name']);
StringFields[Length(StringFields)-1].Size := 50;
StringFields[Length(StringFields)-1].Dataset := tbLicenses;
end
else if (String(TJSObject(JColumnList.Elements[i])['DataType']) = 'DateTimeStamp') then
begin
SetLength(StringFields, Length(StringFields) + 1);
StringFields[Length(StringFields)-1] := TStringField.Create(tbLicenses);
StringFields[Length(StringFields)-1].FieldName := String(TJSObject(JColumnList.Elements[i])['Name']);
StringFields[Length(StringFields)-1].Size := 50;
StringFields[Length(StringFields)-1].Dataset := tbLicenses;
end;
end;
Fld_ACQUISITION_CONTRACT_DATE := TStringField.Create(tbLicenses);
Fld_ACQUISITION_CONTRACT_DATE.Size := 50;
Fld_ACQUISITION_CONTRACT_DATE.FieldKind := fkCalculated;
Fld_ACQUISITION_CONTRACT_DATE.FieldName := 'ACQUISITION_CONTRACT_DATE_CALC';
Fld_ACQUISITION_CONTRACT_DATE.DataSet := tbLicenses;
Fld_ACQUISITION_INVOICE_DATE_CALC := TStringField.Create(tbLicenses);
Fld_ACQUISITION_INVOICE_DATE_CALC.Size := 50;
Fld_ACQUISITION_INVOICE_DATE_CALC.FieldKind := fkCalculated;
Fld_ACQUISITION_INVOICE_DATE_CALC.FieldName := 'ACQUISITION_INVOICE_DATE_CALC';
Fld_ACQUISITION_INVOICE_DATE_CALC.DataSet := tbLicenses;
Fld_LICENSE_EXPIRE_DATE_CALC := TStringField.Create(tbLicenses);
Fld_LICENSE_EXPIRE_DATE_CALC.Size := 50;
Fld_LICENSE_EXPIRE_DATE_CALC.FieldKind := fkCalculated;
Fld_LICENSE_EXPIRE_DATE_CALC.FieldName := 'LICENSE_EXPIRE_DATE_CALC';
Fld_LICENSE_EXPIRE_DATE_CALC.DataSet := tbLicenses;
Fld_LAST_ACTIVATION_DATE_CALC := TStringField.Create(tbLicenses);
Fld_LAST_ACTIVATION_DATE_CALC.Size := 50;
Fld_LAST_ACTIVATION_DATE_CALC.FieldKind := fkCalculated;
Fld_LAST_ACTIVATION_DATE_CALC.FieldName := 'LAST_ACTIVATION_DATE_CALC';
Fld_LAST_ACTIVATION_DATE_CALC.DataSet := tbLicenses;
Fld_LAST_VALIDATION_DATE_CALC := TStringField.Create(tbLicenses);
Fld_LAST_VALIDATION_DATE_CALC.Size := 50;
Fld_LAST_VALIDATION_DATE_CALC.FieldKind := fkCalculated;
Fld_LAST_VALIDATION_DATE_CALC.FieldName := 'LAST_VALIDATION_DATE_CALC';
Fld_LAST_VALIDATION_DATE_CALC.DataSet := tbLicenses;
Fld_SUBSCRIPTION_EVENT_DATE_CALC := TStringField.Create(tbLicenses);
Fld_SUBSCRIPTION_EVENT_DATE_CALC.Size := 50;
Fld_SUBSCRIPTION_EVENT_DATE_CALC.FieldKind := fkCalculated;
Fld_SUBSCRIPTION_EVENT_DATE_CALC.FieldName := 'SUBSCRIPTION_EVENT_DATE_CALC';
Fld_SUBSCRIPTION_EVENT_DATE_CALC.DataSet := tbLicenses;
tbLicenses.OnCalcFields := tbLicensesCalcFields;
tbLicenses.Close;
tbLicenses.SetJsonData(JRowList);
tbLicenses.Open;
FNCGridDatabaseAdapter.AutoCreateColumns := False;
FNCGridDatabaseAdapter.AutoRemoveColumns := False;
FNCGridDatabaseAdapter.Grid := FNCGrid;
FNCGridDatabaseAdapter.Active := False;
FNCGridDatabaseAdapter.DataSetTypeAuto := False;
FNCGridDatabaseAdapter.DataSetType := adsNonSequenced;
FNCGrid.Adapter := FNCGridDatabaseAdapter;
dsLicenses.DataSet := tbLicenses;
FNCGridDatabaseAdapter.DataSource := dsLicenses;
FNCGridDatabaseAdapter.Active := True;
FNCGridDatabaseAdapter.LoadAllDataAndDisconnect;
end;
All columns are already defined in the Adapter and in the Grid, and it shouldn't be erased because of the AutoCreate... setup to False.
But After FNCGridDatabaseAdapter.LoadAllDataAndDisconnect this is how the grid looks:
It replicates the first colum (that is a checbox column) to all other columns.
Here is the setup for Adapter and columns:
What am I missing?