10 Replies Latest reply on Oct 12, 2012 1:45 PM by pdoak_1

    Display non zero sum fields on layout

    pdoak_1

      Title

      Display non zero sum fields on layout

      Post

            

           Info: OS X v 10.8.2
                   FMP 12.0v2
                   Intermediate user
            
           My database contains records of stock transactions and one layout sums the quantity of each stock item using a summary field of the quantity field.  The layout displays a value for all stock items even if the summation for that particular stock item is zero.  Is there a way to have filemaker display only those stock items which have a non zero value.
            
            

        • 1. Re: Display non zero sum fields on layout
          philmodjunk

               Do you have a table where you have only one record for each stock?

          • 2. Re: Display non zero sum fields on layout
            pdoak_1

                 Yes I do.  In that table I have a summary field for the quantity which calculates the stock position.

            • 3. Re: Display non zero sum fields on layout
              philmodjunk

                   I'm mulling over two different approaches--one of which would work from such a table.

                   1) Using the table with one record for each stock and a relationship to your table of stock transactions, the Sum function can be used to sum the related records. Since this is a calcuation field, you can perform a find to omit all stock records where this field is 0.

                   This assumes you can set up a relationship that matches to the correct sub set of records. If you want to match to all records for the same stock in the other table, this is easy. If you need to use additional criteria--say all transactions for a given time period for each stock, this can become a trickier thing to set up, but it still can be done.

                   2) A calculation field can be set up with get Summary () to return the same sub totals as your summary field is producing for each stock. You won't be able to use it in a find to drop out the zero sub totals, but a looping script could omit records for a given stock when the sub total is zero, if you sort your records with the re-order on a summary field option, you can set up a report where the stocks with zero sub totals are listed last. In such a found set, you can loop through your records to find the first one with a zero sub total and then you omit that record and all the records from it to the end of your found set.

              • 4. Re: Display non zero sum fields on layout
                pdoak_1

                     I am slightly confused as to how to get this to work.  The table in question has the following fields:

                       
                •           pnlDate
                •      
                •           Trade Date
                •      
                •           Client
                •      
                •           Security
                •      
                •           Quantity
                •      
                •           sum of Quantity

                     The report selects all records for a given pnlDate and then sorts it by client, security.  I have created another field which is a calculation field using:

                     sumQtyCalc = GetSummary(sum of Quantity;security)

                     When I run the script to show only those records where sumQtyCalc>0 it returns no records.  Am I missing something

                • 5. Re: Display non zero sum fields on layout
                  philmodjunk

                       Yep, you missed:

                       You won't be able to use it in a find to drop out the zero sub totals, but a looping script could omit records for a given stock when the sub total is zero, if you sort your records with the re-order on a summary field option, you can set up a report where the stocks with zero sub totals are listed last. In such a found set, you can loop through your records to find the first one with a zero sub total and then you omit that record and all the records from it to the end of your found set.

                       Neither summary fields nor calculations that reference them can be used in find requests.

                  • 6. Re: Display non zero sum fields on layout
                    pdoak_1

                         I have the report set up so that it is re-ordered on the summary field so that all the zero sub totals are at the bottom.  I realised that I can write a script to go through and find all records with a zero as you described but how do I then show this result as a report?

                    • 7. Re: Display non zero sum fields on layout
                      philmodjunk

                           Do you want the ones with zero totals or non-zero totals? either way, you need only to omit the records you do not want. The reason I suggested this order for the records is that a single step: Omit Multiple Records can omit all records from the current record to the end of the found set.

                           Once you have the desired found set, you can, if necessary, re-sort your records to produce the desired report.

                      • 8. Re: Display non zero sum fields on layout
                        pdoak_1

                             I want all the subtotals that are non-zero.  Could you be more explicit as to exactly what I need to do in the loop script in order to just show the non-zero subtotals.  Many thanks

                        • 9. Re: Display non zero sum fields on layout
                          philmodjunk

                               #First find your records, either manually or as part of this script
                               Sort Records [Restore ; no dialog] --> include the reorder option so that groups with a zero subtotal are listed last.
                               Go To Record/Request/Page [First]
                               Loop
                                  Exit Loop If [ GetSummary (YourSummaryField ; BreakField ) = 0]
                                  Go To Record/Request/Page [Next ; exit after last]
                               End Loop
                               If [GetSummary (YourSummaryField ; BreakField ) = 0]
                                  Omit Multiple Records [no dialog ; Get ( FoundCount ) ]
                               End IF

                               Note: any number equal to or larger than the number of records from the current record to the end of the found set will work the same in the Omit Multiple Records step. Get ( FoundCount) is simply an easy way to specify a number guaranteed to be "large enough" to delete omit the records with a zero sub total.

                          • 10. Re: Display non zero sum fields on layout
                            pdoak_1

                                 Thank you Phil, it worked.  I had not used that method before.  Thank you for explaining in such detail.