PBHolt Posted March 1, 2006 Posted March 1, 2006 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 Posted March 2, 2006 Posted March 2, 2006 (edited) 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 Posted March 2, 2006 Posted March 2, 2006 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 Posted March 2, 2006 Posted March 2, 2006 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 Posted March 7, 2006 Author Posted March 7, 2006 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 Posted March 7, 2006 Posted March 7, 2006 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 Posted March 9, 2006 Author Posted March 9, 2006 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 Posted March 10, 2006 Posted March 10, 2006 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 Posted March 10, 2006 Author Posted March 10, 2006 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 Posted March 10, 2006 Author Posted March 10, 2006 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 Posted March 10, 2006 Posted March 10, 2006 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 Posted March 10, 2006 Author Posted March 10, 2006 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 Posted March 10, 2006 Author Posted March 10, 2006 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 Posted March 10, 2006 Author Posted March 10, 2006 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 Posted March 10, 2006 Posted March 10, 2006 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 Posted March 10, 2006 Author Posted March 10, 2006 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.
Recommended Posts
This topic is 6851 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now