XSL to XML with table relationship

[PBHolt]

I have an FM database with two tables and a relationship based on the OrderId field of both tables. Some OrderId's have mulitple pieces in their shipments, each with a tracking number from FedEx. When generating a default FMPXMLRESULT, the XML created lists the multiple shipments for each order. When trying to create an XSL to do the same and to match the customer's XML format, it only lists the first piece's tracking number and not the others. Is there something additional I need to add to the XSL in order for it to recognize the relationship? Attached are the FMPXMLRESULT and the customer's formatted XML. Any help would be greatly appreciated.

customer.txt

FMPXMLRESULT.txt

[Fenton]

This is possible. It would be easier if it was done from the line items instead of from the Order, because of the multiple DATA elements (as you found). But still doable. However I see another logic problem.

Your POD xml file has 1 order ID (within an OrderUpdate), and multiple shipping items (possible, though your example only has 1). The snag is that it also has only 1 Timestamp per OrderUpdate. But your FileMaker export has separate Timestamps for each item. Multiple timestamps for 1 OrderUpdate do not fit the POD xml structure, as far as I can see.

[P.S. You might want to send me a Private Topic message. Not to be private, but to let me know, in case this gets buried. I have 90% of it done, just need clarification of the above. Perhaps those multiple timestamps are yours? In which case you could just use the 1st.)

Edited March 2, 2006 by Guest
Reminder needed

[Fenton]

OK, PHolt told me that there is more than one possibility of how many timestamps and where they are. The below is the simplest. It just puts the 1st timestamp at the order level, i.e., not for each item. It would be just as possible to put each timestamp separately in each item instead. What would be more difficult would be to decide where to put it, on the fly, from the data FileMaker had.

<?xml version='1.0' encoding='utf-8'?>



exclude-result-prefixes="fmp" version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform">





	

		

			

			

					

					

						

									

					SHIPPED

					

					

						

					

					

								

					

						

						

     					

							

								

							

							FedEx

							

								

							

							

							

								

							

						  

											  

					

				

			

		

	

[Fenton]

This is much the same. But this one puts the timestamp into each item instead. It changes the order a little bit also. As you can see it doesn't take much to rearrange the xml result.

<?xml version='1.0' encoding='utf-8'?>



exclude-result-prefixes="fmp" version="1.0"

xmlns:xsl="http://www.w3.org/1999/XSL/Transform">





	

		

			

			

					

					

						

						

					SHIPPED

					

						

					

						

						

						

							

								

							

							FedEx

							

							

								

							

							

								

							

							

								

							

						  

										  

					

				

			

		

	

[PBHolt]

It took some time for all of this to sink into my thick-headed skull, but I have finally seen the problem.

I have had to add a table to the database due to the nature of how we were receiving files from the customer. It came when we tried to uniquely identify each address of the Order. We have since tried to create another table to uniquely identify each address of each order and then generate the XML from that.

It's not easy being me...

[Fenton]

That's kind of what I meant by "It would be easier if it was done from the line items instead of from the Order, because of the multiple DATA elements (as you found)".

XML/XSL is sometimes confusing, because it may look "flat," in the sense that the data is in a single text file; but it is not like other flat text formats, such as CSV. XML can (and usually does) contain fully "relational" data. You may not recognize it as such, because you don't see a linking ID in each section. But, because it is hierarchal, there is an implied relational connection between an element the elements enclosed below it.

FileMaker, on the other hand, separates related data into separate tables, with an explicit key for a tie. In the case we dealt with earlier in the thread, the FM data was exported from the parent table, with the related data included in multiple elements within the element. It was somewhat difficult to write the XSL, because the data is not "in order" that we're used to.

If the data is exported from (or imported into) the child table, we have the opposite problem. We need to include the parent data. But only once, above the child data. This is much like a FM report, where we bring the parent data into the header, as related data.

With XSL you would reach up the hierarchy to get the parent data. Since we only need it once, we can specify that we only want to process the 1st ROW for this data, ie., fmp:ROW[1]

Then specify the parent data fields. To process the child data we continue by processing every fmp:ROW, but specifying all the fields except the parent ones above.

[PBHolt]

I'm back...

I created a FMPXMLRESULT to show a multiple piece shipment to a single address (see attached). I then took that and created an XSL (see attached) to generate the customers XML. My XSL does not show the multiple pieces of the shipment like the FMPXMLRESULT shows, it only shows the first piece. I read your post about the differences between the parent and child data and could not get it to sink into my head, apparently a problem I still have.

FMPXMLRESULT.txt

myXSL.txt

[Fenton]

Well, the differences between exporting from the parent or the child are useful to know. But that isn't the problem here. Because this FM xml export is much the same as your earlier one; it is from the parent. You can tell by the multiple elements following each other, enclosed by the element. A child export will not have that. It will have the regular something structure. Either is OK to process.

Because you have "related" data, whether you export from the parent or the child table, you're going to have to do something to get both levels of data, and the correct number of records, with the data at the level it belongs.

That is what the extra processing is for in the xsl stylesheets I posted.

The multiple elements determine the number of nodes (don't know what to call them exactly). You must decide which element (field) of the child is sure to not be empty, so you can specify it as the criteria. In the file you just posted, there is only 1 at column 3. That would break the xsl I posted earlier. I could adjust; but it makes sense to me that the Tracking# would have a unique value in each child.

The way I dealt with the multiple elements was to set an xsl:variable to the position() and use that to grab the value from the next one down. What you want, in the "related" data, is the 1st in COL[2], then the 1st in COL[3], etc..

The data is exported to xml like this (from a FM perspective). All the values of the 1st related field are exported one after the other. Then all the values of the 2nd related field, etc.. In other words, the related data is exported "vertically", not "horizontally" like you see in a portal.

FileMaker parent with portal:

Parent1_Field

Child::Field1 tab Child::Field2

Child::Field1 tab Child::Field2

XML:

Parent_Field

Child::Field1

Child::Field1

Child::Field2

Child::Field2

(By "Field" above I mean the data in the field. The field names, which would correspond to the position of the

elements, are back up in the section.)

[PBHolt]

I tried both XSL's that you posted preivously and they only show the first piece as well. I ran a FMPDSORESULT and the XML from that showed a little more about the context of the ShippingID relationship. Is there anyway to see the XSL of either of the default FM XML's prior to creating the XML?

[PBHolt]

I found out why the XSL's you posted were not working. We have sinced added a "ShipmentNumber" field to the database so that it pulls that. I am going to see if I can change them around to collect the Shipment Number field and see what that does.

[Fenton]

1. There is no "XSL of the default FM XML," at least none that we can see. Just do a raw XML export, with no xsl to see what FM is putting out.

2. My XSL (as I said) depend on the 3rd

(ie., COL[3]) to have the multiple elements. That's the way it was in your original XML. Your latest did not have multiple data in that position; but I think it should. That may be why you only got 1 Shipment.

3. If you add a field to the export you have to adjust the XSL to match. This XSL is "hard-coded" to a specific FM export, fields and order. Remember, the elements names, for the final POD result are also being hard-coded into the XSL. So the order matters. If you change the FM export order, you'll still get data in the final xml, but it will be in the wrong place. It is not using the "names", it's using the order. You can adjust either the export order or the xsl to end up with the right result.

[PBHolt]

I did run into the COL's not matching up and was able to fix it so that they did match up. I did run another FMPXMLRESULT and I'm looking at the XML and I can't see any differences between that and a single shipment. Both FMPXMLRESULT's look the same. I guess I'm looking for something that just isn't there.

[PBHolt]

I think I know what's going on now. Changing some of the COL's has given me a glimpse into what you're talking about. In your XSL, you're generating Shipment Number by counting the number of tracking numbers for each shipment. I can remove that because we now have that Shipment Number in the database so now that XSL doens't have to calulate it. Can I just remove that from the XSL and replace it with the Shipment Number field?

[PBHolt]

The more I change your XSL's, I can see how the variable plays it's role. A point of clarification maybe long overdue on my part and that's with the orignal customer's XML that I sent you. The customer's XML did not take into account that a single address could have mulitple boxes in it so that one address could have multuple effectivedatetime's, trackingnumber's and costincurred's. That was something that we discovered in creating the XML and that the customer did not take into account. Hence the addition of some fields to tables and an additional table to parse out addresses to be treated as single records.

I may have failed in being clear about that and I apologize.

What I'm trying to do now is change your XSL to list the multiple effectivedatetime, trackingnumber and costincurred under one Shipment Number. The Shipment Number represents the number of the address, of which there can be up to ten. I think I'm close, but any help would be great.

[Fenton]

Yes, I used the positon() xsl function to get a ShippingNumber. Because you only gave me 1; I needed more, and I didn't have a FileMaker file to export from. So if you have one just get the data instead.

The xsl is custom built for particular export, in most cases. So unless you show us what you've got, we can only build an approximate xsl.

So a shipment record is for 1 address of 1 order to 1 customer? I have to assume from that there is one 1 item shipped to that address, or that we're combining those multiple items into the 1 shipment.

I think my 2nd xsl already does what you're asking for.

[PBHolt]

I GOT IT!!

I used your first XSL and changed it so that it would list multiple boxes of a single address.

"So a shipment record is for 1 address of 1 order to 1 customer?" YES, and I apologize again for not being clearer on that.

I now have something to show the customer and they should be satisfied.

I want to give you a big "THANK YOU" for all you're help. I have learned a tremendous amount and your patience was very appreciative.