12 Replies Latest reply on Nov 17, 2016 4:51 AM by simonpsmith

    Creating a conditional value list within a table

    simonpsmith

      I've been struggling to get my head around making conditional value lists, and all I've read up on them hasn't helped...

       

      I have a "Staff" table which includes a "Job Title" field created as a checkbox set with 5 options. It was done this way as people can fall under more than one title. One of these titles is "Supervisor". There is also a "Current" field, created as a radio button set, to indicate if these staff members are current or not (options of "Y" or "N").

       

      In one of the tabs I've set up on the layout I have an area which relates to the staff member's supervision record, and I want to be able to call on a list that ONLY includes current members of staff that have supervisor status, but cannot get it clear in my mind how best to do this.

       

      Can someone help please?

        • 1. Re: Creating a conditional value list within a table
          coherentkris

          Post your file and we can show you how to make it work.

          Sometimes a "picture" is worth a thousand words.

          • 2. Re: Creating a conditional value list within a table
            Mike_Mitchell

            It sounds like you need a self-join. Here's what you do:

             

            1) Create a pair of calculation fields in your table. One will be a constant field, called "Supervisor", which always has a value of "Supervisor". The other will be "Y", with a value of "Y".

             

            2) Create another table occurrence of the same table on your Relationships Graph. You can call it "StaffSelf" or something.

             

            3) Create a relationship between the table with two predicates:

             

            Supervisor = Job Title

            Y = Current

             

            where the parent table is on the left, and the self-join is on the right.

             

            4) Create a value list, based on related values only, for whatever value you want from StaffSelf, starting from Staff.

             

            This should give you a value list that shows all the supervisors who have a "Y" in their status field.

             

            HTH


            Mike

            • 3. Re: Creating a conditional value list within a table
              simonpsmith

              Perfect!

               

              I worked though your solution, and it did exactly what I was hoping for.

               

              Many thanks Mike.

              • 4. Re: Creating a conditional value list within a table
                simonpsmith

                I have revisited the self-join answer given previously as I needed to make another conditional value list within my same solution.

                 

                This time it is for current members of staff who are a counsellor or trainee counsellor, so that I can use a drop down list on the Clients layout to show who is their current counsellor.

                 

                I thought it would be simple but am having trouble making it fully work.

                 

                Firstly I made a new constant field in my ‘STAFF’ table called ‘Counsellor’ with the values = “counsellor” or “trainee counsellor”.

                 

                Then I made a 2nd 'self' table occurrence, this one called STAFF_self_counsellor, and set the relationships:

                 

                Counsellor = Job Title

                Y = Current

                 

                I created a value list as previously except this time with the value (STAFFfull_name_c) from the STAFF_self_counsellor TO.

                 

                The field I’m trying to populate in the Clients layout is called CLIENTcurrent_counsellor and this is related to STAFFfull_name_c in the STAFF table, but when I try to use it, the drop down list I get is just blank.

                 

                If, within the ‘Staff’ layout I set a field to use this value list, all works fine.

                 

                I’ve also found that in another layout (Diary) related to 'Staff' via 'Client', I can set a field to use this list and all works… just not in the Clients table I need it to.

                 

                I just cannot understand why!

                 

                Help, please…

                • 5. Re: Creating a conditional value list within a table
                  Mike_Mitchell

                  Conditional value lists are dependent on context. You'll need to define the relationship and value list from the TO where you want the value list to appear. I can't be 100% certain of what you've done (since I can't see your Graph), but it sounds like there's no relational connection between Client and Staff.

                  • 6. Re: Creating a conditional value list within a table
                    philmodjunk
                    Conditional value lists are dependent on context.

                    Well, conditional value lists that are based on a relationship are dependent on context. There are several conditional value list options that are not based on a relationship and thus are not dependent on context. There are also ways to do "connector" type relationships that allow a CVL to work in a wider context.

                    A non-relationship based CVL that you might consider is one where you use a calculation to produce the desired list of values. Since such a value list does not use the "include only related values..." option, it will work from any context in your file.

                     

                    This time it is for current members of staff who are a counsellor or trainee counsellor

                    You can define a calculation field in the same table with a calculation such as:

                     

                    If ( Not IsEmpty ( Staff ; List ( "Counsellor" ; "Trainee Counsellor" ) ) ; NameFieldHere )

                     

                    You can then set up a value list to list either just the names by listing the contents of this calculation field or if you are using ID values like you should, you refer to the ID field as field 1, this calculation field as field 2 and either sort or show the calculation field.

                     

                    This is one form of what I call a 'hardwired' conditional value list (because the user is not asked to select the category) in my instructional file named:

                     

                    Adventures in FileMaking #1 - Conditional Value Lists

                    • 7. Re: Creating a conditional value list within a table
                      Mike_Mitchell

                      I was trying to keep it simple ...  

                      • 8. Re: Creating a conditional value list within a table
                        philmodjunk

                        The calculation field method is no more complex than a relationship based version, just different.

                        • 9. Re: Creating a conditional value list within a table
                          Mike_Mitchell

                          "Simple" as in "avoid option overload".

                           

                          But no, a calc field isn't hard to implement.

                          • 10. Re: Creating a conditional value list within a table
                            simonpsmith

                            Thanks for the replies.

                             

                            I had 'tried' to set a relationship between the Clients and Staff TOs, but that's where I'd come up short...

                             

                            I think, for this solution, Phil's way of having a non-relationship based list would work better as this list of 'current counsellors' will be required to be used in a couple of places, on the Clients layout and on a Diary layout, both of which aren't related to the Staff TO.

                             

                            I had a bit of difficulty making the calculation work initially and then, when I did, I had to incorporate an extra bit to only include current members of staff (as ex members still have the positions they held 'ticked' in their record. So I have made a field called STAFFcurrent_counsellors with the calculation:

                             

                            If(STAFFcurrent="y" and (not IsEmpty ( List ( "counsellor" ; "trainee counsellor") ) ) ; STAFFfull_name_c)

                             

                            I have made a value list that uses the contents of this field.

                             

                            One issue I am still having, and can't immediately see the cause of, is that in one of the two places I'm using this value list, the field isn't populating.

                             

                            I have a field on my Client layout called CLIENTcurrent_counsellor which is a drop-down list using values from this new value list. All works fine, and only the current staff members marked as being a counsellor or trainee counsellor show are listed.

                             

                            However, I have another field on my Diary layout called DIARYassessment_counsellor which also is set as a drop-down list using values from this new value list. When I slick on the down arrow for this I get no list at all...

                             

                            Any clues please??

                            • 11. Re: Creating a conditional value list within a table
                              Mike_Mitchell

                              Is the STAFFcurrent field an unstored calculation, perhaps?

                              • 12. Re: Creating a conditional value list within a table
                                simonpsmith

                                Even easier than that...

                                I'd not selected the correct value list after all for this second field, but rather the one I'd tried to create yesterday the self-join way. Similar list name I'd left in case I went back to it...

                                It's now deleted!
                                Thanks for your help...