SetRange - Doesn't

[bazzer747 25]

Hi,

I have a table with a column of numbers, which go from -6 to 54. This table is split into 3 divisions -6 to 16, 14-20, and 21 to 54..

 

I display the records in a dbGrid with a radiogroup (rgDivs) showing the 3 divisions, plus 'All'. When a user selects a division I use a SetRange to limit what is shown. The code in the radiogroup OnClick event looks like this:

 

cChoice:= rgDivs.Items[ rgDivs.ItemIndex ]

with dm.vEnrolments do
  begin
    if cChoice = 'All' then CancelRange
    else
    if cChoice = 'Div 1' then SetRange( [ -6 ],[ 16 ] );
    if cChoice = 'Div 2' then SetRange( [ 14 ],[ 20 ] );
    if cChoice = 'Div 3' then SetRange( [ 21 ],[ 54 ] );
  end;

 

dm.vEnrolments is a View of a table with all the records and cChoice is the value selected in the radiogroup.

 

Clicking 'All', 'Div 2' and 'Div 3' does exactly what it is supposed to do, but clicking 'Div 1', which should show all records from -6 to 16 does not show the negative numbers.

 

Any thoughts on why this should be would be appreciated.

[Attila Kovacs 629]

You could try to use the dataset's ".Filter" and ".Filtered" properties instead of this exotic SetRange.

[bazzer747 25]

Exotic? In what way? Is it known NOT to work in these instances?

[Attila Kovacs 629]

I'm not sure if it's known at any level.

Edited November 12, 2020 by Attila Kovacs

[Uwe Raabe 2057]

Only a few TDataSet descendant introduce a SetRange method. Which component do you use?

[bazzer747 25]

FireDac query

[Javier Tarí 23]

If it is not supported, it should throw an exception. Is the query sorted with an order by?

 

[Uwe Raabe 2057]

7 hours ago, Javier Tarí said:

If it is not supported, it should throw an exception. Is the query sorted with an order by?

 

The order by is not necessary, but an local index on the field in question is.

 

If the problem lies in FireDAC it should be possible to create a small test program with a TFDMemTable.

[bazzer747 25]

Hi,

 

What I didn't show in my code snippet was this:

 

dm.vEnrolments.IndexFieldNames:= 'Exact';

 

'Exact' being the field the SetRange is acting upon. I did read Cary Jensen's FireDac book and took this idea of using SetRange setting the IndexFieldName to the field in question. Which works fine for all values from 0 upwards - it's just why does it not work on negative numbers that puzzles me. 

 

[Uwe Raabe 2057]

Does it work when you replace the SetRange call with a sequence like this?

SetRangeStart;
FieldByName('Exact').AsInteger := -6;
SetRangeEnd;
FieldByName(Exact').AsInteger := 16;
ApplyRange;

 

[bazzer747 25]

Hi Uwe,

Darn, was hopeful there. 🙂 But just the same. However, it got me thinking deeper! An issue I have sometimes when a seemingly obvious problem might not actually be so 'clear cut'.

 

The Field 'Exact' is used in a View made up from several tables, one of which owns the 'Exact' field. When I look at the design I can see the field is set as VarChar(4)! The field is 'supposed' to be  (and should be) set as a float field as it shows golf handicaps that range from -6 to 54. I'm 'guessing' that the field was set as a varchar as some people don't have a handicap so the field is either NULL or has been set to '-'. I saw that just perusing all the values (about 100,000 of them) and then it dawned on me!

 

I can see that this is why the SetRange doesn't work, as I've assumed (that dreadful word) the field is a number and it's actually a character.

 

My solution is to (maybe) create a new float field which the SetRange will actually work on correctly. 

 

Sorry for this error on my part, but many thanks for steering me which caused me to find this out....

 

 

 

[bazzer747 25]

What I've done now is created an internal calculated float field 'Exact2', converting the hyphen and Null values to 99, and set the IndexFieldNames to 'Exact2' and the SetRange now works correctly.

[Guest]

On 11/11/2020 at 6:29 PM, bazzer747 said:

Hi,

I have a table with a column of numbers, which go from -6 to 54. This table is split into 3 divisions -6 to 16, 14-20, and 21 to 54..

 

I display the records in a dbGrid with a radiogroup (rgDivs) showing the 3 divisions, plus 'All'. When a user selects a division I use a SetRange to limit what is shown. The code in the radiogroup OnClick event looks like this:

 

cChoice:= rgDivs.Items[ rgDivs.ItemIndex ]

with dm.vEnrolments do
  begin
    if cChoice = 'All' then CancelRange
    else
    if cChoice = 'Div 1' then SetRange( [ -6 ],[ 16 ] );
    if cChoice = 'Div 2' then SetRange( [ 14 ],[ 20 ] );
    if cChoice = 'Div 3' then SetRange( [ 21 ],[ 54 ] );
  end;

 

dm.vEnrolments is a View of a table with all the records and cChoice is the value selected in the radiogroup.

 

Clicking 'All', 'Div 2' and 'Div 3' does exactly what it is supposed to do, but clicking 'Div 1', which should show all records from -6 to 16 does not show the negative numbers.

 

Any thoughts on why this should be would be appreciated.

    

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.btn_All_Range_RecordClick(Sender: TObject);
begin
  FDMemTable1.CancelRange;
end;

procedure TForm1.btn_SetRange_MEClick(Sender: TObject);
var
  lArrays: TArray<string>;
begin
  //
  lArrays := RadioGroup1.Items[RadioGroup1.ItemIndex].Split([',']);
  //
  if (Length(lArrays) = 2) then
    try
      {
        If the current dataset record is within a new range, then it becomes current after a call to SetRange.
        Otherwise, the current position is set to the first record in the range.

        If either AStartValues or AEndValues has fewer elements than the number of fields in the current index, then the remaining entries
        are set to NULL.
      }
      Caption := lArrays[0] + '  #  ' + lArrays[1];
      //
      FDMemTable1.SetRange([StrToInt(lArrays[0])], [StrToInt(lArrays[1])]);
      FDMemTable1.First;
    except
      // ... any exception here
    end;
end;

As the your table have all values in "string" type, would be better that the values has "nn" format, because the "SetRange()" works better with "Integer" values and the value, likes, "3" and "03" would be positioned in diferent place on index. Better, if all values was "Integer" type, then, none conversions it's necessary!

Try using a FDMemTable with same values in my table, but, dont use "0" before the values! you see?

 

hug

Edited November 14, 2020 by Guest