Show Top 10 by DropDown Selection

[OneStop]

I have a sort of sales report and I'm trying to figure out how to show Top 10 Customers, Top 10 Products, etc....by Salesperson.

 

So I have an Invoice ID, Customer Name, Invoice Total, ProductSKU and SalesPerson ID

I have Salesperson ID in a dropdown from a value list...I'd like to know how to script it so that I can select a SalesPerson ID and see the lists of TOP 10.

[Steve Martino]

The devil is in the details.

Whats a 'sort of sales report'?

What makes something a 'Top Ten' and over what period of time?

What does the relationship graph look like?

[OneStop]

Well by "sort of" I mean a really poor attempt to parse data by simply dragging fields on to a layout and doing a series of finds and extended finds to show the data I'm looking for...

ie  doing a find on a customer ID, looking at Total Sales for a customer in a Total Sales subsummary field and then restraining the find results by salesperson..doing a sort by Totals and Omitting everything but the first 10 records...ie, the idiot way.

 

I know there's an elegant way to do this in FMPro but me and the reports section of Filemaker don't get along. It's too smart and I'm too stupid.

[OneStop]

At the moment the period of time is all time. Top Ten means the top 10 customers by dollars spent ie "invoice totals" for a particular saleperson

Top ten products means the top ten product SKUs sold by a particular salesperson...

[Lee Smith]

Hello OneStop,

Sorry, It is unclear to me what you are needing.

Have you looked at making a Summary Reports? Here is an example what you can do with reports. https://community.filemaker.com/thread/95212

you can find some videos on this here https://www.youtube.com/results?search_query=filemaker+pro+summary+report

If I have totally miss read your need, let us know.

HTH

Lee

[OneStop]

Thanks for the link, it's a good read and I'm sure I'll be referencing it later this evening to study a bit more....but what I was hoping for is some idea of how to "script" the find or summaries in such a way so that when I pick a salesperson's name in the dropdown of the salesperson's field it will only show me the top 10 customers by amount sold, top ten products by amount sold for that salesperson etc...

 

Does that make sense?

[Fitch]

Sounds like you might want more of a "dashboard" rather than a summary report. So you might want to do this from the salesperson table, with portals from there to invoices, etc. You could sort the portals and show 10 rows.

[OneStop]

Well that's kind of the problem...this massive pile of excrement I was handed has everything in one table...no relationships, no keys...nothing...it's just a glorified Excel spreadsheet sitting in Filemaker. So now I'm trying to figure out how to get some actionable data out of it.

 

I have to present a PP presentation using the information that's stored in this thing and I'm just trying to format some displays of that data in order to use them in that presentation. Specifically, I need to show the top tep customers by sales for each salesperson, the top ten product by sales for each salesperson and charts of the year over year sales by month..

 

I know FMPro can do all those things...I just don't know the series of steps to make that happen other than doing a convoluted series of finds, contrains and omits..

[Steve Martino]

So moving forward what would be the plan?  If it were me, I would import the spreadsheet to a temporary table, and break out the data into multiple tables.  This isn't an easy job for a beginner.  But once completed, life would be much easier.

After that I wonder if you could make a database that allows the data to be entered into FileMaker properly and exclusively.  It can then be much easier to create reports and charts.  You can also export the proper bits of information (reports) to Excel if needed.

It's really not a job for a beginner, but it's not impossible.  If you work for a person, and this is going to be something you always need, your boss should allow you to get both-some training, and hire a consultant.  The money spent to get it rolling would probably be much less than a trial and terror method of initially creating it yourself, and would save much time.  You're looking at 3 tables, and a join table for starters.

Any invoice type DB would probably do the job.  Although you could look at the Starter Solution for some inspiration, I wouldn't just copy that.  A great resource would be one of these:

http://philosophy-of-filemaker.thinkific.com/  or call JMO directly.  He's one of the best.

or

http://rcconsulting.com/downloads.html

 

You could attach the spreadsheet and see if anyone wants to take a crack at it.  It doesn't have to be the entire spreadsheet, and/or, you can change the names.

[OneStop]

For various reasons that would lead down a rabbit hole I can't do any of those things mentioned but i do appreciate the sentiment.

 

I'm merely a consultant trying to make do with a pile of junk left behind by another employee. I could dump all the data into a MySQL db and create anything I wanted but again without going down a rabbit hole...I'm stuck with access to this thing.

 

So that being said...I did watch the video recommended earlier and have begun creating a report.

I've created a sub summary part that is set as "Sub-summary when sorted by "Customer Name" and it does indeed show me the data I wanted but when I try to sort those totals after doing a find on the saleperson....the summary info of course goes away...

 

What am I missing?

What I'm trying to do is come up with a total dollar amount by customer and then do a find for the salesperson and then sort by that dollar amount, I'm obviously failing to understand Filemaker's basic functions as this seems like a rather simple thing to accomplish without having to do a bunch of find/sort voodoo

[Fitch]

A couple of ideas:

- have you noticed in the Sort dialog there's a "reorder by summary field option" -- would that help?

- you mentioned SQL -- FileMaker has an ExecuteSQL function, have you looked at that?

[OneStop]

I haven't had very good luck with SQL in Filemaker for some reason...so I thought I'd stick to FM's native functions...

 

This seems like this should be a very simple thing...and I'm finding all kinds of neat tricks like trailing grand summaries in list mode and all that, but I literally have 5 fields.

Customer Name, Date, ProductSKU, Qty Sold, Invoice Total and SalesPerson Name...

Obviously each customer can and does have multiple invoices....So all I'm trying to is add up all the invoice totals for each customer, do a find by the SalesPerson name and then sort the list by the Invoice Total so that I can print off a list of the top 10-20 customers by dollar amount for each salesperson....

 

Customer A       invoice Total = 100.00   Salesperson Joe

Customer A       Invoice Total=25.00       Salesperson Joe

Customer B       Invoice Total = 10.00     Salesperson Joe

 

Customer A      Invoice Total = 10.00     Salesperson Mary

Customer A      Invoice Total = 20.00    Salesperson Mary

Customer B    Invoice Total = 20.00      Salesperson Mary

Customer C     Invoice Total = 30.00       Salesperson Mary

Customer C    Invoice Total = 50.00      Salesperson Mary

This is how the current table view appears (mostly)

 

What I want to see when I do a find/report by Salesperson Name for Salesperson Joe is this:

 

Customer A       Invoice Totals = 125.00    Salesperson Joe

Customer B       Invoice Totals = 10.00      Salesperson Joe

 

 

Then if I do a find for Mary I get:

 

Customer A     Invoice Totals = 30.00   Salesperson Mary

Customer B     Invoice Totals = 20.00    Salesperson Mary

Customer C   Invoice Totals = 80.00    Salesperson Mary

 

 

Then I need to be able to do the sort on the Invoice Totals to show for example that Mary's top customers in order are

Customer C

Customer A

Customer B

 

[Fitch]

Thank you for posting example data, that's very helpful. You definitely can do this with SQL, but as I mentioned, "reorder by summary field" should do what you described.

[OneStop]

"reorder by summary field option"

What does that do and how would I use it?

 

I've attached the file if anyone wants a look at it..it's got demo data in it

BARE.fmp12

[Fitch]

Check it out.

BARE.fmp12

[OneStop]

That's great...and I've recreated it with the buttons and the sort order like in your example except all I get is a list of customer names with their Invoice Totals...no salesperson names....am I supposed to sort this a certain way first or do a find for the salesperson then use the sort button etc?

 

I only ask because I noticed you added another subsummary part that's ordered by salesperson name when sorted by salesperson name..which I have also done...yet the salesperson name doesn't appear in mine?

[OneStop]

NVM, I found my stupidity...I added the Saleperson field to the layout instead of the SalesPerson Name field...

 

 

Awesome...thank you so much...you solved a huge problem for me. Thanks for all the help guys. Truly great forums and super helpful.

[OneStop]

One last thing...I would like to understand why your solution worked. Did I approach the problem from the wrong end? It appears you decided to subsummary by Salesperson first then by Company Name....?

[Fitch]

You mentioned doing a Find for Salesperson and then running the report. If you do it that way there's no need to sort by Salesperson. But I figured it might be nice to do all people at once so I added it. You can have as many sub-summary parts on a layout as you want. The parts will display or not based on the sort order.