1 2 Previous Next 21 Replies Latest reply on May 11, 2015 12:06 PM by philmodjunk

    Filtered value list

    RockSure

      Title

      Filtered value list

      Post

      FMP13ADV/windows


      I have 10 tables that require a status for each record such as open, closed, pending, canceled, and more.
      Each of the 10 tables have a status field being used as the foreign key, popup field and with a value list .
      Each of the 10 tables may a combination of the same but not all status choices.

      I am trying to create one “Status” table as a value list and filter that value list based on the table it being used by. This would allow me one layout for adding and editing status values for all 10 tables in one place.


      I was approaching this from my “Status” table having
      the following fields
       

                                                                                                                                                                                                                                                                                                                                                                               
      primary keyStatus NameUsed by Table
      1opentable1
      2opentable2
      3closedtable1
      4pendingtable3



      Each of the 10 tables also have a calc field with the name of the table such as “TABLE1” set to text and  set as global storage thinking to use this as a match field to the Status::Used by Table field.

      Kinda of lost after that. I have tried to use conditional  value list examples and have got them to work as exampled. I have the Adventure example database so generously supplied by PhilModJunk, thanks for that!

      However I am not using the value of another drop down to conditional limit the choice of the 2nd drop down.  Additional I am using a PK to FK  as my understanding is that is best practice for most any related table.

      Open to any other best practice or solutions. 

        • 1. Re: Filtered value list
          philmodjunk

          Each of the 10 tables also have a calc field with the name of the table such as “TABLE1” set to text and  set as global storage thinking to use this as a match field to the Status::Used by Table field.

          You are on the right track here but there is no need to make this field have global storage. Keep it as a normal, stored calculation field.

          So if you have this relationship:

          Table1::cTableNamecalcFIeld = StatusValues::UsedByTable

          Then you can set up a "use values from field" value list listing values from StatusValues and specifying Table1 as the "include only related tables starting from Table1" option to limit the value list to only the values for table1.

          But note that this requires a different relationship and a different value list definition for each table that you use.

          On the other hand, you could use the "hardwired" option and define 10 different calculation fields for your 10 fields. This still requires 10 different value lists, but avoids the need for 10 different relationships.

          And instead of multiple records for the same status value but for different fields, you could format the "used by" field as a check box field and select multiple tables for the same value. You should find an example of this inside the Adventure #1 file.

          You might also consider using the "scripted find" version of a value list as a way to simplify this set up. (This was released in a revision of the Adventure #1 file so you may need to download the file again if you don't see it in your copy.)

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Filtered value list
            RockSure

            Thanks for the direction.
            I am going to work up the ladder and start with hardwire.
            Although pk field is present it is not used for the value relationships rather the ValueName is Used.
            Main4Calculations table would actual be one of my 10 tables?
            I would then have
            Main4Calculations1
            Main4Calculations2
            Main4Calculations3
            Main4Calculations4 ect.
            with each of the tables having one calculated field
            such as cNameOfTable with the calc value as “NameOfTable”
            , global storage not checked.
            I would have one Values table but 10 instances, one for each of my Main4Calculations table.

            The values:category field would have the 10 table names listed as a custom value list for that field and formatted as a checkbox.
            The values table would also have 10 calculated fields. One for each tablename and this field respectively  would be used as the show name form 2nd field in the values list.

            As an example value:cOneOfMyTableNames
            with the following calculation.

            If ( not IsEmpty ( FilterValues ( Category ; "NameOfTable1" ) ) ; ValueName )  // FilterValues is used because Category can store a return separated list of multiple values one of which might be " NameOfTable1"
             

            So far so good?

            • 3. Re: Filtered value list
              philmodjunk

              If you used the "calculation field in the value table"  option for a hardwired value list, you do not need any additional table occurrences, just 10 different value lists that each refer to a different calculation field in the values table.

              Hmm, that's in order for the value list to work, but you still would need those added occurrences (instances) if you need to link via relationship to other data in the values table.

              • 4. Re: Filtered value list
                RockSure

                Hmm, that's in order for the value list to work, but you still would need those added occurrences (instances) if you need to link via relationship to other data in the values table.

                Just to gain final clarity do I need the 10 relationships for the hardwired schema even if i do not link to other data or so I think.

                My value list has the

                Layout based on Table1 the Table1::StatusFiled has a popup format ,value list as follows:

                __pkValueTableID and displays the ValueTable::ValueName  with the "included values starting from Table1

                In relationship I have
                Table1::cTableNamecalcFIeld = StatusValues::UsedByTable and it works flawless with a relationship and when I tried to remove the relationship and only use the value list it would not display anything and says unrelated table in the pop up.

                I will move on to the scripted schema next but trying to understand if a more effective way with the this schema works and if I am missing something or its done, 

                I think I am learning, with Gods grace and thanks to your help, 

                 

                 

                • 5. Re: Filtered value list
                  philmodjunk

                  Just to gain final clarity do I need the 10 relationships for the hardwired schema even if i do not link to other data or so I think.

                  No. This particular conditional value list method does not use any relationships.

                  I will note that in the Adventures #1 file there are two examples of hardwired CVL's on the same layout. One example is relationship based. The other is not.

                  • 6. Re: Filtered value list
                    RockSure

                    I did get the "no relationship needed" to work. Somewhat confused though. In my solution I would not be setting the filtered catagory, format as checkbox on a layout with the context based on Main4Calculations since Main4Calculations is just one of 10 diffrent tables additional I am only using one of the dropdown/popup list for each of Main4Calculations tables. 

                    I would need to set that Catagory within the Values table and that I could not get to work. I am using the Values table as a global type status table.  One layout and table where I can enter a status and then attribute to what table I need that to show up as a status choice.

                    What am I missing?

                    • 7. Re: Filtered value list
                      RockSure

                      The only way I did get this to work was on a layout based on the values table is to set a custom value for the Values:catagory field with all the table names listed and format checkbox on

                      With this method this is not a dynamic table list but that maybe ok since I would have to add a new table any way and at that time update the custom value list. 

                      Right Track?

                       

                      • 8. Re: Filtered value list
                        philmodjunk

                        I'm sorry, but I can't follow your description of what you have set up.

                        The calculations would be set up only in the Status table.

                        Example, define a calculation field in the Status table with a text result type: cStatusTable1.

                        If ( UsedByTable = "Table 1" ; StatusName )

                        Set up a value list specification that lists values from the Status table and select cStatusTable1 as the source of values for table 1 status values.

                        This value list can now be used on any layout in your file where you need status values labeled for table 1.

                        Repeat this process of adding a calculation field and a value list definition for each additional table.

                        PS. I've come across other method sometimes referred to as "magic value lists" that you may wish to investigate to see if it might be a better choice.

                        • 9. Re: Filtered value list
                          RockSure

                          Sorry, what I was not aware of is the Main table and using that as the category data table. I used a custom value list which I think did the same thing however not dynamic as using the Main table. 

                          • 10. Re: Filtered value list
                            philmodjunk

                            Sorry, what I was not aware of is the Main table and using that as the category data table.

                            Huh?

                            We are using a table named Status and it most definitely should not be a main table for anything but storing values for your value lists.

                            • 11. Re: Filtered value list
                              RockSure


                              In your example category has a value list that is based on instance of a table called Main and further allows for the creation of the checkbox list. Without it where do the category values come from that make the checkbox?

                              In absence of the Main table I created a custom value list that make up the checkbox.

                              Is this not a correct understanding?

                               

                               

                              • 12. Re: Filtered value list
                                RockSure

                                In your example category has a value list that is based on instance of a table called Main and further allows for the creation of the checkbox list. Without it where do the category values come from that make the checkbox?

                                In absence of the Main table I created a custom value list that make up the checkbox.

                                Is this not a correct understanding?

                                • 13. Re: Filtered value list
                                  philmodjunk

                                  Ok, You are talking in terms of my example file. I was talking in terms of YOUR file. wink

                                  But to which value list example are you now referring?

                                  The hardwired conditional value list examples are all set up from a table named "values" and demonstrated on a table named "Main4Calculations".

                                  Only the basic conditional value lists examples are demonstrated on a layout base on Main and draw values from the same values table though using a relationship to specific occurrence of Values called "values4conditoinalvalueList".

                                  Are you now referring to how the Cateogry field is formatted with check boxes?

                                  We haven't discussed how to make those work yet as a way to reduce the number of records in your status table by using check box groups. That will require a more sophisticated calculation given the way check box fields build a return separated list of values in the field.

                                  • 14. Re: Filtered value list
                                    RockSure

                                    Sorry I was using the example in Adventure to see how it works and then implementing it in my solution.

                                     I did create a TableName table, built a value list on that and that populates the check box which is used by the calcFields in my StatusValue table.  

                                    Additional  I have multiple calc fields with the FilterValue statement in the StatusValue table,
                                    one for each of the tables that require a status. 

                                    I built a valueList for each of the tables that require a status (14 of them)
                                    In the tables requiring a status I have 2 fields that make this work one that is LIKE a FK field and actual gets the value from the PKStatusValue table (which got passed by the ValueList) and a calc field that simple has the name of that table which provides the value used only via the calculation and no relationships at all.

                                    Even though this is working would this be a correct way to do this? 
                                    2nd what if any is the drawback to not having the tables actual related as I was thinking of also doing this for the City and State tables. Using this a Global ValueList if there is such a thing. Unrelated of course.

                                    Thanks again for your patience and tremendous help. 

                                     

                                     

                                    1 2 Previous Next