If you wish to order the customers according to fondness, meaning the ones who purchase more would end up on top, you can use the count function and incorporate it in the orderby function.
Assuming the purchase table has reference to the customer table (it most certainly should), you should be able to count the number of times a customer has made a purchase.
- Add a virtual column, [Purchase Count], with type ‘number’, to the customer table, with formula:
Count(Select[Purchase table Name], [Customer Name column in purchase table]=[_THISROW].[Customer Name column in customer table]))
This outputs a number which is the count of purchases made by a customer.
- Add a virtual column named Customers by Count in the Purchase table, with type enumlist and base type ‘ref’, as reference to the customer table. It’s formula would be:
orderby(filter(“Customer Table Name”, TRUE), [Purchase Count], TRUE)
This column stores all the values from the customer table sorted according to the purchase count. If you want to sort ascending, omit the TRUE after the [Purchase Count].
- In the column to choose the customer in the purchase table, go to the valid_if field and input the following bulk dereference formula:
[Customers by Count][Customer Name column in customer table]
This produces a sorted list of customers according to the number of purchases they have made. Don’t use a " . " in the bulk dereference, as the “.” is only meant for single dereference, not bulk.
This was all taught to me once by @Steve, and I owe it completely to him.