1 2 Previous Next 17 Replies Latest reply on Jan 19, 2010 1:10 PM by comment_1

    Need some help with how to set this up.

    dyang

      Title

      Need some help with how to set this up.

      Post

      Hi,

      My supervior ask me to set up a template to do some financial recording for our school. There is only one frield that I cant figure how to make it work. 

      I need a field that can calculate the perivous month's usage and add to this month's total.

       

      Lets say on December 2009, we got $2100 for the catergory , Senior Trip,  and this month we got $3200 for Senior Trip. I will need to add up both months and show a tota. 

       

       

      December 2009 Senior Trip will be one record, and January 2010 Senior Trip will be another record. 

       

      The thing I am confuse about is, how to make a field that will find the related Catergory and get the total or the difference between the two month. 

       

      Also, for the report layout, it is possible for one column to show December 2009 and the Next showing January 2010? 

       

       

      Thanks for reading and helping. 

       

       

        • 1. Re: Need some help with how to set this up.
          littledog
            

          Hi,

          A bit difficult to understand but ...you say:

          "December 2009 Senior Trip will be one record, and January 2010 Senior Trip will be another record. 

          The thing I am confuse about is, how to make a field that will find the related Catergory and get the total or the difference between the two month."

           

          In Summary is option "Running total". Place this Summary field on each record and find: Difference = Running total - Amount on record.

           

          Or I haven't understand question correctly? 

          • 2. Re: Need some help with how to set this up.
            dyang
              

            Okay, I think if I understand this correctly, The "Difference" will give me a Grand calculation. This part I understand what to do but not what I want to show on the print out report or the regular layout.

             

             

            Lets say December 2009 I got some money for Senior Trip. So the amount for Senior trip is $2100. 

            So on January 2010 I got some money for Senior Trip again and the amount for January 2010, is $3100. 

             

            So on this layout, I have a field for Month/Date (January 2010), the Catergory (Senior Trip), the money I collect for this month ($3100) and I also need to do a caculation using the pervious month's money, Which will be $2100 + $3100. 

             

            The reason why I only need the pervious month and not just the grand total is, lets say I want to check November 2009, I want to see  the money I collected on the same Catergory on October 2009 and the total of October 2009 and November 2009. and that is the part I am confuse of. 

             

            Is there anyway to get the field on a record and calculate? Can I use a find script to do that?

             

            • 3. Re: Need some help with how to set this up.
              comment_1
                

              The easy way to do this (if I understand correctly) is to find the records in this and previous month, and produce a report summarized by Category, then Month:

               

              Category: Senior Trip

              • December 2009: $2,100

              • January 2010: $3,100

              Total in Category: $5,200

               

              No calculations are required for this - only one summary field (total of amount).

              • 4. Re: Need some help with how to set this up.
                philmodjunk
                  

                As you work with this report, you may find it useful to explore the various options for creating summary reports.

                 

                Here's a link to a simple tutorial on setting up summary reports that you may find useful:

                Creating Filemaker Pro summary reports--Tutorial

                • 5. Re: Need some help with how to set this up.
                  littledog
                    

                  I will use self-join relationship.

                  So calculated (Month - 1) (table1) is relationship field to Month (related table 2). Return values from table 2 (witch is the same table 1 but "delayed" for 1 month) to table1 layout.

                  • 6. Re: Need some help with how to set this up.
                    dyang
                       so you mean i have to create a relation within the table? How can you do that? Sorry for this many question. I am user of filemaker but first time have to built this kind of data base
                    • 7. Re: Need some help with how to set this up.
                      dyang
                         also, lets say my date format is 1/11/2010,   how can I search it by the month? Thanks alot
                      • 8. Re: Need some help with how to set this up.
                        dyang
                           Found how to find just the month.. lol
                        • 9. Re: Need some help with how to set this up.
                          dyang
                            

                          Okay, I got the summary part, but here is another problem.

                           

                          If I want to generate a report that calculate the total of the amount with in same category and month how can I do that?

                           

                          Lets say on January 2010, all the Senior Trip money collected, and all the Graduation money collected?

                           

                           

                          Can I do the summary by category? I need to put the Senior trip and the graduation on the same report layout. 

                           

                          As far as I understand summary field  sum up all the field and it does not care about the category right?

                          • 10. Re: Need some help with how to set this up.
                            comment_1
                              

                            dyang wrote:

                            As far as I understand summary field  sum up all the field and it does not care about the category right?


                            No. If you put a summary field in a subsummary part, it will return the corresponding subsummary value.


                            • 11. Re: Need some help with how to set this up.
                              dyang
                                

                              I think I am getting there with all this help...

                              but I still need a little help here, 

                               

                              I know that if I create a new layout/report using the coulmner list/report, 

                               

                              I will show all the record. 

                              Which will show in a format as follow:

                               

                              Month:January

                               

                              Date                Type                         Money Collected

                              1/12/2010      Seinor Trip                        150

                              1/13/2010      Seinor Trip                        260

                              1/13/2010      Graduation                        260

                              1/14/2010      Graduation                        1400

                               

                              but this is what I really want to show

                               

                               

                              Month: January

                              Type                           Total

                              Senior Trip                   410

                              Graduation                   1660

                              • 12. Re: Need some help with how to set this up.
                                comment_1
                                  

                                Your layout needs to have two subsummary parts and no body part:

                                 

                                 


                                Month: January subsummary by month, leading


                                Senior Trip                   410

                                Graduation                   1660 subsummary by category


                                • 13. Re: Need some help with how to set this up.
                                  dyang
                                    

                                  I did what you told but some how it is showing only one record.

                                   

                                  Is there any option that I can turn on to show all the Record or all the category?

                                   

                                   

                                  Thanks

                                  • 14. Re: Need some help with how to set this up.
                                    littledog
                                      

                                    So here - in this forum  - cannot be added sample files.

                                    So here cannot be shown any links. 

                                     

                                    How to help to fellows?!!!

                                     

                                    Thats why I use fmforums com in general - normal place, normal forum or post on my portal www dot hierarchy dot lv.

                                    Take a look there. 

                                     

                                     

                                    1 2 Previous Next