3 Replies Latest reply on Aug 15, 2014 1:27 PM by philmodjunk

    Value List Setup



      Value List Setup


           I am sure there is a simple solution to this, but I am getting bogged down.  I am trying to create a Daily Report for our filed forces in Filemaker Pro.  It will be hosted on Filemaker server and used with FilemakerGo.

           Each job is divided up into locations.  Each Location has activities.  Each activity has categories (Labor, Materials, Equipment, Subcontractor, General Conditions).

           Each Job will have a daily report on the days work is being performed.  Job information is pulled from a job table populated from our accounting system.  Location information is being selected in a popover button with a check box (can work multiple locations in a day) populated from a table that contains the Job #, Locations, Costcodes, and Categories.

           I am able to pull up the cost codes in a popover button with check boxes (can also work multiple activities in a day) for all of the categories.  In order to simplify the data entry, I only want to present the Labor and Subcontractor cost codes an activity popover and the material cost codes in a material popover.

           I setup a calculated field in the cost code table that sets a value of LS, M, or NA based on the category.  I just want to setup my value list so that the activity cost codes are essentially filtered by NS and the material costcodes are filtered by M.

           I am just getting bleary eyed looking at this database and walking away from it will probably help me see a solution, but I was wondering if anyone had any guidance.


        • 1. Re: Value List Setup

               What you describe is a conditional value list. While one normally sees examples of this where you select a value in field A and this filters the values displayed for field B, this isn't the only option. Like in your case, the "filtering category" can be pre-defined. There are two ways to do this. One method is to set up a pair of calculation fields that compute constant values of "LS" or "M" in your layout's table as match fields to your costcodes table. since the calculation fields always have the same value, they always match to the same set of costcode records for your conditional value list.

               But you can also define a of calculation field in your costcode table that return the desired costcode if the record is "LS" and is empty if it is not. You can then use that field to supply values to your value list. This can be repeated with a second calculation field for "M" costcodes. The advantage tothis approach is that it does not require any relationships and thus can be used on any layout where you need these value lists.

               For more on conditional value lists, see: (the forum tutorial covers both of the above methods as "option 1 and option 2".)

               There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

               The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

               Forum Tutorial: Custom Value List?

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

          • 2. Re: Value List Setup

                 I was able to accomplish filtering the value list with the first (calculation) method.  I tried the second (table relationship) method first, but could not get the results I wanted.  


                 My current issue is not being able to select items in one of the checkboxes I created.  They show up, but I cannot choose anything.  I tried the formatting the text, checking the relationships, and making sure the related fields are the same field type (number rather than text).  I will continue to search forum posts and Google, but if you have any advice, I would appreciate it.

            • 3. Re: Value List Setup

                   Is it possible that this is a field of type calculation instead of a field of type text? The value of a calculation field cannot be edited.

                   You might also have Browse mode access prohibited if the "Browse" check box is not selected for this field in the Behavior section of the Inspector's data tab.

                   Using Button Setup to format the field as a button would also produce this result.

                   If you have a transparent button on top of the check box field, this will also keep the boxes from being clickable.

                   And there are auto-enter calculations that might produce this result.