FMP XML into InDesign challenges

[Timoshi]

Hey Folks,

I just barely have started getting into this stuff, and I took on two projects simultaneously; one XML from MS Access and one XML from FMP. 

One of the tricky things is I am also using a script to make 20 (or more) pages from a template page to import into, for both projects. The MS Access import I have working beautifully, everything goes where I want it. FMP not so much.

In order to get the script to work, I had to have a "flat" XML, so I have used the FMPDSORESULT method. What happens is that after the first page on the import, my nodes get out of order and there are blank text frames and everything gets spread further and further apart over subsequent pages.

I'll be honest, XML is brand new territory for me, just figuring stuff out from forums and tutorials, copying and pasting, so it's probably something dumb. I got the MS Access thing to work, so I just tried to literally force my file to be a copy as far as structure, added some tags (the "<Query2"> stuff) in what I believe to be the appropriate places, assuming I could make it work...but it doesn't. For this porject. Here's my XSLT that doesn't work.

<xsl:stylesheet version="1.0"  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:template match="/">

<dataroot>
<Query2>

<xsl:for-each select="dataroot/Query2/*">

<xsl:value-of select="Query2/CR_Number"/>
<xsl:value-of select="Query2/Title"/>
<xsl:value-of select="Query2/Year_Created"/>
<xsl:value-of select="Query2/Medium_materials"/>
<xsl:value-of select="Query2/Owner_Credit_Line"/>
<xsl:value-of select="Query2/Provenance"/>
<xsl:value-of select="Query2/References"/>
<xsl:value-of select="Query2/Exhibitions"/>
<xsl:value-of select="Query2/Dimensionsheightcm"/>

</xsl:for-each>

</Query2>
</dataroot>

</xsl:template>
</xsl:stylesheet>

 

 

as compared to my XSLT that does work

<xsl:stylesheet version="1.0"  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>
<xsl:template match="/">

<dataroot>
<Query3>

<xsl:for-each select="dataroot/Query3/*">

<xsl:value-of select="Query3/LOT"/>
<xsl:value-of select="Query3/concatenated_artist_names"/>
<xsl:value-of select="Query3/Title"/>
<xsl:value-of select="Query3/concatenated_medium_date"/>
<xsl:value-of select="Query3/Credit_Line"/>
<xsl:value-of select="Query3/Value"/>
<xsl:value-of select="Query3/Minimum_Bid"/>
<xsl:value-of select="Query3/Participating"/>

</xsl:for-each>

</Query3>
</dataroot>
</xsl:template>
</xsl:stylesheet>

I am guessing you'll want to see the XML, too, but I signed a form saying I wouldn't share the data from FMP anywhere, so let me figure out how to mock up dummy data I guess and just share the other data here...unless you see a glaring problem in the XSLTs?

But here is the MS Access XML which worked great (I am going to trim it down and remove a few entries to save some space here, there are actually about 300 LOTS)

<?xml version="1.0" encoding="UTF-8"?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:noNamespaceSchemaLocation="Query3.xsl" generated="2019-07-25T12:40:08">
<Query3>
<LOT>101</LOT>
<concatenated_artist_names>Paul Davies</concatenated_artist_names>
<Title>L’ Horizon Fade</Title>
<concatenated_medium_date>Acrylic on paper, 2018</concatenated_medium_date>
<Credit_Line>Courtesy of the artist</Credit_Line>
<Value>4000</Value>
<Minimum_Bid>2000</Minimum_Bid>
<Participating>1</Participating>
<LOT>102</LOT>
<concatenated_artist_names>Andy Moses</concatenated_artist_names>
<Title>Geodesy 703</Title>
<concatenated_medium_date>Acrylic on canvas over circular wood panel, 2018</concatenated_medium_date>
<Credit_Line>Courtesy of the artist and William Turner Gallery</Credit_Line>
<Value>12500</Value>
<Minimum_Bid>6250</Minimum_Bid>
<Participating>1</Participating>
<LOT>103</LOT>
<concatenated_artist_names>Gwynn Murrill</concatenated_artist_names>
<Title>Twisting Cheetah 2</Title>
<concatenated_medium_date>Bronze, 2018</concatenated_medium_date>
<Credit_Line>Courtesy of the artist</Credit_Line>
<Value>9000</Value>
<Minimum_Bid>4500</Minimum_Bid>
<Participating>1</Participating>
<LOT>104</LOT>
<concatenated_artist_names>Tony DeLap</concatenated_artist_names>
<Title>Card Trick</Title>
<concatenated_medium_date>Acrylic on linen, 2014</concatenated_medium_date>
<Credit_Line>Courtesy of the artist and Parrasch Heijnen Gallery</Credit_Line>
<Value>35000</Value>
<Minimum_Bid>17500</Minimum_Bid>
<Participating>1</Participating>
<LOT>105</LOT>
<concatenated_artist_names>Kenton Nelson</concatenated_artist_names>
<Title>The Dive</Title>
<concatenated_medium_date>Watercolor, 2014</concatenated_medium_date>
<Credit_Line>Courtesy of the artist</Credit_Line>
<Value>8000</Value>
<Minimum_Bid>4000</Minimum_Bid>
<Participating>1</Participating>
</Query3>
</dataroot>
 

 

and of course the script for InDesign, as copied and then tweaked from https://medium.com/@Fjonan/multi-page-document-with-xml-in-indesign-2ab336ecf389

 

app.doScript("main()", ScriptLanguage.JAVASCRIPT, undefined, UndoModes.FAST_ENTIRE_SCRIPT,"Make20Pages_Auction3"); // makes the whole import one undoable action 
  
function main () {  
  
    if (app.documents.length === 0) {  
        alert ("Please open a document.");  
        return;  
    }  
  
    app.scriptPreferences.enableRedraw = false;  
  
     var doc = app.activeDocument;
    // Create Dummy pages
    createDummyPages (20);
    // Import XML
    importXMLData (doc);
     // Remove the blank pages in the document.  
    removeBlankPages (doc);  
    // Reverse Order of Pages (because due to how this all works its the wrong way around)
    reversePageOrder (doc);
    
    app.scriptPreferences.enableRedraw = true;  
    

    /* ADDS NEW PAGES WITH PLACEHOLDER CONTENT */
    function createDummyPages(anzahldummies) {            
            for (i = anzahldummies; i >= 0; i--){  
                app.layoutWindows[0].activePage.duplicate (LocationOptions.AT_BEGINNING, app.layoutWindows[0].activePage);
            }   
     }
    
    /* IMPORTS XML DATA */
    function importXMLData (doc) {
         
        var myXMLImportPreferences = doc.xmlImportPreferences; 
         
        myXMLImportPreferences.importStyle                       = XMLImportStyles.mergeImport;  
        myXMLImportPreferences.createLinkToXML               = false; 
        myXMLImportPreferences.allowTransform                 = true;  
        //myXMLImportPreferences.transformFilename            = "F:\Curatorial\Registrar\TimCampbell\Auction2020\goodone\Query3.xsl 
        myXMLImportPreferences.repeatTextElements           = true; 
        myXMLImportPreferences.removeUnmatchedExisting  = false;  
        myXMLImportPreferences.ignoreUnmatchedIncoming  = false;  
        myXMLImportPreferences.importTextIntoTables         = false;  
        myXMLImportPreferences.ignoreWhitespace              =true;  
        myXMLImportPreferences.importCALSTables             = false; 
        myXMLImportPreferences.importToSelected              = false; 

        doc.importXML(File("F:/Curatorial/Registrar/TimCampbell/Auction2020/goodone/Query3b.xml")); 
    }
  
    /* REMOVES EMPTY PAGES FROM THE DOCUMENT */
    function removeBlankPages (doc) {  
  
        var pages = doc.pages.everyItem().getElements().slice(0);
        var i = pages.length;  
        while (i-- >1) { 
            !pages.allPageItems.length && pages.remove (); 
            }  
        !pages.allPageItems.length && pages != doc.pages[-1] && pages.remove ()  
    } 

       }

        /* REMOVES EMPTY TEXTFRAMES FROM THE DOCUMENT */
    function removeBlankFrames (doc){
        var myStories = doc.stories.everyItem().getElements();  
        for (i = myStories.length - 1; i >= 0; i--){  
            var myTextFrames = myStories.textContainers;  
            for (j = myTextFrames.length - 1; j >= 0; j--)    {  
                if (myTextFrames[j].contents.length <= 2){  // if you got TAGS inside a frame it will have a length of 2 even if no content has been imported - HACK
                    myTextFrames[j].remove();              
                }
            }  
        }  
    } 

    /* REVERSE ORDER OF DOCUMENT PAGES */
    function reversePageOrder(doc){        
        var pages = doc.pages.everyItem().getElements().slice(0);
        var counter = pages.length; 
        while (counter-- >0) { 
            pages[counter].move (LocationOptions.BEFORE, pages[0]);
        }
    }

And all of that stuff works. So why not my other one?!?! It feels like it's a template thing? I am totally guessing.

Any help would be appreciated!!!!!

Thank you in advance!

Tim

 

[comment]

This is very confusing. If you are trying to export data from Filemaker into another XML schema, then we need to know (a) the structure of your source FMP file and (b) the structure of the target schema. I can't see how any part of the codes you have posted is relevant to this task.

As an aside:

32 minutes ago, Timoshi said:

In order to get the script to work, I had to have a "flat" XML, so I have used the FMPDSORESULT method.

This is a non-sequitur. The XML grammar used when exporting has nothing to do with the result being "flat" or otherwise. It has everything to do with what can be changed in the FMP file without breaking the export: field names (when using the FMPXMLRESULT grammar) or field export order (when using the FMPDSORESULT grammar).

[Timoshi]

OK, as I said, it's all new to me.

As far as being "flat" I was working from the suggestion of the guy who wrote the page-duplicating script. Having the FMPXMLRESULT structure, with field names in one place and references to rows and columns around the data made it too complicated for me to see my way to making my file look like his. FMPDSORESULT made that simpler.

As for the schema for InDesign...all I can do is share an image of where I am trying to go with it. I don't have any more visibility into the schema than that.

I will attach the files this time, hope that works OK.

I appreciate your looking, and I apologize if I am too ignorant of the basics for this to be an appropriate place to seek help.

Thank you in advance.

 

20FMPDSORESULT_b_hypothetical.xml FMPDSO_b_alt.xsl

[Timoshi]

Here is the "after" using the script

[Timoshi]

and pages 2, 3

[comment]

I am not sure what exactly I am looking at. To move this forward, I suggest you do the following: 

1. Enter some dummy data into your FMP file (if necessary, use a copy of the real file). Make sure you have at least two records;
2. Export the fields you need as XML, using the FMPXMLRESULT grammar, with no XSLT stylesheet applied;
3. Write an XML document, using the same dummy data, in the format you need for importing.

Then post the two files here.

Note: I am asking for at least two records, because the files you have shown so far seem to have only one record and no provision for multiple records in their structure.

[Ocean West]

Not sure how relevant it is https://www.filemakermagazine.com/videos/publishing-indesign

[Timoshi]

On 8/12/2019 at 11:21 PM, comment said:

I am not sure what exactly I am looking at. To move this forward, I suggest you do the following: 

1. Enter some dummy data into your FMP file (if necessary, use a copy of the real file). Make sure you have at least two records;
2. Export the fields you need as XML, using the FMPXMLRESULT grammar, with no XSLT stylesheet applied;
3. Write an XML document, using the same dummy data, in the format you need for importing.

Then post the two files here.

Note: I am asking for at least two records, because the files you have shown so far seem to have only one record and no provision for multiple records in their structure.

OK, I will show you how it goes with FMPXRESULT and the same 3 sample records that are shown in the the FMPDSORESULT example above. If you're calling it one record because there is only one set of <Query2></Query2> tags, that's because that's how I got this to work in my file pulled from MS Access, and it works there for hundreds of records. I was trying to recreate my success.

I gave each of the three sample records field data ending with a 1, 2, or 3 in each field to show which FMP record they were exported from.

Attached is how it looks on the import. InDesign does not provide me with an opportunity to see the XML format for importing other than the "Structure View" in the pane at the left. In this case, none of the sample data made it into any of the import text boxes (fields).

I appreciate your time and patience.

Thank you,

Tim

 

first3hypothetical_fmpxmlresult.xml

[comment]

I am afraid these screen shots are not telling me anything useful. Now that you have posted the raw XML export from Filemaker, we know what you're starting with. We still need to figure out what you want to end up with. This is what I asked for when I said:

On 8/13/2019 at 9:21 AM, comment said:

3. Write an XML document, using the same dummy data, in the format you need for importing.

Since you say you have managed to import the data from Access: what does that file look like, just before being imported into InDesign?

--
Another question, which could shorten this process: are you able to import the attached file?

 

test.xml

Edited August 14, 2019 by comment

[Timoshi]

6 minutes ago, comment said:

I am afraid these screen shots are not telling me anything useful. Now that you have posted the raw XML export from Filemaker, we know what you're starting with. We still need to figure out what you want to end up with. This is what I asked for when I said:

Since you say you have managed to import the data from Access: what does that file look like, just before being imported into InDesign?

Thank you -- yes, I understood what you were asking for in terms of an XML document showing the format I need for importing, but I have no idea how to get to that...

I am attaching an XML file (and XSLT) that worked for Access; I have been able to replicate that success with other XML files from Access. The attached has just 4 records.

Query2d.xml Query2d.xsl

[comment]

This is starting to get weird. That XSLT does not make any sense and it's not possible you have managed to get anything useful out of it. I have attached an XML document to my previous post - you may have missed it. Please see if you can import it. 

Edited August 14, 2019 by comment

[Timoshi]

2 minutes ago, comment said:

This is starting yo get weird. That XSLT does not make any sense and it's not possible you have managed to get anything useful out of it. I have attached an XML document to my previous post - you may have missed it. Please see if you can import it. 

I am not surprised to hear that it's weird, as I have just basically been trying to self-teach and copy and paste until something happened right...

I will attach a screenshot again, for which I apologize -- it illustrates that I can import your test XML, but it doesn't put the info where I want it.

Thank you again for your patience and willingness to look at it.

2 minutes ago, Timoshi said:

I am not surprised to hear that it's weird, as I have just basically been trying to self-teach and copy and paste until something happened right...

I will attach a screenshot again, for which I apologize -- it illustrates that I can import your test XML, but it doesn't put the info where I want it.

Thank you again for your patience and willingness to look at it.

Renaming my Query2 to ROW also doesn't do it.

4 minutes ago, Timoshi said:

I am not surprised to hear that it's weird, as I have just basically been trying to self-teach and copy and paste until something happened right...

I will attach a screenshot again, for which I apologize -- it illustrates that I can import your test XML, but it doesn't put the info where I want it.

Thank you again for your patience and willingness to look at it.

Renaming my Query2 to ROW also doesn't do it.

OH WAIT, IT DID

 

5 minutes ago, Timoshi said:

I am not surprised to hear that it's weird, as I have just basically been trying to self-teach and copy and paste until something happened right...

I will attach a screenshot again, for which I apologize -- it illustrates that I can import your test XML, but it doesn't put the info where I want it.

Thank you again for your patience and willingness to look at it.

Renaming my Query2 to ROW also doesn't do it.

OH WAIT, IT DID

 

sorry, my cursor was in the wrong place!

 

[comment]

4 minutes ago, Timoshi said:

but it doesn't put the info where I want it.

What exactly does that mean? IIUC, you're supposed to map the input to placeholders in your InDesign document. What's preventing you from putting the info where you want it?

[Timoshi]

OK, so a straight import works on one page. The multiple pages from the script only gets the first page into my document, though, and I have multiple later pages of blanks and the subsequent ROWs are not put into my document...one more ugly screenshot.... 

Also please notice that the order of the fields has changed after the first page (in the pane at the left) for reasons I do not understand....

[comment]

That sounds like you're not working the InDesign end correctly. The thing that puzzles me the most is that you say you are able to make it work with data from Access - but you're not able to tell us what the difference is.

I can help you with getting the export from Filemaker to conform to any format that works for you. But I cannot help you with finding out what that format needs to be.

[Timoshi]

Well, I appreciate the effort. If I figure out what the difference is, I will come back here and post it.

(Since I can pretty much always get the first page into InDesign correctly, I actually suspect it is the javascript that I need to modify somehow, but I still don't know why for two files that look to me to be following exactly the same structure. Or how, lol. I had hoped that there was some XSLT trick to make the nodes always stay in the same order in a "for-each" situation and have that be a workaround, but I gather that is not the case.)

Thank you for all the time!

[comment]

1 minute ago, Timoshi said:

I had hoped that there was some XSLT trick to make the nodes always stay in the same order

I don't think that's the problem here. The nodes in my test.xml ARE in the same order for every ROW. And you can certainly change that order to anything you want (the simplest way is by changing the field order when exporting).

[Timoshi]

7 minutes ago, comment said:

I don't think that's the problem here. The nodes in my test.xml ARE in the same order for every ROW. And you can certainly change that order to anything you want (the simplest way is by changing the field order when exporting).

Yah, for every FMP export file where I can successfully import a first page to InDesign, the subsequent pages have the nodes (which are in the same order in the XML) mixed up.

EXCEPT for my MS Access files, which look the same, but they work -- in spite of the evidently nonsensical XLSTs, lol.

 

Edited August 14, 2019 by Timoshi
typo

[Timoshi]

11 hours ago, Ocean West said:

Not sure how relevant it is https://www.filemakermagazine.com/videos/publishing-indesign

Thank you for looking; the lesson didn't get to where I need to go.

[Timoshi]

On 8/14/2019 at 4:59 PM, Timoshi said:

Yah, for every FMP export file where I can successfully import a first page to InDesign, the subsequent pages have the nodes (which are in the same order in the XML) mixed up.

EXCEPT for my MS Access files, which look the same, but they work -- in spite of the evidently nonsensical XLSTs, lol.

 

Hey there, just wanted to let you know what fixed my problem -- and it WAS at the InDesign end.

Your time and input definitely helped me get there!

When I copied my target text frames/fields in the document (via script or by hand) for subsequent pages, it pasted them in the order in which they had originally been created on the page, not the order in which were selected, and not the order they appeared in the Structure panel or on the page. The XML import was hitting in the wrong order, so it got messed up. By clearing my InDesign document and starting over, repopulating it in the order that matched my XML order, I was successful.

Sorry for taking up all your time with that. I did also learn that I still know *nothing* about XSLTs, though, lol.

[Ocean West]

If this is something you plan to do quite often you may consider https://www.65bit.com/software/easycatalog/

I've used this works great.

[comment]

29 minutes ago, Timoshi said:

I still know *nothing* about XSLTs,

XSLT can be tricky - but for the purposes of exporting from Filemaker you only need a small fraction of it. After spending an hour or two with a tutorial you should be able to handle most cases. 

 

[Timoshi]

1 hour ago, Ocean West said:

If this is something you plan to do quite often you may consider https://www.65bit.com/software/easycatalog/

I've used this works great.

Thank you -- looks interesting; I will keep it in mind.

[Ocean West]