5 Replies Latest reply on Oct 19, 2009 1:42 PM by philmodjunk

    Grant Balances and Portals



      Grant Balances and Portals


      I am making a client database for a non-profit.


      The way I have this organized, I have a separate table for utility, rent, and security deposit payments sent to clients.  Each table contains the fields FundID, FundName, InitialAmount, and Balance, and the records are a list of available grants.  Each payment can be taken from any grant, so you might have 3 checks all being taken from the ABC grant, one for each utilities, rent, and security.  We keep the grants separated into ABC(utility), ABC(rent), ABC(security), and keep track of separate balances for each.


      First of all, is this a stupid way to organize this?


      Second, Here's what I'd like to do:

      Create a layout that displays select grant balances for all three types of payments. For example, I would like to show FundID 1 balances for utilities, rent, and security, FundID 2 balances for utilities and rent, and the FundID 4 balance for security.


      I have no idea how to go about doing this.  I've been playing with portals and the "initial row" feature, but it's not really working.  Any suggestions would be greatly, greatly appreciated.


      Thanks so much for everything.

        • 1. Re: Grant Balances and Portals

          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

            $900  5/1/2009

            $900  6/1/2009

            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.

          • 2. Re: Grant Balances and Portals

            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

            Fund3                                $5,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.

            • 3. Re: Grant Balances and Portals

              It'll be a lot easier to set up if you structure your report like this:



                Utilities  $1,000

                Rent      $3,000

                Security $6,000

              Fund 2

                Utilities   $0

                Rent      $4000

                Security $6,000

              Fund 3

                Rent       $5,000


              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?

              • 4. Re: Grant Balances and Portals

                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...


                Thanks again.

                • 5. Re: Grant Balances and Portals
                     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.