3 Replies Latest reply on Sep 22, 2010 8:58 AM by philmodjunk

    combining value lists



      combining value lists


      I have two tables, one with a list of designers and one for developers. To manage the privileges for these individuals I have a third table for accounts. I have accounts in a relationship with both the designer and developer tables. To create an account I want to first select either designer or developer from a field and would like to create a drop down value list from that table.

      Is there a way of changing the value list a field uses based on a particular selection in another field. Alternatively, is there a way to combine value lists and then filter it.

      I am not sure if either of this is the best way to go about it but I would appreciate some input on the matter. Thanks

        • 1. Re: combining value lists

          AFAIK there is no way to "combine" data from 2 different tables in a way that results in a Stored field. You must have a stored field for a Value List. So, I see three ways to solve the problem.

          1. Put the field on the layout twice, attach a separate value list to each.

          2. Have only one table, put both Designers and Developers into it, adding any special fields for each.

          3. Have a third table, Contacts, which has a record for every designer and every developer, with a one-to-one relationship. Use this table for the value list (and any other operations which require seeing both as "one" entity. You'd need to put their names into this central table. 

          The relationship to the central table must use its unique serial ID on both sides of the relationship (though a value does not yet exist on either side to start with). The records in the central table can be created automatically via the relational engine when you enter their name, using [x] Allow creation of related records on BOTH sides of the relationship. It will populate the field on both sides at once (wowie). I call this the "David Graham" method, as I first saw it fully used by him; and cannot think of a better name :-]

          Whether it is called for depends on how much and how complex the operations with the two (in this case) data tables, and how separate the operations and relationships are, vs. how often seen as one entity.

          I am assuming that in all cases above IDs are used instead of names. I never use people's names in operational relationships; too long and unreliable.

          • 2. Re: combining value lists

            Thanks for the reply. Looks like the third solution might be the way to go but for now I will go with the first one i.e. have two fields with the two value lists.

            So there is no way perhaps to extract a return seperated list from both value list and then create a single list using a calculation?

            • 3. Re: combining value lists

              There is a function called ValueListItems that will return all the values of a given value list in a return separated list. You could use a script to combine the two lists into a single list stored in a single text field of a one record table and then a value list that references this field in this table will present you with a combined value list.

              You can't just do this with calculation fields as the resulting calculation would be unstored and thus would not have the index needed for the value list to work with.