1 Reply Latest reply on Jul 28, 2011 8:34 AM by philmodjunk

    Modifying related records



      Modifying related records


      I am trying to find out how to modify records that are feeding into another record, through the record recieving the information.  I know it sounds weird at first but I can't be the first person to try this so I would appreciate any help offered.


      I have a database with the ultimate goal of producing invoices and tracking spending.  This part is working fine.  Where I run into a problem is in the Budget/Task table.  I have several different Budget/Tasks.  Each Task also has a single University, Contract Number, Buyer, and Principal Investigator (PI).  Each PI has a single University.  Universities can have multiple PI's, however.  

      In the Budget/Task table these 4 fields are filled through relationships:

      1.  I have the calculation of the PI's name feeding from the PI table

      2.  I have a calculation of the Universities abbreviation feeding from the University table

      3.  I have a Contract Number feeding from its own table

      4.  I have a Buyer's Name feeding from its own table

      Each of these tables contain more information than just the bits I'm pulling for the Budget/Task table but I would like to be able to create a new Budget/Task and enter the information that would normally be filled by these relationships into the empty fields and have a new record automatically created in the related table (i.e. enter the Contract Number on the Budget page and have a new record created in the Contract Number table).

      Is this possible?  Please help, I am so close to having this finished and saving everyone at the office a lot of work.

      Thanks in advance!


        • 1. Re: Modifying related records

          I don't think you are as close to being finished as you think. Your relationship would be better off with some key changes.

          You have a Budgets table that links by a name field to other tables. Linking by such name fields is very problematic. Names are not unique. Names are subject to change in the future. If you mis-enter a name in one of these fields used as relationship keys, create some related records and then discover the error, correcting the error can break the record's links by this name to other records.

          The records should be linked by number fields that auto-enter a serial ID number on the "one" side of the relationship to a matching number field (without the auto-entered serial number setting) on the "many" side of the relationship.

          Example: as I read your Relationships Graph, you'll likely have many budget items for a given university record. Define a UniversityID field as an auto-entered serial number and link it to a number field in budgets of the same name. When you need to refer to the University Name and other fields in the University table from a Budget record or layout, you can do so directly via this relationship. A calculation can refer to Universities::University name to refer to it's name. If you put Universities::University Name on your layout, it will display the University Name for the record related to your budget record.

          When you create a new record, you set up your Budget::UniversityID field with a drop down list or pop up menu of University ID's in field 1 and University Names in Field 2. You can then select a university by name from this list and the value list will inter the required ID into the field.

          If you are on the budget layout and you need to create a new university record, you'll have a problem as you'll need to create a new record in the university table before there is a UnivesityID number that you can use to link it to your current budget record. (The same problem exists when you use the University's name to define the relationship.) The solution is to switch to the University Layout, create the new record, then take the ID number of the new record and return to the Budget layout where you enter this ID number. This can be scripted:

          Go To Layout [Universities]
          New Record/Request
          Set Variable [$UniversityID ; Universities::UniversityID]
          Go To Layout [Original Layout]
          Set Field [Budget::UniversityID ; $UniversityID]

          Note: this does not enter any data into the new record in Universities. With this script, you would need to either place fields on your Budget layout from the Universities table so that you can enter new data, such as the name into these fields, or you'd have to switch over to a Universities layout and update it later.

          The alternative approach is just to use a button to switch to the Universities layout. After creating the new record and entering the data, you can then switch back to Budget and select the new Univerity layout from the drop down or pop up menu.