Re: FMP question -- for Shane Stanley?
Re: FMP question -- for Shane Stanley?
- Subject: Re: FMP question -- for Shane Stanley?
- From: Paul Berkowitz <email@hidden>
- Date: Wed, 01 Nov 2000 11:03:17 -0800
On 11/1/00 4:18 AM, "Shane Stanley" <email@hidden> wrote:
>
> Just for the exercise, I've done it all ("it" being importing
>
> tab-delimited text files into an FMP database) in AS. It stays in the
>
> background, as you said it would, but importing 2 text files with a total of
>
> 10 records (lines) to be entered into 7 fields (a few others are calculated
>
> by FMP, which seems to take no time at all), then checking for duplicates,
>
> takes about 15-20 seconds vs. about 1 sec running the FileMaker script and
>
> flashing to the front.
>
>
How long it takes will depend very much on how you script the import, and
>
the fastest method is not necessarily obvious. Post the relevant part of
>
your script.
Thanks. Here it is. You'll see there's one item in the text that's ignored
(item 3: don't ask me why it's sent, it just is), and one field value to
add, which is the name of the text file. Then there's a check to see if a
duplicate exists. The version below, which creates the record, then deletes
it if there's a duplicate, takes about 40 seconds in a script run from
Entourage (where the zipped text files come in as attachments), with two
text files, one with 4 lines, the other with 5, and 1 duplicate in
existence, otherwise the databases are empty. It will undoubtedly take much
longer when there are hundreds of records to check for duplicates. It took a
LOT longer to do the checking first and not create the record if the
duplicate already exists: almost 2 minutes! I could not find a way to set
the cell values as properties when creating the record: it errored. If
there's a way to do that, it might go a lot faster. (There are other cell
values which are calculated by FMP, and that seems to take no time at all).
All this takes about 1/2 sec. to a sec when run by the FMP script that
flashes FMP to the front, except that I've realized that that FMP script's
duplication check (not my script) is faulty, since it includes item 1 which
is totally irrelevant to genuine duplication and would thus miss any
duplicate!.
-------------
set f to open for access txtFile
set r to read f
close access f
set num to ((count paragraphs of r) - 1) -- get rid of a final
return
tell application "FileMaker Pro"
if whichType = "ss" then
set dbName to shippingName
else
set dbName to receivedName
end if
set ods to AppleScript's text item delimiters
set AppleScript's text item delimiters to {tab}
repeat with n from 1 to num
set theLine to paragraph n of r
set givenItems to text items of theLine
set givenItems to (items 1 thru 2 of givenItems) &
(items 4 thru 7 of givenItems) & {txtFileName}
set numRec to (count records of database dbName)
set newRec to create new record at database dbName
tell newRec to set {cell "lineNumber", cell
"fulfillmentCompanyOrderNumber", cell "invoiceReference", cell "partNumber",
cell "qtyShipped", cell "shippedDate", cell "ImportSourceFileName"} to
givenItems
tell database dbName
repeat with m from numRec to 1 by -1
if (items 2 thru 6 of (get record -1)) =
(items 2 thru 6 of (get record m)) then
delete record -1
exit repeat
end if
end repeat
end tell
end repeat
set AppleScript's text item delimiters to ods
end tell
------------------------------------------------
It's much slower (3 times slower, 120 seconds vs. 40, even with hardly any
records to check) when done like this, although it "feels better":
-----------------------------------------------
repeat with n from 1 to num
set theLine to paragraph n of r
set givenItems to text items of theLine
set givenItems to (items 1 thru 2 of givenItems) &
(items 4 thru 7 of givenItems) & {txtFileName}
set {i1, i2, i3, i4, i5, i6, i7} to givenItems
set checkItems to {i2, i6, i3, i4, i5}
set numRec to (count records of database dbName)
set check to "OK"
tell database dbName
repeat with m from numRec to 1 by -1
if checkItems = (items 2 thru 6 of (get
record m)) then
set check to "noGo"
exit repeat
end if
end repeat
end tell
if check = "OK" then
set newRec to create new record at database
dbName
tell newRec to set {cell "lineNumber", cell
"fulfillmentCompanyOrderNumber", cell "invoiceReference", cell "partNumber",
cell "qtyShipped", cell "shippedDate", cell "ImportSourceFileName"} to
givenItems
end if
end repeat
----------------------------------------------------------------
--
Paul Berkowitz