Help with filter String exceeding maximum length

pawanp · 2010-08-30

Filterstring can only be 250 characters. So if you have 15 customers with numbers that are 20 characters, you are going to run into an error. I'd consider an alternative solution, perhaps one with a temporary record variable.
How to implement and alternative solution using a record variable..
As my key is exceeding maximum character length..
Please someone help on this context.. Its urgent. ](*,)

rhpnt · 2010-08-30

Maybe you should rethink the design or concept of your solution. Using a string as a filter will always run into its 250 character limitation.

mabl4367 · 2010-08-30

There are at least two ways to get around this problem.

1. Create a record variable for the table you wish to filter, set the variables "Temporary" probperty to "Yes".

Fill the temporary record table with records that pass youre looong filter. You can do this in different ways.

You can apply parts of your filter that are separated by "|" (or-operator) to the original table and add the records that pass these partial filters to youre temp-table.

You can also add all records and then remove records that don't pass parts of youre filter that are separated by the "&" (and-operator).

If you have a really complex filtercriteria you could loop through all records and test each one using code. If the record passes all the tests in youre code you add it to the temp table. This is usefull if you want to make a filter that compares fieldvalues to each other. For example you want a filter that givs you all customers where the "Telex No." is longer then the "Phone No.". That type of filter is not possible to create in any other way.

2. In older versions of navision there is a feature called MARK. Instead of putting the records you want into a temp table you can mark them using MARK(TRUE). Then you set the special filter MARKEDONLY(TRUE). To select wich records to mark/unmark you could use the same methodes that I have outlined above.

rhpnt · 2010-08-30

Fill the temporary record table with records that pass youre looong filter.

I think you missed the point that the filter string is too long making it impossible to filter and add records to the temp table. If you end up using a string as a filter you have certainly programmed yourself into a dead end street. A quick & dirty way out of this is to add a field to (in this case) the customer table which holds the data for filtering (boolean, option).

matttrax · 2010-08-30

rhpnt wrote:

A quick & dirty way out of this is to add a field to (in this case) the customer table which holds the data for filtering (boolean, option).

I think that's an awful solution. Adding a modification to the database for something the system can handle out of the box doesn't make sense. mabl4367 provided excellent examples of how this can be done.

mabl4367 · 2010-08-30

No I did not miss the fact that the filter was too long and I did explain how to apply parts of the long filter that ar either separated by the "|" (or operator) or the "&" (and operator) untill the result is the same as if the whole filter was applied.

rhpnt · 2010-08-30

I think that's an awful solution. Adding a modification to the database for something the system can handle out of the box doesn't make sense. mabl4367 provided excellent examples of how this can be done.

We don't know where the filtering takes place nor if it's on the fly or permanent.
Sure it can be done mabl4367's way, but it demands a lot of code and repeated looping through (what can be) a lot of records. If the filtering will be used repeatedly and references the same records (the original post mentions 15 customers) then it would be wise to group such customers using existing options (posting groups, price groups,...) for filtering or by introducing a new grouping/filtering option (field).

P.S.:
I would really like to know what you ment by "handling out of the box".

matttrax · 2010-08-30

rhpnt wrote:

P.S.:
I would really like to know what you ment by "handling out of the box".

I meant just as mabl4367 said. You can use a temporary table or mark the records. Two ways that both work without changing a table structure.

rhpnt wrote:

We don't know where the filtering takes place nor if it's on the fly or permanent.
Sure it can be done mabl4367's way, but it demands a lot of code and repeated looping through (what can be) a lot of records. If the filtering will be used repeatedly and references the same records (the original post mentions 15 customers) then it would be wise to group such customers using existing options (posting groups, price groups,...) for filtering or by introducing a new grouping/filtering option (field).

This is true, but is nothing that couldn't be solved by setting appropriate filters with the right key. That would speed up the search if it is slow. When you add a field as you are suggesting you also have to add the code to make sure it gets set correctly. That code then needs to be documented and upgraded (probably). I guess to me it just seems like a ton of extra work when NAV already provides ways to do it quickly and easily. It's not that it won't work, it certainly will. I just think it would be my last option. If the field would actually be used for more than just filtering, though, then that's a different story

pawanp · 2010-08-31

But how to copy individual records from the filtered table to temporary variable ??

IF ItemVendor.FIND('-') THEN REPEAT

Items.SETFILTER(Items."No." ,ItemVendor."Item No.");
ItemsTemp := Items;
ItemsTemp.INSERT;

UNTIL ItemVendor.NEXT = 0;

Like in my case i want to insert the filtered records to temporary table ItemsTemp on by one
Is the way which i have used is the correct way?. As its throwing me an error. "Item No already exists"
Previous post really helped me a lot. Thanks

SPost29 · 2010-08-31

I see one problem with your code and at least one potential problem.
The problem is you haven't obtained(get or find) the filtered Items record
so your process is copying the first record in the items table then copying that same record again and trying to insert it.
and giving you the error.

The potential problem is that the vendor item table has a two part primary key (item and vendor ) so you could end up inserting an item from one vendor then trying to insert the same item from a different vendor and getting an error.

Steve

pawanp · 2010-08-31

so your process is copying the first record in the items table then copying that same record again and trying to insert it.

You r absolutely right mate. This is what the problem is.
So how to get or find Filtered Record??

pawanp · 2010-09-03

Issue still unresolved.. Not able to filler table with filterString exceeding 1024 character.

mabl4367 · 2010-09-03

Have you tried my suggestions? Have you tried to apply a part of the filter ( less then 1024 chars separated from the rest by the "|"-character) copy all the records that this gives you to a temp table, apply the next part of the filter and so on until the whole filter has been applied?

SLF25 · 2010-09-03

Can you tell us where do you get this filter from? Because if you are the one who generates this filter, then you are doing it wrong, only if this is a user inserted filter then you should do what mabl4367 suggested.

pawanp · 2010-09-04

Can you explain me with the help of code how to apply parts filter??

IF ItemVendor.FIND('-') THEN REPEAT
Items.SETFILTER(Items."No." ,ItemVendor."Item No.");
ItemsTemp := Items;
ItemsTemp.INSERT;
UNTIL ItemVendor.NEXT = 0;

Suppose i want to filter items table with ItemVendor table records.
Thanks..

The above code creates a temporary table with few records. But its only has records. But i want filtered records.

vaprog · 2010-09-05

First of all: will you be fine with a temporary record? If you intend to show the filtered recordset in a form, take care that you don't loose any data or even create inconsistencies in your database. Rather use MARK(TRUE) and MARKEDONLY(TRUE) instead, so you can work with the real data instead of with temporary copies (Mabl4367 spoke of "in older versions on navision", but I am not aware of this being deprecated, obsolete or even abandoned).

pawanp wrote:

Can you explain me with the help of code how to apply parts filter??

Add FIND or GET, as SPost29 already said, like this

IF ItemVendor.FIND('-') THEN REPEAT
  Items.SETFILTER(Items."No." ,ItemVendor."Item No.");
  IF Items.FINDFIRST THEN BEGIN
    ItemsTemp := Items;
    ItemsTemp.INSERT;
  END
UNTIL ItemVendor.NEXT = 0;

or if there are no other filters on Items

IF ItemVendor.FIND('-') THEN REPEAT
  IF Items.GET(ItemVendor."Item No.") THEN BEGIN
    ItemsTemp := Items;
    ItemsTemp.INSERT;
  END
UNTIL ItemVendor.NEXT = 0;

Depending on the filter on ItemVendor you might need to protect ItemsTemp.INSERT against double inserts like this:

IF ItemsTemp.INSERT THEN;

in either of the code fragments.
In this code, with each iteration of the REPEAT loop, the resultset in ItemsTemp represents the set selected by a hypetetical filter containing all the items processed so far, connected by OR operators ('|'). In this example, each filter concatenated by '|' is a simple value, but it might be an arbitrarily complex filter expression.

pawanp · 2010-09-06

mabl4367 wrote:

2. In older versions of navision there is a feature called MARK. Instead of putting the records you want into a temp table you can mark them using MARK(TRUE). Then you set the special filter MARKEDONLY(TRUE). To select wich records to mark/unmark you could use the same methodes that I have outlined above.

Thanks mabl4367 a lot, infact a million.
As a newbie to nav spend a lot of time running towards this issue. Finally!!!

pawanp · 2010-09-06

vaprog wrote:

First of all: will you be fine with ....................

Thanks..

pawanp · 2010-09-06

Final solution for String exceeding maximum length

  IF ItemVendor.FIND('-') THEN REPEAT
  Items.GET(ItemVendor."Item No." );
  Items.MARK(TRUE);
  UNTIL ItemVendor.NEXT = 0;

  Items.MARKEDONLY(TRUE);

  ItemList.LOOKUPMODE := TRUE;
  ItemList.EDITABLE := TRUE;
  ItemList.SETTABLEVIEW(Items);

  IF ItemList.RUNMODAL = ACTION::LookupOK THEN
  BEGIN
  ItemList.GETRECORD(Items);
  Rec."No.":=Items."No.";
  Rec.VALIDATE("No.");

  END;

Help with filter String exceeding maximum length

Answers

Categories