1 2 Previous Next 17 Replies Latest reply on Apr 26, 2010 9:23 AM by philmodjunk

    Need Monthly Summaries and Year to date Summaries in Same layout

    TerryJPM

      Title

      Need Monthly Summaries and Year to date Summaries in Same layout

      Post

      Hi,

       

      I'm using the trial to determine whether to go with the Mac Filepro or do all sorts of nasty things to my mac so I can run Windows and Access.  (trying to recreate a database I have in windows.)  For my business each client has at least two records per month, often four or five.  The records show money received or spent on client's behalf--an amount in one of five numeric records.  I need for each month's bill to contain a listing of the records for that month, a summary for the month of each of five numeric fields, and  the total year to date for each numeric field. 

      Like this:

       

      Suzy Client                              Income        Expense       Net

      02/21/09            Rent              1600                              1600

      02/28/09            mgt. Fee                             128           -128

      02/28/09            Lawn Svc                              30             -30

      Feb. Total                                1600              158           1442

      YTD Total                                3200              349            2851

       

       

      I'm able to get the February total, but not the YTD Total.  To get the feb total, I did a find for 02/01/09..02/28/09 and then my layout is from those records.  The summaries are computed by client code.  To get the year to date totals, I'd have to get the summaries from my original table, the one that shows all records.  I just haven't found a way of doing that.  Another thought is to save the original table under a different name and then use the summaries from that.  I tried that, but the summaries don't print. (I pulled the fields with the portal tool.)  I'm obviously new to File maker, but have done a bit of work in access.  My constraint right now is that my 90 days with Apple for tech support will run out in 2.5 months.  I would like to know that this works because otherwise I need to get the virtual windows, or boot camp or something like that and I'm afraid I'll be burning up the wires to get that to work.

      Thanks for any help you can give me.

        • 1. Re: Need Monthly Summaries and Year to date Summaries in Same layout
          obeechi
            

          "an amount in one of five numeric records"

           

          ?

           

          Did you mean to say 'an amount in one of five numeric fields'?

           

          My opinion: get Fusion, install whatever version of windows you use. Use Access while you work out you FileMaker solution. Don't use Bootcamp. If you're using XP, stay with it until you move to Windows 7. If you have Vista, oh well...but that's what I use. You can Command+Tab between Access and OSX-FileMaker all day long. Just keep in mind that a VM to the Mac (or the host) is just one file (though when inside your VM you'll see lots of files, that's the VM's perspective).

           

          So for individual file back ups, of say an Access file, drag your copy from the VM to the Mac and put them in a folder on the Mac. When the folder has ten copies, Zip the folder and burn to a disc, et, et. After you install your VM, copy the VM to an external drive, and don't boot it up. Just keep this copy as a backup of a fresh install. Then install Access, et. The less the better, to keep your VM light. Once a month, defrag inside the VM (using Windows based tools), then shrink the VM using VMTools (otherwise you're VM will get too large over time). You can (perhaps more optionally) also follow this with a virtual defrag, and then an OSX based defrag using, say iDefrag (note OSX handles defragging by itself, but large files less well, and VMs are large files). 

           

          Whenever you move a VM to another location, because you're changing drives et, if it asks you was that move or a copy. Selecting 'copy' is more likely to trigger a need to reactivate. 'Copy' implies that you're, keeping the source of the copy as a VM you'll still be using. In other words, if you have ten machines, you could just copy the VM to each machine, but then you'll have to reactivate each one and give each their own product key or id. 

          • 2. Re: Need Monthly Summaries and Year to date Summaries in Same layout
            comment_1
              

            It's a bit tricky, but you could do the YTD from the context of Clients (assuming you do have a Clients table), by defining a second relationship to the Transactions(?) table as:

             

            Clients:: ClientID = Transactions 2:: ClientID

            AND 

            Clients:: cYear ≤ Transactions 2:: Date

             

            where cYear is an unstored calculation (result is Date) = Date ( 1 ; 1 ; Year ( Get (CurrentDate) ) ) 

             

             

            Then define the necessary fields as calculations using Sum ( Transactions 2:: Income ) etc. and place them on the report layout, in the sub-summary by client part.

            • 3. Re: Need Monthly Summaries and Year to date Summaries in Same layout
              TerryJPM
                

              Comment,

              Sounds like it would work, but I'm so new at this I don't understand some of the shorthand.  I do have two tables.  One is financial records and the other is client info.  So what you're telling me is that in Client Info I create a field that calculates ytd totals by using that formula, so let's say in my client Info file my records would now be:

               

              Client Code

              Owner Name

              Property Address

              YTD Income

              YTD Expenses

              YTD Net

               

              Is that what you're telling me?  And I would get those summaries from the financial records table showing all records.  then I would do my find for just this month's records and put my YTD amounts on the layout?  That makes sense to me.  I'll start trying, but please feel free to disabuse me of my euphoria if I didn't get it right.

              TerryJPM

               

              • 4. Re: Need Monthly Summaries and Year to date Summaries in Same layout
                TerryJPM
                   You're saying Access is better than filepro?  That I can't do it in file pro?
                • 5. Re: Need Monthly Summaries and Year to date Summaries in Same layout
                  johnhorner
                    

                  hi terry,

                   

                  i think all you need to do is create a layout that has a single line listing your transactions (assuming that is one of your tables) in the body part of the layout (more or less as you showed it in your posting).  then depending upon how you want to organize the results, you will need some sub-summary parts (first would be for year, then probably by month, and finally by client).  when you sort your records according to these fields, you will get an organized summary.  to get the appropriate totals, you will also need a few summary fields (e.g. monthly_total_income, ytd_total_income, monthly_total_expenses, ytd_total_expenses, etc).  for the monthly total summary fields you will want to select the option to "restart summary for each sorted group" and then selet the apropriate group (most likely client in your situation). i use this basic method for a payroll database so that i can see a payees totals for both a short-term pay period, as well as their ytd and even lifetime totals.  if i search for all records, it will break it down yearly, then by payee, then by pay period and i get subtotals for each payee and period as well as a grand summary of all payments in the found set.  hope that is helpful.

                   

                  good luck,

                   

                  john h.

                  • 6. Re: Need Monthly Summaries and Year to date Summaries in Same layout
                    comment_1
                      

                    Almost, but not quite.

                     

                    Let me digress for a moment: you COULD do it the way you describe, i.e. find the financial records from the current year (not ALL records - as that would be just as incorrect as finding for the current month only), get the 3 summary values for each client, and put them in the client's record. However, that would take some advanced scripting, which I don't feel would be right for you at this stage.

                     

                    So instead, we make the client's records from the current year related  to the client - IOW, we create a filtered relationship, so that only current year's record are related. Then we sum the values of the related records only.

                      

                    Note that the filtered relationship is IN ADDITION to the existing relationship, and that you must use another occurrence of the  financial records table in order to create the new relationship.

                    • 7. Re: Need Monthly Summaries and Year to date Summaries in Same layout
                      TerryJPM
                         Thanks for the response.  You're saying Access is better than filepro?  That I can't do it in file pro?
                      • 8. Re: Need Monthly Summaries and Year to date Summaries in Same layout
                        johnhorner
                          

                        hi everyone,

                         

                        first, terry, i would like to reassure you that not only can it be done in filemaker, but that it can be done in several different ways (comment and i each have a method that will work for you).  as for which is better, filemaker or access, i am not familiar with access but i can tell you that filemaker is awesome!  it is a very powerful and infinitely customizable database application.

                         

                        second, you can use the method i outlined without using any scripts or formulas.  i do have a script that will sort the records for me, but that can be done manually, the same way you are maunually finding the records you want even though this could be scripted too.  i have included screenshots below showing my "summary" layout both in layout mode and in browse mode.

                         

                        finally, comment, i am curious about your method.  i understand that your relationship will filter only the current year records for a given client and thereby automates the otherwise additional step of performing a find for the current year's records.  but i have 2 questions.  why do you use the sum function for calculating the totals instead of a summary field?  also, how are you calculating the monthly totals?  are you using a sub-summary part when sorted by month?  please elaborate.  i am always interested in learning new or alternative methods.

                         

                        here are the screen shots from my solution:

                         

                        layout

                        • 9. Re: Need Monthly Summaries and Year to date Summaries in Same layout
                          comment_1
                             The answer is simple: we are not answering the same question. I suggest you review the very first post in this thread to see why your suggestion won't work here.
                          • 10. Re: Need Monthly Summaries and Year to date Summaries in Same layout
                            TerryJPM
                              

                            Comment,

                            Thanks so much for your comment and you too, JohnHorner.  You guys are saving my life.  I sure do like everything else I've seen of FileMaker, so I really want to make it work.  Okay.  Comment:  I think that idea will work.  I had the thought of using another occurence of the table, but I don't know how to do it.  What I did was copy the table and then relate all the records so that if I added or changed a record to the main table, my other table would be updated as well.  The first time I did it I tried to copy the sum fields as well and they did not copy.  So the next time I did, I deleted all the sum fields and re-defined them.  So I guess the question is how do I get a second occurence of the table?  I'm sorry, but when it comes to this I have a bad case of the dumbs.  

                            Thanks so much again.

                            Terry

                            • 11. Re: Need Monthly Summaries and Year to date Summaries in Same layout
                              TerryJPM
                                

                              Hi John

                               

                              Thanks so much for all your samples.  You're amazing.  In looking at this it seems like you enter gross earnings per pay period and then do the running total with restart for gross earnings YTD for each employee.  Each of my clients normally have beetween two and six records which have to be summarized for the pay period and then year to date.  Right now I'm using the sum with the running total with restart to summarize the records for the pay period.  But then what I'm having trouble with is summing the year to date records.  So I need to use the same records twice.  Once to sum and print only the current month's data and the second time to sum the whole year's data.  The bigger problem is that I don't yet understand all this stuff yet.  Thanks for all your help.

                              Terry   

                              • 12. Re: Need Monthly Summaries and Year to date Summaries in Same layout
                                comment_1
                                  

                                Go to the relationships graph, click the 'Add table' button, and specify the 'financial records' table. Or select the existing occurrence of the table and click the 'Duplicate' button.

                                 

                                The new occurrence will be initially named "financial records 2" (you can rename it to whatever you like). Use this occurrence as indicated above.

                                • 13. Re: Need Monthly Summaries and Year to date Summaries in Same layout
                                  TerryJPM
                                     Okay.  It worked up to a point.  I got the total of one of the five fields I was hoping to add up.  It's a field that typically doesn't change throughout the year.  On the other hand, the other field like that didn't work.  These two fields are escrow fields.  The income and expense fields didn't work.  I did the same thing for both.  I wanted to look at the second occurrence of the data base, but I can't seem to find it in the little drop down menu in the top left.  I'm thinking the  fix worked, but I  still have some work to do.  Thanks so much, Comment.  You're great. 
                                  • 14. Re: Need Monthly Summaries and Year to date Summaries in Same layout
                                    TerryJPM
                                       I got it!  It works like a charm!  Thanks so much. 
                                    1 2 Previous Next