Friday, 24 January 2014

Listing all fields in a form

Because the XDP file that defines a Designer form is just a XML file we can run an XSLT over it.

In this case we will produce a list of all the field objects that we can view in Microsoft Excel,  so we will also use Excel to run the XSLT.

First download the XSLT file, XSPFieldList.xslt , and place it in the same directory as the XDP file of your form.

Edit your form XDP file using Notepad or similar and add the following line after the XML declaration;

<?xml-stylesheet type="text/xsl" href="XDPFieldList.xslt"?>

So it should look something like; There is a line in the XSPFieldList.xslt that looks like;

This might need to change to match the namespace in your XDP file (see the last line in the screen shot above)  depending on which version of Reader your form is targeting. The namespace ending in  xfa-template/2.8 means my form is targeting Reader 9.0 and above. For Reader 9.1 and above it would be xfa-template/3.0.

Now in Microsoft Excel open the XDP file (Excel doesn't know about XDP files so your will have to select "All Files (*.*)" in the Open dialog).  The first thing Excel will do is popup an Import XML... dialog; This means it has found our xml-stylesheet line and wants to confirm we want to run the XSLT, so select "Open the file with the following stylesheet applied (select one):"

You will then get a "different format" message, which is fair enough as the XSLT will have changed the format so select Yes; Then you get an Open XML dialog, which defaults to "As an XML table" which is what we want to do, so select OK. The final dialog is the no schema message.  We haven't provided a schema so Excel will create one for us.  This dialog you can turn off. The final result will look something like; There is a lot you can add to the XSLT so think of this as a starting point, and remember to remove the xml-stylesheet line from the XDP as Designer will think the form is created in a new version and not allow you to do much except look at the XML Source.

Response Files

This approach can also be helpful in working with a response file from the collection of returned forms.  If you export the responses as XML then they can be processed by an XSLT and loaded into Excel in the same way.

If your XSLT produces an HTML document containing a HTML table then Excel will still load it into a spreadsheet, but you now can specify business names for the columns not just the field names from the form, so you can now have spaces in the column names.

The XSLT will also allow you to do some decoding, such as turning 1 and 0 into 'Yes' and 'No' or whatever formatting you want.

Radzmar has written a macro to make the first couple of steps easier, so you no longer need to make a temporary change to your XDP file and the macro works out the correct namespace.   More details in a later blog Listing all fields in a form - The macro.
[null, null, {"document_url": "http://adobelivecycledesignercookbookbybr001.blogspot.com/2014/01/listing-all-fields-in-form.html", "unformatted_src": "http://3.bp.blogspot.com/-aUd3gor199E/UuF5nX39yDI/AAAAAAAAAH8/zD3ZDuMC7RM/s1600/xml-stylesheet.PNG", "src": "http://3.bp.blogspot.com/-aUd3gor199E/UuF5nX39yDI/AAAAAAAAAH8/zD3ZDuMC7RM/s1600/xml-stylesheet.PNG", "formatted_filename": "xml stylesheet", "rendered_width": 640, "rendered_height": 58}, null, {"document_url": "http://adobelivecycledesignercookbookbybr001.blogspot.com/2014/01/listing-all-fields-in-form.html", "unformatted_src": "http://4.bp.blogspot.com/-cRK3rfrqRmo/UuF_k_Ha8RI/AAAAAAAAAIM/hJ3Z_-faLpo/s1600/ImportXML.PNG", "src": "http://4.bp.blogspot.com/-cRK3rfrqRmo/UuF_k_Ha8RI/AAAAAAAAAIM/hJ3Z_-faLpo/s1600/ImportXML.PNG", "formatted_filename": "ImportXML", "rendered_width": 400, "rendered_height": 160}, null, {"document_url": "http://adobelivecycledesignercookbookbybr001.blogspot.com/2014/01/listing-all-fields-in-form.html", "unformatted_src": "http://4.bp.blogspot.com/-uPEeIb7i8ps/UuGBCu0Mr4I/AAAAAAAAAIU/_SV0QEv0PdE/s1600/ExcelImport.PNG", "src": "http://4.bp.blogspot.com/-uPEeIb7i8ps/UuGBCu0Mr4I/AAAAAAAAAIU/_SV0QEv0PdE/s1600/ExcelImport.PNG", "formatted_filename": "ExcelImport", "rendered_width": 640, "rendered_height": 80}, null, {"document_url": "http://adobelivecycledesignercookbookbybr001.blogspot.com/2014/01/listing-all-fields-in-form.html", "unformatted_src": "http://2.bp.blogspot.com/-P_4iN13TIWY/UuGBgmvDTAI/AAAAAAAAAIc/qQ65i5n3GCM/s1600/OpenXML.PNG", "src": "http://2.bp.blogspot.com/-P_4iN13TIWY/UuGBgmvDTAI/AAAAAAAAAIc/qQ65i5n3GCM/s1600/OpenXML.PNG", "formatted_filename": "OpenXML"}, null, {"document_url": "http://adobelivecycledesignercookbookbybr001.blogspot.com/2014/01/listing-all-fields-in-form.html", "unformatted_src": "http://3.bp.blogspot.com/-jjun-P8Zp3w/UuLqMXe8-_I/AAAAAAAAAI0/92_aZDO1b9k/s1600/NoSchema.JPG", "src": "http://3.bp.blogspot.com/-jjun-P8Zp3w/UuLqMXe8-_I/AAAAAAAAAI0/92_aZDO1b9k/s1600/NoSchema.JPG", "formatted_filename": "NoSchema", "rendered_width": 320, "rendered_height": 99}, null, {"document_url": "http://adobelivecycledesignercookbookbybr001.blogspot.com/2014/01/listing-all-fields-in-form.html", "unformatted_src": "http://1.bp.blogspot.com/-oJXEsC1V6b8/UuGCj-FRhPI/AAAAAAAAAIk/P2BXBD0H-1I/s1600/ImportResult.PNG", "src": "http://1.bp.blogspot.com/-oJXEsC1V6b8/UuGCj-FRhPI/AAAAAAAAAIk/P2BXBD0H-1I/s1600/ImportResult.PNG", "formatted_filename": "ImportResult", "rendered_width": 640, "rendered_height": 240}, null, null, null, null, null, null, null, null, null, null, null, null, null]