Import Script Forgets Source Table

[nycpost]

I have a script that automates the import of a tab delimited file. My database has two tables, one called "Shot Versions" (which is where I keep versions of shots that our visual effects vendors create) and another called "Notes" (where I keep all the notes I make on the Shot Versions). The script first imports the tab delimited data into the Shot Versions table and then, because I want to make a note on every one of those records, I import the Shot Version IDs from the found set into the Notes table. I then use those IDs as foreign keys to link the Notes table to the Shot Versions table.

The script works ok, the only problem is that in the second scrip step Filemaker never remembers that the Shot Versions table is the source. It always reverts back to another table in the database, a table that it completely unrelated. Because of this I have to keep "Perform without dialog" unchecked so I can manually select the Shot Versions table every time I run the script. Needless to say, after a few imports this is enough to drive someone crazy.

In the Import records script step, I have Specify Data Source as a variable which gets the current file path. Is there any way to specify within that file, which table I need? Is there anyway for me to hard wire into the script that the Shot Versions table is the source that I want or am I doomed to always selecting the table manually?

Thanks,

Mike

[bcooney]

Mike,

This demo file might help. It contains a technique I use to let a user select the source file. However, it is not clear to me why you are creating Notes records ahead of when you need them. Why not have a portal of Notes with Allow Create on a Shot form layout?

[nycpost]

Mike,

This demo file might help. It contains a technique I use to let a user select the source file. However, it is not clear to me why you are creating Notes records ahead of when you need them. Why not have a portal of Notes with Allow Create on a Shot form layout?

Thanks, bcooney! The notes table holds notes made on records from several tables and acts as a giant value list on many different types of shots, hence the reason to make a note record on import for every shot version.

I'm trying to understand the demo file you posted, but still can't figure out how you determine the table within the source file. Is there a way to ID the table?

Thanks,

Mike

[bcooney]

The imports are hard-coded, in a way. You build the import script step manually. Switch to a layout based on the destination table, and create the import step. Add your source file manually so that you see the field-mapping. Then, you can delete the file path and just leave the $source in the source file dialog.

[nycpost]

Sorry, but I don't quite follow. When you say:

You build the import script step manually. Switch to a layout based on the destination table, and create the import step.

Are you talking about manually importing, or importing via script?

I've tried both, and each time it doesn't remember the source table in the FMP document I select.

[comment]

the only problem is that in the second scrip step Filemaker never remembers that the Shot Versions table is the source. It always reverts back to another table in the database

I cannot reproduce your problem. Could you strip your file to the essentials only and post it here?

I have Specify Data Source as a variable which gets the current file path.

What happens if you hard-code the file's name in the Import dialog?

[nycpost]

Sorry for the late reply, bcooney and Comment. I got buried under a ton of work this week and haven't had a chance to check the boards.

Comment, I'm afraid that if I strip the DB down or clone it that some functionality might break. The whole DB relies on many interrelated tables and the presence of values is often required in order for calcs and scripts to run and evaluate properly. I'm afraid if I strip it down it might do our troubleshooting more harm than good.

I think I've come across something interesting here though. Per your suggestion, I tried hard-coding the file name into the Import dialog via the "Add File" command. This yielded the same results as when I plugged in a $CurrentFilepath variable: I am directed to the wrong source table during import.

I then tried making a new script from scratch and tried both the $Current FilePath approach as well as hard-coding the filename. Both work.

This script that I am using was most likely (as are a lot of my scripts) re-purposed from an old one. Interestingly, the incorrect TO that I'm always redirected to during Import is a table involved in that old script.

Could it be that the script is remembering it's original TOG context and that's why it's taking me back to the wrong TO? Could it be that's why it works when I make one from scratch?

Thanks,

Mike

[nycpost]

A little more info. After more testing it looks like my results are pretty intermittent. Sometimes it works, sometimes it doesn't. I'm totally baffled by this.

[comment]

I'm afraid if I strip it down it might do our troubleshooting more harm than good.

I disagree. The key in troubleshooting is to reproduce the problem in isolation.

Could it be that the script is remembering it's original TOG context and that's why it's taking me back to the wrong TO? Could it be that's why it works when I make one from scratch?

It's not impossible - but then your file is likely to be corrupted.

[nycpost]

I disagree. The key in troubleshooting is to reproduce the problem in isolation.

Hi, Comment. I've stripped the file down to the essentials and attached it here. I've made an account for you and I'll PM your login along with some more details. If you could take a look I'd really appreciate it.

Thanks,

Mike

Database Clone.zip