3 Replies Latest reply on Jun 5, 2014 9:11 AM by philmodjunk

    Classifying fields so that they are searchable for report



      Classifying fields so that they are searchable for report


           Hi all,

           I have just transitioned from Access to Filemaker and am still trying to get my head around the differences, so this is probably a typical newbie question, but I was hoping someone on here may be able to help me.

           I'm an bioarchaeologist and I am building a database for my skeletal research. I have a database where the skeletonID is the primary key, and then several tables that adds information about various skeletal features. 

           The table I have gotten stuck on is a table of non-metric traits that each skeleton could have. There are 30 or so of these traits, and they are each coded as 0-absent, 1-present or 2-partial. I have set up a table with the skeletonID as the key, and then fields for each trait - so far so good. Each skeletonID is a record, with all the traits filled in as 0, 1 or 2. Here's the problem: Each trait can have three additional attributes: they can 1) either be cranial or postcranial, 2) primary or supplementary (Standards Classification) or 3) classified into one of 5 groups with names like Hypostotic or Hyperostotic etc. (Saunders classification) These classifications do not really have to be on the layout, but I would like to be able to search for them in queries/reports, so that I can search for "all cranial traits" or "all primary traits" for example. 

           The only way I can think of is to have a portal where you would choose the trait from a dropdown list for each record, and then matching valuelists with the classifications for the following fields (i.e. my table would have only the fields for SkeletonID, Trait, and the three different classes, with a new record for each trait) , but I would prefer to just have all the traits spelled out in the table and listed on the layout if possible. I would also like to have the classification be automatic, so that all "MetopicSuture" entries would automatically be classified as "Cranial", "Primary" and "Hypostotic" for example, without having to choose the classification from a list each time. 

           Is there a more elegant way to solve this? Sorry for the long post, I'm probably not very good at explaining what it is I'm trying to do... I appreciate the help!


        • 1. Re: Classifying fields so that they are searchable for report

               To answer the question asked in the title, all fields except container fields are "searchable" in FileMaker, but I don't think that's the real issue here.

               Am I correct that the "three additional attributes" and the "5 groups" are all specific to particular "traits" fields and are the same for every skeleton?

               Thus a search for "Cranial" means that you are searching for a record where at least one Cranial trait has a non-zero value or possibly a specific value like 1 or 2?

               If I'm right, you don't need a portal with a table of related recors, though a table of related records with one record for each trait would make the search simpler. Without the related table, such a find would need to be scripted to search on all the different fields with the specified "attribute" or "group" with logic that replicates using "OR" in an SQL Where clause--of which you might be familiar having used MS Access.

               Note that if you do end up using a portal to a related table here, a Script can "pre load" each new record in the main table with a specified set of related records with specific values already entered into each field of those records so that you only need record the data specific to the skeleton you are studying and not have to specify which trait before recording whether or not that trait is present.

          • 2. Re: Classifying fields so that they are searchable for report

                 Thanks for the reply!

                 Yes, that's correct - the three additional attributes would be the same for each skeleton and each trait. If I am understanding you correctly, what I would have to do is a regular SQL query (or regular to me, anyway, like I would have in Access), and just make reports for "Cranial" traits and all the others manually. I was just hoping there would have been a less labor intensive way to do it, since an SQL would involve a Where statement with 30 or so "Or Like" for each of the possible attributes.

                 Excuse the newbie question again, but how would a separate table with the traits make the search simpler? Could I do a Lookup table to fill in the attributes whenever a trait "Is Not Null"?

                 Thanks again,


            • 3. Re: Classifying fields so that they are searchable for report

                        what I would have to do is a regular SQL query

                   That's not what I meant. You really can't do a "regular SQL query" in FileMaker using just the built in tools that it comes with. What I referred to was a scripted find that produces the same results as such a query would in Access.

                   Here's why a related table could simplify the search. Since I an unfamiliar with your field, I'm going to use made up values in the related table.

                   Say you have two Parent records (skeleton in your case) related to different sets of child records with these values:

                   Parent #1:

              Category       Name
                   Fruit             Apple
                   Vegetable     Carrot

                   Parent #2:

              Category       Name

                   Fruit             Cherry
                   Fruit             Apricot

                   In FileMaker, you could use a portal to this child table to list the related record for each parent record. (just like a subform or subreport in Access).

                   If you entered find mode and specified "Fruit" in the category field in this portal, the Find would find both of these records. But if you specifed "vegetable" instead, the find would only find Parent #1. When you specify search criteria in a related table like this, FileMaker finds all parent records with at least one related record that matches the specified find criteria. Which would appear to be exactly what you want.

                   For more information of FileMaker scripted finds, see: Scripted Find Examples