The AgWare ClickFORMS software currently offers very limited control over incoming data. This is important when adding this data to an SQL Server database in regards to field formats, allowable data, and required data. Several compromises were made for the structure of the data tables to account for the incoming data.
The real data fields in the appraisal tables are all nullable. The only two items currently required are a unique Appraisal Number (using the “File #” field), and the original file name.
The sizes for the fields have been selected as an upper bound. In almost all cases they are bigger than need for typical data entry. ClickFORMS does not limit the length of data for any cells. So ever for fields such as zip code more than 5 characters are allowed for in the database.
Fields that are cast to specific types are included in two fields. One is prefixed with an “s” to indicate a string version of the field. This contains the unconverted string value. This will be useful when the initial batch of data is updated to see if we need to add conversion routines. For example in the cost approach the sample appraisal data AgWare uses for testing contains a number formatted like ‘500,000 ®”. This fails with the default SQL string to money conversions. It is also possible in ClickFORMS to include units after the size on improvements. There is not separate field for units on the subject. This can cause the same problems as the data is entered like “10,000 bu”.
Comment Cell Markup
Comments from the input report are included in the XML. At this time we are not exporting any of those comments into individual tables. ClickFORMS allows for some formatting on these fields. Individual words can be made bold, italic, or underlined. This formatting is stripped before the text is added to the XML that is stored on the server. Graphics and Word Processor Pages Graphics and Word Processor pages are not included in the XML data. We felt there were no situations where this data would be useful to any reporting processes. The data is included in the report’s PDF file but they are not included as individual data elements.
Two PDF files are included in the XML and corresponding database tables. One is the “Main” PDF file. The “Other” file includes all of the pages that were not initially selected for inclusion in the main PDF.
The files are stored as Base64 encoded text in varchar(max) fields. This matches how attachments are stored for the DataLog. If needed for easier external integration we can change these to blob fields.
Currently the encoded data is included in the XML file during the initial post process. The XML is passed as one parameter to the post stored procedure. If we need to add the ClickFORMS native file format we may want to consider changing this. There is no opportunity to show the user an upload progress bar because everything is being sent in one call. The ClickFORMS clk files will be larger than the PDF files we are currently sending and a progress bar may be necessary.
The command time out for the database connection has been set to 2 minutes. Files that are two large to upload to the server in that time will not be posted.