You can link a table to itself using a second occurrence of the same table, but that may or may not be the best approach here. It depends on your data and how you need to manage the data being looked up. A seperate table where you have exactly one record for each set of values "looked up" can be much better in terms of managing those values. But a self join that allows a table to look up data from itself also works and sometimes makes for a better option as you can add new data to be looked up automatically just by adding new records to the same table.
You can see this link for two ways that data in a related table (whether the same table using that second occurrence or a different table makes no difference in how the methods described in this thread work), can be looked up: Auto Fill
Option 2, the method that copies data from the look up table sounds like what you need here.
Ok. Please explain what I am missing. I have two tables. I am entering data about a silhouette. At one point I need to choose a description number. Choosing the number populates the description field.
This works fine. My question is what if I enter a description number that doesn't exist? I want it to add that number to the related table and also update the record with the new description so that I can then select that description number in future records.
You'll need a script for that. I recommend that such a script ask permission to add the new record in the related table so that typos don't automatically generate new records in the related table.
Say your relationship and tables look like this:
Main::_fkSilhouetteID = Silhouette::__pkSilhouetteID
and you format _fkSilhouetteID as a drop down list of ID's from the Silhouette table.
Then the OnObjectSave script trigger might perform this script:
If [Isempty ( Silhouette::__pkSilhouetteID ) // there is no related record with this ID ]
Show Custom Dialog ["No Slihouette numbered " & Main::_fkSilhouetteID & " can be found in the Silhouette table. Make a new Silhouette record?"]
If [ Get ( LastMessageChoice ) = 1 // OK was clicked]
Set Variable [ $SilID ; value: Main::_fkSilhouetteID ]
Go to Layout [Silhouette]
Set Field [Silhouette::__pkSilhouetteID ; $SilID ]
Go To Layout [Original Layout]
And to enter the description? You can capture that description in an input field in the custom dialog and then enter it into the Silhouette record just like the script does the ID number or you can place the field from Silhouette directly on your Main based layout and then you can simply edit this description field directly.
This looks great. I also set the script to show a dialig box to capture the description if it isn't in the database but it doesn't update the data for the current record. For example, I create a desc for slhouette 12345. It asks me to type in a desc and it adds it to the record but since the description is based on a lookup of what was in the main::silhouette record when it was created it is stil blank. If I add another and use the same silhouette number, the description pops up but the previous record is stil blank unless I go in and select the silhouette number again. Is there a way to do a relookup?
Theres a menu option with that very name in the records menu and there's a script step of the same name, but it relooks up data for your entire found set.
But I think you just need to put a commit records step just before you return to your original layout. You can also remove the description field from your main table and just put the description field from Silhouette on your layout. I originally recommended using the looked up value option as you indicated that you wanted to be able to edit the field. I misunderstood--thinking that you sometimes wanted to change the data in the field to be different from the value put there by the look up. If you need the data to always show the description from the silhouette table, just use that field from your related table.