Importing Question

[masgian]

Rookie here, please don’t shoot (yet)

Ok, Let me tell you what I need to achieve, maybe someone is kind enough to explain this to a FM Greenhorn (me)

I need to Import data from a TAB Separated file, everyday. I know how to do that. But I need at Import Time to have some operations to be performed ON and WITH the data.

1. I need to Copy some fields out of the Record, to another table, based on certain rules, simple rules but I have no clue how to do that. Things like: If the NameId exists in the table and the Phone Filed is empty Update the Phone field…. Etc

2. I need to perform some data corrections, like, to cut out from some text fields portions which are marked… ex… I do in a field after every name a reference included in semicolons. This is constant so I could identify the string by the “(“ and the “)” if I knew how to do this.

Thank you in advance for any help.

[masgian]

ok, I guess I could Import all new data on a "tempTable" and then do all operations there, right?

but, I dident found any way I could Import data on a certain Table (not the default).

also, How I do perform those operations? Copy Field data from a table to another?

[IdealData]

Using the Tempory table idea is a good start, at least your data is now FMP native.

You're going to have to script this one - you probably didn't want to go there yet, but that's the way it is.

Your "temp" file will need to mimic your tab data file, so to get a flying start just drag your TAB file onto FMP - it will auto create a new file and set the field names to f1, f2 etc.

Rename the fields to match the tab file and delete all the records.

Then run file > import and specify the original tab file. This time you will be requested to specify the field order for import. The field names should match the sample data, and complete the import.

Then run ScriptMaker > new script and add a single entry to import record from your tab file. You must specify the tab file, and also "Perform without dialog".

This script is now self contained and knows all about the tab file, and more importantly it knows the sequence of fields to import - but you must not change your tab file layout otherwise you must change the script to suit.

This is only the beginning, but I hope it gives you a start. You can extend the ideas here to complete your task, which will require several scripts to chain together between your main files and the "temp" file.

The key to scripting is to remember that it is only a pre-programmed sequence of user actions, mouse clicks etc., that could still be performed manually from FMP functionality available from the menus.

Master this technique first, then come back to the board with more detailed questions about "shunting" your data around

HTH

[Fenton]

First, look at the top right drop-down of the Import dialog. That is where you specify which table to import into; the source file/table drop-down is in the upper left.

If you are wanting to do such things are only update fields in the original if they are empty, or update them with the new data, if any, but not overwrite existing data with blank data, then you'd best import into a Temp table. Then use a Loop through the Temp records to process the original records, one at a time.

If you are wanting to "update" records in the original, you imply that there is a way of "matching" a unique original record with a unique Temp record. What will you use, a first & last name compound relationship? This might be OK, as long as there are NO duplicates.

Then it's a fairly simple (though tedious) task to compare each field in the matching record to the corresponding field in the Temp record you are on, and move data across as desired, using the unique relationship above.

Alway back up your file before doing anything like this.

Here is an example. I only did some of the fields (as I said, tedious). The Import script is commented. This was a little file I was working on to synch with Apple's AddressBook app and with a Palm. You can ignore that script (it's a Perform AppleScript). It's the only simple address book file I had handy.

Edited November 20, 2005 by Guest
Removed file

[comment]

For crying out loud, Fenton - you should include a warning or something, before this thing starts messing with my REAL address book. You know how we Mac users are - click first, ask questions later...

[Fenton]

I'm sincerely sorry. I did not remember that it would bring in the whole whole address book. I only use my Address Book for experimentation (as you can see). I just grabbed that file as the only simple address type file I had on hand, and added the Import routine for masgian. I should have deleted the AppleScript stuff.

Here is the file again, safely stripped. Once again, I apologize and will try and pay more attention to these things in future.

AddBookFM.zip

[JerrySalem]

Fenton,

Thank you so much for the clear demo for importing.

I have a question. How does FMP7 know what TABLE to import From? In the Source, only the File is defined. In the Import script step, only the table to import to is defined.

What am I missing?

Jerry

[mr_vodka]

Usually when there is multiple tables in a file for import, under the import field mapping screen, there is a pull down menu on the top left corner for your source table.

[Fenton]

And in FileMaker 8 there is a drop-down at the top right, for the target table; and apparently you don't even have to be on a layout of. I didn't know this, but saw it in another developer's file, and it worked.

Edited December 7, 2005 by Guest
change left to right