8 Replies Latest reply on Jan 28, 2014 9:12 AM by dperez@animas.net

    Field Verification Script

    dperez@animas.net

      Title

      Field Verification Script

      Post

           Hello,

            

           I've got a problem that seems simple when I think about it, but I can not figure out the logic to evaluate columns of entries to verify that a Test Description belongs to the indicated Test Group. 

            

           As an Example I've got several Groups of Tests, each with several Test Descriptions within each group.

            

      Test Group   Test Description

           ACT             English

           ACT            Math

           ACT             Reading

           ACT             Science

           AP              Art History

           AP              Biology

           AP              Calculus AB

           AP              Chemistry

           COMP          Math

           COMP          Writing Skills

           COMP          Writing Essay

            

           What I need to find out is,  for each Test Group, are the several Test Descriptions that are available for each group valid?  In other words, I would not be able to have ACT and Biology as a valid pairing since Biology belongs to the AP Test Group.  The validation script I've been trying to create would then mark the incorrect pairings in each record.

            

           Thank you for any help.

        • 1. Re: Field Verification Script
          NicholasFernandez

               Loop an "If" statement that uses patterncount to test all valid answers for each record. Have the "Else" part of your script put a marking in a dedicated field to denoting an error.

                

               Something like:

                

               If:

               (PatternCount(test, "ACT") and PatternCount(description; "English"))

               or

               ((PatternCount(test; "ACT") and PatternCount(description; "Math))

               or.

               ....

               Else:

               Set Field [Error; "yes"]

          • 2. Re: Field Verification Script
            philmodjunk

                 Set up the data that you show in your first post in a table with two fields, one for each column shown. Use this table as a source of values for a value list for selecting descriptions. Use a relationship between a Test Group field in your main table and the test group field in this table. Then you can make  a conditional value list for selecting test descriptions such that selecting "AP", in a test group field in one field results in the value list only showing choices for the description to be Art History; Biology Calculus AB; or Chemistry.

                 This eliminates the need for a validation check by not allowing the user to select/enter the wrong description in the first place. If this is data that is imported from another source and you need to validate. Set up the same related table but use:

                  

                 Not IsEmpty ( TestDescriptions::Description )

                 To confirm that the test group and description fields are consistent.

                 Here are some links on conditional value lists. You only need to read the first or second item listed. I've included links to others if you are interested in learning more on the subject.

                 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.

            • 3. Re: Field Verification Script
              dperez@animas.net

                   Hello,

                    

                   Thank you both for the information.  I think that conditional value lists are definitely something that I'm going to have to investigate more.  I am taking an export of another database, that has very little verification built in, and importing it into a required template that I've recreated in Filemaker Pro 12.  The corrected data in the template is needed by a state agency.  When I look at the RAW data that I'm importing I want my FileMaker database to help me verify the data and then show me the mistakes so they are easily  identified and corrected, before the data is forwarded to the state agency. 

                   I was able to get the following script to work just like I wanted.  The only problem is, I think it may no be the most efficient way to achieve what I want.  If anyone has any idea to help reduce the number of "OR" statements, please let me know.  I have about 155 combinations that will need to be checked.  I've added my test of only 3 checks below. The good thing is, once all of the OR statements are in I won't need to do it again.  

                   Thank you again.  I appreciate the help.

                   Daniel

                   ----------

                   ASSESSMENT_FACT is my Table

                   TEST DESCRIPTION and ITEM DESCRIPTION CODE are my 2 Fields that are being verified against each other.

                   ---------------

                   Go to Field [ASSESSMENT_FACT::TEST DESCRIPTION]

                   Show All Records

                   Go to Record/Request/Page [First]

                   Loop

                        If [(PatternCount(ASSESSMENT_FACT::TEST DESCRIPTION; "ACT") and PatternCount(ASSESSMENT_FACT::ITEM DESCRIPTION CODE; "ENGLISH")

                    

                        or

                    

                        (PatternCount(ASSESSMENT_FACT::TEST DESCRIPTION; "ACCU") and PatternCount(ASSESSMENT_FACT::ITEM DESCRIPTION CODE; "WRITEPLACER")

                    

                        or

                    

                        (PatternCount(ASSESSMENT_FACT::TEST DESCRIPTION; "AP") and PatternCount(ASSESSMENT_FACT::ITEM DESCRIPTION CODE; "BIOLOGY"))))

                    

                        Else

                             Set Field [ASSESSMENT_FACT::TEST DESCRIPTOIN; TextStyleAdd (TextColor ( ASSESSMENT_FACT::TEST DESCRIPTION & " *" ; RGB ( 255 ; 128 ; 0 ) ) ; Bold)]

                    

                             Set Field [ASSESSMENT_FACT::ITEM DESCRIPTOIN CODE; TextStyleAdd (TextColor ( ASSESSMENT_FACT::ITEM DESCRIPTION CODE & " *" ; RGB ( 255 ; 128 ; 0 ) ) ; Bold)]

                    

                        End if

                        go to Record/Request/Page [Next; Exit after last]

                   End Loop

                    

                    

              • 4. Re: Field Verification Script
                philmodjunk

                     Simplifying the expression is one of the reasons for setting up the related table. you can use it and get just this single If expression:

                     If [ RelatedTable::Item Description Code = YourTable::Item Description code ]

                • 5. Re: Field Verification Script
                  dperez@animas.net

                        

                       Hello,

                       I got the Conditional Value Lists going, and allI can say about that is COOL!!!  I'll be using more of those for sure.  :-)

                        

                       Still running into a problem though.  (The Lookup Table is where I have all of the valid entries and where my conditional list are coming from. )

                       I'm using this script to run through all of the entries to verify the Data that I've imported into my template.

                  Go to Filed [ASSESSMENT_FACT::ITEM DESCRIPTION CODE]

                  Show All Records

                  Go to Record/Request/Page [First]

                  Loop

                       If [(Lookup Table::ItemDescription Field 4 = ASSESSMENT_FACT::ITEM DESCRIPTION CODE)]

                       ELSE

                            SET FIELD [ASSESSMENT_FACT::TEST DESCRIPTION; TextStyleAdd (TextColor ( ASSESSMENT_FACT::TEST DESCRIPTION & " *" ; RGB ( 255 ; 128 ; 0 ) ) ; Bold)]

                            SET FIELD [ASSESSMENT_FACT::ITEM DESCRIPTION CODE; TextStyleAdd (TextColor ( ASSESSMENT_FACT::ITEM DESCRIPTION CODE & " *" ; RGB ( 255 ; 128 ; 0 ) ) ; Bold)]

                       End If

                       Go to Record/Request/Page [Next; Exit after last]

                  End Loop

                        

                       At first I thought it wasn't working at all, but found that it is only evaluating one entry per TEST DESCRIPTION.  The strange thing about it is its not always the first entry although most often it is.  Notice the ACCU example below.   I figured out that the way I entered the data in the Value List Table and the way it was getting sorted contributed to the way it appeared in the processed validation.  It IS only properly validating the first instance in each Test Description. 

                       Each of the entries in the example should evaluate as correct leaving them unmodified in plain text. 

                  • 6. Re: Field Verification Script
                    philmodjunk

                         Apologies for over simplifying here.

                         Use:

                         Not IsEmpty ( FilterValues ( List ( Lookup Table::ItemDescription Field 4 ) ; ASSESSMENT_FACT::ITEM DESCRIPTION CODE ) )

                         But your set field steps intended to correct the error won't work as there is no way from just this data to determine which of several related values is the correct value to use in order to make the correction.

                    • 7. Re: Field Verification Script
                      dperez@animas.net

                           IT's ALIVE!

                           THANK YOU!

                           I replaced my If Statement with: 

                      If [(not IsEmpty ( FilterValues ( List ( Lookup Table::ITEM DESCRIPTION CODE) ; ASSESSMENT_FACT::ITEM DESCRIPTION CODE ) )) and (not IsEmpty ( FilterValues ( List ( Lookup Table::TEST DESCRIPTION) ; ASSESSMENT_FACT::TEST DESCRIPTION ) ) )]

                           And it works perfectly.  

                           I'll need to figure out exactly what the logic is doing, but you've got me going.  

                           Thanks Phil.  This is the Best Answer After all.

                      • 8. Re: Field Verification Script
                        dperez@animas.net

                             Oh just wanted to clarify for other readers. 

                        "But your set field steps intended to correct the error won't work as there is no way from just this data to determine which of several related values is the correct value to use in order to make the correction."

                        The Else statements are only meant to Mark the incorrect entries so that they are easily identifiable for later corrections. They are not intended to auto correct from the value list.