Compound filter for a view

Is Customers a N:1 reference field in Sales? You can only select 1 customer for each Sales records?

Maybe something like:

do as server
    let t := this;
    select Sales
        where 'Sale Date' >= t.'StartDate' and 'Sale Date' <= t.'endDate' and
        (var c := Customers;
            count(numbers(t.Customer)[= c]) > 0)
end
do as server
    let t := this;
    let getSales := (select Sales);
    getSales['Sale Date' >= t.'StartDate' and 'Sale Date' <= t.'endDate' and
        (var c := Customers;
            count(numbers(t.Customer)[= c]) > 0)]
end

I put in two different ways cause I saw some lag in my test DB of only 16 records and I don't know how many Sales records you have.  So try either lines 1 - 7 or 8 - 14 and see which is faster.

Sam said:
You are spot on with the second one.

 They both do the same thing. Is the 2nd one faster?

Sam said:
I am able to search for a product purchased within a given date range by using another dMC field for products. 
Do you think it is possible to filter for both at the same time? 
such as trying to search/filter within a given date range to see if--- a chosen customer or customers have purchased a chosen Product or Products?

 Most things are possible in Ninox. Adding another dMC brings up the question do you want the filtering to happen even if no product is selected? I have set it up to take into account an unused Product selection. Please make sure that all the field names match yours. This is on my DB. The big thing to change is line 8 where we set variable "p". You need to trace your path from Sales to Products properly.

do as server
    let t := this;
    let getSales := (select Sales);
    if Products then
        getSales[Date >= t.'Start Date' and Date <= t.'End Date' and
                (var c := Customers;
                    count(numbers(t.Customer)[= c]) > 0) and
            (var p := OrderItems.catalogItems.Products;
                count(p[var subp := this;
                            count(numbers(t.Products)[= subp]) > 0]) > 0)]
    else
        getSales[Date >= t.'Start Date' and Date <= t.'End Date' and
            (var c := Customers;
                count(numbers(t.Customer)[= c]) > 0)]
    end
end

I created a dMC called Products that points to my Products table and lists all Products. But if you want only products that the Client has bought then you can use the record() command in a for loop to get the records selected from Customer then build a list of products.

Lines 8 - 10 is the new part. We have to use two counts because when you trace the relationship from Sales to your Products you will get an array of items, so we need to add a second level of counts.

Sam said:
How would this read if using the reference field searching through the products table. I would be searching for just one product. With the dMC there are too many to easily search with the combo box or radio buttons.

You can try something like:

do as server
    let t := this;
    let getSales := (select Sales);
    if Products then
        getSales[Date >= t.'Start Date' and Date <= t.'End Date' and
                (var c := Customers;
                    count(numbers(t.Customer)[= c]) > 0) and
            (var p := OrderItems.catalogItems.Products;
                count(p[= t.Products]) > 0)]
    else
        getSales[Date >= t.'Start Date' and Date <= t.'End Date' and
            (var c := Customers;
                count(numbers(t.Customer)[= c]) > 0)]
    end
end

Remember that line 8 has to change to match your Path from Sales to Products.

This assumes that you use the default reference field name for the Products table. If needed, change to match yours.

I added the if statement at line 4 to take into account if you leave Products empty or not.

Sam said:
when you have a chance, please attach the small database showing how you handled the filter.

here it is. Open the Quick View then go to the Tab tab.