4 Replies Latest reply on Mar 7, 2009 3:51 PM by comment_1

    adopting IDs after the fact



      adopting IDs after the fact


      This is very much a newbie question.

      Background: Some years ago I, as a total neophyte, started a flat-file database for a tiny non-profit. Fields included names, contact info, volunteer activity, and monetary donations. Last year new blood correctly pointed out the need to place growing donation info into a second table, called Donations. New Blood did that, using name fields to relate Contact records to Donation records. That was, of course, a bad choice. Now we have a fragile relationship between ~1500 Contact records and ~1700 Donation records. 
      I want to change to a relationship based on ID fields. First I would create an auto-enter Contact ID field. Then I would populate existing Contact records with a set of serial numbers, using Replace Field Contents. Next I would create a Donations ID field.
      This is the point at which I lose focus: How to populate the new Donations ID field?  Once that is done I would redefine the relationship (surely needing advice to do it right) to one based on IDs, not names.
      So, hoping this is the right question, how should I populate the Donations ID field? I guess brute force would work: Wade through every Donation record one by one, typing in the appropriate contact ID. Is there an easier way?  Do I have a large conceptual blind spot? We're using FM7.  

        • 1. Re: adopting IDs after the fact


          We did this the following way:

          As always, back up first so you can recover.


          Using the relationship you already have with the name fields :


          Make a new List/Report format layout in the Donations table and put Donations::IDfield and Contact::IDfield in it.  Donations::ID should be blank for every record at this point.

          For my comfort I would also put both Donations::name and Contact::name to assure things line up.


          Write a script which goes to your new layout, find all records, go to record (first), loop, insert calculated result = Contact::ID into the field Donations::ID, exit loop after last, end loop. 


          Run the script.  It will go to your layout and insert the Contact::ID into each Donations::ID based on the existing name match.  When the script is done, simply scroll down the list of records and you should see matching pairs of names and matching pairs of IDs.  You will now be able to create a relationship between the two ID fields which will mimic the existing name field relationship.  Just make sure to use the ID relation in the future. 


          Hope this helps.



          • 2. Re: adopting IDs after the fact

            Thanks, Erich. I'll give it a try.


            • 3. Re: adopting IDs after the fact

              My file has two tables, named Main (for contacts) and Donations. Each contact record may be related to one or more donation records. My objective is to redefine the relationship so it's based on record ID, not name.


              All contact records have been assigned an ID (Contact_ID field in Main). In response to earlier advice from Erich Wetzel (see discussion), I wrote a script to copy Main-table IDs to an ID field in the name-related Donation-table records.


              I followed Erich's step-by-step advice, but I'm inexperienced, this is my first cut at scripting, and I was definitely feeling unsure.


              The script failed. It succeeded in inserting contents of the first Contact_ID field into the Donations_ID field of the first related Donations table record, then fibrillated until I escaped from the script. Any help will be welcome. I'll key in the script steps below.



              Script name: Fill Donation_ID fields

              Go to Layout ["Donation test A" (Donations)]

              Show All Records

              Go to Record/Request/Page



                  Insert Calculated Result [Donations::Donation_ID; Main   Table::Contact_ID]


                  Exit Loop If [Last ( repeatingField )

              End Loop

              • 4. Re: adopting IDs after the fact

                It should be:


                Go to Layout ["Donation test A" (Donations)]
                Show All Records
                Go to Record/Request/Page [First]
                Set Field [Donations:: Donation_ID; Main Table::Contact_ID]
                Go to Record/Request/Page [Next, Exit after last]
                End Loop

                Or simply:

                Go to Layout ["Donation test A" (Donations)]
                Show All Records
                Replace Field Contents [Donations:: Donation_ID; Main Table::Contact_ID]



                Make sure you have a backup.