Filter one table based on the filter of another table

roberte114

How do I filter a table based on the results of a filter on a another table? I am trying to use the a customer card form to display only customers that belong to a certain branch office. So I have two tables (customer and CustomerBranchLocation). I need to filter the CustomerBranchLocation table so that I get a list of all the customers that belong to a particular branch. Then filter the customer table based on the resulting filter of the CustomerBranchLocation. In SQL this would be done with nested queries like

select * from customers where no in (select no from customerbranchloc where location = 127)

I have tried this code:

txtFilter := '';
IF CustFacLoc.FIND('-') THEN
REPEAT
txtFilter := txtFilter + CustFacLoc."Customer No." + '|'
UNTIL CustFacLoc.NEXT = 0;

//To remove the last '|'
IF txtFilter <> '' THEN
txtFilter := COPYSTR(txtFilter,1,STRLEN(txtFilter)-1);

Customer.SETFILTER("No.", txtFilter);

This creates the query fine but gives me an error saying "The filter in the No. field in the Customer table is too large." The txtFilter is very long as there are a lot of records returned so SETFILTER bombs because of it. Is there another way of doing this or can this even be done in Navision? Any help would be appreciated. Thanks.

kriki

The best way is to put the values of the first table "(select no from customerbranchloc where location = 127) " in a temptable.
Then scan the second table and if a record in the temptable exist, it is a good record, otherwise skip it.
I know this is not the best way, because you will read a lot of useless records, but there is no other way.

girish.joshi

Every now and then I come across situations like this where SQL queries seem much more appropriate.

What I do is to use ADO from within Navision to create the query.