Copy paste large set of records to global field
Operating system version
Windows 7 Pro 64 bit
Description of the issue
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]
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."
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.
Filemaker cache size: 256 MB
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.