Sunday, September 17, 2017

Interface for importing XML files into Priority

Today, I learnt how to create an interface which imports XML files into Priority. Although there is some mention of XML files in the documentation, as usual this is so opaque as to be useless. But I found an existing interface and the file which it reads (conversion rates for foreign currencies), and it took about five minutes of study to figure out how the interface is designed.

Here's an XML file which I successfully imported:
(Order) (CustName)8100(/CustName) (Ref)PO170851(/Ref) (Reqdate)13/10/17(/Reqdate) (OrderItems) (OrderItem) (PartName)11335384BLKOF250S(/PartName) (Quant)2(/Quant) (UnitPrice)900(/UnitPrice) (Discount)50.0(/Discount) (DueDate)12/10/17(/DueDate) (/OrderItem) (OrderItem) (PartName)42103(/PartName) (Quant)1(/Quant) (UnitPrice)0.0(/UnitPrice) (Discount)50.0(/Discount) (DueDate)12/10/17(/DueDate) (/OrderItem) (/OrderItems) (/Order) ... round brackets should be replaced by angle brackets
The interface is designed, as usual, in the Form Load Designer (path: System Management > Database Interface > Form Load (EDI) > Form Load Designer). There is no load table, so the field 'file name' should contain the path to the xml file. Most importantly, the field 'File Type' must be X. Once the header has been defined, it is paramount to run the procedure 'Prepare XML tags by File Defs'; as far as I can see, this procedure loads the file and builds a small database of possible tags. This 'database' will be accessed in a later step.

Then, like a regular interface, one defines the screens and fields into which data is to be inserted. Using the above example, data will be added into the ORDERS screen (customer orders), fields custname, reference and tec_prdate (the field names don't have to match the tags), and into the ORDERITEMS screen, fields partname, quantity, unit price, discount and due date. The catch - for which we need the 'tags database' - is that the correct tag has to be entered into the sub-form 'Definition of XML tags': for example, the custname field is read via the tag Order/CustName, and the part number ordered is read via the tag Order/OrderItems/OrderItem/PartName.

All of this implies that the XML file has to exist before the interface can be created. It's not clear to me at the minute how an XML file could be created by the interface if none exists in advance; presumably the program uses the XML tags to create a file, but these tags don't exist until they are read by the 'Prepare XML tags'. Hmmmm. Fortunately, I only need to read XML files, not create them.

I have been sent an XML file, parts of which look like this:

(Preferences)
(document aliasset="" pdmweid="52034") (configuration name="Default") (attribute name="Part Number" value="CLM-054-080000"/) (attribute name="Reference Count" value="2.0"/) (/configuration) (/document)
I get the feeling that I won't be able to read this file.

1 comment:

Yitzchok said...

Noam, you have it all pretty much spot on.

From my experiences with XML file interfaces it seems to me that behind the scenes the XML is translated into a tab-separated file which is then loaded using the TSV logic.

You didn't mention filling in the field order field (next to the from-column and to-column fields in the field definition screen) but I have clearly in my head that that's something which is quite important to do.

You write, "All of this implies that the XML file has to exist before the interface can be created. It's not clear to me at the minute how an XML file could be created by the interface if none exists in advance; presumably the program uses the XML tags to create a file, but these tags don't exist until they are read by the 'Prepare XML tags'."
A file has to exist in advance. The only way to define the tags is to create an example file (by hand, if need be; that's what I've usually done); this has the advantage that you can't actually get tags into the list of tags unless you first built a syntactically-valid XML file.

As far as your other XML file goes - I think that indeed you may have trouble with it. On one hand I have a dim recollection that there may be some way to read from attributes (by which I mean the data within a tag such as 'value="CLM-054-080000"' in your example) rather than element values (the data between tags) but I suspect that I'm confusing Priority with something else.

I can tell you for sure that Priority can choke on valid XML files, such as those involving namespaces; I have had to preprocess those to eliminate that sort of content. It could be that you'll have to write some external program to read in the file you mentioned and translate the attribute values to element values. Powershell might be a useful language for that, actually; XSLT is supposedly the way to do it but not terribly easy to get to grips with.