14 Replies Latest reply on Feb 9, 2014 9:31 AM by MikeMassey

    Filemaker is driving me nuts--list customers with sales of groups of item

    MikeMassey

      Title

      Filemaker is driving me nuts--list customers with sales of groups of item

      Post

           I have a table that contains the following fields [store no], [article no], [sales]

           I have a related table that has [article no], [category].

           I want only the total sales by [store no] of the articles of a particular category.

           I can get the numbers using a summary field in a report but I can't just make a list of [store no] and their sales by [category] ranked.

      Called filemaker and offered to pay but they said it was simple and they would send me information on portals. I can tell by what I read on portals it will not give me the simple list I want. (they didn't send me any info either)

           I am dealing with 42000 records about 1300 store numbers, 40 items about 20 of them are of the category I want.

      Where is place I can learn to better data crunch with Filemaker? All their tutorials are long on format and appearance and short on data drilling. IMHO.

        • 1. Re: Filemaker is driving me nuts--list customers with sales of groups of item
          davidanders

               Operating System Version?  Filemaker Version?  Local or network Database?

               Sometimes screen shots of the tables help explain.

               I understand Store No.  Article No is What?  Category Is What?  Sales is What?  Number Field.

               There are five database design links at the start of this List   http://forums.filemaker.com/posts/f6ed4be796?commentId=222931#222931

               Google "report portals site:forums.filemaker.com"
          https://www.google.com/search?q=report+portals+site%3Aforums.filemaker.com

          • 2. Re: Filemaker is driving me nuts--list customers with sales of groups of item
            MikeMassey

                 Version 11 on OS X. Local. 

                 All of those are fields and only sales is a number the other are text.

                 I have read all of those. 

                  
            • 3. Re: Filemaker is driving me nuts--list customers with sales of groups of item
              philmodjunk

                   When you sort your records to get your summary report subtotals, there's an option to re-order your groups by the computed sub total. I'm not sure from your original description, but that may be all you need in order to "rank them by total sales".

                   There are also a number of other options for computing totals and sub totals that may serve.

              • 4. Re: Filemaker is driving me nuts--list customers with sales of groups of item
                MikeMassey

                I don't know what happened to my previous response to PhilModJunk but I will try again.

                     In Access what I did to get the information I wanted was create a query using fields from both tables that selected the sales by category and had a calculation field that summed each record and then I simply did another query that gave me only the store no and the computed calculation and sorted it descending and I added a count field that I used <100 in the criteria to give me the top 100 stores.

                In FM I can get a report that will find the sales by category but the report will only give me a detailed list with a summary for each customer. It gives me each individual record with a sub total summary following it.  I want a layout that only gives me the summary and the subtotal and then I want to be able to sort and count those subtotals. 

                I can't believe this can't be done and I can't believe it would require a script to do it. 

                • 5. Re: Filemaker is driving me nuts--list customers with sales of groups of item
                  philmodjunk

                       In a summary report, If you want only one row for each group of records, you can set up a layout where there is no body layout part--only one or more subSummary layout parts.

                  • 6. Re: Filemaker is driving me nuts--list customers with sales of groups of item
                    MikeMassey

                         How do you then reference those summaries in other layouts? Say want the top 100 by that summary or I wish to then sort by state or something?

                    • 7. Re: Filemaker is driving me nuts--list customers with sales of groups of item
                      philmodjunk

                           You'd use a different method for computing those sub totals. You might, for example, use ExecuteSQL to acquire those subtotals.

                      • 8. Re: Filemaker is driving me nuts--list customers with sales of groups of item
                        MikeMassey

                             Ok that is beyond me. Wish I had my money back for the three versions I have bought over the years!  

                             Thanks for the time and help. I will just upgrade VM Fusion and Access and stay with something I know.

                        I am sure Fm is great for database IT people but for managers of businesses like me that need to crunch numbers quickly and easily it is just too complex. My customers give me this data and it comes in different forms. I can't pay a consultant to build a new FM data base for every possible file format of sales history some big box retailer gives me. 

                        • 9. Re: Filemaker is driving me nuts--list customers with sales of groups of item
                          philmodjunk

                               You can also use relationships based on your category fields to compute the same sub totals.

                               

                                    I will just upgrade VM Fusion and Access and stay with something I know.

                               Strange, anytime I work with Access I end up devising SQL queries--the very thing you need to know to get ExecuteSQL to work.

                               

                          I can't pay a consultant to build a new FM data base for every possible file format of sales history some big box retailer gives me.

                               I see no reason why it would be necessary to "build a new database" for "every possible file format"....

                                

                          • 10. Re: Filemaker is driving me nuts--list customers with sales of groups of item
                            MikeMassey
                                 

                            You can also use relationships based on your category fields to compute the same sub totals

                            I did do the relationships. 

                                  

                                 

                            I see no reason why it would be necessary to "build a new database" for "every possible file format"..

                            That is because the field names are never the same. The information is never the same. They all have different article numbers for the same product for example. They may give me information that I will only crunch one time. 

                            Look you are just a lot smarter than me and it is not my fault I can read and learn Access without help and cannot read and learn FM with provided documentation

                            If queries are SQLqueries then call them queries and make them easy to use---make them so you can run one and then build another off of the first in less than a minute and then provide some documentation on that. The tutorials spend at least 80% of their time on formats and hardly any on data drilling.

                            Nobody is a bigger Mac fan than me but I am just not smart enough to do FM for the tasks that I do. If I were building the proverbial image database or contact database or non profit contributor database or something I am sure FM would be fine. I don't need any of those. 

                            What I need it do it does not do easily. In access I imported the 42000 records from a spread sheet to make a sales detail table. made a table of items and categories and imported that spreadsheet --ran a query by customer by item category--ran a second summary query and sorted it and did all of that in less than ten minutes.

                                 I still do not have a clue how to work with sub total summaries in FM. I don't even know what a SQLquery is.

                                 It is all my fault. I am obviously an idiot since I can not after hours of reviewing documentation and offering to pay for support even determine the terminology needed to find my solution in FM support.

                            I am sure some of the greatest databases of all time were built with FM but it is a myth that is easy for non DB developers to use to make anything other than simple databases.

                                 Thank you. 

                            • 11. Re: Filemaker is driving me nuts--list customers with sales of groups of item
                              TKnTexas

                                   Mike I understand you frustration.  

                                   http://www.youtube.com/watch?v=SlBqz6mXlqc

                                   Although it is ver9, it will explain sub-summaries.

                                   http://help.filemaker.com/app/answers/detail/a_id/6198

                                   This is another source for help on sub-summaries.

                                   If you are willing to pay for a fix, I have done so when I was needed something faster than I was learning it, try this for someone to do what you need.  Or just google filemaker pro consultant.  It is much easier to use than Access.  But I would never go to Microsoft for an Access help either.  I hope this helps

                              • 12. Re: Filemaker is driving me nuts--list customers with sales of groups of item
                                MikeMassey
                                     

                                          http://www.youtube.com/watch?v=SlBqz6mXlqc

                                     

                                          Although it is ver9, it will explain sub-summaries.

                                     

                                          http://help.filemaker.com/app/answers/detail/a_id/6198

                                     I understand and can do everything explained in those two links.

                                Neither tells me how to use sub summaries like fields and records themselves. (rank sub summaries in a report and extract the top 100 or middle 100 and put them in a report by customer by category for example--a very simple task in Access where you can do queries on queries on queries continuing to drill and summarize as you go--difficult in FM because you must write a script or a SQLexecute demand at every change) I only initially mentioned Access because I believed FM to have easy to use features that would do the same kind of thing. That is not the case as you can see.

                                I am sure if I knew how to write scripts or use acrcane demands like the aforementioned SQL execute I would find FM powerful.

                                     I know enough about access to say there is no possible way that what I want to do is easier to do in FM for a non developer. That is not what a came here to discuss. I did come asking for direction to where I could learn about how to use FM and three of you have pointed to things that will not solve my issue. 

                                I appreciate your time but you have done nothing but confirm my point.

                                     The bottom line for FM and Apple is that it is cheaper for me to upgrade VM Fusion and Windows and Access and to continue to use Access than to pay to find a solution in FM or to even invest the inordinate amount of time that I have already invested to no avail. I would love to leave Microsoft forever but for FM's difficulty of use I could.

                                • 13. Re: Filemaker is driving me nuts--list customers with sales of groups of item
                                  TKnTexas

                                       For every Number or Calculation field I create, I immediately create a summary field.  I use the same field name + .sum.  If I have a field for InvoAmt then I would have a matching field InvoAmt.sum.  Use the summary field in the sub-summary part to get subtotals, such as by vendor in this example.  Or, putting the summary field in Trailing Grand Summary to get totals for the report.

                                        

                                  • 14. Re: Filemaker is driving me nuts--list customers with sales of groups of item
                                    MikeMassey

                                         Right. You will in see my screen shot the summary fields in the table I set up.

                                         What I cannot easily do is to use the results found in a summary field after a find or in a layout. 

                                         Using your screen shot how would rank Acctg_MSV.Sum by vendor of Dep3.sum by date and return the top ten vendors for department 3 for example.

                                    I am sure you can do it in FM but not as easy as in Access where Acctg_MSV.Sum is a expression field in a query and can then be grouped, summed, counted, averaged, used in another expression ect in a subsequent query. 

                                         How would you use TotalDailyDeposit in future layouts easily? What were the 10 worse days for TotalDailyDeposits from Dep3 in 2013? The 10 best? 

                                         No question all of these things can be done with FM but not with simple finds and layouts. PhilModJunk is correct it will require some knowledge of SQL commands.

                                         He was right too when he posted this statement in another thread about FM vs. Access

                                          

                                         

                                    The biggest difference between the two systems is that to get anything significant designed requires a basic Knowledge of the query language called SQL. This query language is very powerful and makes possible certain queries that FileMaker either can't do or can't do easily. But this language is also very cryptic to the new user and thus requires a fair amount of study to learn unless you are already familiar with how programming languages work. 

                                         FileMaker, out of the box, doesn't query tables with SQL. It uses more of a "query by example" method that is much more accessible to new users and makes setting up simple searches of your data very simple to do. But there's a point, as you attempt to produce more and more complex queries where SQL will out perform the way FileMaker works--so there is no simple way to say one product is better than the other.

                                    I am no programmer and do not want to be. Excel to numbers was easy. Word to Pages was easy. Powerpoint to Keynote was easy. Mail is better than Outlook.  Access to FM is not easy.

                                         FM should amend their claims of ease of use in their advertising. It is easy to use if you can manipulate your business to use their templates. It is easy to use if you are an experienced database developer. It is easy to use if you want simple databases. It is not easy to use if you expect to crunch numbers like you did in Access. 

                                         Thanks for trying to help.