3 Replies Latest reply on Nov 20, 2009 9:52 AM by dnbreaks

    Lookup last payment received date



      Lookup last payment received date


      I am using FM Pro 8.5v1 on OSX 10.5.8. The FM database I'm using was created by someone else and I have fairly limited knowledge/experience with databases. What I've been able to do so far has been pretty much done through trial and error, with some luck thrown in. The issue I've come across is I'm trying to create a field that will lookup when we last received a payment from a customer. Right now I have all the fields to make up a statement/invoice and a "paid date" for each one of those invoices. So say ABC Inc. has been billed four time in the past and all of them have been paid with paid dates of 7/20/09, 8/5/09, 8/16/09, and 9/27/09. On the next invoice I create for them I want to be able to show that the last payment received was on 9/27/09. Any help would be greatly appreciated. If more info is needed, please let me know. Thanks.

        • 1. Re: Lookup last payment received date

          You need a field to uniquely identify each customer, ideally this is NOT the customer's name as names change from time to time. Let's assume that you've set up a serial number field named CustomerID.


          Your layout refers to a Table in Layout Setup... We'll call it Invoices for this example.  

          Make this relationship:


          Invoices::CustomerID = Invoices2::CustomerID


          To make it, find the table's TableOccurrence box in Manage Relationships | Graph and drag from customerID outside the box and then back to the same field. A dialog box will pop up so you can create Invoices2, a table occurrence that points to the same table as Invoices. This is called a "self" relationship.


          DoubleClick the relationship line and click the sort check box on the Invoices2 side in the dialog box that pops up. Specify that the records sort by your Payment date field in Descending order.


          Now you can place the Payment date field from Invoices2 on your layout and you'll see the most recent payment date for the current customer.




          You'll have to look over your tables and relationships carefully to see if this works as I've posted or if we'll need to make changes. You might have your payments listed in a related table linked to your invoice record, for example, and that would change the details some, though the technique still can be made to work.

          • 2. Re: Lookup last payment received date
               You can a one line portal that is sorted descending on payment date. Put that on the layout.
            • 3. Re: Lookup last payment received date
                 Thanks PhilModJunk and mr_vodka. I created the self relationship, but ended up using the unique field Contact ID instead of Payment Date and then created the one line portal with the date in descending order. It appears to be displaying as I hoped. Thanks again.