8 Replies Latest reply on Jan 16, 2014 9:21 AM by IlseVerhaert

    Find last deposit

    IlseVerhaert

      Title

      Find last deposit

      Post

           Hi -

           I have a table in which all payments by all clients are reported.

           Would it be possible to perform a find in which I get the last payment per client?

           Thanks!!

            

            

        • 1. Re: Find last deposit
          philmodjunk

               I don't see a way for a simple standard FileMaker find to do that, but there are alternative approaches that may work. Some require FileMaker 12 or 13 to do.

               What version of FileMaker are you using?

          • 2. Re: Find last deposit
            IlseVerhaert

                 We are using both versions.

                 We have a Filemaker Pro 12 license on one computer and a FileMaker 13 license on another.

            • 3. Re: Find last deposit
              philmodjunk

                   Well there's a lot more than two versions out there in use. wink

                   I was still waking up when I first read your post. On re-reading, I see that I was over complicating this. The following will work with all recent versions of FileMaker:

                   Presumably you have this relationship, though your table and field names may differ:

                   Customers-----<Payments

                   Customers::__pkCustomerID = Payments::_fkCustomerID

                   And presumably, you have a date field in payments to record the date a payment was received.

                   If, in this relationship, you double click the relationship line between customers and payments, you can open a dialog box where you can specify a sort order for Payments that sorts the related records by Date in descending order.

                   You can then set up a list view or table view layout based on Customers and include fields from Payments on this layout/in this view. When you add fields from a related table and don't put them in a portal, the "first related record" will supply the data shown in the fields. By specifying this sort order, you make the most recent related record the "first record".

                   Note: setting up this sort order at the relationship level may affect other parts of your database. To keep them unchanged, you may need to add a new Tutorial: What are Table Occurrences? for payments so that you can use it to set up this sorted relationship while keeping the rest of your database unchanged.

              • 4. Re: Find last deposit
                IlseVerhaert

                     Ok thanks, I got them sorted. But how can I get an overview of each client and only the date of the last payment date via performing a find?

                     So only the 'first record' per client is shown?

                • 5. Re: Find last deposit
                  philmodjunk

                       No find is used in this method. You just go to this  layout and this data is displayed on it due to how the relationship is set up.

                  • 6. Re: Find last deposit
                    IlseVerhaert

                         So there is no possibility for me to get this kind of overview? (client + last payment date)

                    • 7. Re: Find last deposit
                      philmodjunk

                           Please read my posts again. You can get exactly what you are describing if you follow my suggestions.

                      • 8. Re: Find last deposit
                        IlseVerhaert

                             Sorry I didn't get the layout creation first, but now I have got it. You rock :) !!