It would be logical to make your Client field(s) global, so that as you browse through various Client Records the same critera apply - unless, of course, you want the flexibility to set a different year and month for each separate record. To remove one level of error-possibilty I would test it using a single field (as opposed to a concatonation) of just 'November2009' directly. You can create the concatonation after you prove the principle works.
The characteristic you describe of the same record repeated in the portal suggests that you have not used EXACTLY the same relationship for portal relationship and the relationship from which you've picked the fields.
Are the fields in both calculations set to return a result type = 'Text'?
Your logic looks good to me.
Yup, I already tried just using a text field where I typed in the value, just in case it might have been some oddity with my calculations, but it gave the same result.
Is it because I used a second Billing table? When I tried to add on a second relationship to Clients between the two original tables (Clients and Billing) it told me I couldn't do that, so I said ok to their suggestion of creating a second billing table. I have to admit that's the part that's a bit confusing for me; if I create records in one of the Billing tables will it also show up in the other?
If that's not the way to do it, how would I add a second relationship between the two original tables?
Oh, something I do notice is that when the identical payments show up, it shows up the number of times that there are bills (I've got 5 test bills in the original Billings database, and there are 5 identical entries in the new relationship portal). I've tested it out, but the principle holds true regardless of how many bills are in there...
I think we have hit the problem.
You only have two Tables in your file: 'Clients' and 'Billing'. You have created a relationship between the two tables, say by linking ClientID to ClientID. When you only had one relationship, FM knew which way you wanted to find a record in the 'Billing' table when you looked across to it from the 'Clients' table. So it was happy for you to leave the names of those tables the same in the Relationships Graph.
Once you create a second way to link to the Billings Table from Clients, Filemaker has to know which way you mean when you effectively say 'Show me the appropriate records that are in the Billings Table'. It ask, "Well what do you mean by 'appropriate'? Do you mean the ones connected by a common ClientID? Or do you mean the ones connected by ClientID amd MonthYear?"
You have to be able to tell FM which to use, so it makes you name the table at the end of each relationship with a unique name. But they are not multiple tables, but called Teble 'Occurances'. You will have two tables: 1 Client, 1 Billing. But you will have 2 OCCURANCES of the Billing Table. Each will contain exactly the same data. The new name you chose (FM probably suggested 'Billing 2') is simply defining that as the 'new' way you find matching records in the Billing Table.
If that is what you have (you can see it in the Manage Database -> Relationships graph) then set the portal AND each field in it to be grabbed from the 'Billing 2' relationship shown at the top of the Specify Portal and Specify Field dialogue as you drag it on to the layout and into the portal.
That did it! I had forgotten to change the fields in the portal to be from Billing 2 when I changed the portal itself to derive from that table. Now it works like a charm.
Definitely one of the "doh!" moments.
Thanks so much!
A suggestion for a "better" date calculation. If your month calcualtion is only needed for filtering a portal, ignore this post. If, however, you want to set up a summary report where you sort and group your records by month (and this is a very useful report to have for this type of DB), try this calculation instead.
Date(month(yourbillingdatefield), 1, year(yourbillingdatefield))
Set this calculation to return "date" instead of the default "number" setting.
Not only can you use this field as your "filter" key, but if you sort by it, will correctly group and sort your records by month in correct ascending or descending order--unlike the text based "month name" calculation you are currently using.
For an example of how to set up an auto-enter calculation on a date field for your matching global field, see this link: