9 Replies Latest reply on Jan 18, 2012 9:46 AM by Alejosor

    Due Date Invoices

    Alejosor

      Title

      Due Date Invoices

      Post

      Hello.

       

      I have a query with regards some calculations.

      What I want to do is to add a field on an invoice statement that shows me how much a client ows our company say in 30, 60 days period.

      Say for example our company issued 2 invoices to one client over a period of 2 months and the total outstanding amount lets say is £9,500.50 and they have paid us in different installements and different amounts towards the two invoices but have not paid all.

      Now, what do I need to do in order that FileMaker show us how much a client ows our company in invoices that are overdue for more than 30, 60, etc days.

      Please see the image below so you can see what I have been doing wrong.

      On the image it gives me a value of 4,500, when the value should be 3,500.

      Bear in mind I am not an expert.

      Thanks.

       

      invoice.jpg

        • 1. Re: Due Date Invoices
          philmodjunk

          Selectively "summing" related records is a frequent issue here in the forum. Using an if function like this (or a case statement) enables you to either sum all related records (expression evaluates as true) or no related records (Expression evaluates as false). It will not sum a selected sub set of the related records.

          To get a selective sum requires either a selective relationship (so that only records meeting the 30 day "age" criteria, for example are related) or a filtered portal with a summary field defined in the portal's table (The filter expression would limit the related records to the specified 60 day "age".)

          The exact details for how to do this depend on the relationships and table you already have in place.

          In particular, how do you record payments for your invoices. A system like this, typically requires two tables added to the basic customers--invoices--lineitems--products setup, A payments log to record each customer's payment and a "join" table to link that payment to one or more invoices to which it is applied.

          Do you have such a structure to your database?

          • 2. Re: Due Date Invoices
            Alejosor

            Hello Phil:

             

            i have  a record per each customer's payment and a "join" table to link that payment to one or more invoices to which it is applied. If a client does a payment I can record that information and I can see the outstanding amount per invoice.

             

            Please see the image below

            • 3. Re: Due Date Invoices
              Alejosor

              This payment is recorded in every invoice

               

              • 4. Re: Due Date Invoices
                philmodjunk

                Do you have an Indexed, stored field in Invoices that marks its status as "paid"?

                Your last post is a source of some confusion. If you have a separate payments table why would ANY payments be recorded in Invoices? Is this a screen shot of a  portal to Payments located on an Invoices layout? (In which case your payments, technically, are not recorded in invoices, but the join and payment tables.)

                This relationship would match to all unpaid invoices for a given customer that ar 30 days old or older.

                Customer::CustomerID = Invoices30Days::CustomerID AND
                cTodayLess30 > Invoices30Days::InvoiceDate AND
                constUnPaid = Invoices30Days::PaidStatus

                cTodayLess30 iw defined as an Unstored calculation field with Get ( CurrentDate ) - 30 and constUnPaid is a calcualtion that returns a constant value matching whatever value you use in invoices to mark an invoice as "unpaid". Please keep in mind that calculations that refer to related tables are unstored and unindexed and thus cannot be used for "PaidStatus" in this relationship. You'd need to use a script trigger to update this field's value each time you log a payment that pays of that specific invoice. I'm assuming here that you would create a new occurrence of Invoices and name it Invoices30Days for this relationship.

                You can also just use Customer::CustomerID = INvoices::CustomerID and then use a portal filter:

                ( Get ( CurrentDate ) - 30 ) > Invoices::InvoiceDate AND Invoices::PaidStatus = "UnPaid"

                You can define a summary field in invoices that totals the unpaid balance on each invoice and put it in a one row portal with the above portal filter. (And PaidStatus no longer has to be an indexed field.)

                In the case of the first option, you can add this same summary field, but specied to be from the INvoices30days relationship to show the total unpaid of invoices 30 days or older.

                The second option requires FileMaker 11, but has the advantage in not needed specialized calc fields and you can use the same set up for both 30 and 60 day old invoices--just with slightly different filter expressions on separate one row portals.

                • 5. Re: Due Date Invoices
                  Alejosor

                  Hello Phil I'm sorry about the confusion.

                  The field that mark the status as "paid is not indexed. I put it manually every time a person pays and invoice in full.

                   

                  Second, the payments as you said are not recorded in the invoices but on a separate table. The scren shot is a portal to Payments located on an Invoices layout, and the payments are recorded in the join and payment table (see the relations table)

                   

                  I'll give a try and back to you if it doesn't work.

                   

                  Thanks

                  • 6. Re: Due Date Invoices
                    philmodjunk

                    The field that mark the status as "paid is not indexed. I put it manually every time a person pays and invoice in full.

                    If you manually change the field it should be indexed and if not already will become so if you use it in a relationship unless have specfically turned off indexing for that field. If you have, turn it back on.

                    Second, the payments as you said are not recorded in the invoices but on a separate table.

                    This is a case where you may need to define a calculation field in invoices that computes the outstanding balance on that invoice if you do not already have such a field (I suspect that you do as "Outstanding" looks to be exactly that). Then you can define a summary field to compute the total of this field for use with either approach for showing the 30, 60 etc day oustanding invoices for a given customer.

                    • 7. Re: Due Date Invoices
                      Alejosor

                      Hello Phil,

                      I created this portal filter in "invoices"

                      ( Get ( CurrentDate ) - 30 ) > Invoices::InvoiceDate AND Invoices::PaidStatus = "UnPaid"

                      This works and I can see the amouts due at 30, 60 and more than 90 days. I did this by creating 3 on separate one row portals.

                      The only problem is that when I want to print an Invoice Statement for a specific client (whith all the outstanding invoices) I don't know how to show the amount due at 30, 60 and 90. In my "Invoice Statements layout I cannot create portal filters from "invoices".

                       

                      There is any way I could copy the results from this portal onto my layout call "statements" that shows records from invoices?

                      • 8. Re: Due Date Invoices
                        philmodjunk

                        In my "Invoice Statements layout I cannot create portal filters from "invoices".

                        Why?

                        On what table is the layout based?

                        Perhaps you need to base the layout on a different table or table occurrence.

                        • 9. Re: Due Date Invoices
                          Alejosor

                          Hi, I think I wasn't explaining properly waht I wanted to achive but found an easy way to show the Balance due at 30, 60 and 90 days in this link

                          http://www.dwaynewright.com/filemaker-thoughts/2011/3/13/creating-a-popular-filemaker-crosstab-report.html

                           

                          Thanks for your patience and help