I am having a bit of trouble figuring this problem out and do not know the best way to accomplish the task. Please keep in mind and excuse my ignorance, for that I am new to filemaker and databases. For all i know everything could be completely wrong in setup/execution.
The background: In the science field, with the goal of capturing all test results from multiple tests formats, and capture them in a table then compiling the Matching and Flagged" results into a second summary table. The catch is that a single sample can and will be ran multiple times and or repeated as a confirmation, but ONLY ONE will be used as the final data. In other words I need to retain all information pertaining to a single sample being tested multiple times, but selectively pull out the flagged records only I have already wrote a script to ensure only 1 record with the uniqueID can have a flag upon user selection.
Table1: Test Results
|Record#||RunName (unique)||SampleUniqueID (repeatable)||Test 1||Test2||ReportFlag|
Table Test Summary:
|SampleUniqueID (unique values)||Test1||Test2||Test3|
This is where I cannot get the calculations field for Test1 and Test2 to work properly. If set up as a calculation it only displays the first found related record and displays. I could not find a way to loop and go to next record in a calculated field. My hunch is that I do not know how to use the List/Lookup/Filter functions correctly.
So I attempted an eSQL which is able to to find the flag values only, but seems to repeat that found value to all records regardless if the uniqueID matches. I also find that the windows refresh doesnt populate changes when I modify the Flag value. Most likely a seperate problem. My concern by this method is the workload due to the anticipated amount of records. (100,000+ /yr)
ExecuteSQL ( "SELECT ROUND(test1) FROM table1 INNER JOIN table2 ON table1.UniqueID=table2.UniqueID WHERE table1.ReportFlag='Y''";"";"" )) Yields results like this
Any advice is greatly appreciatied