1 of 1 people found this helpful
If the records in table A are still supposed to be child records of those two other tables, then you still need to set the two foreign keys. That doesn't collide with the fact that each record in tableA now has its own, unique ID (where previously maybe only the combination of those foreign keys was unique).
What were you using for your key field before you switched to seial #s?
If that field was unique, one thing you could do is to create a value list, based on your serial # key field, but also show the field that the user is going to know, and optionally only show that 2nd field. If you set that field to a pop-up menu, then then the user only sees the 2nd field's values, even though they are actually setting the match (foriegn key) field to the ID #. If that 2nd field isn't unique, this can get you into trouble, as only 1 of the values will show up.
You could also set a script to open up a "picker" window that will present the user with choices, then grab the ID field, based on that selection and set that into the field. Depending on how many values we are talking about, there are a number a strategies for doing this. Do some searching on the forums on picker windows and possibly virtual lists.
The method I usually use is to creat the child records from the parent record using a scripted process that sets a variable to the parent key ID, goes to the child layout to create the record, sets the foreign key to that variable, then returns the user to the parent layout. I usually freeze the window and open a 2nd window for this, then close it and refresh the window at the end of the script.
Since you have 2 parent keys to set, this may not work for you. Is this a join table? If so, a picker strategy may be your best bet.
I used the Value List idea and it works perfectly - thank you!!
I think what I might not have made clear is that there was a long list of foreign keys for connection to both Parent tables and the user wouldn't know what Foreign Key number connected to what record.