6 Replies Latest reply on May 10, 2011 11:41 AM by jetmaster

    Data Entry from one table to another

    jetmaster

      Title

      Data Entry from one table to another

      Post

      I'd like to have a button that runs a script that does this:

      puts the value of a field in one table into the same field in a related table.

      I have the following fields: kp_item_ID and item_name in the first table and kf_item_ID and kf_item_name in the second table.

      What I want to do is put the value of item_name from the first a record in the first table into an item_name field in a record in the second table.

      Hope this is clear....

        • 1. Re: Data Entry from one table to another
          philmodjunk

          Easily done with a single script step, but this is very rarely needed.

          Is this a one to one relationship or one to many? (Is there only one record for a given ID in the second table or could there be more than one with the same kf_Item_ID value?)

          If one to many, will Item_Name be the same for every related record?

          Assuming yes to that last question, here's how to avoid needing to move a copy of the name from table 1 to table 2:

          Both these options do not require any scripting. You'll need to read them over and decide for yourself. If neither appear to work for you let me know and we'll take another look.

          1. On every layout or portal to table 2 where you need the item name, add the Item_name field from table 1 and the correct name will be displayed. With this approach, editing the name in table 1 automatically updates the name shown throughout your database.
          2. There are cases, where you want the name to be entered in Table 2 as a "snapshot" to record what text was in the name field in table 1 at the time the related record in table 2 was created, but you don't want the name to update when the name is updated in table 1. In this case, you can define your name field in table 2 to use the looked up value auto-enter setting to copy the matching name from table 1.

           

          • 2. Re: Data Entry from one table to another
            jetmaster

            I'll try and be more specific.

            The first table is a database of laboratory equipment. Each record is a separate item. Many items require routine maintenance or emergency servicing. For that purpose there is a separate table for each service instance. One of the fields in the service table has to be the equipment item as I need to show the service history for each item via a portal to the service table from the item record. As each item may have multiple servicings the relationship is one to many.

            I could have the user select an item while on the service layout from a menu but there are way too many items to have in a menu. So what I thought I'd do is have a button on the item table/record which creates a new record in the service table and inputs the item name from the sending record into the newly created service record.

            Follow?

            • 3. Re: Data Entry from one table to another
              philmodjunk

              Yes, but have it just input the item ID, you don't need a separate copy of the item name. As long as you have the Item ID, you can use your relationship to access and display the item's name as well.

              Two other ways you can link a maintenance record to a specific piece of equipment:

              1. Use a portal to the Maintentance Records on your Equipment table. If You enable "Allow creation of records via this relationship" for the maintenance table in this relationship, You can just enter data into the rows of this portal and the matching ID number is entered into the new record for you.
              2. On a Maintenance layout, set up the EquipmentID field as a drop down list or pop up menu with a value list that lists all equipment ID's from the EquipmentID field of the Equipment table in column one and the name field from this same table in column 2. When the user accesses the field they can use the name to select the item, but the value list enters the ID. You can then place the name field (if needed) from the Equipment Table on this layout and it will show the correct name once you select the ID number.

               

              This is keeping with a basic rule of relational databas design. Never store identical copies of the same information in two different tables. Instead, store it one table and use relationships to access it whenever and wherever you need access to it.

               

              • 4. Re: Data Entry from one table to another
                jetmaster

                I figured it out...I had done something similar before: Here is the script...

                Copy the field _kp_ID in table 1

                go to the table 2 layout

                new record

                paste value into _kf_ID

                works great

                • 5. Re: Data Entry from one table to another
                  philmodjunk

                  But I wouldn't use Copy and Paste script steps to do it.

                  Your user may have copied data for their own purposes to the clipboard. Your script will overwrite their data with this ID number and they'll find that confusing and possibly irritating.

                  And both steps fail silently if the referenced field is not on the current layout. Thus, modifying the layout at some point in the future could have the unintended consequence of breaking this script.

                  Do this instead:

                  Set Variable [$ID ; Equipment::_kp_ID]
                  go to layout
                  New Record/Request
                  Set Field [Maintentance::_kf_ID ; $ID]

                  and all of the above issues are avoided.

                  • 6. Re: Data Entry from one table to another
                    jetmaster

                    Ok, sounds like a good idea; I changed to the set variable approach...thanks