6 Replies Latest reply on Apr 10, 2014 2:13 AM by d.vanlooij

    create table with top 5 items

    d.vanlooij

      Title

      create table with top 5 items

      Post


           Hi,

           I have a dilemma. In my database I have a source table which is filled by a custom value list from an SQL-database.

           The sourcetable consists of date, line,workplace,code,min-downtime and has records of the past 6 days. Now I want in a seperate table a list of the top5 codes based total min-downtime.

           unfortunately I am a bit stuck at this. Anyone any tips how to create this top5 list?

        • 1. Re: create table with top 5 items
          philmodjunk

               use a portal of only 5 rows, no scroll bar. Sort the records on a field in the portal's table that will order the portal records such that the first 5 are the records you want to list in this table.

          • 2. Re: create table with top 5 items
            d.vanlooij

                 Phil,

                 thanks but my challenge lays in the facts that I don't know how to create the top 5 list. I have a sourcetable with 1000 records and they consist of 300 different codes. However these codes vary over time so I cannot programm them hard in my scripts of calculations. Is there a solution for?

            • 3. Re: create table with top 5 items
              philmodjunk

                   I only know what you tell me about the data in this table. So far, I have very little with which to go on. I do not even know what version of FileMaker you are using and that can make a difference.

                   

                        The sourcetable consists of date, line,workplace,code,min-downtime and has records of the past 6 days. Now I want in a separate table a list of the top5 codes based total min-downtime.

                   What is "total min-downtime"? Is that a value in a single field of a single record or a sub total computed from a group of records? If total min-downtime is a time or number field for a single record, you can sort your portal in ascending order and the 5 records with the smallest value in this field will be listed. The  portal's relationship, perhaps with the assistance of a portal filter, can limit the records listed to just those meeting specific additional criteria.

                   If you have FileMaker 12 or newer, you can also set up an ExecuteSQL query that lists the top 5 and this method can use aggregate functions with the GroupBy keyword to return 5 rows of data that each represent a group of records from this table.

              • 4. Re: create table with top 5 items
                d.vanlooij

                     Phil,

                     Sorry if I was a bit vague. But I am so deep into it with my head that I forgot that you don't have the same insight as I am.
                     So here is another try.

                     I use Filemaker 11 and I have a database with several tables. the data comes originally from an SQL database and is imported based on
                     the selected timeframe, (based on the timeframe selection a specific table with data is imported).
                     The data is stored in the Pareto_data table. This table has several columns, department; line; process; cause; min_downtime(minutes downtime); date.

                     From the pareto_data table I would like to extract the top 5 causes of total min_downtime per date, department and line.
                     Due to the fact that there are several different options of importing data it is almost impossible to script all the options for creating a top 5.

                     I also heard that the group by function can do this but unfortunately I don't use FM13. is there another way to create this top 5?

                     thank you for your patience and answer.

                • 5. Re: create table with top 5 items
                  philmodjunk

                       From the pareto_data table I would like to extract the top 5 causes of total min_downtime per date, department and line.

                       I can parse that sentence a number of ways. But my best guess is that you need to group your records first by date, by department within that date with a different total downtime for each cause, for each production line for that department and date. Is that correct? Do you also need to change these parameters to get different subtotals for downtime based on different grouping of your data?

                       I don't see what this has to do with "there are several different options of importing data". The data would appear to be imported once and then we can group, filter and and summarize that data any number of different ways.

                       I can think of two basic approaches and neither will be exactly simple to set up. The simplest would be to set up a Summary report where you perform a find to find all records in this table for a specified date or range of dates. You'd then use a summary field computing the total of MinDownTime with sub summary layout parts showing the sub totals for each group of records. You can nest multiple sub summary layout parts and get subtotals (or not) at each level of grouping.

                       And your record sort can "re-order" the records based on the value of a summary field based sub summary group to put the groups with the largest downtime subtotals first. A looping script could then omit the records where the sub total down time ranks the group out of a "top five" level.

                       And that's the "simplest" method that I can think of.

                       The alternative uses relationships and an added table so that relationships can match records in this added tableto specific groups of records in this table so that an aggregate function computes the total downtime for each group of records....

                  • 6. Re: create table with top 5 items
                    d.vanlooij

                         Phil,

                          

                         thank you I am gonna work on the "simplest method"

                         greets


                         Dirk