Filtering Portal with date range, unexpected results
I have a database used to analize test results of products. In one layout we have a portal that lists the products by name, dispalying the number of test carried out on each product.
Widget A 24
Widget B 12
Widget C 19
I am using two global flieds to store date ranges for filtering perposes. Within the portal I have the following formula under Filter:
TESTS::Test Date ≥ PRODUCTS::SearchDateBegin and TESTS::Test Date ≤ PRODUCTS::SearchDateEnd
This works fine, but the probelm is that once it finds any test on a product that has a date outside of the above paramenters, it removes the product from the portal, filtering it out. This is not the result I am looking for. What I would rather is all products stay listed, just the number of tests change. Thus, if you have test a product 3 times in the last 3 months, say once per month, then if the user puts a date range from Feb - Mar, it would show a test count of 2 because one of the test dates is in January and fall outside the date search. instead the entire product is being removed rather than just adjusting the count. The count is performed in the TESTS table using a summary field set to count.
Any ideas how to perform the date range search without eliminating the data, rather just updating totals in the portal. If there are 300 products I want them to always display, just update totals.
Hope this makes sense...