How to filter "Value Entry" with Multiple criteria

kirkostas

I want to filter "Value Entry" table on "Source No." and "Item No." fields using multiple criteria. To be more specific I have 1 table for the Customer and 1 table for the Item and I want to collect X Customer and X Items and use them to filter the "Value Entry" table.
The result will be something like this:

"Value Entry".SETRANGE("Source No.",'A1|B1|C5|G4|S2');
"Value Entry".SETRANGE("Item No.",'01|02|04|07|09');

To be more specific this is the T-SQL script for the above example:

"Select * from [Value Entry] where ([Source No.]='A1' or [Source No.]='B1' or [Source No.]='C5' or [Source No.]='G4' or [Source No.]='S2') and ([Item No.]='01' or [Item No.]='02' or [Item No.]='04' or [Item No.]='07' or [Item No.]='09')"

The problem is that I don't know where to keep each Customer or Item value to build such a big string. I need something that I can store more than 5.000 "Source No." or "Item No." values and then apply them to "Value Entry" as I described above.

Sandeep_Prajapati

Use a textBoxes with source Expression, say SourceNoValueString and another one say ItemNoValueString

SourceNoValueString := 'A1|B1|C5|G4|S2';
ItemNoValueString := '01|02|04|07|09'

"Value Entry".SETFILTER("Source No.",SourceNoValueString); 
"Value Entry".SETFILTER("Item No.",ItemNoValueString);

where the filtering is ot be done.... It is a report or form..??

kriki

Try with

WHERE ("Source No." IN ('A1','B1','C5','G4','S2'))
    AND ("Item No." IN ('01','02','04','07','09'))

For the smaller lists.

In case you need more values, you can try to join the tables and filter on those.
Otherwise, you will need to run the SELECT multiple times, (once for each item or each customer without a filter on the other field) and then test each record if you need it.

kriki

[Topic moved from Navision forum to SQL General forum]

kirkostas

It is a report.
And my problem is that I have so many criteria to filter with, that I don't know what variable to use. What will you do if you have to filter value entry table with 5.000 Item No. and 5.000 Source No. at the same time?
Where will you store the values to be able to build such a filter in value entry?
Is there any function in navision which not overwrite the previous filter?
for example:

customer.findset;
repeat
  valueentry.setrange("source no.",customer."no.");
until customer.netx=0;

at the end i want to have value entry filtered with X customer no.

this will always overwrite the previous filter but i am asking you is there any way to do this?

pdj

I doubt you will get a got solution using this approach. I think you should try to look closer at the actual data in the database. If you filter ends up to include 90% of all records you could do it a lot simpler. Simply begin the OnAfterGetRecord trigger with this:

IF NOT IsWithinFilter("Value Entry") THEN
CurrReport.SKIP;

Then write a simple C/AL function to test if the found Value Entry is within whatever filter you wish.

But if your solution is expected to only find 0.01% Value Entries it might not be the best solution. I guess you can set some filters on the Item and Customer tables to get the exact Items and Customer. Then I would estimate which of the filters (Customer or Item) that would result in the least Value Entries. Then I would loop through that table and find all related Value Entries and put them in a temporary value entry variable. Secondly I would go through the relevant records in the other table and search in the temporary table. All found records should then be put in a second temporary table. Finally should you report simple traverse through the second temporary table which holds the resulting dataset.

If neither of these solutions seems ok, then please describe the expected no. of records in the involved tables - both within the filter and outside the filter.