AnsweredAssumed Answered

Creating a Value List from a file with a conditional find - can it be done?

Question asked by swampy_1 on Apr 13, 2010
Latest reply on Apr 16, 2010 by swampy_1


Creating a Value List from a file with a conditional find - can it be done?


I am new to FileMaker, running version 11.01 on a Macintosh (OS 10.5.8) system.  I have a history of using other databases and am trying to come to terms with how to use FileMaker.  My task is to use data from one table to act as input into data from another table.  The tables are joined in a one to one relationship.


In the primary table, called "Classes", I have records which can classified as SuperClass, Class or SubClass.  That way in the one flat file, I can logically embed hierarchy.  The task is to enter a list of drug classes and arrange them in a logical hierarchy.  Each record also has a field which marks whether a record is an end-point, ie no other record exists in this logical sequence.  Entries that are marked this way have an asterisk.  An example of data from the "Classes" table may look like ...



      Aminoglycocide *

      Penicillin *

      Cehalosporin *


      Beta-blocker *


           Thiazide diuretic *

Asthma medication

      Beta2 Agonist

           Short term beta2 agonist *

           Long term beta2 agonist *

      Atropine antagonist *


A snapshot of some data from the "Classes" table looks like this ...



etc etc where you can see that in the one file, simply by selecting a single field, an entry can be classified as "SuperClass" eg Antibiotic, Antihypertensive or Asthma medication, "Class" with Aminoglycoside etc etc.  Of the 13 entries, only 8 are end point records. This is the subset in which I am interested.


In a second table, called "Generic" I want to link the type of medication the specific generic agent is according to its classification from table "Class".  I can easily pull up a values list pop-up menu that contains every entry in the the "Class" file, but that is silly.  For instance, I don't want to see the choice "Antibiotic" because it is not specific enough.  What I want it to do is to put only those entries that are end points in the logical chain, ie Aminoglycocide, Penicillin, Beta blocker etc.


What I cannot see is how you can add a filter to a Value List.  It seems that it is all or nothing.  In my example, I would include a value for a checkbox field that the entry is an end point.  I can see that you can add multiple parameters to a Value list criteria set, but they have to be fields on both sides.  Where can I add a line that says "<Field Value> = 0?


Maybe I am tying something that is beyond FileMaker, but hopefully not.