Anyone with a TableFilter2View function they will share?

pdj
pdj Member Posts: 643
edited 2010-07-22 in NAV/Navision Classic Client
I would like to be able to use the datatype TableFilter, but since Navision doesn't provide a function to actually use the value I guess we have to do it our self. I'm quite sure it is possble using RecRef, FldRef and the Field table. I would just like to know if anyone wish to share their code, or at least confirm that it is possible or say why it isn't :-)
Regards
Peter

Comments

  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Hi,

    To transform a filter to a view on a normal variable you can use GetView.

    Like

    Cust.GetView

    you can use that returnvalue on a RecRef

    It is also possible to create the view syntax yourself. Just have a good look at the coding in tablerelations, flowfields etc. and copy this.
    Marije Brummel | Freelance Business Central Specialist
  • pdj
    pdj Member Posts: 643
    I think you misunderstand me. Navision has a datatype called "TableFilter". There is a very nice interface to input into the field. However; there is no way of using the field value to set a filter on a record(ref). (The on-line help even admits it)

    It is not possible to do a RecRef.SETVVIEW(MyTable.MyTableFilter). Instead I wish to make a function that can do it like this RecRef.SETVVIEW(TableFilter2View(MyTable.MyTableFilter))

    A TableFilter seems to be shown like this:
    <TableName>:<FieldCaption>=<FieldFilter>,<FieldCaption>=<FieldFilter>,..
    I just need to "convert" this into the VIEW format, which is like this:
    SORTING(<Key>) WHERE(<FieldCaption/No>=FILTER(<FieldFilter>),<FieldCaption/No>=FILTER(<FieldFilter>),...)

    The only problem is if the fieldcaption includes "=" or "," (Or if the fieldcaption isn't unique in the table)
    Regards
    Peter
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    You can also use fieldnumners instead of fieldnames. This is done by the GetView statement I mentioned.

    You can also leave the SORTING if you want.

    Just use
    WHERE(FIELD1=FILTER(1)))

    We use this to put the filters on a RecRef.

    I have never used the TableFilter thing.

    The View is a simple string and can be saved in the database this way.

    Navision uses this in the Segment Functionality.

    I hope this helps you.
    Marije Brummel | Freelance Business Central Specialist
  • pdj
    pdj Member Posts: 643
    Yes I know. But I would like to have the end-user entering the filters using the generic window TableFilter (just like pressing ctrl-f7). I agree that a solution migt be to open the default list form, and let the user set filters. And when the user closes the window it should grab the VIEW and store that instead. That could very likely be the solution, I would just prefer using the TableFilter datatype directly, because not all tables has a useable default listform and I like to challenge the on-line help 8)
    Regards
    Peter
  • Marije_Brummel
    Marije_Brummel Member, Moderators Design Patterns Posts: 4,262
    Ok,

    What we have done is created a similair form to the Ctrl+F7 thing and convert the user data to the View.

    If your solution works, then this is even better. It saves a table and and a form. :D
    Marije Brummel | Freelance Business Central Specialist
  • pdj
    pdj Member Posts: 643
    And makes it possible to lookup while setting filters using the fields TableRelations. :-) (Well, I guess you could make that as well using your method)
    Regards
    Peter
  • pdj
    pdj Member Posts: 643
    Can anyone see any problems with this function?
    TableFilter2View(p_TableFilter : Text[1024]) r_View : Text[1024]
// Funtion made by pdj - Aalborg
// Feel free to use, but please keep all comment lines and report any errors at http://mibuso.com/forum/viewtopic.php?t=7587
// TableFilter format:
//   <TableName>:<FieldCaption>=<FieldFilter>,<FieldCaption>=<FieldFilter>,..
// View format:
//   [SORTING(<Key>)] WHERE(<FieldCaption>=FILTER(<FieldFilter>),<FieldCaption>=FILTER(<FieldFilter>),...)
IF p_TableFilter = '' THEN
  EXIT('');

r_View := 'WHERE(';

FOR l_CharNo := STRPOS(p_TableFilter,':') + 1 TO STRLEN(p_TableFilter) DO BEGIN
  CASE p_TableFilter[l_CharNo] OF
    '=': r_View := r_View + '=FILTER(';
    ',': r_View := r_View + '),';
    '"': 
      BEGIN
        l_CharNo := l_CharNo + 1;
        REPEAT
          r_View := r_View + FORMAT(p_TableFilter[l_CharNo]);
          l_CharNo := l_CharNo + 1;
        UNTIL p_TableFilter[l_CharNo] = '"';
        l_CharNo := l_CharNo + 1;
      END;
  ELSE
    r_View := r_View + FORMAT(p_TableFilter[l_CharNo]);
  END;
END;

r_View := r_View + '))';
    Regards
    Peter
  • Duikmeester
    Duikmeester Member Posts: 309 
    OBJECT Codeunit 11002222 TableFilterView
{
  OBJECT-PROPERTIES
  {
    Date=22-07-10;
    Time=11:27:25;
    Modified=Yes;
    Version List=Sebastiaan Lubbers;
  }
  PROPERTIES
  {
    OnRun=VAR
            ltFilter@11002000 : Text[1024];
            lrJob@11002001 : Record 167;
            ltView@11002002 : Text[1024];
          BEGIN
            //ltFilter := FORMAT(TABLE.FILTERFIELD);
            ltFilter := CONSTFILTER;
            ltView := TableFilterView(ltFilter);

            MESSAGE('Before: %1',lrJob.COUNT);
            lrJob.FILTERGROUP(5);
            lrJob.SETVIEW(ltView);
            MESSAGE('After: %1',lrJob.COUNT);
          END;

  }
  CODE
  {
    VAR
      CONSTFILTER@11002000 : TextConst 'ENU="Job: No.=P*,Status=Order,Description=@X";NLD="Job: Nr.=P*,Status=Begroting,Omschrijving=@X"';

    PROCEDURE TableFilterView@11002000(ptFilter@11002000 : Text[1024]) : Text[1024];
    VAR
      liEquals@11002001 : Integer;
      liComma@11002002 : Integer;
      liQuote1@11002003 : Integer;
      liQuote2@11002004 : Integer;
      ltField@11002005 : Text[30];
      ltValue@11002006 : Text[30];
      ltView@11002007 : Text[1024];
    BEGIN
      //<usage>
      //  ltFilter := FORMAT(lrTable.TableFilterField);
      //  ltView := TableFilterView(ltFilter);
      //  Rec.FILTERGROUP(5);                           //OPTIONALLY PUT FILTER IN SECURITY CONTEXT
      //  Rec.SETVIEW(ltView);
      //</usage>

      //TODO: FIND COMMA AFTER FILTER CONTAINING DOUBLE QUOTES
      IF STRPOS(ptFilter,'""') > 0 THEN ERROR('FILTER WITH DOUBLE QUOTE NOT SUPPORTED');
      ptFilter := COPYSTR(ptFilter,STRPOS(ptFilter,': ') + 2);

      REPEAT
        liEquals := STRPOS(ptFilter,'=');
        liComma := STRPOS(COPYSTR(ptFilter,liEquals + 1),',') + liEquals;
        liQuote1 := STRPOS(ptFilter,'"');
        liQuote2 := STRPOS(COPYSTR(ptFilter,liQuote1 + 1),'"') + liQuote1;
        IF liEquals IN [liQuote1..liQuote2] THEN liEquals := liQuote2 + 1;
        IF liComma IN [liQuote1..liQuote2] THEN liComma := liQuote2 + 1;
        ltField := COPYSTR(ptFilter,1,liEquals - 1);

        IF (liEquals < liComma) THEN BEGIN
          ltValue := COPYSTR(ptFilter,liEquals + 1,liComma - liEquals - 1);
          ptFilter := DELSTR(ptFilter,1,liComma);
        END ELSE BEGIN
          ltValue := COPYSTR(ptFilter,liEquals + 1);
          ptFilter := '';
        END;

        IF ltView <> '' THEN ltView += ',';
        ltView += STRSUBSTNO('%1=FILTER(%2)',ltField,ltValue);
      UNTIL (ptFilter = '');

      ltView := STRSUBSTNO('WHERE(%1)',ltView);
      EXIT(ltView);
    END;

    BEGIN
    {
      Field with " not possible
      Value with " not allowed current design
      Field which contains = will be between "
      Value which contains , will be between "
    }
    END.
  }
}

    EDIT: LOL nearly 5 years later I was searching for this. Still no good solution so made my own allowing nearly any character in the field or filter.

Categories