Find last deposit
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?
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?
We are using both versions.
We have a Filemaker Pro 12 license on one computer and a FileMaker 13 license on another.
Well there's a lot more than two versions out there in use.
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::__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.
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?
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.
So there is no possibility for me to get this kind of overview? (client + last payment date)
Please read my posts again. You can get exactly what you are describing if you follow my suggestions.
Sorry I didn't get the layout creation first, but now I have got it. You rock :) !!
Retrieving data ...