4 Replies Latest reply on Jan 7, 2014 3:24 PM by philmodjunk

    Copy paste large set of records to global field

    RamziAbbyad

      Summary

      Copy paste large set of records to global field

      Product

      FileMaker Pro

      Version

      11

      Operating system version

      Windows 7 Pro 64 bit

      Description of the issue

      Hi there,

      I've been having some difficulty solving an issue corresponding to cleaning some records in my database.  I'm running filemaker pro 11 on a computer with 4 gigabytes of RAM, intel i5 quadcore CPU with 3.2 GHz processor.  I also increased filemaker's cache size to 256 MB. 

      The problem is as follows:  I have a table with 11 fields and 32 million records.  One field in my table has a number of erroneous records with the value "null" entered into a number type field called IRS Number.

      I want to write a script that replaces these null values by scanning the rest of the table for records that match a separate field, which is a text field called "Manager Name" and matches them to a found set containing the "null" IRS Number records.

      I have three tables:

      "db"- containing the full set of records with a Primary Key called RecordID.

      "NullIRS" - with three fields: "Manager Name", "IRS Number", "RecordID"

      "NotNullIRS" - having the same fields as "NullIRS"


      To accomplish this, I want to set up two relationships.  These relationships involve finding the null and not null IRS NUmber values, then copy pasting the RecordIDs into a global field called "RecordID" which will serve as a foreign key to establish relationships between the two tables and the original table.  So far I have set this field to type "Number," but I am now trying the same steps but with the global field set to type "text."  I will keep you posted on the results.

      The found set for NullIRS values is about 2 million records, from which follows that the NotNullIRS found set contains the remaining 30 million records.

      I have no problem copy pasting the NullIRS foreign key, but the NotNullIRS foreign key values simply won't paste.  I think what is happening is the clipboard can't accommodate such a large value to paste into the corresponding global field.

      PLEASE HELP, I've been working on this forever.  I keep telling the people at my work that Filemaker simply can't handle such a large table, but they won't listen.

      Steps to reproduce the problem

      On a dedicated layout in the "db" table that contains the RecordID Primary Key, find all non-null IRS Number values.

      Go to Layout[RecordID]
      Copy All Records
      Go to Layout[NotNullIRS]
      Create New Record
      Paste[into field RecordID]
      Commit Records

      Expected result

      That the carriage return - separated list of RecordID values that correspond to the Not Null IRS Number found set, will be pasted into a global field "RecordID" in a separate table "NotNullIRS."

      Actual result

      Copy finished, i see no sign that the values have been pasted into that field.  Not this works fine for the list that is 2 million records long.

      Exact text of any error message(s) that appear

      No error messages received.

      Configuration information

      Filemaker cache size: 256 MB

      Workaround

      Copy and pasting one record at a time but this takes FOREVER- hours and hours.  I don't think this will be a feasible option.

        • 1. Re: Copy paste large set of records to global field
          philmodjunk

               Report An Issue is intended for Reporting Possible Software bugs. Questions on how to work with FileMaker Pro should be posted in the FM Pro Forum Section. (See tab at top of this screen.)

               My guess also is that the clipboard can't handle the size of the copy, or that FIleMaker can't "Copy All" such a large block of data into it.

               

                    Copy and pasting one record at a time but this takes FOREVER- hours and hours.

               I wouldn't use copy and paste for a one record at a time loop through your data, I'd use set variable, set field to do it one record at a time, but "hours and hours" don't necessarily make this an unfeasible option if this is intended as a "one time" fix of the data. Depending on the work schedules of your users, you might be able to kick off such a script at close of business and find everything finished up and waiting for you at start of business in the morning. In some cases, you might even be able to run such a script on a back up copy and then use the back copy to either replace or update data in the working copy.

               And a "middle of the road" work around might be to manipulate the found set such that you copy all records from smaller found sets and then paste into field 1 before using set field to append that data to field 2, thus allowing you to build your list in sections.

               Another way to get a set of return separated values is to use a relationship such that the List function returns the return separated list of values. Then you could use Set FIeld to set the foreign key field to the results of this function.

               And you might consider using Import Records to import the data from your found set into a join table and use it in place of your return separated list of values.You'd use Import Records to create the records and assign a value to one foreign key field and then use a Varible and Replace Field Contents to assign the second foreign key value to it.

               PS. Some of my tables now have several million records and they work just fine. But it does require more forethought in scripting and interface design to avoid getting "bogged down" due to the much larger records sets and indexes that are involved.

          • 2. Re: Copy paste large set of records to global field
            RamziAbbyad

                 So I guess if no one wants to read all of that can you please tell me the maximum size of the clipboard in filemaker 11 pro?  Or whether it is possible to define a relationship based on a multi-key relationship which matches a number type serial field to a global field of type text?  Thanks!

            • 3. Re: Copy paste large set of records to global field
              TSGal

                   Ramzi Abbyad:

                   Thank you for your post.

                   If the clipboard can't handle the large amount of data, try storing the data into a variable and then update the field with the variable value.  For example,

                   Go to Layout [ RecordID ]
                   Go to Record/Request/Page [ First ]
                   Loop
                      Set Variable [ $text ; $text & <field data> & "¶" ]
                      Go to Record/Request/Page [ Next ; Exit after last ]
                   End Loop
                   Go to Layout [ NotNullIRS ]
                   Create New Record
                   Set Field [ RecordID ; $text ]
                   Commit Records

                   -----

                   In essence, I'm looping through each record and placing the data  into a variable, separated by a return.  Once all records have been processed, the record is added to the new table, and the field is populated by the contents of the variable.

                   The variable may also exceed memory, so you may have to either update 1000 records at a time where you keep another variable for counter, and when the counter reaches 1000, you update the field in the other table, reset the counter to 1, and continue with the loop.

                   Let me know if you need additional clarification.

                   TSGal
                   FileMaker, Inc.

              • 4. Re: Copy paste large set of records to global field
                philmodjunk
                     

                          Or whether it is possible to define a relationship based on a multi-key relationship which matches a number type serial field to a global field of type text?

                     Yes and no.

                     In this relationship:

                     Table1::GlobalTextField = Table2::NumberField

                     The relationship is pretty much "one way". You can use it from the context of Table1 to access related records in Table2, but you cannot do the opposite. Thus, a portal to Table2 on a Table1 layout will work. But a field or portal to Table1 on a Table2 layout will not produce results that are generally useful.

                     But using a return separated list of values in a match field like this sets up the field with the list of values as a kind of "limited join table". You can get the same relationship function with a join table so you may want to try using import records to generate records in a join table instead of copying such a large amount of data into a global text field.