Another File Called "Prisons" or another Table called "Prisons"?
If it's a File, what kind of file?
If it's a table, which record in that table? What Field from that table?
Sorry Phil, I thought you was a mind reader. Another file called "Prisons". It has two layouts, one called "Mail Regs" and one called "Prisons".The "Mail Regs" I currently access from a Popover button with a portal as seen in the attached screen capture on the Prisons page.
SO in the "Prisoners" file on the "Details Page 1" layout is where the "Mail Regs" fields need to be populated as a new record in the "Prisoners"-"Prisoners Mail Regs" (when you click on the Update Mail Regs button) layout as shown on the screen capture.
Hope I get this right:
Prisoners::Prison need to appear in Prisoners Mail Regs::Mail Regs Prison field.
Prisoner Mail Regs::Prisoner Mail Regs Date needs to be current date when this new record is created when the Update Mails Regs button is clicked.
Prison Mail Regs::Prison Mail Regs needs to appear in the Prisoner Mails Regs 2::Prisoner Mail Regs Notes field.
and finally the Prison Mail Regs::Prison Mail Regs Date needs to appear in the Prison Mail Regs Date field (Bottom right corner of Prisoners layout.
Another file called "Prisons"
But why use two files? This can be done and there can be good reasons for doing it that way, but it complicates the design of your database system.
It has two layouts, one called "Mail Regs" and one called "Prisons".
Does that mean that you have two tables, one with records for Mail Regs and one with records for Prisons?
From your description, I would guess that you have this relationship between them:
Prisons::__pkPrisonID = Mail Regs::_fkPrisonID
But then your description mentions additional tables. Am I guessing correctly that these tables, Prisoners, Prisoner Mail Regs and Prison Mail Regs are tables in the second file? Are all of these tables in the second file or is Prison Mail Regs a table occurrence with an external data source link to Mail Regs in the other file?
Are you trying to select a Prison for the current Prisoner record in order to link to or copy the mail regs data for that prison?
This database is a bit of a dinosaur, it was built about nine years ago, and the Prisons file was added about six years ago. This has been added to by probably six different people over the years. I am trying to make it functional for now, but we will end up rebuilding it from scratch, but not in the short term.
So, yes, two tables, for the Prisons file, one with records for Mail Regs and one with records for Prisons.
And yes, Prisons----<Mail Regs
Prisoner Mail Regs does not show in the last screen shot. And am I right that you want to create a new record in Prisoner Mail Regs that copies data from Prison Mail Regs? By copying data, you can then edit the Mail Regs data in the Prisoner Mail Regs record. Is that what you meant by "And I want to be able to manually enter info if needed."?
If I am guessing correctly, then you need to add an occurrence for Prisoner Mail Regs linked to Prisoners. You can link an occurrence of Prison Mail Regs to it by Common Key and set up looked up values to look up data from Prison Mail Regs into Prisoner Mail Regs. The match key for Prison in the Prisoner Mail Regs can be set up with a value list of prisons, but a script from the Prisoner layout that copies the value into this field in Prisoner Mail Regs can make this an automatic process.
Prisoner Mail Regs is in the other file Prisoners. Yes I want to create a new record in Prisoner Mail Regs that copies data from Prison Mail Regs. After copying data, I can then edit the Mail Regs data in the Prisoner Mail Regs record if needed. I already have a relationship of Prisoner Mail Regs linked to Prisoners, if that is what you mean by an occurrence.
Is it possible to give me an example of the script. Hopefully I can adjust it to suit. And I do appreciate your help, it has been so helpful in sorting out this database. So would I add the script to the Update Mail Regs button in the Prisoners Details Page 1 layout.
First, you need the following relationships in the other file. (I can see by the italics that this is the file that contains the "Prison" tables and we need the file with the "Prisoner" data):
Prisoners----Prisoner Mail Regs>------Prison Mail Regs
Prisoners::PrisonerID = Prisoner Mail Regs::Prisoner ID
Prisoner Mail Regs::Prison = Prison Mail Regs::CommonKey
This is needed in order to set up the auto-enter field options in Prisoner Mail Regs that will copy data over when Prisoner Mail Regs::Prison gets a value.
The script would be quite simple:
Set Field [Prisoner Mail Regs::Prison ; Prisoner::Prison ]
A script trigger on the Prisoner::Prison field can perform that script automatically.
and if you need to see this happen in the Prisons file, you'll need to add an occurrence of Prisoner Mail Regs to that File's Manage | Database | Relationships in order for the script to work.
Thank you, the Prison name will now move into the "Mail Regs Prison" field. I used the set field script to get the current date in the "Prisoner Mail Regs Date" field. But I don't seem to be able to get the "Prisoner Mail Regs Notes" field to bring in the "Prison Mail Regs" from the Mail Regs table in Prisons.
See attached screen capture in this and the following post I send.
I also need this button to create a new record in the Prisoners Mail Regs, I need to be able to see what previous mail regs were.
Those are not the relationships that I recommended. None of them are multiple field relationships. Each one matches by a single pair of match fields.
The script that I recommended does not create a new record unless "allow creation is enabled" and there are no related records. Then it creates exactly one such record. A different script is needed if you need it to always create a new record in the table.
And why the "Set field by name" script step?
The added step for the current date is ok, but not needed. But the last step has me scratching my head....
To create a new record in a related table, you need a script like this:
Set Variable [$ID ; value: Prisoners::PrisonerID ]
Set Variable [$Prison ; Value: Prisoners::PrisonID ]
Go to layout [ "Prisoner Mail Regs" (Prisoner Mail Regs )]
Set field [Prisoner Mail Regs::_fkPrisonerID ; $ID ]
Set Field [Prisoner Mail Regs::Prison ; $Prison ]
Go to Layout [origional layout]