The PXF File as the Software Equivalent of Plug and Play – A Database Editing Use Case

A few weeks ago we introduced you to the Portable XML Form (PXF), a file format in which all design elements supporting a StyleVision design including XML Schema and instance documents, SPS design files, XSLT, images, and other external files are embedded. End users simply open the PXF file in Authentic and can immediately start editing XML and database data. In this post we’ll create an electronic form for business users to record donations and enter new donors for a fictitious charity called the Green Planet Fund.

Altova’s new PXF file format is a boon to developers and end users alike –the PXF file can be transported, downloaded, copied, and saved like any other office document, increasing both efficiency and error-free deployments. Developers no longer have to send or install multiple files or create a server application to support a design and business users can distribute critical business information quickly and reliably. This is especially true in distributed organizations where business data needs to be sent around via e-mail between different locations or between field employees and their counterparts in the home office. A PXF that supports electronic forms designed in StyleVision and edited by business users in Authentic makes editing business data a snap. In most cases that business data will be contained directly within the PXF file as an embedded XML document, but it is also possible to connect a PXF file to a database server and store the data directly in the database, as the following use case shows.

For this example we’ll be using StyleVision to create an electronic form on which business users will record donations and enter new donors for the fictitious Green Planet Fund. End users interact with these forms in the Authentic WYSIWYG editor, a sophisticated word processor-style interface that allows end users to capture, view, and update XML and database content. Once you click File, New from DB in StyleVision, the Connection Wizard is invoked. Simply select the database type – you will be prompted to browse for the source. Here we’ve identified the DonorsDatabase from the MARKETING006SQLEXPRESS server. The connection string is automatically saved with the design file and re-established each time a business user accesses the form in Authentic. The database is updated with the information that the business user enters in the Authentic form. Once you establish a connection, you can select tables, data views and even SQL SELECT statements in the Insert Database Objects dialog box. For this example we’ve added a SQL SELECT statement (below) that concatenates donor title, first name, and last name and calculates the total amount contributed by each donor. Once you click Add SELECT Statement the SQL SELECT statement becomes available to the StyleVision design, as seen above. Please note that we used Altova DatabaseSpy, a multi-database query, design, and database comparison tool, to build our SQL statement. Although you can export data from the SQL Editor in DatabaseSpy into a range of formats, for this example we simply copied the SELECT statement from DatabaseSpy into the window in the SQL SELECT dialog box in StyleVision. StyleVision generates a temporary XML Schema based on the structure of the database and displays it in the Schema Window. Note that both the Contributions and Donors tables as well as our SQL SELECT statement appear in the Schema Tree. During this transformation StyleVision creates internal XML files – a non-editable one for previews and as the source of the generated XML data file as well as an editable XML file that will write modifications back to the database. Now that we’ve established and saved the database connection string we’re ready to design the electronic form that Green Planet Fund will use to add new donors and log contributions. For this example we’ve created two simple tables in StyleVision – one for recording contributions and another for looking up and adding donors. (Please note that our example is for illustrative purposes only and we’ve shown only a small sample of the fields that we would typically include in a real-world application. The functionality too is far less sophisticated than it would be in an actual project.) We’ve done the following to our design: · Created a design fragment with a table for entering new donors and a table of registered donors that is populated dynamically · Added a checkbox that when toggled will show the registered donors table and the data entry table from the design fragment · Edited the Authentic properties of the registered donors table to retrieve all records · Edited the Authentic properties of the data entry table to retrieve only one record to make it easier to enter data · Used XPath to concatenate each donor’s title, first, and last names for the table of registered donors, using the normalize-space function to remove all white spaces · Used Value Formatting (below) to format the contribution date – this also allows the end user to edit the date via a calendar interface in Authentic Note the design fragment ‘DonorList’ is enclosed within the DB tags and is represented as a single element. The design fragment itself appears at the bottom of the design. Clicking the tabs next to the Design tab will render the report in different formats. Here we’ve presented the Authentic view. Notice that the design is still in standard SPS format (greenplanetdonorsII.sps) – we are now ready to save it as a PXF so that it can be emailed to the client and deployed immediately upon receipt. Once you select File – Save As from the top menu, you can choose whether to save the design as a SPS or PXF. When you Save as PXF File you are prompted to select the files to embed in the PXF file. All files supporting the StyleVision design are automatically made available in the dialog box. Here we’ve checked each of the design elements as well as the XSLT files. This will allow end users to generate output from the StyleVision-designed form in each of these formats directly from Authentic. Please note that you also have the option to include additional files. Although this design does not require other files, this feature makes transporting and deploying projects that require multiple files significantly easier. Now you can send the PXF file that you have just created to the client. All project files as well as the database connection string are embedded in the PXF so that once the end user opens it in Authentic he can immediately start viewing, editing, and entering data. To create a new record in Authentic, place the cursor in one of the fields in the contributions table on the form and click the Append row button on the tool bar. (Alternatively you can select Authentic – Append Row from the top menu.) Now the business user can enter contributions (new data is in bold) … …and new donors. Note that in the SQL Server database the Donor ID is an auto-generated field. Once the end user clicks File – Save from the top menu, the new information is posted to the database, the database generates an ID number, and the new information populates the other tables in the form. (As reflected above, data is populated in real time.) Edits and additions are immediately written to the database. The SQL Server database view below shows that Edward and Julie Jay, whom we have just added to the Authentic form, have been assigned a donor id of 18. Although we did not include it here, the form can be designed to generate an error message when the user attempts to save a new donor without at least one first and last name. This can be accomplished using XPath in the additional validation property of the Authentic properties or by creating a constraint in the SQL Server database. This use case was designed to show how easy it is to create and deploy interactive forms. Electronic forms designed in StyleVision have always been a great way to update both XML and database content – the PXF file simply makes it easier to transport and deploy them.

Have you used the PXF form yet? Or created a really cool database project using StyleVision or any of Altova’s other tools? Please share your story with other Altova users by commenting on this blog post. Think it would make a great case study? Email us at marketing@altova.com – if we use your story you’ll receive a $200 Amazon gift card. We’d love to hear from you!