Understanding and using the Database Design Report – Part 3

In Parts one and two, I described XML, and more specifically, the FileMaker built in XML format that it understands and will import. In this final part, I’ll actually show some XSLT to transform some of the DDR from one format to another.

So, what is XSLT?

First of all, XSLT, as explained in part 2, is a way of going from one XML format to another. It allows you to change any one XML format, such a a FileMaker export, into another, such as a SVG graphic, or an RTF document.

In our case we’re wanting to do something very specific : Process the DDR report and extract from it some data that we can import back into FMP. I’m going to start with some very simple examples, and also show a little more complicated one so that you understand the basic ideas.

The example I’m going to show is extracting all of the account details out of the DDR. So we want to import a list of accounts, along with the relevant details for each account. First lets look back at that part of the DDR :

As you can see there is a node in the list called “AccountCatalog”. This obviously contains all of the accounts we need to access and view. So lets omit the other nodes and expand that one :

<FMPReport link="Summary.xml" type="Report" version="8.5v1"> <File name="CCP_Worksheet" path="192.168.20.4"> <AccountCatalog>


            <Account id="1" name="[Guest]" status="Inactive" managedBy="FileMaker" privilegeSet="[Read-Only Access]" emptyPassword="False" changePasswordOnNextLogin="False">
            <Account id="3" name="User" status="Active" managedBy="FileMaker" privilegeSet="Data Entry" emptyPassword="True" changePasswordOnNextLogin="False">
            <Account id="5" name="FMP_Read" status="Active" managedBy="External" privilegeSet="Read-Only Shared">

</AccountCatalog> </File> </FMPReport>

I’ve added in some extra line breaks, because it’s a bit difficult to read with the line wrapping. I wanted to show here that there is a lot of information in each line.

This particular file has only only 3 accounts, but you can see that all of the data that you need that is related to each account is there. Also note, in the actual data but not shown above, there is also a Description node that is below each of the Account nodes, but I’ve left it out for now. And finally each Account node has a closing tag, so that it’s valid XML, which I also haven’t shown above.

What path am I on?

There is one critical thing to follow in this, and that’s the idea of a Path. XSLT uses the terminology of a path to describe a set of nodes and the direction you go to get from one node to another. It’s based on the idea of the starting point being the “root” node, and you move down the path, by going from one to the next. Each subsequent node is contained within the previous one.

So have a look at the above XML from the DDR and consider the path to the first “Account” node. The path in this case is :

/FMPReport/File/AccountCatalog/Account

The “root” node is the FMPReport node, which contains one “File” node, which in turn contains a “AccountCatalog” node, which in turn contains an “Account” node. That is a path that we can use in our XSLT to access this list.

Also note that there are other possible paths, this isn’t the only one. For example at the File node, we have a range of possible choices, there are 12 “somethingCatalog” nodes and an Options node, but we can pick either one to go down. Path isn’t about the order that the nodes appear inside the parent node, only that one node is contained within another.

Get to the XSLT already!!

So now lets look more specifically at the actual XSLT.

<?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="http://www.filemaker.com/fmpxmlresult"> <xsl:template match="/"> snip </xsl:template> </xsl:stylesheet>

I’ve split the first node into multiple lines to make it easier to read. Remember, that XML and XSLT doesn’t care about whitespace except where it’s to break up one word into another. You can use a space, tab or return, it will still be ok.

So what does all this mean? Well, the first two tags are critical bits of information for the XSLT to work, but for the purposes of this article I’m not going to explain them in detail. Any of the good books on XSLT will start by talking about namespaces and will document these parts. We’re keeping it relatively simple and will be only ever working within FMP, so all we need to know is that these two lines are required and will be the first thing we put in any of our XSLT documents.

The third line is the template line, which essentially gives us a starting point for the XSLT to work on. In this case it’s starting point is “/”, which means start at the root node of the XML we’re going to process. The starting point is the path specified by the match attribute :

<xsl:template match="/">

so, it’s starting at the root node.

You can’t just skip over namespaces and templates!!

Anyone who knows XSLT is probably concerned I’m skipping over these areas. But I’m going to anyway. The purpose of this article isn’t to explain all of the details of XSLT and how it works. I’m trying to give you enough information to be able to understand what’s happening and to do some basic XSLT of your own. If you really need more information about these areas, it’s probably beyond the scope of this article anyway, and you should buy yourself a good XSLT book.

Plus we have the bonus of this sort of template being available, so we can copy and paste these into a new document and get up and working in short order. In all of the XSLT we’re going to write to transform the DDR we can just use one of the existing documents as a starting point, and make adjustments as required.

Back to the code.

There is one important thing that it is worth noting here. These tags are named differently from the others we’ve been working with. They all have “xsl” at the start, followed by a colon and another name. “xsl” is the namespace that the document is using, and I find the best way to think of these nodes is that they are like commands within XSLT. The namespaces are defined in the second node above – the “xsl:stylesheet” node. From there on, everything that starts with this namespace (xsl) is a command, and anything else is data. It will make a little more sense when I expand the XSLT a bit further :

If you remember what the data looked like in part 2, then you’ll see that starting a few nodes in, is exactly the same set of nodes that is required by FMP to import data in. We’ve left some of the details blank, like layout, name etc in the database node. ( If this was a format coming out of FMP, not being imported, FMP would include this information for us. )

The difference between these tags and the first two nodes is that they have no namespace. To put it in other terms, these aren’t commands, they’re data and the XSLT processor is going to just generate these tags in the output data exactly as they appear here. They aren’t modified or changed in any way.

Also, although we’re indenting these in the XSLT two tab stops further than in the real data format, it won’t matter to the import. The indenting and “white space” in XML makes no difference. The only critical thing is the heirarchy of nodes, and we’re maintaining that exactly.

Leaving out everything except the METADATA nodes this time, lets expand it one step further.

<?xml version="1.0" encoding="utf-8"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="http://www.filemaker.com/fmpxmlresult"> <xsl:template match="/"> <FMPXMLRESULT xmlns="http://www.filemaker.com/fmpxmlresult"> <METADATA> <FIELD> <xsl:attribute name="EMPTYOK">YES</xsl:attribute> <xsl:attribute name="MAXREPEAT">1</xsl:attribute> <xsl:attribute name="NAME">id</xsl:attribute> <xsl:attribute name="TYPE">TEXT</xsl:attribute> </FIELD> <FIELD> <xsl:attribute name="EMPTYOK">YES</xsl:attribute> <xsl:attribute name="MAXREPEAT">1</xsl:attribute> <xsl:attribute name="NAME">name</xsl:attribute> <xsl:attribute name="TYPE">TEXT</xsl:attribute> </FIELD> <FIELD> <xsl:attribute name="EMPTYOK">YES</xsl:attribute> <xsl:attribute name="MAXREPEAT">1</xsl:attribute> <xsl:attribute name="NAME">Description</xsl:attribute> <xsl:attribute name="TYPE">TEXT</xsl:attribute> </FIELD> snip </METADATA> </FMPXMLRESULT> </xsl:template> </xsl:stylesheet>

In this example, we’re going to import three fields : id, name and status. We can tell that by the number of FIELD nodes there are in the METADATA node. You will also see here, a new set of four nodes below each FIELD node, that starts with “xsl”. Remember, this means in effect that these are “commands” or programming, and aren’t sent to the output exactly, they’re processed.

Attributes

The xsl:attribute node adds an attribute to a node. So for example this node :

<PRODUCT BUILD="" NAME="" VERSION=""/>

The node is a PRODUCT node. It has 3 attributes, “BUILD”, “NAME” and “VERSION”. In our example, they all have blank values, but they could just as easily contain real data. The xsl:attribute command tells the XSLT engine to add an attribute to the parent node ( which in this case in the FIELD node ) with the details included. So this command :

<xsl:attribute name="EMPTYOK">YES</xsl:attribute>

Turns this :

<FIELD>

into this :

<FIELD EMPTYOK="YES">

It has added the attribute, given it the name specified, and given it the value specified. So once all four of those attribute commands are done, we’re left with this :

<FIELD EMPTYOK="YES" MAXREPEAT="1" NAME="id" TYPE="TEXT"> </FIELD>

And that is the end result that is generated by our XSLT. We could just as easily have included them in the main tag itself like the example above. Both ways give the same result. There are other places that using the attribute command is used though, so it’s a good example to understand how this is working.

Now lets go back to the XSLT and look at the RESULTSET node, leaving out the METADATA nodes this time.

It doesn’t actually matter in the import if the FOUND attribute on the RESULTSET node has a real value or not. FileMaker will import the data just fine anyway. In an export, it would contain an actual record count.

Loop the loop

And now we learn our next bit of XSLT programming : the “xsl:for-each” command. This is in principle very similar to a loop in ScriptMaker, with one subtle difference. In ScriptMaker we need to explicitly tell the script to go to the next record, and also when to exit the loop. In this xslt command we’re being given a set of nodes, and looping through each one, one at a time. Once that is done we stop looping. There isn’t any command to go to the next node, or to exit when you’ve got to the end, it’s all built into this loop.

And so the question is : What am I looping through? Well you’re looping through a set of nodes. And the set of nodes that it’s looping through is specified in the “select” attribute of the for-each command. You may notice that this matches the “Path” I mentioned above. It’s a way of saying, loop through every Account node in the file you find at this path, and for each one, do something.

This concept of the for-each loop and the paths to get to them is the critical learning step in understanding the DDR and XSLT. I’ll get back to it in a moment, but first I’ll show you what exactly the loop does. Lets expand that node further :

The first thing in our “for-each” loop is a “ROW” node. In other words, for each Account node in the DDR, generate a ROW. Back in part 2, you will have seen that a ROW is equivalent in FileMaker to a record. So this will have one ROW for each Account, which will get transformed into an Account record once imported.

This is the biggest trick to understand in our processing of the DDR. You find a list of nodes you want to turn into records, and use a loop ( in xslt it’s a for-each loop ) to generate a list of output data records.

Each ROW is a Record.

The ROW node has a couple of attributes. Again, we put dummy values in them, as we’re importing not exporting. And finally we get to the part where we do all the work. Each ROW has 3 COL nodes – one for each FIELD in the metadata above.

It’s important here that you make sure that things match up. For example you should have as many columns as you have setup in the METADATA section, and that the order matches. So COL # 2 in the list is the right data for FIELD # 2 in the metadata.

And we also get to the other critical part of the programming in XLST which is the xsl:value-of command. This command basically says to get this specific value and return it. More on this in a bit.

Back to the overview.

You can see in this that there are parts of our XSLT which is just like the XML that we’re wanting to import. There are other parts that are commands, that manipulate the data. Anything that isn’t in a namespace is exported exactly as it appears. The xsl namespace allows us to perform commands so we can loop through items, generating the data we need. As long as you can understand the two sections of the XSLT and why they’re there, you’re well on the way to writing your own XSLT and utilising the DDR.

Context

There is a concept in XSLT which is very similar to the idea of context in FileMaker. In FileMaker, when you’re on a layout with a specific base table occurrence, the fields from that table are only one step away. Anything else requires a relationship, and will be referenced via it’s related table. In XSLT, the concept is the same. When I’m inside this for-each loop, the context is the node that you specified in the “select” attribute. In this case the select attribute was “/FMPReport/File/AccountCatalog/Account”, so everything from here on is relative to that node.

The select attribute.

The xsl:value-of command returns to the output some text from the DDR. We tell it which text we want by setting the select attribute. In our case above, we’ve chosen to select “@status”. This will output a value into the output XML of whatever we want, and is how we move the data from input (DDR XML) to output (import data).

The select attribute has a whole bunch of options for how to specify the information you want to get. There are ways of looking at other nodes relative to this one, going either back or forward in the hierarchy. There are ways of getting attributes or whole blocks of text, or even whole nodes and their sub nodes. There are conditional statements, similar to IF or CASE in FileMaker, and there are functions you can perform to modify the data you’re getting before you send it to the output.

In processing the DDR, there are 3 things we’re going to need to do more than anything to get the data we’re after :

Get the value of an attribute of a node.
Change context – move from one node to another.
Get the value of an entire node.

Getting Attributes

To get an attribute value, we use the “@” symbol. So to get the id attribute of the Account node, we use “@id”. So our select statment is :

<xsl:value-of select="@id"/>

This is exactly what we’re doing in the example above. Our context ( the starting point for this value-of command ) is the Account node. And the Account node has a bunch of attributes, one of which is named “id”. So to return the value of the id attribute in the context of the Account node, we just use “@id”.

Moving around a Path

Remember that this is all context dependent. We’re only able to specify this attribute directly because our previous for-each loop put us at this context. To get to the id attribute if you started from the root node (from the start, without our loop) we’d have to specify :

<xsl:value-of select="/FMPReport/File/AccountCatalog/Account/@id"/>

By starting with the a leading “/” we’ve effectively said, go back to the start. You should be able to see here that we’ve used a path in the select attribute in the same way we did in the select attribute of the for-each loop. What works in one select will work in another, if the nodes exist.

You can move from one point to another by using a path within the select statement. In the example I showed above, the account node doesn’t show any sub nodes. But in fact it does have one in a real DDR document. Each account has a Description node like so :

<Account id="1" name="[Guest]"> <Description>Your account description is here.</Description> </Account>

Getting Text

Because the Description node is a node within the account node, it’s in a different context to the current “Account” context. So we need to move to that new node to get the data. And when we’re there, we need to get the entire value of any text inside that node. To do that we use a function :

text()

So our select statement for getting the description of an account is this :

<xsl:value-of select="Description/text()"/>

This says “move from the current context to the child Destination node, and get all of the text within that node.” The context being the Account, so it selects the Description node within the Account node.

The logic for paths works in much the same way as a path in any command line editor, and it follows much of the same convention. “.” is the current node, “..” is the previous ( parent ) node. We follow a path by going “path/to/destination/node/”.

Recap Again

So once you’ve found the starting point you want to work frrom, you find a set of nodes by doing a for-each loop for the nodes you need to export, and generate a ROW in the output data for each record. You then use @ and text() to get the real data, in conjunction with some different paths for accessing child or parent nodes.

There are lots more options for paths and the sorts of things you can use to locate information, if you need to know lots more, I’d suggest a book, I’ve found the O’Reilly ones to be generally good. Everything we need to get out of the DDR we can get with just those two options (“@” for getting attributes, and text() for getting whole nodes), and the knowledge of how paths work.

Recap

There are some concepts here worth touching on again. First, that the XSLT file is actually XML as well, so it follows the same conventions and has the same requirements that the XML files do.

Secondly, there are two distinct parts to the XSLT, regular nodes which get output exactly as they appear in the XSLT and commands, which follow the xsl:commandname syntax. The commands aren’t sent to the output file, they’re processed and the result, if any, is sent to the output.

The third and final thing that we use everywhere in processing the DDR is the for-each loop and various select attributes. The for-each loop allows us to work through lists of information one at a time, and the select specifies which information to get. In the case of the loop, the select tells us which node to process, and when used in a value-of node, it tells us which data to return.

So what other parts of the DDR can I get?

In short, all of it. If you take the example code above, or my example file attached to this page, you can modify it to grab whatever data you want. If you modify this code to work somewhere else, follow a few critical steps :

First, modify the METADATA node to have one FIELD node for each field you want to bring in. Feel free to move the attributes inside the node if you want, instead of having them as separate commands.
Second, make sure that for every FIELD node you have setup in the METADATA, that you have a matching COL node, which corresponds to the data you want to bring in. This is critical, and it’s easy to add an extra COL node and forget to add the FIELD, or to get the order wrong. Everything will probably still work if you get the order wrong, but your data will be in the wrong places.
Finally, add a for-each loop to process the list of nodes you want to get out. You can also have multiple for-each nodes, if you want to process data in multiple places at once. Each loop would generate the same output but for a different part of the DDR.

Pop Quiz

So, can you tell what each of these for-each nodes would process :

<xsl:for-each select="/FMPReport/File/ScriptCatalog/Script">

<xsl:for-each select="/FMPReport/File/LayoutCatalog/Layout">

<xsl:for-each select="/FMPReport/File/ValueListCatalog/ValueList">

Or what about this one that has a for-each inside another for-each :

<xsl:for-each select="/FMPReport/File/BaseTableCatalog/BaseTable"> <xsl:for-each select="FieldCatalog/Field"> </xsl:for-each> </xsl:for-each>

Is it all as straightforward as this? (Said with tongue firmly in cheek.)

Ahh.. no… But this does give you some idea of the way that XSLT works and how to get data out of the DDR. You could from these examples create some XSLT to generate a list of scripts, or layouts or fields. If you’re after some examples of more complex XSLT for getting at some of the details of the DDR, I suggest you download a copy of BaseElements. Included in your licence to BaseElements is a licence to use the XSLT DDR files that we’re using to import all of our data. These examples cover every element in a FileMaker solution and use a few tricks to grab all of the data from every nook and cranny of the DDR.

I’ve attached to this post two of the more simple XSLT files that BaseElements uses. Feel free to use either of these files as they are or as templates for other parts of the DDR or other XSLT projects in FMP.

Can you explain xyz in your XSLT files?

Sure, post a comment below, or contact me directly and I’ll do my best to answer whatever I can.

Haven’t you just given away the keys to BaseElements?

No, not quite. BaseElements has been in development for over 2 years, and 90% of the work involves working in FMP creating the interface and making sure all of the data is related to all of the other data. So just having access to the XSLT doesn’t mean you can create BaseElements any time soon.

Plus I think this is something that is helpful in other ways, not just related to BaseElements. I think there should be more open standards like this from FMI. I’d like to see them document the DDR properly for each new release, and also either endorse or generate their own set of XSLT files that you can use. This would make things better for everyone, not just Goya.

And if it means that others start to look at the same issues and come up with innovate ways to do things, then it helps everyone, not just Goya.

And we’ve got a lot of cool plans and ideas for BaseElements, so it’s not just about the XSLT. As they say, it’s not what you’ve got, but what you do with it that counts.

I hope you found this interesting, and if you have any more queries about XSLT or the DDR, feel free to contact us, or leave a comment on this page.