5 Replies Latest reply on Oct 6, 2011 7:39 AM by miw

    Diminishing Value List



      Diminishing Value List


       I have two tables, "TableA" and "TableB".  The TableA is linked to TableB as   TableA::TableAID----TableB::TableAIDlink.  In the layout window for Table B, I want to use a pull-down field that will select from eligible TableA IDs.  The nuance is that only unused TableAIDs should be selected in the pull-down list (in other words, once a TableB record is linked to an available TableAID, that TableAID is no longer eligible for use by another TableB record).  I have tried making a copy of TableA in the relationships table by calling it UnaffiliatedTableA and using the not equal sign to TableB::TableAIDLink but it does not work.  What is the best relationship set up and ValueList set up to accomplish this?


      Many thanks,


        • 1. Re: Diminishing Value List

          Define this relationship using an added occurrence of TableA:

          TableA::anyfield X AlltableA::anyfield.

          Define this calculation field:

          List ( AllTableA::TableAID ) & ¶ & -1

          Select text as the return type. Clear the "do not evaluate if all referenced fields are empty" check box. Name it cSelectedIDs.

          Now use another occurrence of tableB like this:

          TableA::cSelectedIDs ≠ UnselectedTableB::TableAIDlink

          Now set up a conditional value list that draws its values from UnselectedTableB, include only related values starting from TableA.

          Note: This method often requires using a triggered script with commit Record/Refresh window to force an update of the diminishing value list.

          • 2. Re: Diminishing Value List

            BTW, appending a negative 1 to the end of the list of values insures that all values will be listed when no values have yet been selected. Any value that cannot match any ID in the related table will work for that purpose.

            • 3. Re: Diminishing Value List

               PhilMod - I did as you mentioned but could not get it to work.  In my drop-down list I get a <no values defined>.  Any thoughts?

              • 4. Re: Diminishing Value List

                I made some major errors there in my last post and got this inverted. Embarassed

                Start from a blank page and do this:

                Create a new occurrence of TableB and set up this relationship:

                TableB::anyField X AllTableB::anyField

                Define cSelectedIDs in TableB as:

                List ( AllTableB::TableAIDlink ) & ¶ & -1

                Create an occurrence of Table A and set up a relationship to it like this:

                TableB::cSelectedIDs ≠ UnselectedTableA::TableAID

                Set your value list to draw values from UnselectedTableA, Include Only related values starting from Table B.

                • 5. Re: Diminishing Value List

                   PhilMod - Thanks!  Works great to avoid potential duplication of a TableA link in TableB.  This is a big help in eliminating potential errors for my design