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.
- 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.
- 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.
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.
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:
- 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.
- 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.
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
paste value into _kf_ID
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
Set Field [Maintentance::_kf_ID ; $ID]
and all of the above issues are avoided.
Ok, sounds like a good idea; I changed to the set variable approach...thanks