8 Replies Latest reply on Nov 17, 2011 3:15 PM by boydcrow

    Script set value in Table B field X equal to Table A field Y for single matching related record

    boydcrow

      Title

      Script set value in Table B field X equal to Table A field Y for single matching related record

      Post

      Without leaving my current layout based on Table A, I would like to use a script to find the single related record in Table B, then set the value of Field X in Table B to be the same as Field Y in table A.  

      The tables are related one-to-one so that Field K in Table A matches only one record in Table B, where Field L in Table B has the same value.

      Ideally I would still be in the same layout at the end of the script.

      I've looked at numerous examples of Perform Find and Set Field but haven't been able to figure it out.

      Any ideas?

        • 1. Re: Script set value in Table B field X equal to Table A field Y for single matching related record
          philmodjunk

          Describing a problem with letters instead of names and not given an actual example of what you are trying to do makes it harder to help you, not easier. Often, noing the main purpose of what you want to do makes it possible for us to suggest an alternative approach you may not have realized was a practical option to consider.

          Without leaving my current layout based on Table A, I would like to use a script to find the single related record in Table B, then set the value of Field X in Table B to be the same as Field Y in table A. 

          a) a script can leave the current layout and return invisibly. That is, the user never knows that this happened.

          b) A looked up value setting on Field x can copy the value of Field Y into it when the field defined in Table A used to define the relationship between the two tables is edited. This does not require a script.

          • 2. Re: Script set value in Table B field X equal to Table A field Y for single matching related record
            boydcrow

            I have tried using a lookup field:

            I have table Computers and table Network Addresses, which are related through three different table occurrences of Network Addresses

            1.  Network Addresses - COMPUTERNAME relates to COMPUTERNAME

            2.  Network Addresses 2 - IPUseFlagIs1 relates to CompBlankFlag

            3.  Network Addresses 3 - AvailableIPsPopup relates to IP Address

            At the point of interest in the data entry process, I have already entered COMPUTERNAME on a data-entry layout based on Computers. I can select an available IP address from a pop-up field.   At that point, I want to find the record in Network Addresses that corresponds to that IP address and copy the COMPUTERNAME into the matching COMPUTERNAME field.

            (Eventually I want the lookup field to be COMPUTERNAME but in the interest of simplicity I am creating a lookup field with a different name.)

            I have defined a lookup field in Network Addresses - IPRelatedComputerNameLookup and specified:

            starting with table Network Addresses 3

            lookup from related table Computers

            Copy value from field COMPUTERNAME

            The dialog box displays the following description,

            '(When a new entry is made in the field "COMPUTERNAME", this lookup will copy the value from the first matching related record in the table "Computer Attributes")'

            Unfortunately, when I click on the AvailableIPsPopup field and select an IP address, I do not find the COMPUTERNAME value written to IPRelatedComputerNameLookup.  

            So, how is this done with a lookup or, barring that, a script?

            • 3. Re: Script set value in Table B field X equal to Table A field Y for single matching related record
              philmodjunk

              The look up won't work here as we don't have a valid relationship to the IP Addresses table and we need the IPAddresses table to be updated intead of the computers table.

              Your value list of available IP addresses enters what? An IP Address or a serial number form a field in IP Addresses?

              Either way, you can define this relationship:

              Computers::SelectedIPAddress = SelectedIPAddress::IPAddress

              Then this script step:

              Set Field [SelectedIPAddress::ComputerName ; Computers::ComputerName ]

              can update the selected record in IPAddresses with the specified computer name of the current record where you have selected this IPAddress field.

              • 4. Re: Script set value in Table B field X equal to Table A field Y for single matching related record
                boydcrow

                In different parts of your reply you refer to "IP Addresses" table.  In the suggested relationship, SelectedIPAddress seems to be used as both a table and a field, if I understand the hierarchical naming convention.

                Is IP Addresses table the one I have described as Network Addresses?

                Should I create another table named SelectedIPAddress?

                I don't understand about not having a valid relation to the IP Addresses table.  Assuming you mean Network Addresses table, AvailableIPsPopUp in Computers relates to IP address in table occurrence Network Addresses 3.  Since the IP addresses already exist in Network Addresses and I just put an entry in AvailableIPsPopUp by selecting a value, how is that not a valid relationship?  Both fields are restricted to unique values.

                My value list of available IP addresses provides a list of IP Addresses in Network Addresses where COMPUTERNAME is blank. The selected value just sits there to serve as a relationship link.  It's a little confusing because my boss insisted on a complete list of all available addresses on the network rather than just entering as needed.

                • 5. Re: Script set value in Table B field X equal to Table A field Y for single matching related record
                  philmodjunk

                  You are almost correct. In Computers, it's the name of a field. It's also what I used as the name of a table occurrence (not a table). Sorry for the confusion that may have caused. The names as given should work, but you can also name them differently if you want.

                  You do not create a new table. You do create a new occurrence of your existing table of IP addresses.

                  They are valid relationships, but not for the purpose you need here. They work fine for your value lists, but you now need a one to one link that connects your current record in Computers to a specific record in the NetworkAddresses table so that you can update it with the correct value in its Computers field.

                  Hmmm, I'm not a network geek, but why do you need specified IP addresses? wouldn't it be simpler to use DHCP to let a server on your network assign the IP addresses on an as needed basis?

                  • 6. Re: Script set value in Table B field X equal to Table A field Y for single matching related record
                    boydcrow

                    I get the gist of what you are suggesting.  I am still not clear whether the field SelectedIPAddress is the one I have named AvailableIPsPopup which will hold the value selected from a list of available IPs.


                    (I've been out for a couple of days.)


                    Static IP addresses are essential for quick troubleshooting of network problems.  Static IPs are also necessary for a simple implementation of local DNS so that computers can be accessed by name.  As a rule, DHCP addresses are reserved for guest computers.  The DHCP address pool is kept small, so that it is easier to spot any kind of drive-by wireless hacking.

                    • 7. Re: Script set value in Table B field X equal to Table A field Y for single matching related record
                      philmodjunk

                      Yes it should be the field where you use the value list to select an IP address.

                      • 8. Re: Script set value in Table B field X equal to Table A field Y for single matching related record
                        boydcrow

                        Thank you for your solution.  While it turns out that the relationship you suggested is exactly the one I described as #3 in my 14 Nov 11 post, the script line was what I needed and now works.  Apparently, a script is what I needed after all. 

                         

                        Thanks again.