Import options being forgotten

[Mandu]

I have been having problems with scripts that use the Import step: certain settings are being forgotten.

There are two scripts: Load, and Restore.

Load will import a flat, tab-separated file into an FM table "FlatOrders".

Restore will Import the data from an FM file "OrderDB.fp7" that was "Saved as Copy" earlier.

In both cases I want to be prompted for the location of the source file, but otherwise it should use the same Import Order and other options as before.

I eventually found that I had to put each script through one "dry run" with the Source Data file pre-specified, in order for all of the settings to stick. After the dry run, I re-edit the Import script step and DE-select "Specify Source File" and click OK. From then on, the script will prompt me for the file location and proceed to do the right things.

UNTIL... I run the other script again.

Let's say I got Restore working correctly, prompting me each time for the OrderDB backup file. Then I got Load working correctly, prompting me each time for the appropriate file.

When I then Restore again, it fails. So I de-select "Perform Without Dialog" on the Import step, and run it again. When the Import dialog comes up, showing me the Import Field Mapping, the Source is no longer "Invoices" but has changed to "FlatOrders" - completely wrong.

What made it change?

And verily, Load will at some point resume reading the first record of each batch again - the one containing column names.

I'm completely baffled. I suspect that the problem lies in de-selecting the Specify Source File option, possibly causing the "First Record Option" and the Source table to being forgotten. But how else should I make the script throw up the "Find File" system dialog box, while remembering those other criteria?

[rellis32]

I believe your problem may lie in that you are using different file names for importing. Filemaker remembers the import order of the last file imported.

Example:

You import tab1.file, set the import order, import

Your file will be imported correctly.

You import tab2.file, no need to set import order because the file being imported is identical in format to tab1.file.

You import db.file, Filemaker still has tab2.file import order in it's config, so thats the order it defaults to.

A fully scripted import does not have this problem as the import info is stored with the script.

I import 3 seperate spread sheets into FM daily. I have them scripted because I save the spread sheets as the same name everytime, just overwriting the old file.

The flexibility for doing this is there, you can have the file imported set by a variable if you have some convention to the way you name your files you are importing. Or if you need a backup, just do a Save As in Excel and save it over the same file on a daily basis, then you can set your script to import the same file.

Good Luck!

Bob

[Mandu]

I import 3 seperate spread sheets into FM daily. I have them scripted because I save the spread sheets as the same name everytime, just overwriting the old file.

Ok, I see. In my case I'm importing a file called apr13.txt today, and apr14.txt tomorrow. Also, when I do a nightly backup I name the file Backup-06-04-13.xyz today, etc., and if I have to run a restore I want to choose a particular Backup-yy-mm-dd.xyz.

I do want to fully script it, but it appears there's no way to leverage the Mac OS Open/Save dialog box (or whatever its equivalent is on Windows) as a way of letting the user choose the file for import. At least, not as easily as I'd hoped/expected.

Thanks for helping clear this up for me!

[J__]

If I may interject here . I am going to assume you are using the import script step. Make sure that when you select the script step ( Import Records ), you check off 'perform without dialog' and check off the 'specify import order' and click the button to the right of that item. Then in the dialog that appears specify your sort order. In that dialog also make sure to select 'arrange by' drop down list to be 'last order'

after you have arranged your fields accordingly, click ok.

FM should now 'remember' that order you specified and each time you run that script step, it should use arrange the order based on the last order, which is in a sense memorized now.

I don't think it matters what file you pick, it's whether you say remember last order arranged by or not.

If I misunderstood the need here, then my apologies in advance,

hope that helps,

sincerely,

J__

[Mandu]

J__,

You've understood exactly what I need. I've verified that Arrange By specifies "last order" in both instances. But I cannot get it to reliably remember everything after I uncheck "Specify Data Source". It now seems to remember the target table field order, but forgets the source table that I specified - it reverts to a different table, resulting in a totally crazy field mapping.

If you have any other thoughts, I'd welcome them. By now I would have just Applescripted it, but the solution has to be cross-platform.

[J__]

Hi,

Good, I'm glad I understood, I remember struggling with this problem myself at one point! So, i know how you feel.

other thoughts? hmm, well here are a few, you probably have them covered, but you never know. First off, I'm doing this in FM7 Advanced. Hmmm

anyway, you find the 'import records' script step.

once it's in your script, double click on it to invoke the data source dialog. Where it says file types, pick Filemaker (at least for what I do, that's what I have done)

click the add files button in this dialog and select a sample file. Click ok. The dialog will close and you will be back in scriptmaker and you should see the filename as a parameter in your script step. the 'specify order' check box will be checked now.

now click 'specify order' check box, the order specify dialog should appear. Make your changes, then click 'ok'. You'll be asked if you want to import it, click yes. The dialog should close and your back in the main screen of scriptmaker.

check of 'no dialog' and finally uncheck the 'specify data source' check box. If you look at your script step for import records you will see the filename disappear. Don't worry it's ok.

(oh and if you see 'windows ansi' that's ok too - even for mac (since we are cross platform right?)

click ok and run the script.

does that work? It should.

if it doesn't, well then take a look at this little sample i whipped up. Attachment is zip, file is called Import_sample. You click the import button and select a file and the data is imported. Pardon the crudeness, but i think you get the idea. There's one script, check it out! to test, just make a copy of the file and import the same file (grin) you should have dups when you go to the 'view list' layout.

does that work? Hope so.

import_sample.zip

[Mandu]

Thanks for giving such detailed instructions - I think I have been doing it almost exactly that way all along, except possibly not specifying "Filemaker files" in the Select Data Source dialog.

Following your instructions to the letter, it works - the first time, the second time, the third time... up until I run a different script, ScriptB, one which contains an Import step to load a flat .txt file into a different Filemaker table. If I then go back to ScriptA and run it, the Import parameters are all hosed. I deselect "Perform Without Dialog", run it again, and I can see in the ensuing "Specify Import Order" dialog that the Source table has changed. [it always changes to the name of the Target table from the Import step in ScriptB.]

So I suspect that the fact that it works the first, second, third time is merely due to the way that, in most applications, dialogs tend to remember their most recent settings at the application level, rather than due to those settings having being saved and retrieved for that specific Import statement.

I played a bit with your attached file, but couldn't break it. I think, however, it lacks some of the complexity that causes mine to break; namely, multiple tables in the database file and multiple scripts that use Import.

Oh, well. Thanks for your help - and if any other ideas occur to you, I'll be glad to try them.

Chap

[J__]

Interesting. I thought that the import script steps remembered their own settings. I'll have to test this a bit.

Here's a thought: Is the second script you are running "ScriptB" is that a copy of the first script? Did you create script a and then say "You know this next script is going to be just like script A, I'll just copy it and then tweak it a little bit"...

Or are you calling the same script somehow, like reusing it for more than one kind of import. If you are doing something like this, then yes it's going to get hosed .. there's a restore option, but I can't seem to get that to work (right now)...

I was pretty sure it was not a global setting for import script steps... so now I'm going to have to check that out.

very interesting. Sorry that wasn't the solution,

sincerely,

J__

[Mandu]

ScriptB is entirely separate. I wrote it from scratch, and it loads a tab-separated value text file into a completely different table than ScriptA, which imports a FM table. A and B do not share any code that I can think of.

As long as I leave "Specify data source" checked in the options for both Import statements, the individual Import statements *do* retain facts about the source data, such as what table I want to use (when the source data is a FM file) or whether Record 1 contains field names (when the source data is a tab-separated text file). It's when I uncheck that option, to present a file-finding dialog to the user at runtime, that the retained settings become vulnerable to getting stepped on, presumably when some other Import step is run.

It seems a bit shabby to tell the client "when you want to load a new batch of orders, rename the file to "Orders.txt" and place it in the folder named "Batches". But it looks like that's what I'll have to do, unless I want to write an Applescript and a Visual Basic script to provide the missing functionality.

Odd. I have a hard time believing this can't be done purely through Scriptmaker. It just seems too clumsy.

[J__]

i have been thinking about this a little bit since I last mailed you. I did another test with 2 separate scripts and it doesn't get hosed.

what I do is select specify data source , pick a file, in that dialog. Run it as a test. I then uncheck the 'specify data source' check box (and of course perform without dialog is check box is checked.

by unchecking the 'specify data source,' you remove the file that has been specified, but not the other settings. So, when you run the script, the dialog will popup letting you pick the one you want.

In a test file i created I import 2 separate tables from different filemaker databases and (yes they are 2 separate scripts) and it seems to remember the settings.

if this still doesn't work, i can't help but wonder if it's a bug in fm8. As i am using fm7.

hth,

sincerely,

J__