This doesn't answer your question specifically, but I think you might need to consider the architecture of your solution if you want it to be robust and scalable for the future. If there is a good reason for the architecture you have set up then I apologise. FileMaker is the king of workarounds and it may be that your method of doing it suits your business case.
If I understand you correctly your client records (each record in the client table representing 1 client) have multiple payments and payment dates in the repeating fields. This doesn't seem wise. A better arrangement would be to have payments in a separate table related to the clients by the client ID. Then using a global field to find the specific year you can create a new relationship between clients and payments that will have summary fields to give you the data you need.
Thanks. That does make sense. At the moment I don't have the expertise to redo that, although I have had extremely minimal exposure to related tables. I will study up on that and try to redo the structure of my database. if you have any advice on where I can read up or learn about the basics of rated tables for this kind of use I'd appreciate it.
In the meantime it seems to be, speaking from relative ignorance, that I must be overlooking something simple with regards to my problem with this formula.
Thanks for replying.
All the best,
By the way, based on your suggestion, it sounds like I may need 3 tables, 1 with basic client information, a 2nd with payment related data, and a 3rd for keeping track of client sessions. I'm a dialect coach and keep track of the date, number and length of sessions, as well as how many sessions have been consumed of the package of sessions purchased. So does 3 tables sound right?
Hi Jon. Three tables sounds good by the way you have described it. I don't think you can go past the FileMaker Training Series when it comes to learning. The Basics version is free and if you find that useful and decide you want to go further then you can pay for the advanced. Database Skills, FileMaker Pro Training | FileMaker. Time you spend now on understanding how to relate tables to each other will have vast benefit for your database architecture. Better to get it right at the beginning before you have too much data in the database than try to solve it in a years time. Chris
It sounds to me like there could be a need for more tables.
A Client may sign up for Session Package
You track individual Session Attendance.
You track Payments.
Is the relation of payments to Session Packages strictly one-to-one? They have paid for the session package; or have not paid?
Or might there be multiple payments for the package?
Is there any kind of "catalog" or price list of Session packages?
Excellent advice. I'll do that. Thanks!
Thanks Bruce. I've actually started building 3 tables and have been experimenting with the relationships. I need to redo several formulas to make this work. And as Chris said, I need to study this all some more. I've been reading the Filemaker Missing Manual for some help as well.