5 Replies Latest reply on Jun 13, 2017 3:46 PM by brianb

    Pull Flagged Data From Similar Records in Table

    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