1 Reply Latest reply on Jun 10, 2016 1:34 PM by mikebeargie

    Creating a drop-down that only shows unassigned records.

    tods

      I am creating a database for a small internet service provider.  I am trying to create a database that associates person information (In the first table) with an IP address (from a second table).  The IP addresses are unique in themselves and once an IP address is used for somebody it should never be used again unless that person leaves or requests a change.  I already have the table that contains all the IP addresses we want to assign.  The IP Address itself is the primary key as it's unique.  I want to create a dropdown list that only shows unassigned IP addresses. 

       

      So lets say I'm on Person A who has an ip address of 192.168.1.5, and Person B has an IP address of 192.168.1.6.  I go to add a new Person C to the database, and click on the dropdown, I want to see it look like this:

       

      192.168.1.2

      192.168.1.3

      192.168.1.4

      192.168.1.7

      192.168.1.8

      ...

      Only the unassigned IP addresses are shown.

       

      I have been working on my own trying to figure this out an am at a loss.  How can I accomplish this?

       

      -Tod

        • 1. Re: Creating a drop-down that only shows unassigned records.
          mikebeargie

          You need to use relational value lists, where the relationship will filter out "used" IP addresses

           

          The easiest thing to do would create a trigger on the drop down field, so when an IP address is selected, it silently marks that IP address and being used:

          Set Variable [ $ip ; Users::AssignedIP ]

          Go To Layout [ IP addresses ]

          Perform Find [ IPs::address = $p ]

          Set Field [ IPs::used ; 1 ]

          Go To Layout [ original layout ]

          Make sure IPs::used has an auto-entry of zero.

           

          Then in your users table, create a field that is just a global calculation of 0.

           

          Relate the users table to the IPs table based on the global calc field, and the IPs::used field, essentially only relating users to IPs that are NOT used.

           

          In your value list, configure it to select from the IPs table, address field.

           

          Check the box that says "use only related values", and select "starting from" to be Users.

           

          Now your value list will only show you the related "unused" IPs.

           

          You will need to add a "clear IP" button as well that "unsets" an IP from being used, but that's really simple too:

          Set Variable [ $ip ; Users::AssignedIP ]

          Set Field [ Users::AssignedIP ; "" ]

          Go To Layout [ IP addresses ]

          Perform Find [ IPs::address = $p ]

          Set Field [ IPs::used ; 0 ]

          Go To Layout [ original layout ]

          1 of 1 people found this helpful