I want to update the Prison address in the Prisoners database by clicking a button "Update" or letting it automatically fill in.
Why “update”? If a prisoner “belongs” (is related) to a specific prison anyway, any address change for that prison can be seen from the context of the prisoner by simply displaying the address field(s) from Prison.
As an aside: what if a prisoner is moved to another prison? With only two tables, you will have to assign the prisoner to that new prison, which means overwriting the previous prison, and so lose historical data (the prisoner's “prison history”, and the prison's "prisoner history”).
Therefor, consider using a third, so-called join table: Prisoner --< PrisonerInPrison >-- Prison
In English: a prison can have many prisoners, and a prisoner can be in many prisons (though only one at a time, so you need to give a prisoner a “current prison”).
PrisonerInPrison now holds a complete history (past and present) of … well, prisoners in prison.
I have two separate databases one called Prisons and one called Prisoners.
The correct terminology would be “one database with two tables”.
yes I probably don't need to update, but I do need the prisoner's address details to reflect the address of the prison he is in. I don't need the history as you suggest because I already have a change of address option, which shows all the details I need. I just need the prison address fields to be populated when I select the prison from the drop down list in the prisoner's file. If the prison address changes then this should also change when I make changes to the prison address fields. I also need to be able to change the address manually because sometimes the addresses vary depending on the circumstance of the prisoner.
erolst is correct. IF you have a relationship between the two tables, any selection from the drop down menu can "show" the related address. can you post a screen shot of you Relationship Graph (tab under Manage Database)?
yes I probably don't need to update, but I do need the prisoner's address details to reflect the address of the prison he is in.
I answered this in the first part: if there is a relationship between prison and prisoner, you can display the relevant address fields from Prison on a Prisoner layout, and even change them there.
If you are using auto-entered keys for your relationships (as you should), and have a relationship like
Prison::id (primary key) = Prisoner::id_prison (foreign key)
• creating a value list as 1. field: Prison::id, 2. field: Prison::(name, show second field only)
• putting the field id_prison on a Prisoner layout
• formatting it as a popup with that value list, and …
• selecting a prison name
will actually enter the prisonID and assign that prisoner to that prison – so you can simply put related fields from the Prison table onto the Prisoner layout, and even change their contents.
So no need to update anything; changes in related fields will automatically be reflected wherever that field is referenced.
Note that in some cases this is not desired, e.g. customer addresses; if they, move and their address is subsequently modified, all old invoices would be falsified; this is why here you would store the current address values with the invoice, rather than reference those fields.
Thanks everyone, I sorted it out with all your help. I do have an issue that appears to be global, when selecting from a drop down list I can see the Name I am after but when this is chosen the ID is entered. I am sure I have set up the value list correctly (attached). This has happened a few times throughout the database. I en up having to add a filed for the actual name I am trying to select. I can't see an option to upload a screen shot of the value list.
Thanks everyone, I worked it out with everyones help. I do have one problem and it appears to be global. When I select the name from drop down list I can see the name I am after but when I select this the ID is inserted into the field. I am sure I have set up the value list correctly as beverly suggested (sorry but none of the image formats I add is accepted even PDF). this has been a problem throughout the database which is an old Version 8 that has been upgraded to 13.
That doesn't work with a dropdown; the reason is that a field formatted as dropdown still allows manual input, so the “raw” content (the id) is visible/accessible. A popup, OTOH, doesn't allow input, so the ID (VL first field) can be resolved to the second field from the value list.
In short: use a popup control. If you must use a dropdown, there's a way to “have your cake and eat it, too”:
Place two copies of the field onto the layout. On the left a very narrow (a few px) copy formatted as dropdown, with an object name; next to it on the right a normal sized copy formatted as popover, and defined as a button with the command: Go to Object [ "objectNameOfDropdownCopy" ].
This will allow you to select from the dropdown, and utilize the popover display capability.