1 2 3 Previous Next 98 Replies Latest reply on Mar 21, 2013 9:57 AM by SwissMac

    How can I restrict a summary field to a date range when displayed in a report?

    SwissMac

      Title

      How can I restrict a summary field to a date range when displayed in a report?

      Post

           I'm sorry if I don't explain this well, I am not sure exactly what I am doing and don't use FM much, just in flurries every year or so, but with great help from people on here I am learning a lot! :-)

           I have a report that displays the following info per row:

           dValues::Date
           ClientInfo::ClientName
           dValues::Cash
           dValues::Value
           dValues::TotalInAccount
           sjSelectedQuarter::TotalInAccount (A self-join to dValues - thanks Phil!)
           dCashInOut::TotalNetChange (a summary of TotalNetChange)
           dValues::ChangePercent

           The report is based on dValues and the tables referred to are related in this way

           ClientInfo --< dValues = sjSelectedQuarter
           ClientInfo --< dCashInOut

           I want to show the summary total of all cash movements into and out of the account, filtered by the time period but all I get is an unfiltered total of all cash movements, in all timeframes. How can I ensure only those cash movements within the specified time period are added together in the summary field?

           The summary field dCashInOut::TotalNetChange summarises as a total dCashInOut::NetChange where dCashInOut::NetChange ia  Calculated field on dCashInOut::CashIn - dCashInOut::CashOut

           I was thinking I might need a global field, but couldn't work out of what, or where to put it - maybe dValues::gQuarter? Should I then put this into the report row too? Or is there a problem with the relationships?

           My report shows the records for a particular date range, per client. There are many entries for each client in each time period, but I only want to summarise those relevant to the time period being examined. The totals for the beginning and end of the time period are correctly filtered, but the values from dCashInOut are not.

           Where am I going wrong?

           TIA!

        • 1. Re: How can I restrict a summary field to a date range when displayed in a report?
          philmodjunk

               Without restructuring your relationships and assuming that you have Filemaker 11 or newer...

               You can put one row portals to dCashInOut on your layout. Define a portal filter that filters records in dCashInOut to include only those related records whose date falls in a specified date range.

          • 2. Re: How can I restrict a summary field to a date range when displayed in a report?
            SwissMac

                 Thanks for coming back to me. If it is a one row portal, how can it show multiple records of multiple clients on the report? :-/

                 I have this kind of setup and can't work out why the Summary Field dCashInOut ::NetCashIn cannot be totalled in the Report Trailing Summary, nor can I restrict its date range. I am not sure how a one row portal would work and show me the info I am after?

            Report Body

                 Client 1 - Values::Field 1 - Quarter::Value SelectedQuarter::Value - Summary Field dCashInOut::NetCashIn

                 Client 2 etc
                 .
                 .

                 Client n etc

            Summary Section

                 Grand Total of Quarter::Value - Grand Total of SelectedQuarter::Value - Grand Total of Summary Field dCashInOut::NetCashIn

                  

                 My workaround is going to have to be to total up the figures manually and enter them manually into Quarter::NetCashIn as a fixed amount per quarter like I do already for the Values per time period. It's a bit clunky and non-optimal, but I really can't work out how to structure it any other way.

            • 3. Re: How can I restrict a summary field to a date range when displayed in a report?
              philmodjunk

                   Each client should be a different record in your report's found set. You want a single row portal because you would place this portal in the layout body to show a different total for each client.

                   what group does your sub summary layout part summarize? (What's the "sorted by" field?)

              • 4. Re: How can I restrict a summary field to a date range when displayed in a report?
                SwissMac

                     Sorry, I'm having a senior moment here - tired and brain not functioning properly. Do you mean I end up with a multi-page report, with one client on each page with the portal underneath the client name at the top of the page in the title area and then the portal containing all of the entries for NetCashIn?

                     Or, do you mean I make a portal only as wide as a single field and have it display just one field which somehow totals up all the other transactions for the given period, which makes me ask how does it know to synchronise both the period in the portal and the period in the rest of the report?

                     As for the sorted by field, I don't have one because it didn't show anything even when it was there. I wasn't sure what it meant anyway. I read the Help pages on it but they said the same (even had a screenshot!) as the dialog box which wasn't at all clear.

                      

                • 5. Re: How can I restrict a summary field to a date range when displayed in a report?
                  philmodjunk
                       

                            Do you mean I end up with a multi-page report, with one client on each page with the portal underneath the client name at the top of the page in the title area and then the portal containing all of the entries for NetCashIn?

                       No. I mean a list view layout where each client record forms a horizontal row of data. The one row portal can be formatted to look like it is just another field in this row of data..

                       

                            As for the sorted by field...

                       Sorry, I misinterpreted that last section:

                       

                  Summary Section

                       

                            Grand Total of Quarter::Value - Grand Total of SelectedQuarter::Value - Grand Total of Summary Field dCashInOut::NetCashIn

                       As a sub summary layout part instead of a trailing grand summary.

                       This final grand total can also be shown in a filtered portal that filters to a date range, but the relationship will need to be different so that it matches to data from all clients in your report. This may be easy or difficult depending on your answer to this question:

                       Does your report limit the records to just certain clients or does it list all clients?

                  • 6. Re: How can I restrict a summary field to a date range when displayed in a report?
                    SwissMac

                         The report runs a Script which uses a Find command to find all Active Clients that were Active in a specified Quarter (which is hard coded into the script because I couldn't work out how to copy the method we did before that allowed selection of the starting quarter for analysis). 

                    • 7. Re: How can I restrict a summary field to a date range when displayed in a report?
                      philmodjunk

                           Does that mean you have a field in Clients that marks the active clients as active?

                           If so, that value can be easily included as part of the portal filter expression or as part of the relationship on which the portal is based.

                      • 8. Re: How can I restrict a summary field to a date range when displayed in a report?
                        SwissMac

                             Yes, I am using a Boolean Radio Button to mark clients as Active, but this status normally remains unchanged for years. There are maybe two or three changes per year only across all clients so I don't need regular access to it. Are you thinking of replacing the script with portal filtering somehow? I am not sure how this can help me display the overall total...

                        • 9. Re: How can I restrict a summary field to a date range when displayed in a report?
                          philmodjunk

                               No, i'm determining how to set up your portal for the grand total of net cash in.

                               If you add a relationship to dCashInOut that uses the X operator, the relationship will match to all records in that table. You can then use a portal filter expression such as:

                               AlldCashInOut::Active = "yes" And
                               AlldCashInOUt::Date > Globals::gStartdate And
                               AlldCashInOUt::Date < Globals::gEnddate

                               You may need to change the "yes" to reflect the actual radio button value you've specified for that field to mark clients as 'active'.

                          • 10. Re: How can I restrict a summary field to a date range when displayed in a report?
                            SwissMac

                                 I see - I think! So that would mean replacing the global sjSelectedQuarter::gQuarter with Globals::gStartDate and then adding a second Global field. I thought it needed a second Global somewhere (I must have learned something then).

                                 Will I then need to update the Layout that uses the gQuarter with a drop down list to set the starting quarter date? Having the two as settable quarters makes a lot of sense logically - and can use the pop up window dialog nicely as that allows for up to three fields to be set on entry to the layout.

                                 i am assuming that AlldCashInOut is a duplicate of dCashInOut here?

                                 FYI, the 'Active' field is in a different table, ClientInfo. Would I have to move it to AlldCashInOut?

                                 Sorry, that's a lot of questions, but things do seem to be getting slowly clearer... :-)

                            • 11. Re: How can I restrict a summary field to a date range when displayed in a report?
                              philmodjunk

                                   Yes, I should have noticed that "active" is in the client table, where it should remain. If you set up your relationships right, you can modify my expression to reference the active field in an occurrence of clients:

                                   AlldCashInOut, BTW, is a new occurrence of dCashInOut.

                                   dValues----X---<AlldCashinOut>----Clients 2

                                   Then your expression would refer to active in Clients 2.

                              • 12. Re: How can I restrict a summary field to a date range when displayed in a report?
                                SwissMac

                                     So, I need a new Globals Table plus two new Table Occurrences, of dCashInOut and of ClientInfo? I then link them as you suggest, remove the script from the existing report, but I'm unsure about where to place the global fields? Do I need to change any fields around or do I create a drop down/pop up dialogs to set them with?

                                     And then there's the one row portal; which table is it based on and which field? Sorry, it's getting late here and I'm still in the office, unfed and I guess the old brain is slowing up. You've probably told me already, I apologise for any repetition...

                                • 13. Re: How can I restrict a summary field to a date range when displayed in a report?
                                  philmodjunk

                                       A new globals table? for what?

                                       A global field can be defined in any table in your database file and it can be accessed from any layout, script or calculation in that file. The only time a global field needs to be defined in a specific table is when it is used as a match field in a relationship.

                                       I'm not suggesting that you remove your script. It's needed to pull up the desired found set. This set of relationships is just to get one specific grand total to appear at the end of your report.

                                       Yes, I have already specified on what table occurrence it should be based and the field you would put in it is the summary field from the same table occurrence.

                                  • 14. Re: How can I restrict a summary field to a date range when displayed in a report?
                                    SwissMac

                                         I'm sorry, I must have misunderstood... you wrote this:

                                         

                                              AlldCashInOut::Active = "yes" And
                                              AlldCashInOUt::Date > Globals::gStartdate And
                                              AlldCashInOUt::Date < Globals::gEnddate

                                         I assumed Globals::Startdate meant Table=Globals, Field=gStartdate and the same for Globals::gEndate

                                          

                                    1 2 3 Previous Next