13 Replies Latest reply on Aug 31, 2011 11:32 AM by philmodjunk

    Personal and family records

    DonaldBennett

      Title

      Personal and family records

      Post

      I'm in charge of a youth program. I have a database set up for persons and related families called people. No problems there (anymore). I've set up a separate database to track events we have. Some are fundraisers and some we pay to attend. This database is set up to track how much each youth earns at fundraisers and how much they spend at other activities. I can always tell the parents how much is available to spend. The database references the person table in the people database. It's working perfectly.

      The parents have asked that I track money earned and spent on a family basis instead of a personal basis. For the life of me, I can'y figure out how best to implement this. I don't want to start adding fields to the external people database as I use it for various purposes and I'd like to keep it as clean as possible. Compounding the problem is the fact that some youth are in multiple families.

      I'm open to any ideas you might have on which direction to take and/or methods to use. Attached is my current relationship graph.

      Thanks,

      Don

      relationships.jpg

        • 1. Re: Personal and family records
          rjlevesque

          I would use data separation in this case to make your job easier. Simply build your new solution and add both your other solutions as external files to your new solution. Just put all your layouts, scripts, calulations into your new solution. The new solution will pull all data from your existing solutions, it will act as nothing more than an interfact to access your already existing data.

          • 2. Re: Personal and family records
            DonaldBennett

            I'm sure you're right, but why would it be easier creating a new database with the same info than building on the existing one. I'd still have the problem of figuring out how to tie all the varied relationships together. My sense is it would add another layer of complexity. Could you maybe be a little more specific? I'm still pretty new at this.

            • 3. Re: Personal and family records
              philmodjunk

              RJ makes a good suggestion, but I recommend solving the data relationships first. Then you can take a swing at converting to a data separation model as a future improvment if you want to.

              I've been dealing with "kids with multiple families" in a new database project that I'm working on also. I suggest showing us how you managed that issue in your database so that we can take a swing at related fundraising data to a child's family via the link to the child's record.

              (In my system, I have a Families table and a people table with a join table in between to handle the multi-family issues.)

              • 4. Re: Personal and family records
                mgores

                I would be interested too in how you do the financial part.  I am trying to come up with a solution for my sons scout troop and we have similar fund raising or fund spending events and need to keep track of each scout's money.

                • 5. Re: Personal and family records
                  DonaldBennett

                  I too have a family table, a person table,and  a join table. In the join table I have a checkbox value list to show their family position; Head, Spouse, or Child your idea). I have portals setup in the family to show family members based on the value list checkoff. I'm still working on how to easily relate a person to two families. I only have one youth that fits that at the moment so it's kind of on the back burner.

                  • 6. Re: Personal and family records
                    philmodjunk

                    Then you can place external data source type table occurrences of Families and the Person_Family join table in your fundraising file just like you have done with your person table. And you can then replicate the same relationships.

                    If you then define summary fields in EventPeopleJoin to compute the needed totals (You may already have these), you can place these fields on a layout based on families to show the totals for the entire family rather than just one person.

                    • 7. Re: Personal and family records
                      DonaldBennett

                      Back to the book. I've avoided summary fields so far. I got my totals using calculations with SUM (). I'll let you know what happens.

                      Mark,
                      If you'd like a copy of my database(s), just let me know. My son's a scout, as was I. No charge, but no guarantees either! Wink 

                      • 8. Re: Personal and family records
                        philmodjunk

                        Sum also works, but then you'd be adding a calculation field to the Families table--something you wanted to avoid doing.

                        • 9. Re: Personal and family records
                          mgores

                          Don,

                          Would love to have a look at it.  Have been working on data forms/entry for testing at work and switching over to tracking scouts and money is giving me trouble for some reason. Laughing

                          • 10. Re: Personal and family records
                            DonaldBennett

                            Mark,

                            Email me and I'll send you a link to it.

                            • 11. Re: Personal and family records
                              DonaldBennett

                              Your idea worked, PhilModJunk. I've used the summary in several calculations and I've noticed that some of the fields don't update until I've switched layouts and back. Is this usual? Is it possible to write a script to force an immediate update?

                              Thanks,

                              Don

                              • 12. Re: Personal and family records
                                rjlevesque

                                Food for thought - In the data sep model you don't exactly replicate data...you create a solution that can group together, or display and play with the data from any sources you like. In other words think of the new solution as a sort of viewer where you can pull data from any source you say, manipulate it, calculate it, etc. You're not storing anything in that new solution necessarily.

                                This way you are able to do whatever you want with your existing data, without worrying about messing anything up sort of speak in the process of adding new features, enhancements, etc.

                                • 13. Re: Personal and family records
                                  philmodjunk

                                  Don,

                                  You've put your finger on the key shortcoming of using summary fields. They often fail to update when you modify a value in the portal that affects the summary's computed value.

                                  Depending on the format of the field, you can use script triggers such as OnObjectModify, OnObjectSave and such to run this script:

                                  Commit Record
                                  Refresh Window Contents