Wiki

Signatures captured via a Versago form treated as a .png image. The image information is stored in the database as part of the form data record. To use the signature image the data must be converted in a way that can be used by Crystal Reports. Once this conversion is completed the resulting image can be used in a Crystal Report for presentation.

There are two basic mechanisms for doing the conversion. One is to create an actual image file that is saved do disk. Another is to do the conversion as part of the Crystal Report execution.

This page describes the various steps used to accomplish these methods. The information provided assumes that the user has a working knowledge of Versago, Bizweaver, and Crystal Reports.

To begin you should determine a locations where the signature images will be saved. This can be anywhere that will be accessible by Crystal Reports.

Bizweaver is used to convert the database information back to a .png format image file. A basic workflow is illustrated below.

The workflow assumes that you will want to create the signature image as soon as the Versago record is saved, so it will be called from the Versago form save using the Bizweaver web service. The call passes the Versago record ID to Bizweaver as a variable.

A SQLCommand(Reader) step reads the associated database record and parses out the data needed to create the image file.

SELECT
t0.vgoRecNum, SUBSTRING(t0.signature,23,9999999) AS "SignatureData"
FROM <YourTable> t0
WHERE t0.vgoRecNum = WorkFlow(VARIABLES).Get(vgoRecNum)

<YourTable> is the table used by the source form “vgoRecNum” is the record id (primary key) of the form record. “signature” is the column name that holds the signature data.

Use the table, primary key and signature column name values for your environment.

A FileWriter step converts the “signature” data string to a .png image and saves the file in a designated folder. The file name must be something that can be parsed by Crystal Reports in a way to tie the image back to the source record. In this example it is done by using the record id followed by “_Signature.png”. In this example, the file created for record 10 would be 10_Signature.png.

C:\<Your File Path>\WorkFlow(SQLCommand_getData).Get({FIELD_vgoRecNum})_Signature.png

The data source for FileWriter is WorkFlow(SQLCommand_getData).Get({FIELD_SignatureData}) and the output method is Binary Output. This is illustrated in the following image.

The Crystal Report is configured so that it can use the image file created in the Bizweaver process.

The first step is to create a formula to serve as a pointer to the image file. The example below shows how this is done.

“C:\Program Files (x86)\TWBS\Versago\VersagoApp\SiteImages\Signatures\”+cStr({Form_Features_Hdr.vgoRecNum},0)+“_signature.png”

• “C:\Program Files (x86)\TWBS\Versago\VersagoApp\SiteImages\Signatures\” is the path to the folder where the signature images are located.
• +cStr({Form_Features_Hdr.vgoRecNum},0)“ converts the record ID used in the report to a string value.
• “_signature.png” completes the name of the image file.
• The three elements are concatenated together with the ”+“ to create a single string.

An image file placeholder is added to the report in the desired location. This must point to an existing file in the source folder. Ideally this will be a “no image available” image so that something is displayed if the appropriate file cannot be found.

• Open the “Format Graphic” function for the placeholder image.
• Move to the “Picture” tab.
• Click the “Graphic Location” format formula editor.
• Add the formula created earlier.
• Save the formula and the associated information.

The approach to use the signature data as part of the Crystal Report execution requires either a SQL View or the use of a Crystal Report “Command.” Either method will use a variation of the following SQL query:

CREATE VIEW vgo_vw_Form_Features_Hdr AS
SELECT
b.*, CAST('' AS XML).value('xs:base64Binary(sql:column("a.TrimSignature"))', 'VARBINARY(MAX)') AS 'ConvertedSignature'
FROM
(
SELECT t0.vgoRecNum, SUBSTRING(t0.Signature,23,99999) AS 'TrimSignature' 
FROM
<YourTable> t0
) a
INNER JOIN <YourTable> b ON a.vgoRecNum = b.vgoRecNum

The “create view …” line is not used in a Crystal Reports Command.

As with the Bizweaver example the following values are specific to your environment.

• <YourTable> is the table used by the source form
• “vgoRecNum” is the record id (primary key) of the form record.
• “signature” is the column name that holds the signature data.