AnsweredAssumed Answered

Pull Flagged Data From Similar Records in Table

Question asked by disabled_brianb on Mar 21, 2017
Latest reply on Jun 13, 2017 by disabled_brianb

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.

 

 

Example:

 

Table1: Test Results

                                                                                                   

Record#RunName (unique)
SampleUniqueID (repeatable)Test 1Test2ReportFlag
1abc123-123-12310
2def123-123-12320Y
3ghi123-123-1235Y
4jkl555-555-5554

 

Table Test Summary:

SampleUniqueID (unique values)Test1Test2Test3
123-123-123520-
555-555-555---

 

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

 

SampleUniqueIDTest1
Test2
Test3
123-123-123520-
555-555-555520-

 

 

 

Any advice is greatly appreciatied

Outcomes