GETFILTERS field name

Belias · 2010-11-16

hi everyone, this topic is directly related to this other one, but the topic is totally different, so i'll post a new topic
http://www.mibuso.com/forum/viewtopic.php?f=32&t=44611
i noticed that using GETFILTERS, nav retrieves the field caption instead of the field name: this generate problems while trying to run hyperlinks. do you know if there's a method to obtain the filters with the field name instead of their caption?
The workaround is to use something like

LANGUAGE := 1033;

before the getfilters, but it does not work if the caption is different from the field name, or if the enu caption does not exist.
Thanks in advance

einsTeIn.NET · 2010-11-16

What about GETVIEW? It has the UseNames parameter...

Edit: Oh, sorry, I should have read the linked topic before. :-#

Belias · 2010-11-16

UseNames is a tricky name for this parameter:
take a look at the help:

If this parameter is true (default value) or if it is empty, then the returned string contains references to field captions

i've been fooled by this one, too... :-$
if i set the parameter to false, i have to trim the field no. from the string, and then go through the field table

Belias · 2010-11-16

I solved it with this, but as i'm not satisfied, i'll leave the topic open to better solutions...

//populate a temporary table with all the fields of the needed table only: the "field" table reading is deadly slow with these filters
TBFieldTemp.SETRANGE(TableName,mytablename);
IF TBFieldTemp.ISEMPTY THEN BEGIN
  TBField.SETRANGE(TableName,mytablename);
  TBField.FINDSET;
  REPEAT
    TBFieldTemp.TableNo := TBField.TableNo;
    TBFieldTemp.TableName := TBField.TableName;
    TBFieldTemp."No." := TBField."No.";
    TBFieldTemp.FieldName := TBField.FieldName;
    TBFieldTemp."Field Caption" := TBField."Field Caption";
    TBFieldTemp.INSERT;
  UNTIL TBField.NEXT = 0;
END;

//retrieve the field name
TBFieldTemp.SETRANGE(TableName,mytablename);
TBFieldTemp.SETRANGE("Field Caption",myfieldcaption);
TBFieldTemp.FINDFIRST;

kapamarou · 2010-11-16

I've used this solution for an official report that needs to allow only specific filters. It used to build a string with the filters applied and compare it to the GETFILTERS result. The problem was that depending on the user's actions, GETFILTERS would return the fields in a different order. So I used the same solution as you did, but I didn't have any issues.

Why do you filter on TableName? The primary key is TableNo,No. Filtering like

SETRANGE(TableNo,DATABASE::MyTable) will be faster.

Belias · 2010-11-16

:-k
now that you tell me, i can use tableno, instead of tablename and then retrieve the table name (that i need in the called function you can see in the linked topic) from the field table...

my issue is that i need the field name instead of the field caption in order to make the report hyperlink work.
for example:

dynamicsnav:////runreport?report=18038613&filter=%22Customer%22.%22No.%22:33&filter=%22Customer%22.%22Name%22:22
works but

dynamicsnav:////runreport?report=18038613&filter=%22Customer%22.%22Nr.%22:33&filter=%22Customer%22.%22Nome%22:22
doesn't work

einsTeIn.NET · 2010-11-16

Belias wrote:

UseNames is a tricky name for this parameter:
take a look at the help:

If this parameter is true (default value) or if it is empty, then the returned string contains references to field captions

Yeah, I know. But I was thinking of set the parameter to FALSE and work with Field No.

I wouldn't go for a temporary table.

kapamarou wrote:

Why do you filter on TableName? The primary key is TableNo,No. Filtering like

SETRANGE(TableNo,DATABASE::MyTable) will be faster.

But if you want to go for it, that should help in case of speed.

But you could also work with RecordRef and FieldRef. The following snippet of code should help you to receive the name (not tested and not adhered to common development rules):

RecRef.GETTABLE(Rec);
ViewTxt := RecRef.GETVIEW(FALSE);
ViewTxt := COPYSTR(ViewTxt,STRPOS(ViewTxt,'WHERE'));
FieldNoTxt :=
  COPYSTR(ViewTxt,STRPOS(ViewTxt,'Field') + 5,STRPOS(ViewTxt,'=') - (STRPOS(ViewTxt,'Field') + 5));
EVALUATE(FieldNoInt,FieldNoTxt);
FRef := RecRef.FIELD(FieldNoInt);
FieldNameTxt := FRef.NAME;

Belias · 2010-11-16

einsTeIn.NET wrote:

RecRef.GETTABLE(Rec);
ViewTxt := RecRef.GETVIEW(FALSE);
ViewTxt := COPYSTR(ViewTxt,STRPOS(ViewTxt,'WHERE'));
FieldNoTxt :=
  COPYSTR(ViewTxt,STRPOS(ViewTxt,'Field') + 5,STRPOS(ViewTxt,'=') - (STRPOS(ViewTxt,'Field') + 5));
EVALUATE(FieldNoInt,FieldNoTxt);
FRef := RecRef.FIELD(FieldNoInt);
FieldNameTxt := FRef.NAME;

I like it, thanks! :thumbsup:

Belias · 2010-11-16

Hi einstein, i adapted your code to my function like this

FNTAppendFilter(pINTTableNo : Integer;pTXTFilterValue : Text[1024]) : Text[1024]
CLEAR(RecRef);
CLEAR(FieldRef);
RecRef.OPEN(pINTTableNo);

pTXTFilterValue := COPYSTR(pTXTFilterValue,STRPOS(pTXTFilterValue,'WHERE'));

REPEAT
  lTXTFieldNo := COPYSTR(pTXTFilterValue,STRPOS(pTXTFilterValue,'Field') + 5,
                         STRPOS(pTXTFilterValue,'=') - (STRPOS(pTXTFilterValue,'Field') + 5));
  EVALUATE(lINTFieldNo,lTXTFieldNo);
  FieldRef := RecRef.FIELD(lINTFieldNo);

  pTXTFilterValue := COPYSTR(pTXTFilterValue,STRPOS(pTXTFilterValue,'=') + 1);
  lTXTFieldValue := COPYSTR(pTXTFilterValue,STRPOS(pTXTFilterValue,'(') + 1,
                            STRPOS(pTXTFilterValue,')') - STRPOS(pTXTFilterValue,'(') - 1);
  lTXTFilters += '&filter=%22' + RecRef.NAME + '%22.' + '%22' + FieldRef.NAME + '%22:' + lTXTFieldValue;
UNTIL STRPOS(pTXTFilterValue,'Field') = 0;
EXIT(lTXTFilters);

and it works just fine, in any language...the only problem appears if a filterstring contains a parenthesis (the closing parenthesis make a big problem) or an '=' sign, but what do you expect? it's a workaround for an unexisting feature... :roll:

einsTeIn.NET · 2010-11-17

Belias wrote:

RecRef.OPEN(pINTTableNo);
...
lTXTFieldValue := COPYSTR(pTXTFilterValue,STRPOS(pTXTFilterValue,'(') + 1,
  STRPOS(pTXTFilterValue,')') - STRPOS(pTXTFilterValue,'(') - 1);

To avoid the problem with brackets I wouldn't go this way.

If you use

RecRef.GETTABLE(YourRec);

like I did, then you could get the filter by

FRef.GETFILTER

There is no need to extract it from the string.

And if you do it in this way you could also check if there's an equals sign within the filter.

Belias · 2010-11-17

good one! =D> =D>
but i want to point out that using GETTABLE in my function would force me to pass the rec variable as parameter, making the function useless, because it can't be called from anywhere...
what i can do is to use RecRef.SETVIEW instead of RecRef.GETTABLE, and the problem is solved.
thanks for the unvaluable contribution and here's the final version of the function (this should be 100% bug proof) :thumbsup:

FNTAppendFilter(pINTTableNo : Integer;pTXTFilterValue : Text[1024]) : Text[1024]
where pINTTableNo = no. of the table
where pTXTFilterValue = mytable.getview(false) from the caller object

CLEAR(RecRef);
CLEAR(FieldRef);
RecRef.OPEN(pINTTableNo);
RecRef.SETVIEW(pTXTFilterValue);  //THIS ONE HAVE BEEN ADDED

pTXTFilterValue := COPYSTR(pTXTFilterValue,STRPOS(pTXTFilterValue,'WHERE'));

REPEAT
  lTXTFieldNo := COPYSTR(pTXTFilterValue,STRPOS(pTXTFilterValue,'Field') + 5,
                         STRPOS(pTXTFilterValue,'=') - (STRPOS(pTXTFilterValue,'Field') + 5));
  EVALUATE(lINTFieldNo,lTXTFieldNo);
  FieldRef := RecRef.FIELD(lINTFieldNo);

  pTXTFilterValue := COPYSTR(pTXTFilterValue,STRPOS(pTXTFilterValue,'=') + 1);
  lTXTFieldValue := FieldRef.GETFILTER;   //THIS ONE HAVE BEEN MODIFIED
  lTXTFilters += '&filter=%22' + RecRef.NAME + '%22.' + '%22' + FieldRef.NAME + '%22:' + lTXTFieldValue;
UNTIL STRPOS(pTXTFilterValue,'Field') = 0;
EXIT(lTXTFilters);

P.S.: excuse me for the notation with txt, int and so on, but it's a company policy...

Thanks kapamarou and einstein...i'll post (probably in the blog) a sample report that uses also this logic to create links to filtered lists...it's very good to make the customer able to navigate from an "Item list report" directly to the inventory of the item, for example.

einsTeIn.NET · 2010-11-17

Also good solution! Well done, Mirko!

But you don't need to pass the record as parameter. You can also pass the necessary Record Ref instead of pINTTableNo as Table No.

Belias · 2010-11-17

einsTeIn.NET wrote:

Also good solution! Well done, Mirko!

But you don't need to pass the record as parameter. You can also pass the necessary Record Ref instead of pINTTableNo as Table No.

Yes, but this means that i have to declare a recref variable in the caller object, instead of using a simple
Database::"my table name"
and you know....i'm tooooooo lazy for this: here's a sample function call:

TBItemLedgerEntry.SETRANGE("HC Requisition","Document No.");
TBItemLedgerEntry.SETRANGE("HC Requisition Line","Line No.");
TBItemLedgerEntry.SETRANGE("Location Code","Transfer from Location");
TBItemLedgerEntry.SETRANGE(Correction,FALSE);
mystring := CUJumpToUrl.FNTAppendFilter(DATABASE::"Item Ledger Entry",TBItemLedgerEntry.GETVIEW(FALSE))

that is more easy than
-declaring the recref
-apply the filters
-add a recref.gettable(tbitemledgerentry)
-call the function

einsTeIn.NET · 2010-11-17

Belias wrote:

and you know....i'm tooooooo lazy for this

perhaps I would be too...

GETFILTERS field name

Answers

Categories