Have a look at the ValueCount ( ) function.
Structurally you're still missing some pieces to your puzzle though.
Your relationships should look something like:
Contacts ---<- Donors ->--- Donations
Contacts can have many donation requests, and donations can have multiple donors. So each donor instance (or request as you call it) links to both a single donation, and a single contact.
The way you currently have it, you'll have duplicate contacts in your requests table.
You can try to use ExecuteSQL in a calculation field in Donations Table.
ExecuteSQL ( " SELECT count(donors) FROM Donations " ; "" ; "" )
Accidentally, it resolves duplications and deals with multi-valued fields
Although, I think Mike Beargie is right and you probably need to look into your relationships setup.
Sorry about that, you are correct in fact, this is exactly what my relationships look like.
Unfortunately this gives me an incorrect figure of "1" when I use:
ValueCount(Donation Request::Customer ID)
inside of the Donations table. Is this what you mean?
Thanks for the tip nicolai, unfortunately this function only works if I use it inside of the Donation Request table. It works perfectly there, but if I look that value up from the Donations table it is a static number rather than a dynamic number according to the visible found set.
When I use this function from within the donations table it returns a "?"
@ugaphotog This is a really interesting function, thanks for forwarding, although in this application I can't figure out how to use it without causing duplicate values to be counted, since I'm using it in a list layout.
No. You need to use field where the name of the person is, not the ID.
But if you're looking for unique values only, then you'll still have a problem because the ValueCount ( ) function will not look for that. It only counts the number of values, not whether they're unique. That's assuming you don't already account for that elsewhere, of course.
Hmmm, yes my thoughts exactly. Could you elaborate on your last sentence?
Update: I got this to display a figure, but I realize it's only checking the first value of each field
returns a count of 2 donors instead of 3
I'm not 100% sure I understand your donation request vs donation problem, but I think you're trying to find the count of unique values in a given field. My example (so you can compare it to yours): A library wants to know how many unique people are checking out books. Tom may have checked out 87 books in the last year, Maisie checked out 3, etc., but we want to count Tom and Maisie each as just 1 individual patron or checker-outer. Hope you're following me so far — and hope my example is pertinent to your problem.
If so, here's one fairly simple way to do it.
1. Create a value list based on values in this field. Since we're going to have to refer to this value list by name in a calc formula, I always name such a value list carefully: "NAMES | DO NOT CHANGE".
2. Now create your calc like this:
Let ( n = ValueListItems ( Get ( FileName ) ; "NAMES | DO NOT CHANGE" ) ;ValueCount ( n ))
The value list gives you (indirect) access to the index of that field. The functions ValueListItems and ValueCount then give you the answer to your question. NOTE the use of "Get ( FileName)" to refer to the present file.
The main weakness of this approach is that it will break if you change the name of the value list. That's why I advise naming the value list with a warning not to change its name — and placing a note in your developer notes and in the script or calc so that you have some other info about the original name of the value list, if your function breaks in future.
There are ways to improve this approach, but that's the basic idea. "More than one way to skin a cat." True of almost everything in FileMaker.
It's not a weakness of the particular solution I described that it relies on the values actually representing unique items — I expect virtually every solution to the problem will do so. But in any case, it's better to count IDs rather than actual names. I mean, if you're looking for unique values in a field that anybody can type anything into, you might count somebody like me two or three times, because I might be in there as
- Will Porter
- William Porter
- William M Porter
but it's all just little ol' me. Just something to be aware of.