5 Replies Latest reply on Aug 22, 2011 3:00 PM by EricLangendorff

    conditional value list - shouldn't this be simpler?

    TerryCoolidge

      Title

      conditional value list - shouldn't this be simpler?

      Post

      I've read several posts related to Conditional Value Lists, and I've also worked with the Knowledge Base article as well as a couple of sample files found on the internet.  I'm understanding the concept of a CVL, but what I've found doesn't seem to address the extemely simple way in which I want to use one.

      I have a table with 400 records representing the membership of an organization.  There are different "types" of members as indicated by a field called "status."  Members can be "active," "withdrawn," "out of town," or "deceased."  I have a simple value list for the four status types.  When working with this database, I'm almost always dealing with the "active" members.  The other types are mainly for record keeping, but the "active" members are the ones that I'm constantly doing stuff with (sorting, finding, running reports, assigning to committees, sending emails to, etc.).  When assigning members to committees, for example, I'd like to be able to have a pop-up menu only display the active members and not every single member (past and present).  Right now I have a layout with a portal that works for establishing my many-to-many relationships, but the pop-up menu for choosing the members to populate a committee shows every single member name instead of just the active members.  However, I don't want to have to first choose from a pop-up menu to indicate "active" before then choosing a member from a second pop-up menu.  I just want my member pop-up menu field to ALWAYS only show my active members.  Do I have to create some intermediate table first, or is there a way to simply have a pop-up menu only display records based on a found set, or something like that?  Am I being clear about what I'm trying to do?

      Thanks in advance for any help anyone can provide.

        • 1. Re: conditional value list - shouldn't this be simpler?
          EricLangendorff

          For the purposes of this discussion, I'm going to assume that the name of the table occurrence you're working with is called Source.

          Create a global calculation field in Source whose value is "active".

          On the Relationships tab of the Manage Database window, create a new table occurrence called Target (or whatever you want to call it) for the table that has your members' names and statuses.

          Create a relationship between Source and Target with the condition that the global "active" field in Source must match the status field in Target.

          in the Edit Value List window, click on "Specify field...", and, in the window that pops up, under "Use values from first field", select Target from the pop-up menu.  In the pane below that, click on the field with your members' names.  Below that, select the "Include only related values starting from:" radiobutton, and select Source.

          If you want to be able to vary which members are displayed in your list (say, for example, you sometimes want a list for deceased members instead), then instead of linking to a global calculation field, use a global text field.  Changing its value will then change which members appear in your value list.

          • 2. Re: conditional value list - shouldn't this be simpler?
            philmodjunk

            Nothing wrong with that approach though you don't have to specify global storage for the calculation field. You can specify normal storage options and get the same result.

            You can also use "Option 1" in the following tutorial if you prefer not setting up a relationship just for the one "hardwired" conditional value list:

            Custom Value List?

            • 3. Re: conditional value list - shouldn't this be simpler?
              EricLangendorff

              > you don't have to specify global storage

              Well, it'll use fewer resources that way.  ;-)

              And you'll almost certainly want global if you decide to use a dynamic filter (as described in the last paragraph of my previous post). Otherwise you'll have to make sure the filter is set correctly every time you switch records.

              > You can also use "Option 1" in the following tutorial

              Sure, but that method's harder to change if you want to alter the value list, and since the original poster mentioned using portals already, I figured setting up relationships wasn't foreign territory.

              You're right, though. It is simpler, and there's definitely something to be said for that.

              • 4. Re: conditional value list - shouldn't this be simpler?
                philmodjunk

                Global storage makes sense if you want to make the field a data field. Global calculation fields, in my experience, do not update like they should--especially over a network where I've seen some very strange results. (And I'm not talking about the way global fields changed by a client do not retain the chnaged value here.) An Unstored calculation, on the other hand works just fine for this.

                For most uses, I dont' think the difference in how many resources are used will be significant for this kind of calculation.

                Well our poster did say that he wanted simple didn't he? (Nothing wrong with using your approach and I prefer it in most cases over using a calculation field like this. Though it really isn't difficult to switch to the other, relationship based approach--just takes some time we don't have to spend if we set it up that way to begin with. )

                • 5. Re: conditional value list - shouldn't this be simpler?
                  EricLangendorff

                  > Global calculation fields, in my experience, do not update like they should--especially over a network where I've seen some very strange results.

                  Oops!  I forgot about network applications.  Yeah, that could definitely make a difference.

                  > An Unstored calculation, on the other hand works just fine for this.

                  > For most uses, I dont' think the difference in how many resources are used will be significant for this kind of calculation.

                  Hence the wink in my previous comment, but you've invalidated that by suggesting an unstored calculation.  So much for my attempts at a joke.

                  > switch to the other, relationship based approach--just takes some time we don't have to spend if we set it up that way to begin with.

                  I'm a huge advocate of frontloading work.  I'd much rather do all the heavy lifting in the beginning of a job, when only a general concept is expected, than later, after the project is being used, and somebody is panicking and needs results right now!  Also, if you decide you need to make a change several months from now, and if you haven't built your solution for expansion, it's much harder to re-acquaint yourself with how your database is structured and how to accomplish what you want.

                  But like I said, simpler definitely has its merits as well.