You have too many tables. Instead of putting different types of grants in different tables, store all of these in one table and use an additional field "GrantType" to identify the type of grant (utility, rent, security, etc.)
Now you can use finds and sorts to select and group specific types of grants on a unified report.
You can also filter a portal so that only grants of a selected type are visible if that's something you need.
For your report, base it on this new unified table and set up a summary report with sub-summary parts. You can create a Sub-summary part to show data identifying each client and use additional sub-summary parts to group payments to a specific client by grant type with the individual payments listed below each such payment category:
Client John Smith -- first sub summary part
Rent -- 2nd sub-summary part
and so forth
Security --same 2nd sub-summary part
Client Joan Jones
Is this what you want. If so, you might want to look up summary reports in filemaker's help system to learn more.
Thanks for getting back to me so soon. I'll combine my tables as soon as I can. Things like that take me forever since I'm such a newbie.
So, yes, that's partially what I need to do. What I really need is a list of how much money we have left in each grant, divided into type, though. Something like this: (I hope my formatting doesn't get too messed up...)
utilities rent security
Fund1 $1,000 $3,000 $5,000
Fund2 $0 $4,000 $6,000
I ran into problems before because I wanted to display just selected funds on my "grant balances" sheet. The reason is that some funds are more important than others. I couldn't figure out how to just display Fund2, Fund4, and Fund8, for example.
I hope I made that somewhat more clear. Thanks so much for your help.
It'll be a lot easier to set up if you structure your report like this:
The other approach is also possible, but it takes a lot more work to set up and is much less flexible. Will the above report format be acceptable?
Yes, it would be alright. Eventually, I may want to change it, but the most important thing for now is the information.
And speaking of combining my tables...
Is it possible to make it so that payment information entered in one portal is automatically assumed to be a certain FundType? (UTILITY, RENT, SECURITY) For example, if we pay a client $100 out of Fund1 for utilities, I want the social worker to be able to just choose FundName in the "utility box" and not have to worry about figuring out and entering whichever randomly assigned "FundID" was given to Fund1-UTILITY (vs. Fund1-RENT, etc.)
Am I making any sense? I'm really sorry...
Yes, though it requires a more sophisticated relationship that includes "filtering" the portal to show only one type. You need to put together a good combination of Tables and Relationships before you go any further. If you already have data entered in multiple tables that should be merged into a single table, you can do this with the Import Records tool that's found in the file menu.