Import xml with AppleScript

[slehar]

Hi, I'm a newbie with FMP, and a newbie with AppleScript.

I want to automate the importation of a bunch of XML data files already in format, with an AppleScript script (unless someone has a better idea?)

I have 5 databases (tables) and each of my customers have 5 XML files, containing a record for each of the 5 database tables. I'd like to prompt for a customer name, then go to a folder that contains that customer's data, and extract the 5 XML file under that customer's folder and import them into the databases.

Can I do this with an AppleScript script? Maybe something like this?

tell application "FileMaker Pro"

    tell database "dbase1"

        open

        import "Macintosh HD/folder//file1.xml"

    end tell

    tell database "dbase2"

        open

        import "Macintosh HD/folder//file2.xml"

    end tell

    tell database "dbase3"

        open

        import "Macintosh HD/folder//file3.xml"

    end tell

    ... etc for 4 and 5 ...

end tell

I'm just guessing wildly, I've been searching around and I can't find the documentation for getting AppleScript control of the Import Records from XML File function. Can somebody point me to the resource?

[slehar]

Tom's FileMaker Pro Questions & Suggestions:

http://www.win.tue.nl/~wstomv/fmp/QuestionsSuggestions.html#DatabaseGlobals

suggests that parameters can be passed to scripts using Global Fields, which are fields which are stored once only, not duplicated for each record.

So I could put the folder name (named for each customer) in the Global Field, and then use that as the path in the script to import that record.

There are some weird trade-offs here -- if I define the source folder in the script, then I can't have the user change it to import records from a different customer, I'd have to have a custom script for each customer.

What I'd like is a script that prompts the user to type in, or browse to the customer's source folder, and then just import all his .xml files each to the appropriate table in the database. Sounds simple enough -- how do people do this stuff?

[Fenton]

There are 2 things you need to know about. The first is the use of the script step Set Variable (not a global field) to store a FileMaker syntax file path to an external file for Import (or Export). It is in FileMaker help, under the Creating databases topic (near the bottom). Unfortunately there in no mention of this in the Reference, Script steps, Set Variable (poorly cross-referenced, IMHO). There are alse several examples of using Variables for this here on the forums.

The 2nd thing is that the way to call a FileMaker Import from AppleScript is to use the do script phrase; within a FileMaker tell block (if outside FileMaker; internally tell blocks are generally neither needed nor desired when run in a Perform AppleScript step, unless nested; the top level IS an implied FileMaker tell block).

tell database "whatever"

do script "script name"

end tell

But really, AppleScript is not needed for this. There is some confusion in your post. Your AppleScript implies multiple "files", whereas other places you say "table". Which is it? Likely this is all 1 file, with multiple tables. In either case a FileMaker script can do all of this. Just use script Variables to store the different XML file paths (in FileMaker syntax, either absolute or relative).

[Fenton]

Oh, actually I was incorrect when I said FileMaker could do all this. You would need to get the file names from the folder. AppleScript could do that. I don't know what the structure of your folders is, but if you can specify it from data within the database, then pass that to (or calculate it), then AppleScript could get the files, using either "list folder", or the Unix 'ls' command. 'ls' returns a return-separated list, so it's easier. Example:

set aFolder to quoted form of "/Users/fej/Desktop/Shoes"

do shell script "ls " & aFolder

You'd need to add the FileMaker prefix ("filemac:/") and full directory path (including DriveName) to get a full path.

[slehar]

Hi Fenton,

Thanks for your help. Yes I have multiple tables in on database. And I discovered the Set Variable[ ] function. The problem is that I won't know the customer names, and thus the customer's folders, until running the script, which I was hoping to do automatically as I create those customer folders and populate them with the xml data files. I wish you could say something like $variable = Dialog("Enter customer name"). Is this part best done in AppleScript?

I've got a kludge working now, where I created a new global field, which can be loaded with the path to the customer's folder by the script that does the data transfer to that newly created folder, and then I construct the path using the text in that field.

But is there a more elegant solution that does not require the kludge of an additional global field? I was excited to find the Show Custom Dialog script step, only to be disappointed that you can't type in anything to the dialog, all you can do is a binary YES/NO type of thing.

[Fenton]

Yes, you can include the global field in a Custom Dialog. In the Tab at the top of the CD on the right is "Input Fields"; you likely never noticed it. You can specify your global fields there, as well as give them a nice label.

Be aware that only the default button [ Get (LastMessageChoice) = 1 ] can actually use the values in a global Input Field.

I have to wonder why you wouldn't already have typed the Customer name into a field, in the Customer's record (which is where I'd do the whole thing from). Why would you need to type it into a global? If you're typing it into a global, then you'd certainly need to type it correctly, as it must Find a Customer record, and it's folders. You could of course combine the global entry with a Find. But Finds are not always accurate, so I would want to see the result first.

Or Find the Customers who did not yet have XML files. Or read the customer name for each file added recently, by reading it from the recent XML files.

I'm rambling. But it seems to me there may a better way than typing the name into a global. I'm not quite getting the data flow. I know from experience that it is good to build a method which is not easy to break, even if it seems a little more trouble to start with.

[slehar]

Here's the details of my application. I have a Python program that reads the xml output of some other software product (Pearson's Psychcorp clinical assessments) which produces five separate data files for each of our voluntary participants (ptpts.). My program prompts for a ptpt. name, which is uses to create a folder by that name, copies all files into that folder, converts the 5 files to the format required by FMP, then (hopefully) it open FMP and imports the data from each file into the 5 corresponding database tables.

The folder name was already typed in by the user of my Python program, and the folder was created before even calling FMP. I suppose I could have auto-generated the folder name from the ptpt. name in my Python program, and trust FMP to reconstruct the same name the same way from the ptpt. name field in the database. But I wanted the user to have the ability to create multiple folders for each ptpt if they come back for more testing, and be able to name them "John Jones 1", "John Jones 2" etc., with separate folders for each. I'm not sure if I'm ready to revisit that issue at this point. Its a user-flexibility v.s. strict standardization issue, with pros and cons each way.

[Fenton]

I'm still not clear on the workflow, as is common when it's someone else's; and I don't really need to know. It's just that whenever someone types a name, there's a weak point. I find people's names to be pretty unreliable for much of anything automated. Even perfectly typed names can be duplicates.

As far as flexibility for later testing, it seems that AppleScript could check for the existing folders first, and create others as needed, so it wouldn't have to ask.

Another crazy idea. There is a new AppleScript-like language, introduced in OS 10.6, AppleScriptObjC, which can be used to "merge" AppleScript-like commands into other programing languages. So you could possibly run the AppleScript part within the Python structure. But I don't know; I'm not a "programer", just a high-level (which is the lowest level, kind of backwards-speak) "scripter".

[slehar]

Ok I think I'm getting a big-picture understanding of the problem and the proper solution. You're telling me the best way to communicate paramaters and values to my database is through the XML files that I'm sending it already to import, so it will have those parameters and values easily accessible as regular fields in the database, no need to pass them in through a dialog.

So in my Python program, when the user types in the intended folder name (e.g. "John Jones") it will check it against the & fields in the xml file and issue a warning if they don't match. If the user ignores the warning, (e.g. for "John Jones 2" not == "John Jones") then the new value will be used for a new field to be passed in to the database, even though it does not match "John" & "Jones" so it will know how to find the right folder to import the xml file from.

Thanks very much for your help, I think I'm all set now!

[slehar]

Ok, I have a much clearer idea of my problem and the proper solution. Here's a quick summary in case someone else has this same issue.

I'm writing a Python program that logs in to a remote server and creates a "Participant" folder named for the participant (e.g. "Jane Doe"), and dumps 5 xml data files into it, that should serve as import records for 5 different databases.

Then what I'd like to do is, operating remotely, write an AppleScript script and save it on the remote server, then execute that script, to open each database in turn, and for each one, import the appropriate xml file from the "Jane Doe" folder.

The problem is that AppleScript's FileMaker commands do not include an "Import" command. So instead, I need write 5 separate FileMaker Import scripts, and call them from my AppleScript (do script FileMaker script "import") and pass them the "Jane Doe" folder so they will know where to find their xml file.

But AppleScript does not pass parameters! And FileMaker scripts are not real scripts that are accessible in text files, so I can't write those scripts remotely to hard-code the "Jane Doe" folder, as I do with the AppleScript!

And I can't pass the "Jane Doe" path as a field in the xml files themselves, because you have to know where they are before you can read anything out of them!

So the solution is to create a NEW database back in the home folder (a standardized location), called Imports.fp7, and every time I do a remote data-dump, I open Imports and add a record with "Folder Name" = "Jane Doe".

Now my AppleScript, after dumping the new "Jane Doe" data, opens the Imports database and adds a record for "Jane Doe". Then it calls each of the 5 databases in turn, and for each one, it calls

do script FileMaker script "import"

with a separate "import" FileMaker script that I provide for each database.

Now each of these FileMaker "import" scripts, first goes to the standard location and opens the Imports.fp7 database, reads the "Jane Doe" Folder Name, THEN it knows where to look for the "Jane Doe" data, i.e. in

/ Jane Doe / datarecord.xml

Whew! Problem solved!

But wait, now there's one last problem! Each of the 5 databases has its own custom "Import" script, so I have to open the database e.g. Database1, to execute its own "import" script,.

So far I have this FileMaker script for Database1:

Open File["Imports"] # The one that holds "Jane Doe"

Go to Record/Request/Page [Last] # Get the last record

Go to Field["Folder Name"] # <== !! can't be done!

When I try to "Specify" the target field, it shows me the fields for Database1.fp7, not the fields for Imports.fp7, so I don't have a "Folder Name" field to select!

How do I select a field from a different database than the one my script is defined for? Was it not enough to Open File ["Imports"]?

[Fenton]

I cannot really follow your procedure; but then again, I sometimes have problems following my own.

The statement, "But AppleScript does not pass parameters!" is too vague to really argue for or against. But AppleScript can read/write from/to FileMaker global fields, it can read/write from/to text files, as well as from the general folder/file structure. So I'd have to say your statement is not really true. Maybe you should re-evaluate your procedure, if this is a big sticking point.

Edited September 30, 2010 by Guest

[slehar]

>> The statement, "But AppleScript does not pass

>> parameters!" is too vague to really argue for or against.

If I could pass a parameter from AppleScript to a FileMaker script, that would solve my problem immediately! Please tell me I was wrong, and that IS possible after all?

I would love to do this in AppleScript:

tell application "FileMaker Pro"

	activate

	tell database "Imports"

		open {"Macintosh HD:Users:steve:Documents:Database9:Imports.fp7"}

		do script FileMaker script "import"

		set FolderName to get data of field "Folder Name" of last record

	end tell

	

	tell database "WAIS-IV"

		open {"Macintosh HD:Users:steve:Documents:Database9:WAIS-IV.fp7"}

		do script FileMaker script "import" #<== and pass parameter FolderName

	end tell

end tell

so that my FileMaker script "import" would go to (in the case of "Jane Doe")

/Jane Doe/record.xml, record2.xml, ... record5.xml

and import them from there.

Again, I cannot read FolderName from a field in record.xml because it doesn't know where it is, until it has opened it and read the "Folder Name" field from it to know where it is hiding.

Yes I am beginning to get the sense that I am swimming against the current, and I would much rather do this the same way everyone else does it.

The constraint that is holding me back is that "John Doe", "Jane Doe", and everyone else's folder all contain the same filenames, i.e. record1.xml, record2.xml, ... so that is why I label each folder by the participant's name. But that means I have to tell FileMaker where they are, so I have to pass the "Jane Doe" parameter in to the import script.

Maybe I should just simply dump ALL data files in the , import them (FileMaker will know where to find them) and then AFTERWARDS create a new "Jane Doe" folder and move them all into there. Is that what everyone else would do?

[Fenton]

I'm still not quite following the whole procedure. But, as it gets simpler, and I know it can be simpler, it gets easier.

You can set a global field in FileMaker via AppleScript. This requires that you know which database and which layout or table (the phrase "table" in AppleScript ALWAYS means the "table occurrence" name(s) on the Relationship Graph; that is what the API exposes). There must be at least 1 record in the table. But you do not need "current record" nor anything else to set a global field (as it is the same value for all records).

set folder_name to "Jane Doe"

set cell "z_gFolderName" of table "Constants" to folder_name

save record

To use the above in a FileMaker Import script step you need to set it, as a FileMaker syntax relative or absolute (best) path, into a script Variable first. That is the only thing which an Import step will accept as a path. Example:

filemac:/Macintosh HD/Users/fej/Desktop/med_1285860441-Graph.jpg

I think you can see that this is like a prefixed cross between an AppleScript Mac path and a Unix path; it can be coerced from either.

When you first set up the Import, you need to choose a "real" file, as you cannot use script Variable (even a global one) to line up the Import Order. But you can have more than 1 path in an Import dialog.

filemac:/Macintosh HD/Users/fej/Desktop/med_1285860441-Graph.jpg

$filepath

So, if the above "real" absolute path is missing (which it will be), Import will try the 2nd $filepath.

P.S. Your AppleScript does not make sense really, as you're telling a database named "Import" to open itself; which it ain't gonna do.

Likely the safest way to open FileMaker files via AppleScript is to use a tell "Finder" block to open 'em all before you get started.

Edited October 1, 2010 by Guest