1 Reply Latest reply on Aug 13, 2009 9:37 AM by TSGal

    Complicated Lookup or merging of data



      Complicated Lookup or merging of data


      I have a Main Table with 15,000 records that I would like to add some select data to.


      I am pulling the data I want to add from 10 "Large Tables" with about 10,000,000 records in each table, so I am trying to figure out the most efficient way to do this. I only need 5 records form each table of 10,000,000 for each of the records on the Report Table of 15,000.


      My Main Table (15,000 records) looks like this:


      F1         F2            F3          F4         F5       F13

      G6000     Data1     Data2     5609     6609 etc 7806

      G7000     Data1     Data2     6795     3948 etc 8560


      F1 = Unique Record number

      F2 = Data for the record

      F3 = Data for the record

      F4 = ID number that appears in F1 of Large Table 1

      F5 = ID number that appears in F1 of Large Table 2

      etc. so F13 is the ID number that appears in F1 of Large Table10


      Large Tables (10,000,000 records each) are all set up the same w/ four columns and look something like this.


      F1         F2         F3         F4         F5

      5609       A         B           C         100

      5609       A         A           A         200

      5609       B         C           A         5000

      5609       B         A           A         567

      6795       A         B           C         200

      6795       A         A           A         201

      6795       B         C           A         5500

      6795       B         A           A         600


      F1 - ID number that appears in corresponding field in the Main Table (this id number repeats itself multiples times for every each data point in F5

      F2 - Code that helps identify F5

      F3 - Code that helps identify F5

      F4 - Code that helps identify F5

      F5 - The data I am after.


      I know the first step is to establish relationships between the fields with ID numbers in the Main Table and the Large Tables. For example I established a relationship between F4 in the Main Table with F1 in Large Table 1 and another between F5 in the main table


      Here is a simplified example of what I am trying to do:


      Assuming I want to add the values from the Large table that come after F1=(Desired ID) F2=B F3=C and F4=A into the Main Table. I assume I need to do the following:


      1. Create a new F14 field for the Main Table where the data from Table 1 will show up.
      2. Create some sort of script or look up that puts the F5 data in the F14 field when F1, F2, F3 and F4 equal specified values.

      I guess another way to do this would be to constrain the Large Tables so that they only show a certain combination of F2, F3 and F4 fields and so there is only one instance of the ID number in each table, and then do a look up. The only problem with doing that is that the data in the Tables will be updated form time to time and would require the tables to be revised with each update. However if there is a way to do a lookup on constrained data (or a found set) it may not be a problem.


      Any ideas or direction would be helpful.



        • 1. Re: Complicated Lookup or merging of data



          Thank you for your post.


          If you want to add/update select data, then isolate/find the select data first (e.g., find all records where data is 8/13/2009).


          Once the select data is found, return to your main table and import from the table that includes the select data.  In the import dialog box, select the "Import Action" to "Update matching records in found set".  You would then select the F1 field as the matching field.  If there are extra records without a matching F1 field, you can also check the option to "Add remaining data as new records", and those records will be added to the end of the main table.


          Once you create your F14 field, you can have a script that finds the records where:


          F1 = <desired value>

          F2 = <desired value>

          F3 = <desired value>

          F4 = <desired value>


          Then, issue a Replace Field Contents script step where the contents of F14 are populated by F5.


          For example:


          Set Error Capture [On]

          Enter Find Mode []

          Set Field [ F1 ; <desired value> ]

          Set Field [ F2 ; <desired value> ]

          Set Field [ F3 ; <desired value> ]

          Set Field [ F4 ; <desired value> ]

          Perform Find []

          If [ Get ( FoundCount ) > 0 ]

             Replace Field Contents [ No dialog ; F14 ; F5 ]

          End If




          This should give you a good starting point.  Let me know if you need clarification for any of the above steps.



          FileMaker, Inc.