Options

Kind of stuck...

rhpntrhpnt Member Posts: 688
Maybe it's a stupid question but my brain is in a "idle" kind of mode today and I have this simple problem (so it seems).

I have to look up a list form which has to filtered on three different fields with the OR operator. I tried using the TableRelation property but the filtering there works only in AND mode.

I'll get myself some strong coffee now...

Answers

  • Options
    ShedmanShedman Member Posts: 194
    What do you mean?
    Do you want to filter on Field1 OR Field2 OR Field3?
    Or do you want to filter Field1 on Value1 OR Value2, Field2 on Value3 OR Value4, Field3 on Value5 OR Value6?
  • Options
    mohana_cse06mohana_cse06 Member Posts: 5,504
    why dont you write it on OnLookup trigger? :-k
  • Options
    rhpntrhpnt Member Posts: 688
    Shedman wrote:
    What do you mean?
    Do you want to filter on Field1 OR Field2 OR Field3?
    Or do you want to filter Field1 on Value1 OR Value2, Field2 on Value3 OR Value4, Field3 on Value5 OR Value6?

    As the first option; three boolean fields bool1=true OR bool2=true OR bool3=true.
  • Options
    rhpntrhpnt Member Posts: 688
    why dont you write it on OnLookup trigger? :-k

    I'm doing that for one hour now but...as I wrote in my initial post.

    I'm having my coffee now, maybe this will help. :wink:
  • Options
    ShedmanShedman Member Posts: 194
    You can't do this in a straight-forward way. Your best option is probably to fill a temporary table with the three different filtersets.
  • Options
    rhpntrhpnt Member Posts: 688
    Shedman wrote:
    You can't do this in a straight-forward way. Your best option is probably to fill a temporary table with the three different filtersets.

    This is with what I came up so far...
    TABLE FIELD-ONLOOKUP TRIGGER
    
    CLEAR(rec_item);
    CLEAR(rec_tmp_item);
    CLEAR(rec_tmp_item_2);
    
    rec_item.SETRANGE(someBool1,FALSE);
    rec_item.SETRANGE(someBool2,TRUE);
    IF rec_item.FINDSET THEN BEGIN
      REPEAT
        IF (rec_item.someBool3 = TRUE) OR
           (rec_item.someBool4 = TRUE) OR
           (rec_item.someBool5 = TRUE) THEN BEGIN
          rec_tmp_item.INIT;
          rec_tmp_item.TRANSFERFIELDS(rec_item);
          rec_tmp_item.INSERT(FALSE);
        END;
      UNTIL rec_item.NEXT = 0;
    END;
    
    frm_item_list.LOOKUPMODE(TRUE);
    frm_item_list.SETRECORD(rec_tmp_item);
    IF frm_item_list.RUNMODAL = ACTION::LookupOK THEN BEGIN
      frm_item_list.GETRECORD(rec_tmp_item_2);
      someField1 := rec_tmp_item_2."No.";
      MODIFY;
    END;
    

    the form shows more than the desired/inserted records.
  • Options
    ShedmanShedman Member Posts: 194
    What are the values of SomeBool3, SomeBool4 and SomeBool5 for the records you don't want in your recordset?
  • Options
    rhpntrhpnt Member Posts: 688
    Shedman wrote:
    What are the values of SomeBool3, SomeBool4 and SomeBool5 for the records you don't want in your recordset?

    Well, like in the code the TRUE ones should be included the FALSE ones not.
  • Options
    ShedmanShedman Member Posts: 194
    The code says that if any one of the booleans is TRUE, the record is inserted in the set. So you're bound to get some records where one or two of the booleans are false.
  • Options
    rhpntrhpnt Member Posts: 688
    Shedman wrote:
    The code says that if any one of the booleans is TRUE, the record is inserted in the set. So you're bound to get some records where one or two of the booleans are false.

    Exactly. Sorry if I misunderstood the question (the coffee is not working - yet).
  • Options
    ShedmanShedman Member Posts: 194
    Could you give an example of a record that is in the set, that you don't expect to be there? What are the values of the booleans in that record?
  • Options
    rhpntrhpnt Member Posts: 688
    Shedman wrote:
    Could you give an example of a record that is in the set, that you don't expect to be there? What are the values of the booleans in that record?

    Well, the form basically shows all the items. Even those with someBool1 = TRUE. It simply ignores any filter or any SETRECORD/SETTABLEVIEW.

    Is it me or...?
  • Options
    ShedmanShedman Member Posts: 194
    There is no SETTABLEVIEW in you code, only a SETRECORD. SETRECORD only sets the record where the focus is on.
  • Options
    rhpntrhpnt Member Posts: 688
    Shedman wrote:
    There is no SETTABLEVIEW in you code, only a SETRECORD. SETRECORD only sets the record where the focus is on.

    Yes I know. I also tried with SETTABLEVIEW (any combination you could imagine). The pasted code is only a snapshot in time.
    Did I mention that "rec_tmp_item" is a temporary record type variable? Can it be that a temp variable cannot be used in this context (as a source for form lookup)? NAV online help says nothing on that matter.
  • Options
    rhpntrhpnt Member Posts: 688
    :shock: Just found out that FORM.RUN(31,rec_tmp_item) works as required, meaning the form shows only the filtered/inserted records from the temporary item record variable.
    But when I try to use the form as a variable (as shown in my previous post) it ignores the temp item variable totally. :-k

    OK, can anyone tell me how to get the record from the lookup form by using "IF FORM.RUNMODAL(31,rec_tmp_item) = ACTION::LookupOK THEN..."
  • Options
    ShedmanShedman Member Posts: 194
    That's odd ...

    It's probably returned in rec_tmp_item.
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    If this is something you need to do often, then create a new field that is the OR of the three existing fields and filter on that.
    David Singleton
  • Options
    rhpntrhpnt Member Posts: 688
    If this is something you need to do often, then create a new field that is the OR of the three existing fields and filter on that.

    I'm sorry but I don't know what you mean (I'm kind of slow today).

    Anyway, I kind of solved it using MARK-s on rec_item and SETTABLEVIEW on the original form variable. I'm sort of frustrated by such NAV behavior. I really thought it was me...
  • Options
    David_SingletonDavid_Singleton Member Posts: 5,479
    In my experience there are two cases where you need an "OR" scenario. One is a one off type scenario, or at least a scenario that does not happen often, maybe because the conditions of the ORs changes each time it is used. For these cases, the temptable scenario generally works fine. Marks are problematic, so I never use them. Especially if there are a lot of records, or if you need to do any sorting.

    The second which I find is the more common, is where the original system design was wrong, and instead of fixing the design, its easier to just blame Navision. In these cases, I look at the data model and fix it. It is often a big job to fix a badly constructed data model, but the performance and simplicity advantages are generally worth it.

    A good example is Applied entries in Navision, for many versions they used marks, until they finally realized that it was simply a bad data model. They changed the model, and now its simple.
    David Singleton
  • Options
    rhpntrhpnt Member Posts: 688
    I agree. I also hate to use marks but in this scenario I simply ran out of options. You are right about the design, it is a bad one and those boolean fields could be redesigned into an option field but thats another story to tell.

    Thanks for the time you all!
  • Options
    SogSog Member Posts: 1,023
    The problem here is that you have a list form with a real table as sourcetable, (sourcetabletemp = false). Next you set the table view on this list with a temp recordset. This view is not filtered, if I'm not mistaken the sourcetableview copies the filters of set recordset and applies it to the form.
    I believe you'll have to copy the form, set the sourcetabletemp on true, and use this form instead. (But no guarantees)
    rhpnt wrote:
    If this is something you need to do often, then create a new field that is the OR of the three existing fields and filter on that.

    I'm sorry but I don't know what you mean (I'm kind of slow today).

    Anyway, I kind of solved it using MARK-s on rec_item and SETTABLEVIEW on the original form variable. I'm sort of frustrated by such NAV behavior. I really thought it was me...

    This has crossed my mind to, what David means is that you could add another boolean field that is the result of the or.
    so you have bool1,bool2, bool3,bool4,bool5 and boolOr.
    BoolOr = bool3 OR bool4 OR bool5 (if I'm not mistaken)
    |Pressing F1 is so much faster than opening your browser|
    |To-Increase|
  • Options
    rhpntrhpnt Member Posts: 688
    Sog my man!

    I must admit I wasn't aware of that property (it isn't even listed in NAV help). Anyway, created a new form with that property set to "Yes", form is filled on Open trigger with that stupid "OR" filter and then finally the form gets called from the initial table field with GETRECORD.

    Works like a charm...

    Thanks again you all!

    p.s.: That coffee thing isn't working, waiting for the day to end.
  • Options
    mabl4367mabl4367 Member Posts: 143
    You need to set the filters on bool1 and bool2 before loping like you already have done. Then you need to loop through the records three separate times and copy the records found in each loop to the temp table. Before each loop you can only set one additional filter on one of the fields bool3,4 or 5.
Sign In or Register to comment.