10 Replies Latest reply on Mar 17, 2014 9:49 AM by philmodjunk

    Creating a new record in a portal but using info from two different files

    sherab4

      Title

      Creating a new record in a portal but using info from two different files

      Post

           I have a portal called Mail Regs in a table called Prisoners. When I click on the button "Update Mail Regs" I need it to create a new record in the portal, add the current date in the date filed, select the Prison name from the address field in the Prisoners layout. Then bring in the Mail Regs from another File called Prisons and also show the last entered date on the Prison's mail regs Date Entered field.

            

      Mail_Regs.png

        • 1. Re: Creating a new record in a portal but using info from two different files
          philmodjunk

               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?

          • 2. Re: Creating a new record in a portal but using info from two different files
            sherab4

                 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.

                  

                  

            • 3. Re: Creating a new record in a portal but using info from two different files
              philmodjunk
                   

                        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----<Mail Regs

                   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?

              • 4. Re: Creating a new record in a portal but using info from two different files
                sherab4

                     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

                     Prisons::PrisonID = Mail Regs::Common Key

                     You are partly correct, Prisoners and Prisoner Mail Regs are tables in the second file (Prison Mail Regs are in the Prison file).

                     Yes, I am trying to select a Prison for the current Prisoner record in order to link (not to) but copy the mail regs data for that prison. And I want to be able to manually enter info if needed.

                     I hope this is clear.

                      

                • 5. Re: Creating a new record in a portal but using info from two different files
                  philmodjunk

                       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.

                  • 6. Re: Creating a new record in a portal but using info from two different files
                    sherab4

                         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.

                    • 7. Re: Creating a new record in a portal but using info from two different files
                      philmodjunk

                           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.

                      • 8. Re: Creating a new record in a portal but using info from two different files
                        sherab4

                             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.

                        • 9. Re: Creating a new record in a portal but using info from two different files
                          sherab4
                          /files/21d5ccdf5e/Mail_Regs_Relatinships.png 983x568
                          • 10. Re: Creating a new record in a portal but using info from two different files
                            philmodjunk

                                 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 )]
                                 New record/Request
                                 Set field [Prisoner Mail Regs::_fkPrisonerID ; $ID ]
                                 Set Field [Prisoner Mail Regs::Prison ; $Prison ]
                                 Go to Layout [origional layout]