7 Replies Latest reply on Dec 10, 2014 3:44 PM by philmodjunk

    Conditional value lists



      Conditional value lists


      2 problems:

      1. Hierarchical value list: I've got a parent > child > grandchild list on a 'Config' layout that works fine in terms of only related values of child/ grandchild appear if their parent/child (respectively) is selected. Also a script clears the child list if there's a change in the parent.

      a. The grandchild list is unlike the checkbox fields for parent/child, it is a portal from a related table because the list is v. long and therefore needs a scroll bar. However, am I right that in using a portal I can't keep Config records of different combinations of these lists? Is there any alternative to using a portal, ie. getting a scroll bar that would enable me to both keep records of different grandchild list AND using a script to clear this list when the related child record is cleared.

      b. Assuming a. can be solved, how would a script look is you instead of clearing all child values when a parent value is cleared, you instead only clear related ones.

      2. Is there an example (eg. within the Adventure files) of a conditional list that has a 'universal value', always listed and other values listed only 'on condition'.

        • 1. Re: Conditional value lists

          My memory is able to recognize that this is an ongoing project for you. But after 68,000 comments posted on many different threads, I'm not about to trust that memory to recall the current configuration of your data model and any supporting scripts used. So not being able to fully recall the details here is going to handicap my ability to answer effectively.

          a) I am not sure that I can parse the details here correctly. I don't see why you can't have different records that then produce different lists of values in the selection portal. Can you provide an example of what you want to do and cannot currently achieve? I suggest either documenting your current data model in detail or posting a link to a thread where you have already done so in order for me to better understand the details of the issue.

          b) this is possible, The adventure files demonstrate multiple approaches to how a hierarchical chain of CVL's might be set up, some using ExecuteSQL and others not. A general suggestion that I can make is to look at either using ExecuteSQL or a combination of FilterValues and ValueListItems to produce a new value list after changing a value "upstream" in the chain of CVL's.

          2. This sounds like what I call a "special value" in the "Adventures in FileMaking #2" file. You define a calculation field with an expression such as:

          List ( ValueField ; "UniversalValue )

          and select it as the source of values for your value list in place of ValueField. This adds an extra value to your list that will always be part of that list as long as your relationship matches to at least one record in the value source table specified for this value list. In the examples provided in this file, it shows how to play a few extra tricks to sort such a value to the head of the list and include a value separator line if such is desirable.

          • 2. Re: Conditional value lists

            Your memory serves you well !

            1a. The Config table/layout has fields with value lists for Country > Company. So for a particular Country, only those Companies with products show in the Company checkbox field. Selection of the Company choices then lists Products for those companies in a portal on the Config layout. The portal contains 2 fields: Product::ProductSelected (checkbox) & Product::ProductName. Config is related to Product table.

            The problem is that if in say a 2nd Config record I change the ProductSelected field, this will reset those products selected in the Products table. This effectively means I've over-written the selection I made in the 1st Config record.

            1b & 2. I will look into your info, thank you.

            • 3. Re: Conditional value lists

              The problem is that if in say a 2nd Config record I change the ProductSelected field, this will reset those products selected in the Products table. This effectively means I've over-written the selection I made in the 1st Config record.

              As I recall, you actually need to record this data in yet another table related to the config table. If that is correct, then the problem is that you are recording info in the config table that should not be recorded there as you need different value selections for each record in this other related table. I remember going round and round with that on that, pointing out that your data model didn't match what I recommend for HCVL's as it screws up the relationships needed in order to get this to work.

              The only work around that I can think of that I didn't discuss with you then is some kind of script performed by the OnRecordLoad trigger that moves data from the current layout record in to fields of a Config record and then, when a value is selected, a different triggered script copies it back the other way to preserve the selection. But this can result in pretty awful record locking situations if there is any possibility that two or more users might be selecting values from these value lists at the same time. (You'd need a way to set up at least one Config record for each current user and link each user to their config record to avoid interference issues.)

              The other alternative is a relationship that generates one new Config record for each and every record that needs to use Config to look up data from these value lists. That would work, but may be little or no improvement over linking the table occurrences used directly to the layout's table occurrence (and then needing to repeat this structure [and a new set of CVLs] when you need the same functionality on a layout based on a different table [occurrence]).

              • 4. Re: Conditional value lists

                I may regret saying this, but the HCVL works because I put Config upstream of where the list is required. But the 1a. problem remains, entirely because I have to use a portal for Products. If I could use a value list from Products on a checkbox Config::Products field life would be easy.........except the list is so long it needs a scroll bar. Is there any way of having a scroll bar on a checkbox field?

                • 5. Re: Conditional value lists

                  Yes, but that still leaves you with the problem that you are recording data in the config table that should not be recorded there if you want the values to persist. And you can also have issues if more than one issue is interacting with these value lists at the same time. And this does not alter my suggestions for how you might fix this.

                  Is there any way of having a scroll bar on a checkbox field?

                  Did you forget the CheckBoxes with scroll bar examples found in "Adventures In FileMaking #2 - Enhanced Value Selection"?


                  • 6. Re: Conditional value lists

                    When you say 'Enhanced Value Selection' do you mean 'new methods' on the 'Checkboxes with scroll bars' tab?

                    • 7. Re: Conditional value lists

                      I meant all 6. They are all fairly similar and all work in current versions of FileMaker. The "new methods" only work in newer versions of FileMaker, but the old methods are still workable in .fmp12 files. They all use the same basic technique for presenting the user with what looks and functions like a set of check boxes but with the scroll bar that you can't actually use with a single field formatted with check boxes.