Are your relationships set up like this?
---<. Means "one to many"
Yes . PRODUCTS has 10 records and DATA has many records with matching products.
However, there are no join tables. The relationship direct from Products > Data based on a match of the PRODUCT.
Then your answer is no. What you describe does not match what I posted.
Please post a screen shot of the relevant portion of your relationship graph. Where the data for your report is located and how the tables are related are critical details.
The screenshot in the OP showed the table relationship to the right of the sample report I am wanting to create.
Products > DATA
Perhaps that part of the screenshot was cut off or truncated?
My reply to your post was confirming that one record in PRODUCTS relates to many records in DATA. I thought that was the basic question since my OP didn’t say anything about invoices or line items.
What am I missing?
Yes, but my smart phone clipped the right part of the image leaving no clue that there was additional info to see if I opened the image for a close up. On my computer, I can see the relationships.
Ah. That explains it.
You can add more occurrences to your relationship graph that use your global fields as match fields with inequality operators or you can use ExecuteSQL to get the counts that you want.
Which approach would you like to use? (if you are not familiar with SQL, use the extra occurrences approach).
I’d prefer to use the extra occurrences approach.
Assuming a value of 1/1/2017 for global_date_1 and PRODUCTS::product_id = DATA::product_id
for each of the 10 records in PRODUCTS, I want to show the customer count for two dates. (global date 1 and 2)
Customer Count for Date 1 is:
- count of DATA::org_id if the date ordered is less than global date 1 LESS count of DATA::Org_id if date-canceled is less than global date 1
Customer Count for Date 2 is basically the same - substituting global date 2, of course.
I’m assuming I’ll get the Difference by naming the results and creating a variable that uses the named objects to calculate the difference.
Thanks for your help.
You can set up these relationships for one global field. Just replicate using the second global field to get column 2:
Products::GlobalDate1 < DATAbyOrderedGD1:: date_ordered
And another relationship:
Products::GlobalDate1 < DatabyCanceledGD1::date_Canceled
Count ( DATAbyOrderedGD1:: date_ordered ) - Count ( DatabyCanceledGD1::date_Canceled )
Gives you the difference.
I've used < because you specified "before the date". If you actually want "on or before the date", use ≤