Multiple conditions to a field filter

slmaluwa

hi
I am trying to find a way to add multiple filters programmatically. Example. i filter the Location table to select more than one locations and i want to apply all these locations to ITEM table location filter table.
I tried the following and it didn't work.

Location.RESET;
Location.SETRANGE("A Field To Select",true);
if Location.find('-') THEN begin
   item.reset;
   REPEAT
     item.SETRANGE("lOCATION FILTER",Location.CODE);
   UNTIL Location.NEXT=0;
end;
MESSAGE(ITEM.GETFILTERS());

It displays only the last Location code.

Is there a way to do it?

kapamarou

I would do the following:

Declare a txtLocFilter of Text 250.

Then do this:

IF Location.FIND('-') THEN REPEAT
IF txtLocFilter = '' THEN
txtLocFilter := Location.Code ELSE
txtLocFilter := txtLocFilter + '|' + Location.Code;
UNTIL Location.NEXT = 0;

item.SETFILTER("Location Filter",txtLocFilter);


But test it because I think that you'll get a runtime error if your text becomes too large.

You could improve this code by checking for continuous location in order to create a more complex string that will contain something like this:


Loc1..Loc6|Loc9|Loc10..Loc19

Hope it helps...

kriki

What do you want to do with the "Location Filter" on Item.
If you explain that, maybe we can find a better way to solve your problem.

Belias

kriki wrote:

What do you want to do with the "Location Filter" on Item.
If you explain that, maybe we can find a better way to solve your problem.
What do you want to do with the "Location Filter" on Item.
If you explain that, maybe we can find a better way to solve your problem.

exactly, because the code you wrote (slmaluwa) is totally crazy... :-k
every loop you do in repeat-until structure, overwrites the previous filter...

slmaluwa

Hello
Sorry, I missed to see the last two replies.

What I wanted to do is simple requirement of getting the (sum) total inventory available in all those selected location.
I thought setting up this filter and doing a CALCFIELDS(Inventory) and display the qty on the form.

I thought it will be faster than looping through each location to set individual filter and do the calcfields

I am all ears for learning

kriki

In case you have lots of locations, and only a few locations you need, it is better to loop the locations.
The reason is that OR-ing can make SQL decide to read all records in the SIFT.

If you need to run that code a lot, it is also a good idea to have the locations you need in a temptable on the form to avoid requesting all the time those locations.

slmaluwa

kriki wrote:

In case you have lots of locations, and only a few locations you need, it is better to loop the locations.
The reason is that OR-ing can make SQL decide to read all records in the SIFT.

Sorry, bit confused. How exactly the LOOP to be constructed?

kriki wrote:

If you need to run that code a lot, it is also a good idea to have the locations you need in a temptable on the form to avoid requesting all the time those locations.

Is it the Location table to be made temp in that form? Out location table has around 18 locations.

kriki

OnInit: This is to fill up the temptable when the form is initialized

recLocation.RESET;
recLocation.SETCURRENTKEY(...);
recLocation.SETRANGE("A Field To Select",TRUE);
IF recLocation.FINDSET THEN
  REPEAT
    tmpLocation := recLocation;
    tmpLocation.INSERT(FALSE);
  UNTIL recLocation.NEXT = 0;

This function, you can call where you want. You can also create a field on the item card and put this function in the SourceExpression.
(BTW : you can also call this function from another object. The OnInit of this form makes sure the temptable is loaded in the form-variable)

Function CalculateInventory(IcodItemNo : CODE20) : OdecInventory : DECIMAL
BEGIN
  OdecInventory := 0;
  tmpLocation.RESET;
  IF tmpLocation.FINDSET THEN
    REPEAT
      CLEAR(LrecItem);  // "LrecItem" is a local record-variable on table Item
      LrecItem."No." := IcodItemNo;
      LrecItem.SETRANGE("Location Filter",tmpLocation.Code);
      Lrecitem.CALCFIELDS(Inventory);
      OdecInventory += Lrecitem.Inventory;
    UNTIL tmpLocation.NEXT = 0;
END;