1 2 Previous Next 21 Replies Latest reply on Jun 27, 2017 7:20 AM by taylorsharpe

    Remove zero's from subsummary report

    ndveitch

      Hi there,

       

      What is the best way for removing zero totals from a sub summary report. I have a summary report that is working as required. There are some lines in the summary report that have a total of zero, but this is to be expected as the records for the summary report are both positive and negative amounts and then a user will match them off if they total zero. If the balance is not zero, then the user knows that either an invoice has not been raised, or money has come in and is not assigned to an invoice or a credit note has been raised and not settled, ect. So if the balance is zero, then the user can quickly go in and match it off, and this will remove it from the report, but if there is a balance in the total field, the user needs to send the report to that files assigned user to get a reason as to why there is a balance.

       

      So the user doing the matching has requested that they have the ability to remove the summaries that have a zero total so that they can send just the list with balances to the relevant user. I was thinking of creating a plain text field and update it with a 1 or 0 depending if the total is 0 or not, then run my find off that field. I did this in another program using the GetSummary() function, but it slowed my system down so much I ended up using a plain text field and did a replace field contents every time the script ran which was not ideal. For that solution it worked, but for this one I want to find out if there is a better way to do it.

        • 1. Re: Remove zero's from subsummary report
          Johan Hedman

          Use Hide object when

          • 2. Re: Remove zero's from subsummary report
            ndveitch

            That would be for objects on the screen surely? I need to be able to quickly display the report with only summaries that have either a positive or negative summary.

            • 3. Re: Remove zero's from subsummary report
              Johan Hedman

              Then you need to Loop through your records and only show records with values that you want to see

              • 4. Re: Remove zero's from subsummary report
                beverly

                Yes, I understand. You only want to SHOW the values that are greater than or less than 0 for the total. You are correct in your methods if that works ok for you.

                 

                There might be other ways (ExecuteSQL and virtual list/table/report ?), but without "seeing" more of what you are doing, it may be difficult to refine your method. And other ways may not be "better".

                Beverly

                1 of 1 people found this helpful
                • 5. Re: Remove zero's from subsummary report
                  ndveitch

                  I was really hoping there is an easier way to do this. I can have anywhere from 2'000 to 100'000 records to loop through. 

                   

                  As an example, below i have my summary report which the user will be presented with.

                  Screen Shot 2017-06-26 at 2.41.23 PM.png

                  They want to be able to quickly reduce the summary report down to only the summaries with values and then send to the person who is responsible for the account so that they can give information as to why the summaries are not zero.

                  • 6. Re: Remove zero's from subsummary report
                    ndveitch

                    I don't want to hide from display, i want to hide from report. I can only work on the system tonight, so i have extracted the above example to excel, and hopefully this makes more sense.

                    Screen Shot 2017-06-26 at 3.00.58 PM.png

                    The top section is as it is now when I export to excel. I need to find, if possible, a quick way to export the bottom section should the user want to email to the assigned person to get comments.

                    • 7. Re: Remove zero's from subsummary report
                      Johan Hedman

                      Do a cmd+R to extend your search. There omit records with Amount by File that is 0. Then Export your records

                      • 8. Re: Remove zero's from subsummary report
                        beverly

                        Are you able to FIND these records (not = 0)? Or is your screenshot after your process loop?

                         

                        Beverly

                        • 9. Re: Remove zero's from subsummary report
                          erolst

                          You could do a Fast Summary to omit records group-wise from the found set if they have a zero balance.

                           

                          If you do a reorder by balance summary, you can stop at the first group with a non-zero balance, but then you'd have to do a re-sort by date; this repeated sorting may or may not negate the performance gain from a short-circuited loop.

                           

                          Let's face it: you need to exclude an unknown number of records based on a group attribute, so that requires some crunching.

                           

                          OTOH, and if this is only for display*: if you do assign Hide Object when: not GetSummary ( Balance ; theDate ) to the fields in the sub-summary part and check Sliding & Visibility > Slide up" and also check "Also resize enclosing part", you should be able to achieve a convincing imitation of a vanishing act for the respective parts.

                           

                          *Those invisible group records are still in the found set, of course.

                          1 of 1 people found this helpful
                          • 10. Re: Remove zero's from subsummary report
                            ndveitch

                            I will try this when I am back on the system later tonight.

                            • 11. Re: Remove zero's from subsummary report
                              taylorsharpe

                              Beverly's virtual list is how I would do it with a group SQL function.  Not withstanding that a I'm not sure why you don't like having a script loop through an array to omit records because that is pretty easy too. I am going to assume each of these lines is a sub-summary and not a body part of FileMaker.  If it is a body part, just do a constrain of everything not equal to 0. 

                               

                              "SELECT File, Max ( te_File_open ), Max ( ETA ), Max ( ETC ), Max ( Name ), Max ( \"Dept.\" ), Sum ( \"Amount by File\" ), Max ( \"Assigned To\" ), Max ( Comment ) FROM [somefile] WHERE \"Amount by File\" <> 0 GROUP BY \"File\""

                               

                              As Beverly said there are still a lot of questions.  What if there were several Amount records that added up to Zero, do you want a line?  In other words, do you want a report showing only ones where Amount was not zero or maybe where the county of sub summary records is greater than 0? 

                               

                              Any of these scenarios can be done if we know more about them.  And what are you constraints or things you might want to learn?  That you don't want to use SQL?  That you don't want to or don't know how to write loops to omit records?

                               

                              Also, if these are sub summary results, that is not what is going to export from FM.  The raw data from the body will.  All the more reason to use a virtual list and SQL if that is the case. 

                              1 of 1 people found this helpful
                              • 12. Re: Remove zero's from subsummary report
                                ndveitch

                                Hi Taylor,

                                 

                                I will go over the virtual lists again. The list that I showed is only the sub summary, I have removed the body for these lists. I am not used to using SQL as my SQL skills are very limited.

                                 

                                The script that runs to show this report removes all the records that have already been seen to (matched off) and only shows summaries of the files that still need to be reconciled, which is why for the admin ladies they need to see any files that have a zero balance so that they can match off those files.

                                 

                                I have done the looping script in the past, and I was wondering if there is a better way of doing it. When I get back on to the system later tonight I will try the ideas that have been listed here and if I battle to get it right I will have to resort to a loop/omit script.

                                • 13. Re: Remove zero's from subsummary report
                                  taylorsharpe

                                  Of course if you do SQL, you could have Excel make an ODBC database connection to FM and use the SQL to pull it directly into Excel already grouped.  Or maybe you could make a RESTful API call to FM with things nicely grouped together (new feature... I haven't ever tried grouping in it). 

                                   

                                  Well, as you know, there are always quite a few ways to achieve the same goal in FileMaker. 

                                  1 of 1 people found this helpful
                                  • 14. Re: Remove zero's from subsummary report
                                    erolst

                                    ndveitch wrote:

                                     

                                    I have done the looping script in the past, and I was wondering if there is a better way of doing it. When I get back on to the system later tonight I will try the ideas that have been listed here and if I battle to get it right I will have to resort to a loop/omit script.

                                    The difference to your previous attempts would/could/(hopefully) should be that omitting records by the group is faster than flagging them (i.e. write data) individually. Also, throw in a Freeze Window before the start of that loop.

                                    1 of 1 people found this helpful
                                    1 2 Previous Next