8 Replies Latest reply on Apr 9, 2016 7:41 AM by erolst

    Using Set Field

    sherab4

      I have two separate databases one called Prisons and one called Prisoners.

      I want to update the Prison address in the Prisoners database by clicking a button "Update" or letting it automatically fill in.

      I thought I would need a script with Set Field to bring in all the relevant address info into the Prisoners record when I select the Prison name from the drop down list in the prisoners file. It doesn’t have to be done with a button, auto infill would be good too when the prison name is selected.

       

      Can anyone give me an example for the script to do this and any other changes I would need to make to the relevant fields that need to be filled in.

        • 1. Re: Using Set Field
          erolst

          sherab4 wrote:

          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.

           

          sherab4 wrote:

          I have two separate databases one called Prisons and one called Prisoners.

           

          The correct terminology would be “one database with two tables”.

          • 2. Re: Using Set Field
            sherab4

            • 3. Re: Using Set Field
              sherab4

              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.

              • 4. Re: Using Set Field
                beverly

                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)?

                beverly

                • 5. Re: Using Set Field
                  erolst

                  sherab4 wrote:

                   

                  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)


                  then

                  • 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.

                  • 6. Re: Using Set Field
                    sherab4

                    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.

                    • 7. Re: Using Set Field
                      sherab4

                      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.

                      • 8. Re: Using Set Field
                        erolst

                        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.