5 Replies Latest reply on Mar 15, 2011 2:27 PM by philmodjunk

    Managing contact table and donation table through donor ID relationship

    Vellouria

      Title

      Managing contact table and donation table through donor ID relationship

      Post

      Hello,

      This is my first time using the FM Pro forums :) I am running FM Pro 9 on my Mac.

      I am managing a table of our donors and contacts ("Sponsors Table", where each contact is a separate record), and a table of Donations made to our charity ("Donation Table", where each donation is a separate record) in a single database.

      As it's set up now, the donations are linked to contacts in the "Sponsors" table by a unique serial ID. When creating a new donation record, I've set it up so that I can enter the unique serial ID of the contact and their first name, last name, and organization automatically appear.

      My question is this - sponsors or sometimes deleted from our database if they haven't donated in a long time. However, I still need to print reports of all the donations that happened in previous years, including those by sponsors who have since been deleted. Because the donation records are pulling info from the sponsors table, when a sponsor is deleted their first name, last name, and organization will also be deleted from the records of all of the donations they have made, correct?

      How do I a avoid this problem while still linking these tables through an ID? any ideas? Is there a way to freeze the information once it's entered into the donation record so that even if the information is deleted from the sponsors table it will still be in the donation records? However, this option would also not allow me to fix errors (i.e. the mispelling of a name) in the sponsors table and let it also update each corresponding donation record in the donation table.

      I am a relatively new user of FM Pro. I understand portals, etc, and am pretty computer savvy. Any ideas on what to do? Please advise!

      Thank you!

        • 1. Re: Managing contact table and donation table through donor ID relationship
          philmodjunk

          Best solution is to not delete these individuals from the database so that their info remains present for report purposes. You might consider adding a status field to your sponsors table where you can select "inactive" or some such value instead of deleting the sponser from the table. You can then perform finds that omit "inactive" sponsors when you need to work only with active sponsors. This also makes it possible to restore someone easily to active status without completely re-entering their info and also leaves them with their original ID. 

          • 2. Re: Managing contact table and donation table through donor ID relationship
            Vellouria

            Thank you, Phil, this is what I suspected. It is easier for the purpose of mail merges, etc to delete them, but I suppose every time we do a mail merge we will just have to remember to exclude people through a find.

            I will make an active/inactive field or something similar. Thanks!

            • 3. Re: Managing contact table and donation table through donor ID relationship
              philmodjunk

              Such an 'exclude' can be automated so that you don't have to remember each time.  

              Many developers specifically design their system to prevent the actual deletion of records from key tables, 'marking' them in some way and then designing the interfaces to hide them from the user. The user thinks they have deleted the record, but the devloper (or a script he/she sets up for the purpose) can magically 'undo' the delete and bring back an accidentally deleted record.Wink

              • 4. Re: Managing contact table and donation table through donor ID relationship
                Vellouria

                Thanks Phil. Ideally, I'd like to run a script trigger onlayoutload that excludes the Inactive Sponsors, but I'm running FM Pro 9 and I don't think it includes script triggers. I don't want to move them to a different table (an archive table, for instance), because the donation records are still set up to pull information from the sponsors table, and not the archive table.

                The easiest solution I can think of for now is to write it into a script, and include all of the finds/excludes/whatever for that mail merge in a script so I don't have to keep re-writing them. The inactive sponsors will of course still show up when simply browsing records this way.

                If there is a better solution, please advise. Thank you!

                • 5. Re: Managing contact table and donation table through donor ID relationship
                  philmodjunk

                  Given the limitations of version 9, that's probably the best approach. You can tack on code like this to the end of the find script:

                  //put steps that set up find criteria specified by user here
                  New Record/Request
                  Set Field [Sponsors::Status ; "Inactive"]
                  Omit Record
                  Set Error Capture [on]
                  Perform Find[]

                  That adds an "omit" request to the end of anything else specified as criteria in the script.