Setrange: how to use a Variable as Fieldname

bre

Normally, i do as follows:
ITEM.SETRANGE( Description, 'Car');

How can i do this:
var := 'Description';
ITEM.SETRANGE( var, 'Car');

Can anybody help?

Arjan_Somers

You can do it with a recordRef
Something like:

textVar := 'Description'; 

recRef.GETTABLE(Rec);
FOR i := 0 TO recRef.FIELDCOUNT DO BEGIN
  fieldRef := recRef.FIELDINDEX(i);
  IF fieldRef.CAPTION = textVar THEN
    fieldRef.SETRANGE('Car'); 
END;

DenSter

bre wrote:

Normally, i do as follows:
ITEM.SETRANGE( Description, 'Car');

How can i do this:
var := 'Description';
ITEM.SETRANGE( var, 'Car');

Can anybody help?

You can't do it like that, and I don't understand the purpose. If you write a line of code to set the Var to a field name, you might as well write a line of code to set a filter in that field instead. It would still be one line of code, and you have one less variable to worry about.

Alex_Chow

bre wrote:

Normally, i do as follows:
ITEM.SETRANGE( Description, 'Car');

How can i do this:
var := 'Description';
ITEM.SETRANGE( var, 'Car');

Can anybody help?

The 2nd set of code does not make sense to me as well. Perhaps if you explain more on what you're trying to accomplish, we can have a clearer direction on how to help you.

Waldo

I think all he wants is setting a filter on a variable field. I think the "Description"-part is something generic. Am I wrong?

Small tip: mind the keys! Setting filters on fields requires setting sortings (SETCURRENTKEY) to the right keys to maintain performance...

bre

Yes, you are right!
The 'Description' changes, so i want to use ist as variable.
There ist a table with only about 500 Records, but also 400 Fields.
Some of this fields are named:
1_Display
2_Display
3_Display
4_Display

What i want to do is exactly:
for counter = 1 to 4
var := strsubstno( '%1_%2', counter, 'Display');
ITEM.SETRANGE( var, 'Car');

DenSter

If you include the BEGIN and END, that would be 4 lines of code, all different. You could also type "ITEM.SETRANGE(_Display, 'Car');" copy it, paste it 4 times and fill in the numbers.

I'm giving you a hard time, it would be nice to have this type of capability, but we don't.

David_Cox

I don't think you can use a variable as fieldname for filtering, 400 fields seems a bad structure for a record.

More information about what you are trying to do would help.

I would use a case statement and pass the result to a function, maybe to insert into a temp table or mark a variable recordset, to use on my report or form, but you still have to hardcode the field name

TempTable.DELETEALL;

for i = 1 to 4
case i of
1:
BEGIN
ITEM.SETRANGE( 1_Display, 'Car');
MyFunction(Item);
END
2:
BEGIN
ITEM.SETRANGE( 2_Display, 'Car');
MyFunction(Item);
END
3:
BEGIN
ITEM.SETRANGE( 3_Display, 'Car');
MyFunction(Item);
END
4:
BEGIN
ITEM.SETRANGE( 4_Display, 'Car');
MyFunction(Item);
END
END;


Then run the report on the temp table

Arjan_Somers

Like I said before, you can do that by using recordRefs.

Maybe recordRef Code doesn't look as nice as normal code, and it may be slower,

BUT IT WILL WORK!

I'll post the function you need in a sec.

kriki

DenSter wrote:

If you include the BEGIN and END, that would be 4 lines of code, all different. You could also type "ITEM.SETRANGE(_Display, 'Car');" copy it, paste it 4 times and fill in the numbers.

I'm giving you a hard time, it would be nice to have this type of capability, but we don't.

Actually we have with recordreference and fieldreference:

rerRecordReference.GETTABLE(ITEM);

FOR Lint := 1 to 4 DO BEGIN
  txtDisplayFilter += '|' + FORMAT(Lint) + ' Display';
END;
txtDisplayFilter := COPYSTR(txtDisplayFilter,2); // delete the first '|'

recField.RESET;
recField.SETCURRENTKEY(TableNo,"No.");
recField.SETRANGE(TableNo,DATABASE::"Item");
recField.SETFILTER("Field Name",'%1',txtDisplayFilter);
IF recField.FIND('-') THEN
  REPEAT
    firFieldReference := rerRecordReference.FIELD(recField."No.");
    firFieldReference.SETRANGE('Car');
  UNTIL recField.NEXT = 0;
rerRecordReference.SETTABLE(ITEM);

Remark:
It would be easier if you rename your fields to "Display 1".."Display 4". In this case the filter could be (if course if you don't have a field "Display 10"!)

recField.SETRANGE("Field Name",'Display 1','Display 4');

Arjan_Somers

if you add the following function to your table, you can do exactly what you asked for in your first post.
(Except that you need to use: ITEM.SETFILTER2( var, 'Car'); instead of ITEM.SETFILTER( var, 'Car');
And i made a function for SETFILTER, but it could be just as easily be made for SETRANGE.)

Here you go:

PROCEDURE SETFILTER2(fieldName: Text[30];filter: Text[30]);
VAR
  recRef: RecordRef;
  fldRef: FieldRef;
  i: Integer;
BEGIN
  recRef.GETTABLE(Rec);
  FOR i := 0 TO recRef.FIELDCOUNT DO BEGIN
    fldRef := recRef.FIELDINDEX(i);
    IF fldRef.CAPTION = fieldName THEN
      fldRef.SETFILTER(filter);
  END;
END;

kriki

Arjan Somers wrote:

if you add the following function to your table, you can do exactly what you asked for in your first post.
(Except that you need to use: ITEM.SETFILTER2( var, 'Car'); instead of ITEM.SETFILTER( var, 'Car');
And i made a function for SETFILTER, but it could be just as easily be made for SETRANGE.)

Here you go:

PROCEDURE SETFILTER2(fieldName: Text[30];filter: Text[30]);
VAR
  recRef: RecordRef;
  fldRef: FieldRef;
  i: Integer;
BEGIN
  recRef.GETTABLE(Rec);
  FOR i := 0 TO recRef.FIELDCOUNT DO BEGIN
    fldRef := recRef.FIELDINDEX(i);
    IF fldRef.CAPTION = fieldName THEN
      fldRef.SETFILTER(filter);
  END;
END;

If possible, use the the fieldnumber as parameter, so you don't have to search the field for the fieldreference. You can directly go to it with

fldRef := recRef.FIELD(intFieldNo);

Arjan_Somers

Wich is extra important if youve got a couple hundred fields to search through.

skynet

Hello,
I have the same problem and I don't want to create a new topic.
Is it really the only solution ?

kriki

skynet wrote:

Hello,
I have the same problem and I don't want to create a new topic.
Is it really the only solution ?

Yes

MTC

DenSter wrote:

bre wrote:

Normally, i do as follows:
ITEM.SETRANGE( Description, 'Car');

How can i do this:
var := 'Description';
ITEM.SETRANGE( var, 'Car');

Can anybody help?

You can't do it like that, and I don't understand the purpose. If you write a line of code to set the Var to a field name, you might as well write a line of code to set a filter in that field instead. It would still be one line of code, and you have one less variable to worry about.

It's called a dynamic program, where the code doesn't know at write time what fields or even tables it is dealing with. There are many scenarios where you have to do this. As others have said, it can be done with recordRef and fieldref.

jm

bre wrote:

Yes, you are right!
The 'Description' changes, so i want to use ist as variable.
There ist a table with only about 500 Records, but also 400 Fields.
Some of this fields are named:
1_Display
2_Display
3_Display
4_Display

A table with 400 Fields seems to me wrong database design.
Especially if the fields are related to each other.

Arturo

Any chance to use FIELDCOUNT and FIELDINDEX record functions in NAV 2013 R2?

Edit: Sorry i found it, just need to create a RecordRef type variable.