2 Replies Latest reply on Jan 3, 2011 8:52 AM by philmodjunk

    Creating a new record via a portal to a different table

    MiloMV

      Title

      Creating a new record via a portal to a different table

      Post

      I have created a database for our foundation to keep track of single  and multiple donations and pledges given by our patrons. It now has three tables: 1)  DONORS, which holds all the donner contact information including the two match fields "Firstname" and "Lastname." 2) DONATIONS,  which holds the donations given with a "Date" field and and  "Amount" field as well as the two match fields for the Donors table. 3) PLEDGES which holds pledge payments received with a "Date" field and and  "Amount" field as well as the two match fields for the Donors table.

      I linked the DONORS and DONATIONS tables with the matchs fields and opened a portal  in the DONORS table that lists all the dates and donation amounts  brought in from the DONATIONS table. I have it set up so you can enter  the date and donation amount right in the portal on the DONOR table and  it creates the corresponding record in the DONATIONS table.

      I have also linked the DONORS and PLEDGES tables with the matchs fields and  opened another portal  in the DONORS table that lists all the dates and pledge amounts  brought in from the PLEDGES table. I have it set up  so you can enter  the date and pledge amount right in the portal on  the DONOR table and  it creates the corresponding record in the PLEDGES table. So far so good. But here's my problem.

      I need to have a separate table for the pledges so I can perform calculations on them seperately from the outright donations (such as subtracting the running total of a client's pledges received from the actual amount he pledged, etc.). However, once a pledge payment is received and filled into the portal of his pledges, it now becomes an actual donation as well and I would like to have it automatically added as a record in the DONATIONS table.

      I have been trying to make it so that once you enter the date and amount of a pledge payment into the pledge portal, it not only creates a record of it in the PLEDGES table, BUT ALSO creates one in the DONATIONS table. This should then also make it appear in the donations portal on the client's record. No matter what relationship connections I have tried, including using mulitple table occurances, I cannot seem to make this happen. I have named the date and amount fields the same in both the DONATIONS and PLEDGES tables so they would match. Hope I have been clear in what I am trying to do.

      Thanks in advance for any help with this. Milo

        • 1. Re: Creating a new record via a portal to a different table
          Rajkumar

          for this i think u can make use of script trigger "onRecordCommit", which will run a script.

          In the script, u can check for pledge amount to be >0; if it is, then go to layout "Donations" and create a record, and it'll automatically create a portal row in "Donar" layout. Anotherwise simply exit the script.

          Try this, hope it'll work.

          • 2. Re: Creating a new record via a portal to a different table
            philmodjunk

            This can be scripted, if your script also puts the needed match data in the new record as well as the donation amount, but you might give careful thought to putting pledge payments directly in the donations table since that's what they are. One table can record the amount pledged and your donations can then record all donations whether or not they are due to a pledge drive. You can add a drop down in your donations table that allows you to select the applicable pledge record for that donor if you might have multiple pledge payments for different pledges from the same donor. And there are a number of ways that you can work with subsets of your donation records in order to see just the donations that apply to a specific donor pledge or even a specific pledge campaign without having to put the data in two different tables.

            A few other comments on your design:

            Matching donors by first and last name is not a good idea. Names are not unique and donors may change their names at any time. Names can also be entered with incorrect spellings that you find you need to correct at a later date. In all those cases, changing a name breaks the connection to the related pledge and donation records unless you very carefully update them first. Use a serial number in your donors table to uniquely identify them and use this field as your Primary Key to link to your related tables and you will avoid the issues inherent to using names for this.