Best relationship to count "customers per month"

[Angus McKinnon]

I've been tasked with finding a way of tracking the number of customers that transact with our company every month.

In our FM solution, we have a table of customers, which relates via an ID number to an order table (which in turn links to an Items table for the line items on each invoice)

Some customers order multiple times each month, so I can't just use the number of orders for this metric. There is date information on each order and I need to figure out a way of relating that to customers successfully. 

Ultimately the output I'm looking for would be something like:

Jan: 76

Feb: 89

Mar:66

And so on...

I'm wondering if there's something clever I could do with a sub-summary on a multi-part layout? 

[comment]

Are you really still in version 15?

[Angus McKinnon]

16 minutes ago, comment said:

Are you really still in version 15?

My own machine is on V16 but we still have some lower-spec machines running V15 so I need to build with them in mind. 

[comment]

That makes it more difficult, since you cannot use the UniqueValues() function.

See if the attached demo makes sense. In the real implementation, the value list would be defined to use values from the Customers table, of course. And the body part of the report would be removed (I kept it in just to check that the results are correct). Likewise the sub-sorting by CustomerID.

You could probably do something similar with ExecuteSQL(), but then you would have to think how to present the result.

 

 

CountUniqueInGroup.fmp12

[Angus McKinnon]

This is all incredibly helpful, thank you. I have a lot to chew through!

UniqueValues() might yet be an option here, as it looks like it's available in v16. The machines that I still have running v15 are used for basic order placement, and wouldn't be accessing this kind of report. 

I'm pretty sure I can replicate the demo inside my own solution - will work my way through that over the next few days. Thanks again.