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.
(PatternCount(test, "ACT") and PatternCount(description; "English"))
((PatternCount(test; "ACT") and PatternCount(description; "Math))
Set Field [Error; "yes"]
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
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.
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.
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]
If [(PatternCount(ASSESSMENT_FACT::TEST DESCRIPTION; "ACT") and PatternCount(ASSESSMENT_FACT::ITEM DESCRIPTION CODE; "ENGLISH")
(PatternCount(ASSESSMENT_FACT::TEST DESCRIPTION; "ACCU") and PatternCount(ASSESSMENT_FACT::ITEM DESCRIPTION CODE; "WRITEPLACER")
(PatternCount(ASSESSMENT_FACT::TEST DESCRIPTION; "AP") and PatternCount(ASSESSMENT_FACT::ITEM DESCRIPTION CODE; "BIOLOGY"))))
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)]
go to Record/Request/Page [Next; Exit after last]
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 ]
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]
If [(Lookup Table::ItemDescription Field 4 = ASSESSMENT_FACT::ITEM DESCRIPTION CODE)]
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)]
Go to Record/Request/Page [Next; Exit after last]
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.
Apologies for over simplifying here.
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.
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.
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.