12 Replies Latest reply on Aug 15, 2014 2:16 PM by dmb_fmp

    Field based Value List

    MattLeach

      I have a table that stores employees.

       

      Currently it contains 2 fields, name and supervisor. The supervisor field is a checkbox to indicate the employee is a supervisor. Boolean checkbox with value of 1 if checked.

       

      What i am trying to do is create a value list containing a list of just the employees that are supervisors for use in another table.

       

      My thought was to create a constant in my employees table, lets say zz_const_super and set this field to 1. Then create a TO called Supervisors and join it to the employee table as so:

      Supervisors::supervisor = employees::zz_const_super

       

      Then base the value list off of Supervisors::Name field, however this returns a list of all the employees, not those just marked supervisor.

       

      Is this a step in the right direction on how to accomplish what i need or am i completely off base?

       

      Thanks

        • 1. Re: Field based Value List
          erolst

          If you take the effort to set up that relationship, why don't you use it? 

           

          You have

           

          Employees::zz_const_super = Supervisors::supervisor

           

          So to get the values from records flagged as Supervisors only (target), you need to check “Include only related values”, starting from the Employees TO (source).

           

          On related notes: you should use IDs, not names, i.e. define the value list as 1st field: Employee:ID, 2nd field: employee::name, and use it to set a foreign key.

           

          Also, if you call the constant, say, zz_const_one, you'll remember later that you could use it to create other relationships that target a flag field, which – indeed – you can.

          • 2. Re: Field based Value List
            dmb_fmp

            I found a solution for you by simply adding a calculation field (with text result) to your table that has the "name" & "supervisor" fields. The calculation field could be called something like name_supervisors and the calculation you could use could be:

             

            If (supervisor = 1; name; "")

             

            Then make a value list based off of your name_supervisors field, which you can use in your other table.

            1 of 1 people found this helpful
            • 3. Re: Field based Value List
              MattLeach

              But i have used it. Below is how my relationship graph is seutp:

               

              RG.png

              and here is how the value list is setup:

               

              VL.png

               

              I have 2 test records setup in the employee table. One with the supervisor box checked (value of 1) and one without.

               

              When i attempted to use the value list, it appears empty.

              • 4. Re: Field based Value List
                MattLeach

                dmb_fmp - That will not work as you cannot base a value list off of a calc field as it cannot be indexed.

                • 5. Re: Field based Value List
                  erolst

                  MattLeach wrote:

                   

                  But i have used it.

                   

                  You could have mentioned that …

                   

                  Anyway:

                   

                  Supervisor field indexed?

                  Matchfields on both sides have values?

                  Does the record with the flag value also have a value name field? (sanity check)

                  What do you see when you create a portal from Employees into Supervisors?

                   

                  And you have noted what I said about names and IDs?

                  1 of 1 people found this helpful
                  • 6. Re: Field based Value List
                    erolst

                    MattLeach wrote:

                    you cannot base a value list off of a calc field as it cannot be indexed.

                     

                    Who told you that a calculation cannot be indexed?

                    • 7. Re: Field based Value List
                      MattLeach

                      Sorry about that, thought i had put that in original post but must no have.

                       

                      Yes i have noted your suggestion, i set it up the way you suggested with the ID as the first field and name as the second field, displaying only the second field.

                       

                      Supervisor field is indexed.

                       

                      Flagged records have a name.

                       

                      I setup a portal to from employess to supervisors and can see just supervisors in the portal.

                       

                      I also see i forgot to add this bit in my original post.

                      There is a table called clients with a field for supervisor in which i am trying to utilize this list.

                      So on the Clients::Supervisor field i have it setup as a drop down list, using values from the Supervisors value list which is setup as above.

                      • 8. Re: Field based Value List
                        MattLeach

                        FileMaker did when i attempted it lol. It advised that the value list would not work when i tried to save it.

                        • 9. Re: Field based Value List
                          erolst

                          MattLeach wrote:

                           

                          FileMaker did when i attempted it lol. It advised that the value list would not work when i tried to save it.

                           

                          If you cannot create a value list using the field cSupervisorName defined as

                           

                          Case ( isSupervisor? ; name )

                           

                          then the calc field cannot be stored, and the reason is that either the name field or (more likely) the superVisor flag field is set to ”do not index" – which is most likely the reason why the relationship does not work. Check the setting for the supervisor flag, then try your value list.

                          • 10. Re: Field based Value List
                            keywords

                            Yes it will. See attached demo.

                            • 11. Re: Field based Value List
                              MattLeach

                              Found the issue, my relationship with the employees and clients was screwed up and apparently i overlooked it. Re-did it and now the list is populating.

                               

                              Thank you for your assistance.

                              • 12. Re: Field based Value List
                                dmb_fmp

                                Weird - totally worked for me. Here's what it looks like:

                                 

                                1.png2.png