Innovative Data Solutions

Moving Data Within FileMaker using XML

This is a concept shown by Fabrice Nordmann at this years Pause on Error London.
Having seen how he uses this, I have used it in a couple of solutions and found that it does make moving data around really easy and saves on creating lots of variables, so cuts down development time as well.

The basics of this technique is to put the data you are moving into Tags so it becomes xml-ish. Then just reference the Tags when you want to use the data.

Fabrice has very kindly posted the custom functions he created at www.fmfunctions.com
(xmlSet, xmlGet and xmlRemove are the ones I have been using).

Here is an example of  how this process works:

Let’s say I have a sales system with a quotes module and an orders module and I want to move a quote to a sale.
The information I need to move from the quote is the customer id, the quote notes and all the product ids and quantities. So I would have one “set variable” script step:
Variable: $xml
Result: xmlSet( “QUOTE” ;
xmlSet( “CUSTOMER” ; Quote::Customer_id ) &
xmlSet( “NOTE” ; Quote::Notes ) &
xmlSet( “PRODUCTS” ;List( QuoteLines::Move_xml )
)

The Move_xml field in the Quote lines table would be:
xmlSet( “PRODUCT” ;
xmlSet( “ID” ; QuoteLine::Product_id ) &
xmlSet( “QTY” ; QuoteLine::Qty )
)

This would return a string that looks like this:

<QUOTE>

<CUSTOMER>123</CUSTOMER>

<NOTE>Hello world</NOTE>

<PRODUCT>

<ID>555</ID>

<QTY>2</QTY>

</PRODUCT>

<PRODUCT>

<ID>567</ID>

<QTY>5</QTY>

</PRODUCT>

</QUOTE>

This example has 2 products but the Product tag would repeat for as many as you have.

The next stage of our script would then create the Order record and do Set Field: Order::Customer_id to xmlGet( “CUSTOMER” ; $xml )
Then Set Field: Order::Notes to xmlGet( “NOTE” ; $xml )

To create the Order Lines goto the lines layout and the script would be:

Loop
New record
Set Field OrderLine::Product ID to xmlGet( “PRODUCT” ; $xml )
Set Field OrderLine::Qty to xmlGet( “QTY” ; $xml )
set Variable $xml to xmlRemove( 1 ; “PRODUCT” ; $xml )

// This removes the first product from the xml

Exit Loop if: isempty( xmlGet ( “PRODUCT” ; $xml ))
End Loop

This then Loops though creating line items until there are no more products.
We now have a finished Order with all our lines.

This is much easier than setting lots of variables as the order of the xml doesn’t matter. You can add extra Tags without breaking anything and having child records only requires a calculation to hold that data. The number of records doesn’t matter.

I found this process a lot easier to implement than to explain, as I only have to create one  variable with all my data and then I can extract this as required.

Simon Ward

Simon Ward

Simon has been developing FileMaker databases since the late 90s and joined Linear Blue in 2006. Over this time he has developed database systems for clients in many different industries from Order Processing to Book Publishing. Simon’s BSc in Chemistry from Thames Valley University comes to the fore in his analytical skills and he is certified in FileMaker versions 7 through 12.

More Posts - Website

One Response to Moving Data Within FileMaker using XML
  1. […] Read the whole thing. Moving Data Within FileMaker using XML […]

Leave a Reply

Wanting to leave an <em>phasis on your comment?

Trackback URL http://www.linearchat.co.uk/2011/11/moving-data-within-filemaker-using-xml/trackback/
Email
Print