Need XSLT Help!

[Jeff M]

Something with this XSLT stuff is not clicking. I've looked at tons of samples but none quite match what I need. I've attached my XML data document.

I want to import Page Number value and the Key value, but only on the entries where the "Field" value = SKU_ID VALUE". For example...see entry index 9 and 19.

I know the XML is formatted a bit strange, but I don't have any control over it. Can someone help me with the formatting on the XSLT file?

Page_Data.xml.zip

[comment]

Try something like:

...































...

[Jeff M]

Ok, gave that a try...I got an error about a required "Found" element missing. Here's my entire code:

<?xml version="1.0" encoding="UTF-8"?>



       

	

		

			0

			

			

			

				

					

						YES

						1

						

						TEXT

					

				

			

			





































			

		

	

[comment]

Try this one?

ezcat.zip

[Jeff M]

Well, that one worked perfectly. While I was hoping for a response, I continued to mess with it and got this far on my own...

<?xml version="1.0" encoding="UTF-8"?>



       







0











YES

1



TEXT









0









0

0

Mine was importing, but only the first column I specificy. I would get all records for Page Number, but none for Key. If I switched their order in the code, it would get all records for the Key and none for the Page Number.

In comparing the two bits of code, the main difference I see is where you defines the fields. While I am very, very thankful for your help, I'd love to learn what I was doing wrong so I'm more likely to get through this on my own next time.

Thanks!!!

[comment]

Well, you have defined only one FIELD in the METADATA section (and its name is an empty string). There should be a FIELD in METADATA for every COL in RESULTSET (these are matched by their order in the result tree).

[Jeff M]

Well, now that you explain it like that it makes perfect sense. So if I define two fields in the metadata, but below select 3 values, it will use the first 2? And if I define fields "One" "Two" and "Three" above, but below I select Two, then Three, then One...they'll be in the wrong order?

[Jeff M]

Alright, so...

My scope is changing a bit...I also need to be able to import the InDesign filename which is in the "Title" area of the XML file. Ideally I would want to import that Title data into each record into a field named FileName. What's the trick to grabbing the title once, then writing it into each record?

[comment]

So if I define two fields in the metadata, but below select 3 values, it will use the first 2?

It will "use" all three - but you will see only two fields on the source side of the import dialog, so Filemaker will import (at most) two.

And if I define fields "One" "Two" and "Three" above, but below I select Two, then Three, then One...they'll be in the wrong order?

From your point-of-view, yes.

I also need to be able to import the InDesign filename which is in the "Title" area

One way would be:

(this is a "lookup" inside the loop).

[Jeff M]

You sir or madam, are amazing! Thank you so much. It not only works perfectly, but I think I actually understand why now. :

My next task will be to get FileMaker to import a whole folder full of these at a time. The XML files will all have different names, and the import function in FM seems to want that to be static. I'm thinking of writing an Applescript that will copy and rename 1 file at a time to a folder where the FM script expects to find it, then have the Applescript tell FM to do script "Import XML file." Am I overthinking this?

[Fenton]

It is no longer necessary to rename each file (then rename it back). FileMaker can now accept dynamic paths in an Import step, if you preset the path into script Variable. You would first capture all the file paths from the folder, using AppleScript, and set them into a global field. Then use a FileMaker Loop to go thru and put them into the variable and import.

This is a small example file I made, which does "csv" files. Just change that to "xml" in the AppleScript.

P.S. You must set up the Import step with the script variable as the 1st line in the file path dialog, but with a real existing xml file as the 2nd line. Otherwise you will not be able to set up the Import Order. FileMaker cannot do that using only a script variable. (Actually, maybe it can with a $$ global variable, never tried.) After the Import Order is set up, it should work with any file of the same structure. The "real" file you used is then no longer needed; but if you ever need to edit the order, you'd need to put (or one like it) back.

Import_Files_of_Folder_Choose.zip

Edited May 22, 2010 by Guest

[Jeff M]

Yes...that would work perfectly, except I didn't mention I was trying to setup the Mac to auto import them. I was going to set a small stand alone app to check the folder every 5 minutes or so, then do it's thing. Your method, which is fabulous by the way, would require a human to trigger it each time, right? As far as I know, FileMaker still doesn't have timed events, does it?

[Fenton]

The reason that I had it using "choose folder" is because it is a simple example file, so anyone can choose a folder and see that it works. But you could just as easily just point it to a folder. But then it needs to know the relative path to the folder. You can calculate that within FileMaker.

Here is an older example file,* which Imports csv files from a folder. The scripting is a bit clunkier in this file.

*The name of the file has "8" in it, because the file was from earlier; like I said, kind of old.

Import_csv_wFM8.zip

[comment]

I was going to set a small stand alone app to check the folder every 5 minutes or so

Just wondering if you couldn't simply attach action to the folder on add items. That way the Applescript only needs to pass the added items to Filemaker.

[Jeff M]

Fenton, thanks for the example files. They are very helpful. I see what you mean about just changing a few things and getting there. I'm going to see what I can make work...my main goal is to have it not require a human to import the files. I just want that to happen automatically on a Mac in the corner of my office.

The only reason i was leaning toward the application is a cooworker already has one made that does it. So really, if I have him add a Do Script command to his applescript code...presto.

The folder action idea might be the easiest (had I not had the sample files from Fenton, that is). I've had mixed results with folder actions in general in the past, but I don't see why it wouldn't work well in this situation.

The XLS / XML import is working perfectly now. I wish there was a way I could thank you, so I guess I just say thank you.