1 2 Previous Next 15 Replies Latest reply on Aug 8, 2014 2:07 PM by user12413

    Reports Intermittently Displaying Data Incorrectly in Browse Mode

    user12413

      I am hoping someone can help me pinpoint this problem.

       

      I have several reports that group data by project manager. On occasion, when in Browse mode, parts of these reports will only show the header repeatedly in place of the detail lines (Figure 1); further down in the same report the data displays correctly (Figure 2). When I switch to Preview mode, the report displays all correctly. The problem is not consistent: 1) can happen in any report at any time, 2) does not happen on any report all the time, 3) does not happen for one manager specifically, and 4) may display part of any given manager's data correctly, then switch to headers only.

       

      I do need to see these reports in Browse mode as some data is updated dynamically within the report screen.

       

      Thank you in advance for any ideas on what is wrong or how to fix!

       

      Figure 1

      Figure 1.pngFigure 2.png

        • 1. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
          usbc

          Hi,

          A little hard to guess (a screen shot in Layout Mode might help). However, you might just have a sorting or more exactly, a lack of sorting of the records.

          I'll guess that you are using FileMaker version 11 or 12 or 13. They have persistent sorting and IF this is your problem then you can choose various methods such as Sort on Layout Open/ etc.

          • 2. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
            user12413

            We are running Filemaker 13 Pro and Server. 

             

            In playing around with this some more, I have discovered an oddity.  The problem only seems to persist in this report when I run it by date, and only for a particular time period.  Using a date range for records to include, if the report starts from any date between 7/10/13 to 8/20/13, only the headers show for the first few pages of the report.  If I begin the report at  a date either earlier or later than 7/10 to 8/20, it sorts and displays correctly.  The date range is only used to select records -- records are not in any way sorted by date.

             

            Given that my problem at this time seems to be confined to a particular range of dates and we are not likely to repeat that period of time in future reports, I think I will just consider this explored enough for now. 

             

            Thanks for your help!

            • 3. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
              keywords

              It's up to you, but before you leave the issue behind entirely it might be worth exploring a little: WHY this particular date range is problematic; WHAT is different about the records in this date range compared to those outside of it.

              • 4. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
                Stephen Huston

                Besides, if one doesn't know WHY it's going wonky, how will one deal with it when it begins again at some future time with a different range of dates which won't work correctly?

                 

                The WHY is worth uncovering.

                • 5. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
                  alquimby

                  Any chance there is an error in a Date field entry? In Find mode, use Insert from Index to look at all entries in the Date field. Maybe a year was incorrectly entered, like 1913 instead of 2013. (Hopefully you are using the required 4-digit year for the Date.)

                   

                  When you do your find, are you sure you are getting every record between the dates specified?

                   

                  You might try a sub summary report, with Date as the summary field, and a count field to get the count of each group. Then you can compare the counts to those you get in your reports.

                   

                  When you sort the sub summary by Date, any anomalies will show up.

                   

                  Al Quimby

                  • 6. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
                    user12413

                    Thank you all for your input. Every little bit helps.

                     

                    I didn't mean to sound flippant about just dropping this for now.  I realize I need to get to the bottom of the problem -- just don't have much time to work on Filemaker problems (FM development is not the main focus of my job). 

                     

                    I tried the Insert from Index -- what a cool function!!  Did find 5 records that had wrong dates or wrong format.  I don't have an error capture on that field currently -- guess I need to put one in.  Didn't seem to affect my record count -- only pulling certain invoices for this report and apparently none of these were included.

                     

                    Are there were other circumstances that may cause reports to temporarily go nuts that I should be checking into?

                     

                    As I said before, the weird display comes up only on occasion.  The report was displaying correctly this afternoon but was giving me the same problem (of showing only headers repeatedly in place of "body" records in part of the report) earlier today -- both good and incorrect displays were before I "fixed" the bad dates.  Would a date problem cause intermittent problems like this?  And why would the report display incorrectly in Browse mode but display correctly in Preview?  I just want to make sure I shouldn't be looking at a bigger problem (like corrupt records, people accessing the records at the same time we run the report, etc.).

                     

                    Thanks,

                     

                    Deanna Jacoby

                    • 7. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
                      alquimby

                      Deanna,

                       

                      Are there were other circumstances that may cause reports to temporarily go nuts that I should be checking into?


                           Your report layout may have some issues. You said you are grouping by Project Manager, so I assume you have a sub summary part sorted by Project Manager. A report should not "temporarily go nuts" if it is set up correctly and the layout shows records from the correct context (meaning the layout is showing records from the correct table occurrence).

                       

                      Al

                      • 8. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
                        usbc

                        I just re-read your reply when this thread came up again.

                        It might just be as simple as this:

                        I get that the report is not sorted by date in the Sub-summary part.

                        But it might be that because the data is entered in chronological order (as things happen) AND because FMP presents records in the order of creation by default, the report works in browse mode ...

                        UNTIL you do a find for certain dates . Then, the records are not necessarily sorted as needed by the sub-summary. That was my coment about having a persitant sort (by using a sort script /trigger) when ever the layout loads.

                        Then again, maybe I'm guessing out of my hat.

                        • 9. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
                          user12413

                          Oh, I think I see what you are getting at.

                           

                          My report allows the user to select records based on any combination of the following: invoice date, invoice billing period, client company, project manager, project assistant manager. Then I do a find on the invoice table based on whatever criteria has been selected. 

                           

                          Next, I find related records in the invoice line item table to get our fee (one line item of the invoice) and the amount of reimbursables (another line item of the invoice).  If the client company field is blank, the sort defaults to, in order of sort: project manager, project name, invoice number, invoice line item (fee, reimbursables).  I have checked to make sure that the records I am sorting are using the same fields that I am summarizing on.

                           

                          Only after records have been selected and sorted is the report displayed in browse mode.  There is no body to this report -- it is all subsummaries in this order: client company, project manager, and invoice number, with totals displaying in this order: project name, project manager, client company.  A grand total is displayed based on the Invoice Line Item totals.  So if I understand your meaning I am using a persistent sort on load.

                           

                          Date is never used to sort, only to select what records will be processed.  (Our invoice numbering system is based on year and  month - YYMM### - so don't really care what the actual date of the invoice is - I am sorting in chronological order on invoice number.)

                           

                          I have also noticed that if I run the report using same date range but only a single manager, everything displays correctly.

                           

                          I still don't understand why the report shows incorrectly in Browse but correctly in Preview.  Is there something about how FM displays data that I am missing?  I am not making any changes in between switching modes.

                          • 10. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
                            Stephen Huston

                            If you have multiple subsummary parts on one layout, only those for which the appropriate sorting has been performed will appear.

                             

                            This is a "feature" which allows you to use one layout for multiple types of reports depending on the order in which you sort the records. Neat if used creatively, but a problem if you don't control the sorting precisely at all times.

                            • 11. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
                              alquimby

                              If your desired report display order with totals is: Project Name, followed by Project Manager (one or more for each Project Name), followed by Client Company (one or more), then your sub summaries should be in that top to bottom order on the layout. Your sort order would be Project Name, Project Manager, Client Company. If you wanted another report that just showed Project Name with each Project's total, you would sort by Project Name alone–Project Managers and Clients (and their totals) would not appear in the report.

                               

                              The newly published FileMaker Pro 13–The Missing Manual has a chapter (15) devoted to reports. The accompanying files for that chapter (that can be downloaded) include an excellent example of summaries and various sorts (as mentioned above by Stephen). Although the book is about FileMaker 13, these reporting principles apply to previous versions also.

                               

                              Al

                              • 12. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
                                user12413

                                Yep, I agree!  Those multiple parts can be a mess if not done correctly.  I am in fact using different subsummary parts in the same layout to display different records based on the sort.  And, I see I just got another posting regarding the sort, so will answer here:  My report is set up to sort either by client or by project manager, but not both.  So, two different sorts, two different reports.  The report that is giving me grief needs to be sorted first by project manager, then by project, then by individual invoices within the project (see below).  I believe my sort is correctly set up and I actually am already getting the correct data based on this sort - at least I was for the last two years!!  It just does not display correctly in Browse sometimes -- that only became an issue when the report was run for an entire year's worth of data rather than a portion of the year (am using 4-digit year and we normally run the report month to month).

                                 

                                Prj Mgr 1

                                   Project A

                                         Invoice 1

                                         Invoice 2

                                                        Total Project A Invoices

                                   Project B

                                         Invoice 1

                                                        Total Project B Invoices

                                Total for Prj Mgr 1 (all projects)

                                 

                                Prj Mgr 2

                                    Project C

                                        Invoice 1  etc

                                 

                                Still not sure I understand why the report would always display correctly both in Preview and when printing, and even frequently display correctly  in Browse.  It just does not display all the report correctly all the time in Browse.  Most of the report (same headers, same summary parts) shows correctly in Browse.  And sometimes, running the exact same report, same period, same parameters, it works.

                                 

                                My biggest problem and frustration in fixing this is the inconsistency.  Logically (at least to me!) if my sort were incorrect, records would never show correctly.  Also, if my sort were incorrect, the entire report would not show correctly, not just a section.  Nor would the display change based on Mode - right? (not sure about that part).  And since I can't get it to break all of the time, it has been very difficult to determine what the problem is.

                                 

                                I've attached a copy of the sort from by script as well as a copy of the database layout.  Could the problem be the way the sort is set up using the various tables?

                                 

                                Sorry if I am sounding a little frustrated.  I just want to figure this out but not spend the rest of my life doing so. Haha

                                 

                                Note: service type sort key just sorts to make sure the fees group and total first, then the reimbursables group and total within a given invoice.

                                • 13. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
                                  usbc

                                  "Could the problem be the way the sort is set up using the various tables?"

                                  Sure it can. If you said that before, I missed it, sorry.

                                   

                                  I decided that for complex reports using data from multiple tables and time periods that I'm not smart enough to do it. So I create a separate table for reports.

                                  It has all the fields I need for all the data regardless of its source. I script the finds and imports into the Report table (catch and release / wash-rinse-repeat).

                                  The Report table has a menu page for the user to pick a report and its parameters.

                                  I use layouts for the various iterations. I find that importing is faster than propagating via the server and the whole mess becomes "flat file" simple.

                                   

                                  On the other hand, you mention that it worked for 2 years and you are using V13 so maybe someone will point out a difference between whichever previous version you were using and 13.

                                  Intermitent anything is always the hardest. I'm sure it is justifiably frustrating.

                                  • 14. Re: Reports Intermittently Displaying Data Incorrectly in Browse Mode
                                    alquimby

                                    If it does not show correctly in Browse mode "sometimes" have you checked every line item record within the problematic date range to be sure there are no data entry issues, empty fields, etc.?

                                    1 2 Previous Next