13 Replies Latest reply on Mar 5, 2013 8:17 AM by johnw54

    Conditional Value List with Filter

    johnw54

      Title

      Conditional Value List with Filter

      Post

           I have created a conditional value list that works fine thru relationship joins.  However I would like to also filter it on a field.  So once the value list shows up in the droplist i need to filter on the field.... If Type = "F" then omit from the droplist.

      Structure.jpg

        • 1. Re: Conditional Value List with Filter
          philmodjunk

               "filtering values" is the entire purpose toa  conditional value list, so this should indeed be possible to do, but there is not enough detail in your post to deterimine exacly how to implement that requirement that type = "F" filter the values in the value list.

               Is FABRICS_Structure the source of values for a value list used on a Fabrics layout?

               Is Fabrics the specified Common Forum Relationship and Field Notations Explained for your layout?

               is Type, a field in the data source table of FABRICS_Structure?

          • 2. Re: Conditional Value List with Filter
            johnw54

                 1.  yes

                 2. Yes

                 3. Fabrics_Structure is a 2nd TO of Fabrics so it is in both Occurences.

                 Thanks for your patience.

                  

            • 3. Re: Conditional Value List with Filter
              philmodjunk

                   You have two options:

                   1) Define a calculation field, constF as:

                   "F"

                   with text specified as the result type.

                   Then the relationship for your conditional value list can be:

                   Fabrics::Structure = FABRICS_structure::Structure_Filter AND
                   Fabrics::constF = FABRICS_structure::type

                   2) Define a calculation field in Fabrics with an If function:

                   IF ( Type = "F" ; //put field that holds the value you want in your value list here )

                   Now refer to this calculation field in the value list set up in place of "field that holds the value you want in your value list".

              • 4. Re: Conditional Value List with Filter
                johnw54

                     I went with #1.  What I'm looking for is that the Type "F" does not show in the dropdown list so I set the relationship to reflect that.  I must be doing something wrong because the dropdown shows type F and all of the other types.  The field I added constF is set to text.

                • 5. Re: Conditional Value List with Filter
                  philmodjunk

                       I did miss the omit reference, but using  ≠ like you have should work.

                       What result type have you specified for constF?

                       a result type of "text" should be selected.

                       What type is defined for the type field?

                       This should be a field of type Text.

                       Is the check box field in your screen shot the "Structure" field used in the relationship?

                  • 6. Re: Conditional Value List with Filter
                    johnw54

                         constF is text... Type field is Text... indexed, auto-enter calculation replaces existing value (the calculation is Upper(Type).

                         The checkbox is using Fabrics::Structure Filter.   The value list for the checkbox is called Structure and is a Custom Value List.

                    • 7. Re: Conditional Value List with Filter
                      philmodjunk

                           Looks to me that Structure is the field that should be formatted with the check box group given the "filter by" label. As designed, selecting a value in this check box controls whether or not the current record will appear in the conditional value list, it will not control which values from the rest of the table will appear in the value list, so this looks backwards to me.

                      • 8. Re: Conditional Value List with Filter
                        johnw54

                             I originally tried using Structure with the checkbox.  What happened was I had the field Structure on the layout and when I changed the checkbox... the fabric's Structure field would change to whatever was checked in the checkbox.

                        • 9. Re: Conditional Value List with Filter
                          johnw54

                               There is no data in the Structure Filter field.  I think I have this thing all messed up.

                          • 10. Re: Conditional Value List with Filter
                            philmodjunk

                                 I think you should first try this relationship to see if it changes the results of how your value list functions:

                                 Fabrics::Structure Filter = Fabrics_Structure::Structure AND
                                 Fabrics::constF ≠ Fabrics_Structure::Type

                                 And your conditional value list should list values from Fabrics_Structure with include only related values options setup with Fabrics selected as the "starting from" table.

                                  

                            • 11. Re: Conditional Value List with Filter
                              johnw54

                                   Ok I tried what you suggested... please see image.  My checkbox displays data from Fabrics::Structure and it gets it values from a simple value list with custom vales.  When i change from fabric to leather to vinyl within the checkbox it changes the data in the field in the record.  What I'm trying to do is use the Checkbox as a filter to populate the dropdown box.  If I select leather in the checkbox I want all the Leather items to be listed in the dropdown. Vinyl should do vinyl etc.  And in any of the 3 choices if the type is an "F" then do not show the name in the dropdown. Now what happens is when 1 of the 3 choices is made it still changes the record to show the new Structure as selected as if I was using the layout to change or enter for the first time the structure for that item.  And when I click on the dropdown there is nothing in the list. 

                                   I'm sorry Phil I guess I'm not getting it.

                              • 12. Re: Conditional Value List with Filter
                                philmodjunk

                                     Apologies, but I changed my mind and wanted you to keep the check box field unchanged. I did not make that clear to you in my last post.

                                     The idea is that the fields on the left hand side of this relationship as stated below control what values on the right hand side supply values to the conditional value list.

                                     Fabrics::Structure Filter = Fabrics_Structure::Structure AND
                                     Fabrics::constF ≠ Fabrics_Structure::Type

                                • 13. Re: Conditional Value List with Filter
                                  johnw54

                                       I got it. thanks for all your help.