Kind of stuck...

rhpnt

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...

Shedman

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?

mohana_cse06

why dont you write it on OnLookup trigger? :-k

rhpnt

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.

rhpnt

mohana_cse06 wrote:

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.

Shedman

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.

rhpnt

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.

Shedman

What are the values of SomeBool3, SomeBool4 and SomeBool5 for the records you don't want in your recordset?

rhpnt

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.

Shedman

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.

rhpnt

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).

Shedman

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?

rhpnt

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...?

Shedman

There is no SETTABLEVIEW in you code, only a SETRECORD. SETRECORD only sets the record where the focus is on.

rhpnt

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.

rhpnt

: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..."

Shedman

That's odd ...

It's probably returned in rec_tmp_item.

David_Singleton

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.

rhpnt

David Singleton 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...

David_Singleton

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.

rhpnt

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!

Sog

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:

David Singleton 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)

rhpnt

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.

mabl4367

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.