Importing XML Amazon info XSL provided

[Cabinetman]

I suck at using the search option in here so once again I'm sorry. I am putting up my XSL for getting Amazon info for whomever since I really got it and developed it with the help of so many here to begin with anyway.

I started with what I already had and am trying to edit it.

SSssooooo here's what I want to do.....

Get 1 record with the ISBN, Lowest New price, Lowest Used price (which I already get)

Then take each offers - Merchant ID, Condition, Subcondition, Note and Price

Putting them into the fields:

merchantID1...Condition1..Subcondition1..Note1 ..Price1

merchantID2...Condition2..Subcondition2..Note2 ..Price2

merchantID3...Condition3..Subcondition3..Note1 ..Price3

Etc..... up to 5

At least that's my thought.....

Or is it better/easier to import all merchants into 1 field, Conditions into 1 field, etc. and calc out each one after that??

I would appreciate any help or direction to a previous topic on the subject.

Here's also what I'm starting with as far as the XSL and special thanks to those who've helped in the past... You know who you are just by looking at my XSL. LOL

amazon_info_search.zip

amazon_info_search_offers.zip

[comment]

Please post the raw XML file, and indicate what needs to be imported, into which table/field.

[Cabinetman]

OOPS ! Fixed the file...Sorry !

I was thinking for each offer up to 5 of them...

Merchant/MerchantID - MerchantID 1, MerchantID2, ..3, ..4 & ..5

OfferAttributes/Condition - Condition1, Condition2, etc...

OfferAttributes/SubCondition - SubCondition1, SubCondition2...

OfferAttributes/ConditionNote - ConditionNote1, ConditionNote2.....

FormattedPrice/Price - FormattedPrice1, FormattedPrice2.....

If I can see how to get just one of them set up I think I can do the others. I just can't figure how to get it to go to the next field in the sequence after each offer.

Thanks!

offers_test.zip

Edited June 1, 2009 by Guest

[comment]

I asked to see the XML file you get in response to your request. What you posted is another stylesheet.

I don't think having 5 identical fields is good data structure. You should keep the multiple offers(?) as records in a related table. You may import the data twice (once into the parent table, once into the child table), or import everything into the child table and then create the parent records, or perhaps another method - I really cannot say without seeing the incoming data.

[Cabinetman]

Ok fixed the file.... I think the multiple records would become unmanageable.

There would/could be 500 to 5000 ISBNs being checked with a possible 5 - 15 records on each for New, Used and Collectible.

I was also thinking of possibly say MerchantID having 5 repetitions...etc

:(

Edited June 1, 2009 by Guest

[comment]

I think the multiple records would become unmanageable.

I think exactly the opposite. I am not sure what you intend to do with this, but this is a clasic parent-child relationship and it would be best to let Filemaker to handle it as such.

[Cabinetman]

This information will have to be updated several times a day in order to compare a listings current price to others selling the same title in the same condition.

It takes around 30 min's to update about 2000 records at the moment and it'll take a double import to get both the 'New' and the 'Used' info.

I'm far from knowing whats best though... so I welcome your opinion!

Thanks

[comment]

Do you think it takes less time to update 1 record with 5 fields than 5 records with 1 field each?

[Cabinetman]

Yes.... I do....LOL.... but maybe I need to explain further.

The update is downloaded thru a URL from Amazon.

I can update 10 at a time using the URL I have to in order to get the current info. Ssssooooo the fewer the records the quicker I can do it.

Does that make sense? i may not be explaining it well enough...

[comment]

No, I am afraid it makes no sense to me. The document you download is the same no matter how you break it up when the data goes into Filemaker.

This has actually nothing to do with importing - it's a matter of correct data structure. I would say the same thing if you were entering the data manually.

[Cabinetman]

I guess that's where I misunderstood. I was looking at it as downloading more than updating.

But to make sure we're clear....

There are say 2000 ISBN 's that I will download the info on using the URL and I can request 10 at a time. The format will be as the XML file I uploaded but with 10 ISBN's total instead of 1 ISBN(I can upload another XML with the 10 if needed and will in a bit anyway)and then it'll have 5 MerchantId's in it for each ISBN with the condition, cost, etc. for each Merchant.

I have to draw in this info so that when I go to edit my sale price I can do it based on what others are selling it for and ignore my own listing.

Like I said I'm far from knowing how best to structure this but I'm already up and running with my database's... so I'm open to your suggestions on how best to set this part up.

Thanks

offers10_at_a_time_XML.zip

Edited June 2, 2009 by Guest

[comment]

To make sure we are clear, please clarify "updating". Suppose you already have an item in your solution, with say 4 offers. Now you do a new request for this item, and you get 6 offers: some new, some modified, some the same - how do you want to handle this?

[Cabinetman]

I will have to replace the existing data with the new data...

[comment]

OK, so just to be even clearer: if an existing offer is not repeated in the import, it should be deleted, right? IOW, delete all existing offers of an item before re-importing the item?

[Cabinetman]

Yes that is correct. All previous offers will be deleted and replaced by the current downloaded set of offers...

[comment]

I don't know - it seems like a fairly simple matter to me: clear out the child Offers table and import the new data. It will link up automatically to the parent Items table by the ASIN code.

If any fields in the parent item need to be updated too, this can be done by including the item data in the import, then running a script to update the parents (there are so few item attributes in the XML, it's hardly worth importing into a temp table).

[Cabinetman]

OK ... I see that part I guess. I'm not totally sure how to do the XSL but prob. can figure it out. Guessing it's similar to what I'm already doing in updating ISBN's anyway..

But then how would you pull this data in?

Table 1 = All active listings (lets say 500)

Table 2 = A catalog of these and many more ISBN's (say 5000 that will update only those that match current listings)that will be updated pulling in salesrank since it isn't in this XML

Table 3? = Offers

Once I download and create the new Table - offers - I need to compare price to those offers matching the ISBN AND disregard 1 or more offers based on them being mine or someone's I don't want to regard when I price mine.

I guess I could use a find to delete my own or another seller but I think I still want to see them in there...

[comment]

Table 1 and Table 2 look like they could be one - with a flag to mark active items.

Disregarding your own offers should be easy, if your MerchantID remains constant. With a little more sophistication, you could even have a list of merchants to ignore, so their offers wouldn't show in the portal.

[Cabinetman]

I know but I'm wayyyyy past that since I started this back under FM5 and don't want to redo scripting and such right now.

Also It allows me to do things easier based on what I know... I know next to nothing about portals... but guess I'll be learning.

AAaaannnddd... can I easily adjust the sale price in table 1 based on table::offers ?

[comment]

IMHO, you're never past doing it right.

can I easily adjust the sale price in table 1 based on table::offers ?

If you follow my suggestion of duplicating the data, it's just a matter of running a few Replace Field Contents steps (replacing parent fields with the data from related child). Watch for record locking issues if you have co-users, though.

Note that if you do not intend to edit the data, you could just display it directly from the child table.

[Cabinetman]

Possibly but at the moment I prefer the separate tables... There are lots of considerations to numerous to mention. Mostly since I can't spend the days restructuring for my own use.

SSsoooo before I leave you alone.. lol... how would I structure the XSL (reason I started this) section to take and create the new records?

does this look close???

			

				1

				

					

						0

						1

						

							

								

							

											

						

							

								

							

						

						

							

								

							

						

						

							

								

							

						

						

							

								

							

						

						

							

								

							

								

						

							

								

							

							

						

							

								

							

								

					

				

[nrg software]

I'd highly suggest you look at alternatives to XSL - Use a URL plugin to get the data and have a FMP script add/update records as need be by parsing out the raw XML response via a custom function.

http://www.briandunning.com/cf/1

[Cabinetman]

Hhhhhhhuuuuummmmmm.... interesting... very interesting...... Seems like a lot of editing to get all the data.. maybe. I'll have to look over this for a bit to wrap my small mind around it. lol

[comment]

does this look close???

Not really (what are you doing, guessing?). Try adapting the attached sample - it includes a few fields from different areas.

Import.xsl.zip

[comment]

I'd highly suggest you look at alternatives to XSL - Use a URL plugin to get the data and have a FMP script add/update records as need be by parsing out the raw XML response via a custom function.

In what way would this be better?

[Cabinetman]

Sadly... yes I was. I have a headache, am hungry and since I'm still learning I usually guess until I work out the bugs.

I've had no training and basically teach myself. Been using FM since V3 all together......

For now I'm going with the XSL since I'm more familiar with how it works..

I've set up the table... will import the data and then figure out the portal/linking to get the info I want to show on the Listings table.

Thanks. I can get it from here I think... lol At least this part.

[comment]

I usually guess until I work out the bugs

I wish I was that lucky: I am NOT guessing, and I still get it wrong many times.