Options

GETFILTERS field name

BeliasBelias Member Posts: 2,998
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
-Mirko-
"Never memorize what you can easily find in a book".....Or Mibuso
My Blog

Answers

  • Options
    einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    What about GETVIEW? It has the UseNames parameter...

    Edit: Oh, sorry, I should have read the linked topic before. :-#
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • Options
    BeliasBelias Member Posts: 2,998
    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
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    BeliasBelias Member Posts: 2,998
    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;
    
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    kapamaroukapamarou Member Posts: 1,152
    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.
  • Options
    BeliasBelias Member Posts: 2,998
    :-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
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    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;
    
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • Options
    BeliasBelias Member Posts: 2,998
    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:
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    BeliasBelias Member Posts: 2,998
    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:
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    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.
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • Options
    BeliasBelias Member Posts: 2,998
    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.
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    Also good solution! Well done, Mirko! :mrgreen:

    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.
    "Money is likewise the greatest chance and the greatest scourge of mankind."
  • Options
    BeliasBelias Member Posts: 2,998
    edited 2010-11-17
    Also good solution! Well done, Mirko! :mrgreen:

    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
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Options
    einsTeIn.NETeinsTeIn.NET Member Posts: 1,050
    Belias wrote:
    and you know....i'm tooooooo lazy for this
    :lol::lol::lol: perhaps I would be too... :wink:
    "Money is likewise the greatest chance and the greatest scourge of mankind."
Sign In or Register to comment.