1 Reply Latest reply on Jul 23, 2012 6:04 AM by philmodjunk

    Conditional lists



      Conditional lists



      I want to create a filter system for the data base.

      I have several tables but this only effects 2. Table1 is Customers, Table2 is Contacts ( i am a small pr company) I currently have 10 customers (call them CustA....CustJ)I also have 12000 Contacts that I have picked up over the years. Some contacts a relevent to some clients and not others whils are only relevent to a client some of the time.

      I want to be able to have a tick box on each contact that allows me to select multiple clients that the contact is relevent to. depending on which contacts I select another list will allow me to select a sector 

      Example. CustA has several sectors (consumer, IBC, Nationals, uk,) CustB has (satellite, medical, TV) CustC has (wildlife IBC Legal)

      Now I have a contact that is relevet to CustA in the sectors IBC and UK and relevent to CustB in the sector TV. I would like to be able to tick the boxes CustA and CustB and the second list change to list onlty the sectors relevent ie. Consumer, IBC, Nationals, UK, Satellite, medical and TV

      Is this at all possible?


        • 1. Re: Conditional lists

          A contact can be linked to more than one customer and a customer can, presumably be linked to more than one contact.


          Contacts::__pkContactID = Contact_Customer::_fkContactID
          Customers::__pkCustomerID = Contact_Customer:_fkCustomerID
          Customers::__pkCustomerID = Sectors::_fkCustomerID

          Fields with names starting with __pk should be auto-entered serial number fields. Those starting with _fk should be number fields. A portal to Sectors on your Customers layout can be used to list each sector assigned to that customer.

          It possible to set up a portal to customers on the Contacts layout to look like a set of check boxes (tick boxes) where clicking an empty box creates a related record in Contact_Customer and clicking a selected check box deletes such a record. If you create one Sector record for each sector that is appropriate for a given customer, a portal to sectors on the same layout will list all the sectors for all the selected customers.

          Getting the portal to look and function like a set of checkboxes requires scripting and conditional formatting. Here's a demo file that illustrates this approach as well as some simpler ways work with a many to many relationship: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7