Track Popular Customers

[poslaw]

I was just wondering if someone can give me a starting point on this. I have a customer service database. We track our client orders with this. Our clients go through a lot of employees. We don't know when they leave the employ of our client. When showing our clients orders, I want to show our clients contacts in a portal sorted by the highest number of orders by a contact, over the last 30 days.

Table 1: Orders

Table 2: Client

Table 3: Client Contacts

Portal: Want to show contacts sorted by number of orders over last 30 days.

This way once a contact stops ordering our product because they no longer work for our client, they'll drop to the bottom of the list after 30 days or so.

Thanks!

[RalphL]

Off the top of my head, I would make a summary report sorted by Client Contacts. Export the summary to a temp table. View the temp table in the portal sorted by number of orders.

You would have to do this by script. Clear the temp table, then find, sort and export.

[LaRetta]

Hi poslaw,

Another possible way ... If you create another table occurrence of your Orders (called ContactOrders) and attach it to your Client Contacts table (joined as indicated below), then create a calculation (number) called cCountOrders in Client Contacts with:

Count(ContactOrders::OrderNumber)

Create your Contact portal on your form layout and place your Contact and cCountOrders within it. You will have a count of orders by Contact restricted to last 30 days. Note that, if a Contact may also work for another Client, you will need to add the ClientID to that relationship to restrict the orders per Client. Remember to then sort that Contacts portal descending by cCountOrders. Include your date filter by creating a cDateLast30 calculation (result of date, unstored) with: Get(CurrentDate) - 30. Include that in your join as:

ClientContacts::cDateLast30 'less than or equal to' ContactOrders::OrderDate

AND

ClientContacts::ContactID = ContactOrders::ContactID

LaRetta :wink2: