3 Replies Latest reply on Jul 7, 2009 9:55 AM by GeoNick

    Conditional value lists



      Conditional value lists


      I am trying to set up a value list that shows only active jobs from a file with numerous job numbers.


      The value list is to be used in a Time recording file to assign a job number to each interval of work time.


      In the Jobs list file I added a field called Status that contains the text either "active" or "Inactive".


      Then I added a calculation field with the result = "Active" to act as the key field for a self join relationship with the Status field.


      Then from the time record file I set up a relationship to the Job list file vi the Job ID number. Then I added a second instance of the Job list file and created the self join relationship.


      Then I set up the value list from the Time record file and used the related job number field from the second instance of the jobs list file. I set this list to show only the related values starting in the first instance of the Jobs list file (and also to show the Job name field in the menu)


      Then I associated the list with the job number field in the Time record file.


      The abbreviated list works perfectly if the field already contains a job number from the jobs file (even if that job is inactive), but if the field is empty I get nothing.


      What's gong on?

        • 1. Re: Conditional value lists

          Amazing how venting a little spleen can solve problems.


          I created a second instance of the Time record file and set up a relationship to the second instance of the Jobs list file.


          That bypassed the ID relationship and now the menu works fine on the Job ID field.

          • 2. Re: Conditional value lists
               One other option is to create a stored calculation field, that if the job is Active, stores the job ID/number.  Then, create a value list based on that calculated field.  No joins or relationships necessary.
            • 3. Re: Conditional value lists

              Well, actually I would still need the original ID relationship since the field is in a separate file (upgraded from Filemaker 6 and havent got around to merging into one file yet).


              Anyway, your solution is much simpler and hence less likely to break.