4 Replies Latest reply on Apr 16, 2010 2:04 AM by swampy_1

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

    swampy_1

      Title

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

      Post

      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 ...

       

      Antibiotic

            Aminoglycocide *

            Penicillin *

            Cehalosporin *

      Antihypertensive

            Beta-blocker *

            Diuretic

                 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.

       


        • 1. Re: Creating a Value List from a file with a conditional find - can it be done?
          fitch

          Typically a checkbox field that's a Boolean is set up to use a value list that's simply the number 1, so it's 1 if checked, empty if not. Create a calculated field that is also 1. Make a self-relationship of the calc to the checkbox field.

           

          Now you can create a value list that shows only the related values.

          • 2. Re: Creating a Value List from a file with a conditional find - can it be done?
            swampy_1

            Dear Tom,

             

            I understand what you are saying but I cannot see in the programme where you can set a calculation to the Value List definition.  My two options on the setup titled "Specify Fields for Value List ..." are...

             

            • Include all values

            • Include only related values starting from {Name a file}

             

            This latter choice seems like the place where a calculation could be placed, but it only shows files and not fields or calculations.

             

            Where can I put a sieve calculation?

            • 3. Re: Creating a Value List from a file with a conditional find - can it be done?
              fitch

              The calculation engine is not available everywhere in FileMaker. Value Lists, Sorts, and Finds for example have their own quirky ways. "Include only related values" is what you want. You're not specifying a file, but rather a Table Occurrence (TO).

               

              You have to go to the relationship graph (File menu > Manage > Database), create a TO of the table that contains the values, and link it to the TO of the table with the calculated "constant."* In this case, they are two TOs of the same table.

               

              (*It's also possible to use an auto-entered field for the constant, or a global field.)

              • 4. Re: Creating a Value List from a file with a conditional find - can it be done?
                swampy_1

                Thanks for trying to help Tom but this is just going nowhere.  I have guessed from your note how to go about this but no matter how many times I try, I either get all records from the parent file or none.  I now know why I used more programmable databases in the past where you can write procedures that actually get the job done.  Harder work but there is nothing like a bit of sensible code through which you can trace to do almost anything you want.

                 

                Thanks for your help but I think I'm going to have to put FileMaker back into the "not serious database" category again.