Importing from Amazon Web service with the ISBN

[Bernard M]

Hi,

First of all: Sorry: I'm a french speaker.

I try to import data from Amazon into FileMaker but without success

I found the example from Jay Welshofer on

http://www.filemaker.com/products/technologies/xslt_library.html

but it's no more compatible with the actual Amazon Web services.

I found posts on these forums with great contributions from Fenton. Though I tried to custom the examples given, i didn't get a result.

So, is there anybody with successfull stories in this topic ?

Has anyone actual exemples of Amazon import into FileMaker by ISBN ?

I use this kind of URL (is it the correct way ?) to get the info:

http://webservices.amazon.com/onca/xml?Service=AWSECommerceService&SubscriptionId=1EJ6WSKMCCDBSJCYFAG2&Operation=ItemLookup&ItemId=1556228600&ResponseGroup=ItemAttributes

I get the bd1.xml file here attached

I'm totally novice with xslt.

I tried to custom an exemple with only 2 fields to begin of Fenton (bd1.xsl), but no import occur.

Thanks for your help !

Bernard M

Brussels (Belgium)

Bd.zip

Edited March 6, 2007 by Guest

[Fenton]

The problem, which I believe we covered before here, but which is worth revisiting, is one of "namespace". Amazon has changed their response to include Amazon's namespace, in the root element:

This effectively adds that namespace to all elements below (unless explicitly assigned to another namespace). This is the same as what FileMaker does.

What is required is to declare Amazon's namespace in the tag, give it a user-assigned prefix, then exclude it. Add this:

xmlns:amz="http://webservices.amazon.com/AWSECommerceService/2005-10-05" exclude-result-prefixes="amz"

(P.S. You don't need to exclude the "xsl" namespace, it's automatically excluded in xsl processing.)

Bd1.xsl.zip

[Fenton]

I forgot to mention, you also need to use your assigned prefix in every place where you're targeting an Amazon element (you don't need this for attributes, they inherit it from their parent element).

 

  

   0

   

   

    

     

Notice "amz:" in front. Also notice "ASIN" is all caps; xsl is case sensitive.

Also (optional), you don't need a RECORDID number; FileMaker doesn't care what those are when importing. XSL coders often use position() for that and count() for the total records. It's sometimes useful when you're troubleshooting the result in an xslt editor; but FileMaker doesn't care at all.

Edited March 6, 2007 by Guest

[itsalljustaride]

I'm having somewhat similar problems. The example on Filemaker's site from Jay Welshofer is way out of date by now.

It's been so long since I've done any work with XSL, so I'm basically completely ignorant on the practical end of it. I understand the theory, just not the practice.

My ultimate goal is to be able to enter a UPC, ISBN, or ASIN in a field, then have a user hit a button that searches the Amazon Web Services and fills in the relevant media details automatically.

How would this work within Filmaker?

I am able to, currently, generate a valid URL that displays the XML info based on the entered UPC, ISBN, etc, and I can see the fields that I need to import, it's just filling in that middle part that is stumping me.

I realize I need to have the XSL in a "FMPXMLRESULT" format, but what does this mean? How do I know what that format is?

[itsalljustaride]

Ok, I was a bit hasty (after two days of searching), and i've made some breakthroughs. I just realized that you attatched an example file, which has been very helpful. I can manually import data to a record (some of it). Let me play with it and I'll see where I get before I have to come crying back again :o

[itsalljustaride]

Alright. I have a pretty good idea of what's going on now, and I have a good working prototype.However, there are a few things not working just right.

1.) Some details like "Actor" have multiple entries, but when I run the import, only the first one gets added to my repeating field. I set the "MAXREPEAT" value to a number greater than 1 for each, but I still only get 1 result out of the 5 or so available. Perhaps I am misunderstanding the nature of that tag.

2.) Amazon passes the information for cover images as a URL. I'm not sure how to use that URL to add the image it points to to a container field.

3.) One of the details I'd like to add is the language info, both spoken and subtitled. The way this works in Amazon's XML is thus:

−

Chinese

Original Language

Dolby Digital 2.0 Surround

−

Chinese

Subtitled

−

English

Subtitled

I have two fields in FM called "Language Spoken" and "Language Subtitled". What I need to do is add the language to either FM field depending on what "Type" the Amazon XML specifies.

Thanks for any help you can give.

[Fenton]

Sorry, meant to post earlier. But I got waylaid by pesky clients -]

1. I don't think the MAXREPEAT actually does anything. It was a good idea that never got implemented.

So basically you should import any "many" data into its own table, using its own xsl stylesheet. Which will be much like the main table's xsl stylesheet, but with the ROW selection xpath modified for the "many" element, and the selection for-each and values for the specific elements you want for your child table.

3. Pretty much the same as #1 above. Bring them into their own table. You could just bring all children into FileMaker, and , then do whatever further in FileMaker. Or you could use a test for Type to select which you wanted for the FileMaker fields. [i'll have to do that tomorrow; fading fast here].

2. You can't import an image with xsl. You can import the path, then use another tool to download the image as a file. Then Insert that. The usual caveats about images and container fields apply. But these would likely be very small images, so I'd say Insert them as embedded.

I don't know whether you know any AppleScript. So you might need help with this (tomorrow :-). A basic example using AppleScript to get an image (Jimi Hendrix) and write it to a file:

(* The url to get the image from *)

set theImageUrl to ""

(* The name to save the image as *)

set ODel to AppleScript's text item delimiters

set AppleScript's text item delimiters to "/"

set theImageName to (text item -1 of (theImageUrl as string) as list) as string

set AppleScript's text item delimiters to ODel

(* Where to save the image *)

set desktopPath to (path to desktop) as string

set targetPath to quoted form of POSIX path of (desktopPath & theImageName)

(* Get the image using curl *)

try

set theCurlCommand to ("curl -o " & targetPath & " " & theImageUrl)

do shell script theCurlCommand

end try

[Fenton]

Or, using FileMaker 8.5, just show the image in Web Viewer.

[itsalljustaride]

Ok, I've made a bit of progress concerning getting images in.

I have it set up to run the Amazon import through the XSL template. Then it runs the applescript that pulls down an image from Amazon. Then it runs a FM script that imports that image from the desktop. Finally it runs an Applescript that removes the original image off the desktop. It's a bit of a lengthy process, I'll admit, but it works

There is however, one detail missing. I'm not sure how to pass the URL of the image to the applescript that downloads it. Right now I just have a URL to one image hard coded, so no matter what title I'm querying it will always give me that one image. Any way to assign that URL as a variable and pass it to the Applescript that downloads it?

[Fenton]

You can pass the image URL to an AppleScript, to download to the desktop, then Insert it, then "remove" it (with "rm", which does not even put it in the trash).

I didn't bother with the image name, as you already have it in the image URL.

It would also possible to use the "temporary items" folder; then you wouldn't need to remove it. But that's more work.

Originally I had a 1 second Pause in the FileMaker script. But it didn't seem necessary, so I took it out.

ImageFromURL.fp7.zip

[itsalljustaride]

Thanks, that works great, except for the if statement, which kept telling me my URL field was empty or invalid even if it wasn't but I took it out and it works fine.

Now, as for the repeating fields, just so I'm understanding you right, I need separate tables for each element, or just one that acts as a temporary container for adding the data to another field in the main table?

I took a guess and created 4 more tables named "Actor", "Director", "Track", and "Language", then created relationships between the respective fields in both tables with allow create and delete checked. Is this correct?

[Fenton]

That sounds right, a table for each of the "many" entities; especially things like author. You do not really need "allow creation" on, because you're going to be importing all the data, not entering it. You would need to import the main table first, capture the FileMaker serial ID, then Replace it into each child after importing.

Or, alternatively, using the ID of the item to tie them together, importing it with each child. The way to do that is to reach "up" the hierarchy with the xsl, using the syntax "../" to denote "up a level" in the path.

Your original xml you posted is slightly different from what you're talking about now; is it just different because that was a book and this is a movie? What is the xml now, or what command are you using to get it (and we'll get our own -).

[itsalljustaride]

The test XML URL that I'm using is:

http://ecs.amazonaws.com/onca/xml?Service=AWSECommerceService&AWSAccessKeyId=07AKH4ZVRB72TNXHSRR2&Operation=ItemLookup&ItemId=0767853512&ResponseGroup=Medium

If you look at the "Actor" child of "ItemAttributes" you'll know what I'm trying to do.

I'm still not quite clear on how filemaker would handle this. What kind of fields should be in the "many" tables? And how do the information from those tables get displayed in the main table's repeating fields? Sorry, relational databases are a brand-new monster for me. I have a lot of time, though, to learn.

You can download a copy of my database so you know how it works as of right now. It is a modified version of the "Lending Library" template.

http://www.the-bob.org/~itsalljustaride/webtest/Database.zip

[itsalljustaride]

Thanks for all your help so far Fenton. I've gotten a bit further on this, but I can't quite get it. Using the above Amazon URL I can get 5 new records to be created on import, but I either get 5 empty records, or 5 identical records that all have the first actor, "Minzhi Wei".

If I use the RESULTSET path:

with a DATA path like this:

Then I get 5 empty records

If I change the DATA path to "../amz:Actor" then I get 5 identical records for "Minzhi Wei". I'm missing something that's right in front of my face, I can feel it :P

[Fenton]

The path would just be:

"." meaning current value at this location. Because you're already there, from your 1st path, for the .

[itsalljustaride]

Ok, that works great. So I am now able to import everything just fine to their own respective tables, now how do I get the info I just imported from those tables into the repeating fields in the main table? I'm one step away!

I notice that the found records in each table are always the records that were last imported, could this be a way to get them in via a script? And would I want to keep adding to these tables or only have the records in them that are from the last import, then clear them out when I do another? It would seem a bit redundant to keep adding entries to the tables when they only really function as a medium to get to the main table, or would keeping all entries be preferable?

Again, thanks for all your help so far.

[Fenton]

I think you're on the wrong track here, with repeating fields. They are not much good for real data; especially data which may be later used for, well, practically anything. But in your case I can see all kinds of operations that would want to operate on proper related tables, and would be substandard at best if used on repeating fields.

The "child" records belong in the child table, linked to the parent table via the parent ID. You can show them in a portal. So there's no need to move them.

There are 2 possibilities.

1. Import data which can be used for the parent ID when importing the data. This may be possible with the Amazon data. You can "reach up" to a higher level with the xsl, to get the parent ID (ASIN), and import it with the child records. But:

    a. It must be unique, for anything you'll ever import

    b. It must be in everything you import.

2. If either of the above is not a sure thing, or if you just prefer FileMaker IDs, then you can use them instead. The child records will often need their own auto-enter serial ID anyway.

The key fact is, as you said, Import produces a found set. So, if you've only import 1 parent, then you can create an auto-enter serial ID for its table. Then capture it, and Replace it into any child imports, at that time, into a foreign key.

[itsalljustaride]

oooooookay, it's all starting to make sense now. haha.

I was structuring my relationships based on what the data I wanted to show up was(i.e. Actor = Actors) when it should be based on the ID or something (I used the ASIN, like you suggested)

I tested the relationship now and it works fine with a portal. Each table has a field for a filemaker ID, and the ASIN to use as a link, so I can't forsee any issues with identical records.

Thanks for helping me crack that nut :

You mentioned earlier about the images and caveats of them in filemaker. At what point does filemaker strain under stored images? I'm using the large images from Amazon, which are about 50 kb each.

[Fenton]

I think you could put quite a few 50 K images in FileMaker, that's not very big. But, it's true, it all adds up. The only real downside I'd see to storing them in the same file is that it would slow down things like Save a Copy As, and duplication of the file, etc.. A fairly simple alternative is to just have another file for Images, and link the two, much the same as any child data. After all, image downloading and Insert is going to be a separate script from the main Import.

[itsalljustaride]

I've got something really weird happening with the image import.

If i put the field for the image URL in the layout it works fine, but if I take it out, it breaks the import. It gives me an error message saying it can't find the image file. If i put the field back in, bingo, works great.

Any reason why this would be happening? I don't see why having the field in the layout makes any difference.

[Fenton]

If AppleScript is looking for a FileMaker field, then it either needs to be on the layout, or referenced via a specific layout, so that it can find it. I find it easier to just put the field on a layout, often dedicated to AppleScript. That way it doesn't break if you modify the layout name (but it does if you remove the field; 6 of one, half dozen of the other). So, if it's something I wrote, it needs the field on the layout.

[itsalljustaride]

fair enough. I guess I'll just hide it behind the image or something. Thanks.

[Cabinetman]

That sounds right, a table for each of the "many" entities; especially things like author. You do not really need "allow creation" on, because you're going to be importing all the data, not entering it. You would need to import the main table first, capture the FileMaker serial ID, then Replace it into each child after importing.

Or, alternatively, using the ID of the item to tie them together, importing it with each child. The way to do that is to reach "up" the hierarchy with the xsl, using the syntax "../" to denote "up a level" in the path.

Your original xml you posted is slightly different from what you're talking about now; is it just different because that was a book and this is a movie? What is the xml now, or what command are you using to get it (and we'll get our own -).

I can't thank you enough for all the posts I've read of yours on XSL and Amazon.

Using this info I've been able to set up my own Listing/Catalog database for inventory (mostly books and some CD's/DVD's which I sell on several sites) using Amazons AWS for product info.

I've been able to set up my image download to put a face on a title... so to speak. But I had to use a plugin ($) since I'm Windows XP. I wish I could create my own but maybe in time.

My only other desire is just as this person has... Multiple returns of certain data, i.e. -Actor, Format, Languages, etc. and how to import those multiples.

I know that tables are the best way to go but it's more work than I want to do. I really just want a quick, down & dirty way to get it in for reference.

I don't have much need for this info except to verify the product. No need to export or actually do anything with it.

Isn't there a simpler way to get

-

Alyssa Milano

Holly Marie Combs

Rose McGowan

into 3 fields like actor1, actor2 & actor3 or even a repeating field?