5 Replies Latest reply on Mar 25, 2009 1:05 PM by philmodjunk

    Invoice date comparison with single customer

    umcken

      Title

      Invoice date comparison with single customer

      Post

      I have created an invoice layout with portals for the customer and line items.  The invoice includes two dates - essentially date of creation and date of completion. When the same customer is on another invoice, I would like to compare the date of completion of THAT customers LAST invoice with the date of creation of the current invoice and perform a conditional formatting based on that comparison.

       

      Is this possible? 

        • 1. Re: Invoice date comparison with single customer
          philmodjunk
            

          Yes.

           

          As always, the challenge is in implementing the details. There are a number of different ways you can do this. Your specific database table, relationship and layout design may work well with the following solution or not:

           

          You need a field in your customer table that uniquely identifies each customer. A serial number field in your Customer table is the simplest option for this.

          You need a field in your invoice table that uniquely identifies each invoice--once again, I recommend using a serial number which you probably already have.

          Define a number field in your Customer table: InvoiceNumb 

          Whenever your user completes an invoice, set it up so that a script executes:

          Set Field [InvoiceTable::completionDatefield, get(currentdate)]

          Set Field [customerTable::InvoiceNumb, InvoiceTable::InvoiceNumberField]

           

          If you are using FMP 10, you might choose to use a script trigger to performt this script. With older versions, you can put a button on your layout and define it to perform this script.

           

          Create a relationship between your Customer Table and your Invoice table where CustomerTable::InvoiceNumb = InvoiceTable::InvoiceNumber Field.

           

          This requires creating a new Table Instance for your invoice table. In your relationship graph, you should see something like this:

           

          Invoice Table::Customer Number >----CustomerTable::Customer Number

                                                                      CustomerTable::InvoiceNumb-------------Invoice Table2::Invoice Number

           

          Where Invoice Table and Invoice Table2 are different "instances" of the same table.

           

          Now you can use conditional formatting expressions that refer to fields in Invoice Table2 in order to access information from the most recently completed invoice for the current invoice's customer.

           

          Hope that helps.

           

          • 2. Re: Invoice date comparison with single customer
            comment_1
               There is no need for a script trigger. (As an aside: you should NEVER depend on a script running succesfully for having correct and up-to-date data in your system).

            Define a self-join relationship of the Invoices table, matching CustomerID to CustomerID.
            Use a calculation along the lines of =

            CreationDate < Max ( Invoices 2::CompletionDate )

            for your conditional formatting.



            • 3. Re: Invoice date comparison with single customer
              philmodjunk
                

              comment wrote:
              There is no need for a script trigger. (As an aside: you should NEVER depend on a script running succesfully for having correct and up-to-date data in your system).

              Define a self-join relationship of the Invoices table, matching CustomerID to CustomerID.
              Use a calculation along the lines of =

              CreationDate < Last ( Invoices 2::CompletionDate )

              for your conditional formatting.

               

              Comment, I mentioned script triggers as an option. It can be useful for some FMP 10 solutions and not for others.

               

              I see no reason why your solution won't work as well as mine and has the advantage of eliminating the need for a script. I used simple field = field matching simply because I figured it was easier to explain and understand.





              • 4. Re: Invoice date comparison with single customer
                comment_1
                  

                PhilModJunk wrote:
                Whenever your user completes an invoice, set it up so that a script executes

                I can only repeat : you should NEVER depend on a script running succesfully for having correct and up-to-date data in your system. It doesn't matter if you trust the user to click a button, or a script trigger to run. Both can fail.


                • 5. Re: Invoice date comparison with single customer
                  philmodjunk
                    

                  Comment, until I ready your last post, I didn't understand your issue.

                   

                  No disagreement on my part, I agree.

                   

                  I've created and maintained a customer invoicing system since it was and FMP 3 solution. I was keeping my solution focused on the issue at hand.

                   

                  In my system, when the data entry clerk completes the invoice, the button they click triggers a script that first checks for errors and missing elements. It doesn't permit the user to change the invoice status to "complete" until this test passes.

                   

                  Script Triggers, in fact, are one of the new tools for catching data entry flubs at the moment they occur.

                   

                  I like your approach, which is why I gave you a Kudo.