1 2 Previous Next 15 Replies Latest reply on Nov 21, 2013 10:21 AM by TSGal

    Payments: Month to date, Year to Date

    IlseVerhaert

      Title

      Payments: Month to date, Year to Date

      Post

           Hi, 

           I am quite a FileMaker novice and in need for some support...

           I work for a social business providing microcredits to communities (we sell water filters to the families of that community). Each community collects the money from their families on a monthly basis and deposit this to us. In total we have five different kinds of payment plans (One community always sticks to one kind of payment plan, but some communities have other payment plans than others)

           On both month to date, and year to date basis, I want to keep track of:

           - the actual payments each community did
           - the payments each community should have made (as they not always pay what they are supposed to pay), which will be related to the payment plan

           Your help will be appreciated!!

           Thanks!

            

            

        • 1. Re: Payments: Month to date, Year to Date
          TSGal

               Ilse Verhaert:

               Thank you for your post.

               Are you trying to do this with Filemaker Go?  (You've posted to the FileMaker Go forum).  If so, you won't be able to make the changes in FileMaker Go. You can only do this with FileMaker Pro.

               Are the payments made in a separate table?  If so, then you may want to create portals into the "payments" table with filters for current year and current month.  You can then create a Calculation field that uses the Sum function on the related records to get a total.

               Where is the expected payment amount stored?  You could then compare this to the calculation and if it is short, you can either flag the record or display a "Pay Now" message.

               Let me know if you need additional clarification.

               TSGal
               FileMaker, Inc.

          • 2. Re: Payments: Month to date, Year to Date
            IlseVerhaert

                 I am working with FileMaker Pro, not Go, sorry for the confusion.

                 The payments are stored in a separate table, right now the payment date is as stated as a four digit date. How can I use filters for current year and current month?

                 The expected amount is not stored yet, as I am still looking for a formula that combines the community with its payment plan.

                 Thank you so much for helping me out.

                 Ilse

            • 3. Re: Payments: Month to date, Year to Date
              TSGal

                   Ilse Verhaert:

                   Perhaps you can give me the formatting of the "four digit date".  How do you recognize the year?  The month?  The day?

                   Is the payment plan recognized in a text field?  If so, an "If" expression should be able to extract the desired data.

                   TSGal
                   FileMaker, Inc.

              • 4. Re: Payments: Month to date, Year to Date
                IlseVerhaert

                     My dates are formatted as the following: Month/Day/Year

                     I´ll try out the 'if' expression for the payment plan, thanks!

                • 5. Re: Payments: Month to date, Year to Date
                  TSGal

                       Ilse Verhaert:

                       I'm a little confused on the "four digit date" when you say the date is formatted as m/d/y, as this is a minimum of five characters.  Perhaps you can elaborate a bit more.  Better yet, is the field type defined as Date?  If so, then you can use the Year function to pull out the current year, or use a range from 1/1/2013..12/31/2013.

                       Keep me posted with any progress.

                       TSGal
                       FileMaker, Inc.

                  • 6. Re: Payments: Month to date, Year to Date
                    IlseVerhaert

                         Thank you! For the current format of my dates I was referring to 'options for field' - Strict Data Type: 4-digit year date

                         Right now, I thus added two fields, in which one the month of payment is stated, and in the other field the year. 
                         Is there a possibility to display in an additional field the payments of the 'current month' (which thus will automatically change over time)

                         How do you summarize the payments of a certain year, in an additional field?

                          

                          

                          

                    • 7. Re: Payments: Month to date, Year to Date
                      TSGal

                           Ilse Verhaert:

                           Thank you for the clarification regarding the date formatting.

                           Yes, you can use a calculation for the "current month".  For the current month, you would use:

                           Month ( Get ( CurrentDate) )

                           Breaking this down, you would get the current date and then extract the month from that.  You could then compare this to the records in Payments table.  Since I'm assuming you would records that span over years, you would also need to use this in conjunction with:

                           Year ( Get ( CurrentDate) )

                           ... to extract the current year AND current month.

                           TSGal
                           FileMaker, Inc.

                      • 8. Re: Payments: Month to date, Year to Date
                        IlseVerhaert

                             Ok, got this, but how are you then able to make the constraint that the 'MonthlyPayment' - field only displays the amount deposited of the current month? 
                             And then, how are you able to make the constraint that the 'YearlyPayment' - field only displays the total amount deposited in the current year?

                             Using portals, using summary field,...?

                             Thank you for your patience..

                        • 9. Re: Payments: Month to date, Year to Date
                          TSGal

                               Ilse Verhaert:

                               Set your Filter to:

                               Year ( Get ( CurrentDate ) ) = Year ( <Payments date field> )  and  Month ( Get ( CurrentDate ) ) = Month ( <Payments date field> )

                               This will filter those records for the current year and month, and then you can sum the records in the portal.

                               Does this make sense?

                               TSGal
                               FileMaker, Inc.

                          • 10. Re: Payments: Month to date, Year to Date
                            IlseVerhaert

                                 I am sorry, I am completely lost. I have a table with following fields:

                                 - Community
                                 - Amount Payment
                                 - Date Payment (month/day/year)
                                 - Date Payment (month)
                                 - Date Payment (year)

                                 Many communities can thus make payments. I want to get a resume of payments done per month per community, and then also track the payments per community per year. 

                                 I have no portal yet, nor summary records...

                                 If you say 'Set your filter', you mean that I should create a portal first, and add following available fields: 

                                 - Community, Date Payment (Month), Date payment (year), Amount Payment.

                                 The filter of the portal should then also be your formula:

                            Year ( Get ( CurrentDate ) ) = Year ( <Date payment field> )  and  Month ( Get ( CurrentDate ) ) = Month ( <Date payment field>

                                 Or do I still see it wrong...

                            • 11. Re: Payments: Month to date, Year to Date
                              TSGal

                                   Ilse Verhaert:

                                   From  your description, you don't need a portal.  Just create a Sub-Summary report by Community, and within that, a Sub-Summary by Year, and within that, a Sub-Summary by Month.  First create a Summary field "Total Amount" that is a Total of the Amount Payment field.  Then, place the Community field in the Sub-Summary by Community part.  Place the Month field in the Sub-Summary by Month along with the Summary field Total Amount.  Place the Year field in the Sub-Summary by Year along with the Total Amount field.  If you want a detail of each transaction, then place the Date Payment and Amount Payment fields in a Body.  If not, remove the Body part so you just have a Summary report.

                                   TSGal
                                   FileMaker, Inc.

                              • 12. Re: Payments: Month to date, Year to Date
                                IlseVerhaert

                                     Alright I got this, but now the payments of all months are displayed separately in the report, and I only want the amount of the current month displayed, and the sum of the year to date payments.

                                • 13. Re: Payments: Month to date, Year to Date
                                  TSGal

                                       Ilse Verhaert:

                                       Create a portal into the same file, and link by Community and Year.  Create a Calculation field that sums the amount for the portal records, and include this in the Sub-Summary (or Grand Summary).

                                       Then, perform a Find for just those records in the current month (and year) and sort.

                                       TSGal
                                       FileMaker, Inc.

                                  • 14. Re: Payments: Month to date, Year to Date
                                    IlseVerhaert

                                         Why do I need a portal if all data is stored in one table? And how do I link by community and year? What kind of fields do I need to sort/filter?

                                         If I succeed in making that kind of portal you are talking about, what function do I need to enter to sum the amount for the portal records?

                                    1 2 Previous Next