Re: FMP question
Re: FMP question
- Subject: Re: FMP question
- From: Shane Stanley <email@hidden>
- Date: Thu, 02 Nov 2000 11:43:12 +1100
On 2/11/00 9:59 AM +1000, Paul Berkowitz, email@hidden, wrote:
>
"with data". Aha! I tried "with properties", and specifying the cells, in
>
the existing database and got nowhere. But FMP was calculating the other
>
fields in the existing layout, which I think are still wanted. Can this
>
method be used somehow with the existing layout?
Not really -- but creating another layout is easy enough. It doesn't have to
be visible at all.
>
Where does this n come from in:
>
>
if n {b, f, c, d, e} then
>
>
And my email couldn't read the character after 'n', what is it? (This may
>
turn out to answer the previous question too.)
"does not equal". Because FMP will match "Johnson" with "John", this does a
check for an exact match.
>
>
> That should speed things up quite a bit. But if you're going to be getting
>
> more than a handful of records at a time, there are ways that are
>
> considerably faster still...
>
>
You intrigue me
It happens I've been playing with this myself recently. Ignore your checking
for duplicates for the time being.
Until a week ago, I would have said "create new record at end with data
{...}" is as fast as it gets. The number of Apple events is more or less
what counts with FMP, and that method amounts to one per record. Sounds
convincing.
But the "field" class lets you get and set the same cell in more than one
record at a time. So if you have 20 records, you can set the value of the
first cell of each with:
tell layout x
set field 1 to {1, 2, ... 20}
end
And if your layout contains only, say, three fields, you can use:
tell layout x
set fields to {{1, 2, ... 20}, {1, 2, ... 20}, {1, 2, ... 20}}
end
So if you can reorder your data appropriately, you can populate a lot of
records with one event -- and it happens quite quickly. Reordering
tab-delimited data might require something like:
on makeFieldList(theData, fieldCount)
set fieldList to {}
repeat with i from 1 to fieldCount
set recTemp to {}
repeat with aRec in theData
set end of recTemp to text item i of aRec
end repeat
set end of fieldList to recTemp
end repeat
return fieldList
end makeFieldList
That still leaves the problem of creating the new records first.
Unfortunately, "create new" makes only one at a time, which would probably
cost us any time saved by setting fields rather than records.
But there's another command that can be used: duplicate. And FMP can
duplicate a lot of records quite fast.
So if we make a record at the end, then another, then duplicate them both,
then duplicate the last four, and so on, we can create a large number of
empty records with a relatively small number of commands. You could use
something like this:
on createRecords(howMany)
tell application "FileMaker Pro"
tell database 1
if howMany < 4 then
repeat howMany times
create new record at end
end repeat
else
create new record at end
create new record at end
set n to 2
repeat
duplicate (records -n thru -1)
set n to n * 2
if n * 2 > howMany then
duplicate (records -(howMany - n) thru -1)
exit repeat
end if
end repeat
end if
end tell
end tell
end createRecords
Then all you have to do is show the new records:
show records -howMany thru -1
And set the fields, this time addressing the found set (document 1) rather
than the database:
tell document 1
tell layout x
set fields to fieldList
end tell
end tell
So the work of importing 50 records with seven fields has gone from 50
events to nine. For 100, it takes 10.
Of course, reordering the data takes time, especially when you get into
longer lists, but the couple of tests I've run suggest it's a much quicker
method overall, even for a small number of records.
(My rough timings for a tab-delimited file of 300 records with 5 fields into
a simple database: using "create new record with data...", 30 seconds; using
above method, 8.5 seconds (of which more than 6 were spent reordering the
data!); using the above method but breaking the data into chunks of 50
records at a time, < 5 seconds.)
--
Shane Stanley, email@hidden