1 2 Previous Next 24 Replies Latest reply on Jan 9, 2017 6:21 PM by rtolliver

    how to limit display record number in a sub-summary

    rtolliver

      My current database has over 75000 records and I'd like to constrain the number of records that show up in the sub-summary.

      Is there a way to limit or constrain the number of records in a sub-summary?

        • 1. Re: how to limit display record number in a sub-summary
          philmodjunk

          Can you describe the criteria by which you want to do that constrain?

           

          And what aggregate data you might want to show in the sub summary parts?

           

          In general terms, yes, you can specify find criteria and a sort order that controls what records appear grouped with a given sub summary part. You can even remove the body layout part in order to only use subsummary parts and not list individual records at all. But this will affect any aggregate data (Sums, averages, max, min, standard deviation...) you might want to display in the sub summary parts.

           

          But the devil is in the details that you haven't yet posted here in this discussion.

          • 2. Re: how to limit display record number in a sub-summary
            rtolliver

            The Records contain the following fields:

                 OrderNo

                 OrderDate

                 VendorID

                 BunkNo

                 SpecieID

                 Description

                 Thickness

                 Thickness2

                 Width

                 Length

                 Layers

                 BdFt

                 Cost

             

            The Sub-summary displays "SpecieID" when sorted by OrderID so I get a summary of records for each SpecieID,

            I have 710 SpecieIDs

             

            Details: I need to look at the entire database, summarize by specie, then constrain the number of records for each specie.

            • 3. Re: how to limit display record number in a sub-summary
              philmodjunk

              You still haven't explained HOW you want them constrained. I can read what you describe any number of ways.

               

              What is the relationship (not database, but conceptual) between Order Number an Specie ID?

               

              Normally, to get a subsummary for each species, you'd sort by the Specie ID field so it is not clear to me why you would instead sort by Order number.

               

              Is it possible that you want data arranged in the report like this, with one row of data for each different species that makes up an order?

               

              Order Date   Order number 1

                 Species A

                 Species B

                 ...

              Order Date   Order number 2

                  Species A

                  Species B

               

              ...

              • 4. Re: how to limit display record number in a sub-summary
                rtolliver

                The constraint I want is the "number of records" for each Specie, I have 710 different SpecieIDs so I only want to see 4 to 5 record for that SpecieID.

                 

                There is no relationship between Order Number and Specie ID, all of the data is in a single table.

                 

                 

                I'd like the Report to look like this;

                 

                 

                SpecieID (A)

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt 

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdF

                 

                SpecieID (B)

                      OrderID     Date      BunkNo      Thickness      Length     Width      Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                 

                SpecieID (C)

                      OrderID     Date      BunkNo     Thickness      Length     Width      Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                 

                SpecieID (D)

                      OrderID     Date      BunkNo     Thickness      Length     Width      Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                     OrderID      Date      BunkNo      Thickness     Length      Width     Layers     BdFt

                • 5. Re: how to limit display record number in a sub-summary
                  erolst

                  There is a versatile technique called fast summary. In your scenario, all you need is a summary field that counts a non-empty field (eg the primary key).

                   

                  Order the records by specieID (and any other criterion, but specieID must be the primary sort field), then use GetSummary( ) with the summary field to get the group count (ie orders per specie); if it is greater than a threshold value, go to RecordNumber + thresholdValue + 1 and omit the difference, if not, you don't.

                   

                  Either way, you go to the next group (unless it is the last one). Wash, rinse, loop.

                   

                  Not sure how long that would take with 75,000 records, but the script itself is short, so let your computer do the heavy lifting.

                  • 6. Re: how to limit display record number in a sub-summary
                    philmodjunk

                    "The Sub-summary displays "SpecieID" when sorted by OrderID so I get a summary of records for each SpecieID."

                    if there is no conceptual ( not database), relationship,  then was the above statement  made in error?

                     

                    and to repeat the same question a third time, what criteria determines which "4 or 5" species to include in your report and which to exclude?

                    • 7. Re: how to limit display record number in a sub-summary
                      user19752

                      This may be the case to use portal.

                      • 9. Re: how to limit display record number in a sub-summary
                        BruceRobertson

                        Do you have a Specie table? You need one. It needs to include the unique list of SpecieID.

                        Then, you can relate it to your order table.

                        You can then create a field, RecentOrders.

                         

                        Define this to be an auto-enter text field:

                        LeftValues( List( SpecieOrders::OrderID); 5)

                         

                        Set up another relationship from this field to the Orders table.

                        Refresh this field with a replace operation and then go to related records for your report.

                        I suspect you may want to define the relationship from Species to Order as sorted, by Order Date, descending.

                        • 11. Re: how to limit display record number in a sub-summary
                          rtolliver

                          There is no relationship listed because all of the Fields used in this Report are in a single table "Current Inventory".

                           

                          As for the what criteria determines which value "4 or 5" is used there is none, these are random values selected for testing. But you are correct in asking the question since there is such a range of records returned.

                           

                           

                          The sub-summary I described does work, all I need is to identify the best way to constrain the number of records per SpecieID.

                           

                           

                          • 12. Re: how to limit display record number in a sub-summary
                            rtolliver

                            Bruce thanks for the sample file it looks like what I need.  I will have to add scripting to address Philmodjunk's question for what is the criteria that determines the records constraint value.

                             

                            I do have a Specie table but as I've told Philmodjunk the Report that I've created is from a single table called "CurrentInventory", it contains all of the Fields used and it works.

                            • 13. Re: how to limit display record number in a sub-summary
                              philmodjunk

                              If you can explain your criteria, it's possible that there are other, simpler solutions. but "4 or 5" just doesn't provide the needed detail to tell if that's the case or not. From what you've posted thus far, it's even possible that you just need to perform a find with the right criteria specified though I suspect that it's not actually that simple.

                              • 14. Re: how to limit display record number in a sub-summary
                                rtolliver

                                The "CurrentInventory" table has 75000 records, these records represent a "To Date" list of all Specie of lumber purchased.

                                 

                                The test report provides a sub-summary for each SpecieID when sorted by OrderID and SpecieID.  The number of records for each Specie can very from 1 to 10000.

                                 

                                Criteria:

                                1 - I need a way to count the records for each specie in the sub-summary

                                2 - If Count is less than 5, use Count value as the constraint

                                3 - If Count is greater than 5, use 5 as the constraint

                                1 2 Previous Next