Data Integration Module (DIM) User Guide

Overview

This guide is designed as a reference to provide step-by-step instructions for all menu options in Field2Base Data Integration Module. This guide will be continuously revised as program updates occur. Release Notes are also provided with notification of the program updates.

 

---

Article Sections

DIM Features and Definitions

Using the DIM Project Manager

Using the DIM Administrator

Troubleshooting

Appendices

• Using Conditions in Projects
• Outputting “Per Job” CSV Files in Projects
• Common Database Connection Strings
• Creating SQL Updates Data Transformations
• Limiting Job Downloads in the Data Integration Module Service
• Limiting Download Batch Sizes in the Data Integration Module Service
• Data Integration Module Filenames and Folder Structure
• Additional Project Options Not Available in the Project Manager

 

---

Installation of the Data Integration Module

System Requirements

Installation Components

Setup Walkthrough

 

System Requirements

Compatible Operating Systems:

• Microsoft Windows XP Professional and Tablet PC Editions
• Microsoft Windows Vista Home Premium, Business, and Ultimate Editions
• Microsoft Windows 7 Home Premium, Professional, and Ultimate Editions
• Microsoft Windows Server 2003 Enterprise Edition (and Newer Versions)

Hard Drive / Disk Space:

• A Minimum of 1GB should be held in reserve for initial use with Data Integration Module. Additional Disk Space may be required depending on the number of Tablets your Company has, the amount of Traffic processed, and your Company’s Data Archiving Requirements of processed Forms.

The Data Integration Module has no other requirements and is a lightweight service intended to efficiently process Form Data.

---

DIM Features and Definitions

Basic Features

• Automatically Downloads Form Data from the Field2Base Server
• Provides Transformation of Form Data into a variety of Formats to a Backend Server including CSV and SQL
• Provides an easy to learn Utility for creating Projects to transform your Form Data
• Provides a comprehensive Utility for managing the Data Integration Module Service and other administrative functions.

 

Advanced Features

• Provides an Interface to use Conditionals with Form Data Transformations
• Has the Ability to process Multiple Projects of Form Data at the same time
• Allows the use of Executables and Custom Code to transform Form Data

 

Project Manager Utility

The Data Integration Module Project Manager is the Utility used to create and edit Projects for processing Form Data based on Field2Base eForms.

See Using the DIM Project Manager for further details. 

 

Administrator Utility

The Data Integration Module Administrator is the Utility used to manage the Jobs of processing Form Data and the Data Integration Module Service itself as well as providing easy access to Logs for troubleshooting.

See Using the DIM Administrator for further detail.

 

Definition of a Project

In the Data Integration Module, a Project is the entity that contains the base information from a Field2Base eForm file and the details of how you want Form Data transformed. The Data Integration Module service will use a properly setup Project and process Form Data into the appropriate format for use with a Company’s backend server. 

The basic steps for creating a Project in the Data Integration Module are:

1. The Data Integration Module Project Manager is opened
2. The Field2Base eForm is loaded into the Project Manager Forms folder for use
3. A New Project is created from the eForm file loaded
4. Basic Project Properties like Field2Base Service Access and Initial Transform Type are setup
5. Form Region Transformations and Additional Transform Types are setup
6. The Project is then Saved and Enabled for use with the Data Integration Module

 

back to Top

 

Definition of a Job

In the Data Integration Module, a Job is the actual Form Data that has been downloaded from the Field2Base server. It is considered a Job until the Form Data has been transformed into the setup format defined in the Project. You can view and manage your Jobs queue with the Administrator Utility. The requirements for creating a job are:

• The Data Integration Module is installed on the Company’s backend server
• Access to the Field2Base Service has been granted
• The Company’s backend server has access to the external Internet and can connect to the Field2Base service
• A Project has been setup and is enabled on the Company’s backend server
• Forms in Field2Base have been sent and processed by the server (No Data on the Field2Base server means no Jobs will be created)

The steps for creating a job are:

1. The Data Integration Module will contact the Field2Base Server asking if any Forms have been processed for your Company.
2. The Field2Base Server will respond and package Form Data up into Jobs for the Data Integration Module to download.
3. The Data Integration Module will download each Job and place it in the queue.

 

The Data Transformation Process

1. The first Job in queue will be opened.
2. Based on the settings of the Project for the Job, the Data Transformation will begin by doing the following:
   • For CSV Transformations, a new or existing CSV file will be opened
   • For SQL Transformations, a connection to the SQL Server will be opened
   • For Custom Code and Executable Transformations, the respective command will be run with open access to the Job’s data
3. At this point, CSV and SQL Transformations will continue with the rest of the process described while Custom Code and Executable Transformations will process the data in their own fashion
4. For each Region in the Form, the service will take the data in the Job and follow the transformation instructions in the Project and match it up to the proper column in the CSV file or SQL database
5. Once all Regions have been processed, the Project will be checked to see if multiple Transformation types; if there are multiple Transformation types, each Region will be processed again using the new mappings
6. Once all Transformations have been completed, the connection or file is closed
7. Custom Code and Executable Transformations meet back up at this point with CSV and SQL Transforms assuming they were completed successfully
8. The currently running Job is then closed and moved to a Processed folder
9. The next Job in the queue will then be processed

Note	In this version of the Data Integration Module, only one Job in queue can be actively transforming Data.

 

back to Top

---

Using the DIM Project Manager

The Data Integration Module Project Manager is the Utility created to easily create an Integration Project from a Field2Base eForm for use with the processing service to send data to a Company’s back-end system.

Project Manager Interface

Loading eForm Files

Creating a New Project

Mapping Values in a Project

Auto-Backup of Projects

Enabling a Project

Testing a Project

 

Project Manager Interface 

The File Menu involves the Basic File and Application functions of the Project Manager.

• New - allows you to create New Projects from Forms loaded into the Project Manager. 

• Open - Allows you to open an existing Project.

• Save - Allows you to save a Project. There are no options to “Save As” because there can only be a single Project per loaded Form in the current system and the Project file generation is internal to the Utility.

• Edit Login Info - Allows you to change the Server Login Info for a Project. This option is only available for use with existing Projects.

• Close - Allows you to close any existing Project without exiting the application.

• Load Forms - Allows you to load Forms for use within the Project Manager.

• Exit - Closes any existing Project and exits the Project Manager.

A. Page Selection and Zoom Options

The Page Selection interface allows you to either go through the Form page by page using the arrow buttons or you can use the Drop Down to skip directly to the page of your choosing. The Zoom Options allows you to zoom in or out on the Form currently viewed. 

B. Form Viewing Window

The Form Viewing Window allows you to actively work with the Form loaded for this Project. This window gives you the ability to select Regions when mapping Regions to Columns.

 C. Drag and Drop Mapping Button

The Drag and Drop Targeting button is located at the top center of the Project Manager. This is where Regions are dragged and dropped to when mapping Regions to Columns. Enabled means that a Column has been properly selected and Regions can be mapped. Disabled means that no mapping can be done while in this state. 

            

D. Mapping Window

The Mapping Window is found in the center of the Project Manager screen right below the Drag and Drop Mapping Button. This is where all of the actual Mapping of Data is recorded and viewed. Additionally, this window is where a Condition can be set on whether or not to output the results of a Data Transformation.

• Current Mapping

  The Current Mapping section displays what the mapping value (Region Page and Name) is for the selected column. In addition to Regions, text can be added the Current Mapping section with or without Region values in it. An example use of this would be that a Column in the Output did not have a related Region in the Form and text needed to be there for import into a Company’s Backend system.

  It is important to note that any text entered like this is interpreted as a single string with any Region values mapped.

• Mapped Values

  The Mapped Values section displays all of the columns for the Data Transformation Output and the values assigned to each. These values are set by using the Drag and Drop Mapping System. 

• Condition Section

  The Condition section is where a Condition can be set on whether or not a Data Transformation should be outputted. If no conditions are needed, then nothing should be entered here. See the appendix "Using Conditions in Projects" for details on the valid formatting to create a Condition. 

E. Data Transformations Window

The Data Transformations Window consists of the main window that displays the setup Data Transformations for the current Project and the various buttons used in conjunction with Data Transformations.

There are four different types of Data Transformations available for use with the Data Integration Module. They are CSV, SQL, Custom Code, and Executable represented in the Buttons on the upper right hand part of the Project Manager as CSV, SQL, Custom, and Exec.

1. CSV

   A CSV Data Transformation Type will result in creating an outputted CSV file on Job data processed by the Data Integration Module. The required elements for a CSV Data Transformation Type are:

   • Output Path – The Folder Location where the CSV Output file will be placed after being processed by Data Integration Module
   • Output Filename – The Filename that the CSV Output file will have
   • Number of Columns – The Number of Columns the CSV Output file will have for mapping Regions to
   • Column Headers – If Column Headers are Enabled, each line entered here represents a Column in the CSV Output file

   When Column Headers are Enabled, the Number of Columns is not required and any value entered there previously is disregarded. The standard method for Output with CSV Data Transformations is to output to a single file and append additional data to that single file; however, the Data Integration Module does allow Projects to be setup to output individual CSV Output files.

   See the appendix "Outputting Per Job CSV Files in Projects" for details on how to Output individual CSV Files for every Job Processed.

2. SQL

   A SQL Data Transformation Type will result in performing a SQL Insert into a Company’s Backend Data from the Job data processed by the Data Integration Module. The required elements for a SQL Data Transformation Type are:

   • Database Connection String – The standard Connection String to connect to the SQL Database
   • Selected Table – The Table Selected from the Database that will provide the Columns for Mapping

   The Database Connection String is derived from the type of SQL Database used on a Company’s Backend Server. See the appendix "Common Database Connection Strings" for the Common Database Connection Strings Field2Base has encountered as well as a link to a helpful site that helps Users discover their SQL Database’s Connection String.

   The standard type of SQL Data Transformation performs an Insert into the SQL Database. The ability to run an Update on Rows already inserted into the SQL Database is available, but requires editing of the Project file outside of the Project Manager. See the appendix "Creating SQL Updates Data Transformations" for details on how to add a SQL Update to a Project.

3. Custom Code

   A Custom Code Data Transformation Type will run externally written code on the Job data processed by the Data Integration Module. The required elements for a Custom Code Data Transformation Type are:

   • Custom Code Location – The Filename and Folder path of the Custom Code that will be run on the Jobs processed
   • Selected Class – This the Class that will be used in the Custom Code that contains the proper Interfaces from the Field2Base Data Transformation SDK for use with the Data Integration Module

4. Executable

   An Executable Data Transformation Type will run an external Executable on the Job data processed by the Data Integration Module. The required elements for a Executable Data Transformation Type are:

   • Exec Location – The Filename and Folder Path of the Executable that will be run on the Jobs processed

   The remaining elements are all optional.

Note	It is NOT recommended to try and use the Custom Code or Executable Data Transformation Types without first contacting Field2Base since these types require external development.

The Project Manager displays all of the current Data Transformations set up in the Project in the window below the Data Transformation Type Buttons. Selecting the Data Transformations or its Child Objects (i.e. CSV Row or SQL Insert) will allow the User to do various functions that are available by using the Data Transformation Editing Buttons.

There are four basic Editing functions available for use with Data Transformations in the Project Manager.  These Editing buttons can be used in varying degree with the Data Transformations or their Child Objects.

• Function: Creates a Blank Row (for CSV) or Insert (for SQL) on the Selected Data Transformation

• Edit - can be used with CSV and Executable Data Transformations

  Function: Re-opens the initial Data Transformation Setup window

  Note	SQL and Custom Code do not have this Option since editing this Type of Transformation is the same as deleting the existing and creating a new one.

• Copy - can be used with Rows (CSV) and Inserts (SQL) [Child Objects Only]

  Function: Makes a Copy of the Selected Row or Insert and adds it to the end of the Parent Data Transformation

• Delete - can be used with ALL Data Transformations and Child Objects

F. Details Window

This is the area where details of the Data Transformation are displayed for review. This not an editable box; it is essentially a way to display the expected output for the Transformation you have selected. You do have the option to copy text from this area and paste it elsewhere.

 

back to Top

Loading eForm Files

The first step to creating a project using the DIM Project Manager is loading the eForm that you need to export the data from. The Project Manager Utility uses the template information residing in the eForm and translates that information for the Data Integration Module Service to use for processing Jobs.

There are two methods by which to load these Forms. The first method is to load them in with the Load Forms option in the File Menu of the Project Manager. A File Browser window will appear and allow you to browse to the folder of your choosing and select the eForm file(s) you want loaded.

 

The second option is to manually copy the eForm file(s) into the Forms folder found in the Program Files folder for the Project Manager Utility. The location of this folder is:  My Computer > Local (C:) Drive (or other designated Drive Letter for your PC) > Program Files (x86) or Program Files folder > F2B Data Integration Module folder > Forms folder.

 

Creating a New Project 

1. Open the F2B Data Integration Module Project Manager. The Project Manager will open with No Project Loaded.
2. Go to the File menu and select New.
   
3. The Open Form screen will appear. Select the .eform for this Project and click Open. These are the eForms that have been loaded into your DIM Project Manager (see Loading eForm Files for information on how to load an eForm).
   
4. Log in with your company's RFI Service Client login.
   
5. The Initial Transform Type Selection screen will open. Select the appropriate Transformation type and click OK.
   
6. Set the path and filename for your output file. Specify either the number of columns needed in your output file (with no headers) or enable column headers by checking that option. If you enable column headers, you need to list the headers as shown below.
   

 

back to Top

  

Mapping Values in a Project

Note

Before starting to map values in a Project, you need to plan which Regions should match up with which Columns in the Output. It is highly recommended that the User of the Data Integration Module works with the Company’s back-end Server Administrator to establish these Mappings prior to starting a Project in the DIM.

To map a region value to a column:

1. Click on the Row in the Data Transformation Window to display the column names in the Mapped Values.
2. Select a Column in the Mapped Values window.
3. Select the appropriate Region and drag it to the Drag and Drop Regions Here button.
4. You will see the expected output in the Details window (at the bottom of the Project Manager) for your review.

You can manually enter the following types of values into the Current Mapping Entry Box. 

• Text (i.e. “Form sent by ”, “Form sent on “, etc.)
• Regions (i.e. {Page1@Region1}, {Page1@Region2}, etc.)
• Internal Values

Any of the values you enter in the Current Mapping will NOT be processed; they are taken as is in a single string and sent to the output as is. This means that you cannot add two Regions together or change the format of a Region when Mapping. These types of actions need to be done either before the Form is sent to F2B (in Forms Designer) or after the Job is processed (by your Backend Server / Custom Code / Executable). Multiple Regions can be mapped to the same Column and when this is done, they will be interpreted with no spaces in between them so adding spaces or additional text may be needed when this type of Mapping is done.

 

Auto-Backup of Projects

The Data Integration Module Project Manager will automatically backup each version of a Project to a folder in the Field2Base Program Files folder (C:\Program Files\F2B Data Integration Module\Backup). This backup will occur whenever the Project is saved in the DIM Project Manager. If you edit outside of the Project Manager, backup snapshots will not be taken.

 

The backed up Project will have a filename based on the Date and Timestamp. It will have the following format:

     [Form Name].[Date of Backup].[Time of Backup].xml

To restore a backed up Project:

1. Go to C:\Program Files\F2B Data Integration Module\Backup.
2. Copy the Project (XML file) you want to restore.
3. Go to C:\Program Files\F2B Data Integration Module\Jobs.
4. Paste the Project (XML file) to this folder.
5. At this point, you can delete it the existing Project if you want to avoid conflicts. 

 

back to Top

 

Enabling a Project

Assuming all Mappings are complete on a Project, to enable a project, click on the Project Disabled button.

Once Clicked, the button will show as "Project Enabled".

To Disable an Enabled Project, click the Project Enabled button. This button toggles to disable/enable the Project.

Once a Project is enabled, the default setting for the Data Integration Module Service is to download and process the Form’s entire history (all sent Forms) from the Field2Base server. There is a way to limit the amount of Jobs (Sent Form Data) downloaded by the service based on a Date and Timestamp, but it requires an update directly the XML file (outside of the Project Manager). For more details on how to limit the downloading of Jobs from the Field2Base server, see the appendix Limiting Job Downloads in the Data Integration Module Service.

back to Top

Testing a Project

The Data Integration Module was created to be a flexible piece of software that works in various back-end scenarios. We cannot provide a straightforward set of Testing Steps due to the fact every Company’s testing scenario will be different. With that being said, we can detail several scenarios that need to be considered before you Enable a Project and start Testing.

Testing Environment Scenarios

1. All Testing with the Data Integration Module will be done in a Non-Live Testing Backend Environment.
2. All Testing with the Data Integration Module will be done in a live Backend Environment AND all Test Data can be Deleted (or can be discarded).
3. All Testing with the Data Integration Module will be done in a live Backend Environment BUT all Test Data can NOT be Deleted.

If the first two Scenarios match up with a Company’s Testing Environment, then no special steps need to be taken for Testing. However, if a Company matches Scenario 3, then it is recommended that a system to identify Test Data be established before Testing begins. An Example of this would be to use a Test Project Code for all Forms sent in while Testing.

Company History Scenarios

1. The Company has previously Sent Data for this Form that can be used for Testing with the Data Integration Module.
2. The Company does NOT have previously Sent Data for this Form that can be used for Testing with the Data Integration Module.

The first Company History Scenario means that historical Data can be used to test if there are no conflicts with the Testing Environment Scenarios. However, the first scenario can also result in actually having too much Test Data. If that is the case, it is recommended that the Job Downloads are limited. See the appendix Limiting Job Downloads in the Data Integration Module Service on how to limit the Job Downloads in the Data Integration Module Service.

As for the second Company History Scenario, it just means that the Form needs to be put into use in Field2Base and tests need to be sent through before Testing can truly begin.

There are definitely more unique or strange circumstances involving Testing out there, but these Scenarios should cover the generic situations that could plague Companies with issues.

Form Re-ID Usage when Testing

If a Company does not feel comfortable with testing the Project with a live Form, a copy of the Form can be created and renamed for testing. This requires access to Forms Designer and adds an additional temporary test Form to the Company’s resources, but it does mean that test data can be easily identified and discarded. Essentially, the Form will be re-saved with a new unique Form Template ID to separate it from the original.

To create a copy of a Form for testing:

1. Open the eForm in Forms Designer
2. Go to the File Menu and select the Save As > New Template option.
   
3. Save the copied form to your local PC.
4. Go to the Form tab and select the Properties button and add “TEST” to the Form Name entry box
5. Copy the Form ID from the box below the Form Name. You will need to use this template ID when setting up your test form in the DIM.
   
6. Save and publish your test form. You should publish your test form to a Test project (or Sandbox project) for your company. For details on how to create a new project, see the Projects section in Admin Portal Quick Start Guide.
7. You can now close Forms Designer.
8. Copy the new Test eForm to the C:\Program Files\F2B Data Integration Module\Forms folder.
9. Open the C:\Program Files\F2B Data Integration Module\Jobs folder.
10. Make a copy of the original DIM Project named after the eForm.
11. Rename this copied Project to match the Test form name.
12. Open this Project in a text editor.
13. Paste the new form’s ID (from Step 8) into the <formTemplateId></formTemplateId> tag, replacing the previous form's ID.
14. Make sure that the <enabled> tag is set to True (<enabled>True</enabled>).
15. Save the test DIM Project.
16. Restart the Data Integration Module Service in the Administrator to begin processing.

Keep in mind, since this is a new Form Template ID, Tablet Users will have to submit completed forms for it to starting processing data with the Data Integration Module.

Note	Remember to delete the Test Form once testing is complete to avoid confusion in the future. If your Company plans on making updates to the Project, it is a good idea to leave the Test Form and Test Project active for easy testing in the future.

 

Testing Task List

1. Test the Server Login Info to confirm there are no “Invalid Login” exceptions in the Logs Viewer of the DIM Administrator Utility.
2. Test the Job Downloads from the Field2Base server to confirm there are Jobs in the Queue Monitor in the DIM Administrator Utility.
3. Test the Jobs processed by the DIM to confirm the Jobs have been completely processed by using the Processed Job Status Filter of the Queue Monitor in the Administrator Utility.
4. Test the processed results to confirm data is being outputted. For CSV, this means you need to check the Output folder defined, and for SQL, Executable, and Custom Code, this means you need to check the Database or System where the data was sent.

If issues do occur at any of these stages, please refer to Troubleshooting the Data Integration Module.

 

back to Top

---

Using the DIM Administrator

The Data Integration Module Administrator is the Utility created to easily manage the various aspects of the processing Form Data. The Administrator Utility is the main method to see how enabled Projects are currently processing with the Data Integration Module Service. 

DIM Administrator Interface

Using Filters in the Administrator Utility

Managing the Data Integration Module Service

Ongoing File System Maintenance

 

DIM Administrator Interface

The Queue Monitor allows the User to monitor the current Job queue of the Data Integration Module and provides filters based on Form (Project), Date, and Job Status.

 

The Log Viewer gives the User an easy method to view and filter the Data Integration Module’s Logs. Logs can be filtered by Form (Project), Date, and Job Status.

 

The Service Manager provides the User with the basic functions of Starting, Stopping, and Restarting the Data Integration Module Service as well as the ability to see the current status of the service.

 

The Administrator has the ability to refresh the contents of each section by using the Refresh button located to the right of Service Manager Button. Clicking the Refresh button will make the Utility refresh the Queue Data, Log Data, or Service Status with the most recent information while still retaining the filters you have selected previously.

 

back to Top

 

Using Filters in the Administrator Utility

One distinct advantage of using the Administrator Utility is the ability to use the various filters created in the Queue Monitor and Logs Viewer sections. Project Filters allow you to select individual Projects or allow you to view the Data for All Projects. Date Filters allow you to quickly set Start and End Dates or a Date Range for the Data that is current being viewed. Job Status Filters allow you to view specific states Projects are in or allow you to view the Data regardless of the Job Status.

 

Managing the Data Integration Module Service

The Service Manager section of the Administrator Utility provides a basic interface to stop, start, and restart the Data Integration Module Service. The goal of this interface is to provide quick interactions with basic service functionality when troubleshooting instead of having to work with the Window Service Control Manager (SCM).

If you ever need to directly access the service in Windows SCM, the name of the Module’s service is “F2B Data Integration Service”.

back to Top

 

Ongoing File System Maintenance

The Data Integration Module Service does not actively archive or delete any of the Logs, Jobs, or Output files associated with the Data Integration Module. If storage is an issue on the PC running the application, it is recommended that a process is setup to regularly perform maintenance on these files.

 

back to Top

---

Troubleshooting

The Data Integration Module has three main areas where issues may arise: 

Troubleshooting Installation Issues

Troubleshooting Connectivity Issues

Troubleshooting Processing Issues

 

Troubleshooting Installation Issues

User Account Privileges

If you encounter any Permission related issues when installing the Data Integration Module, you will need to check and make sure that you have Administrator rights so that the Program AND Service can be installed for use on the PC. If you do not have Administrator Rights for this PC, it is recommended that you speak with your Company IT Administrator to obtain these permissions.

.NET Framework Compatibility

As indicated in the Setup Walkthrough section, .NET Framework 2.0 or newer is required for the Data integration Module to be setup on your PC.

It has been confirmed that .NET 4.5 does work correctly with the Data Integration Module, but if you need to work in a newer version, please contact F2BSupport@field2base.com to see if there are any Compatibility issues.

Tablet PC SDK Requirement for the Project Manager

If you are using a PC that is not running a Windows Vista or Windows 7 Operating System, make sure that you have the Tablet PC SDK installed. Otherwise, you will receive errors when trying to open Forms in the Project Manager utility.

 

back to Top

 

Troubleshooting Connectivity Issues

Network Issue

If you are getting any type of Web Exceptions when running the Data Integration Module Service, the first step is to always confirm that you can connect to the Field2Base Server’s download service.

You can do this by opening up your Internet Browser and going to the Web Administration Portal for your Company since it requires the same server and port to access and download Form Data.

If you cannot access the F2B Web Admin portal, it is recommended that you confirm you have a working Internet connection by browsing to another public site like www.field2base.com.

If browsing other web sites does work, proceed to the next issue in this section to see if the cause of the issue can be identified.

Firewall Issue

If you see a message like the one shown below, it means that the Data Integration Module Service cannot reach the Field2Base server because there is most likely a Firewall block in place on the computer’s network on Port 2001.

At this point, you will need to speak with your Company’s Internet Administrator and request that the Port 2001 for the Field2Base Server IP be opened.

The Field2Base Server IP Addresses are as follows:

• Customers: 216.27.21.2
• Resellers/Demo: 64.204.27.44

Local Account Issue

If you see a message like the one shown below, it means that the Data Integration Module Service cannot reach the Field2Base server because the server address, salesdemo.field2base.com for Resellers/Demo or fieldconnect.field2base.com for Customers, could not be resolved.

This usually indicates the local account the Data Integration Module Service is using does not have access to the external Internet.

To confirm that this is the issue, it is recommended that you open the Data Integration Module Service in a Command window using Console Mode. This will create a new set of logs to review.

You can run the Data Integration Module Service in Console Mode by doing the following:

1. Open the Data Integration Module Administrator
2. Go to the Service Manager
3. Click Stop
4. Confirm the Service Status is stopped
5. Go to the Start Menu
6. Select Run
7. Type in “cmd”
8. Press Enter
9. Type in the following:

   ”C:\Program Files\F2B Data Integration Module\F2BDataIntegrationService.exe” –cl

10. Press Enter
11. You will now see a Command window with the message:

    “Running F2BDataIntegationService in Console Mode. Press enter to stop”

12. Let the Service run for about a minute
13. Press Enter to Stop the Service

If you are running the service in the Command window and still seeing the same issues, it is recommended that you contact F2BSupport@field2base.com and have your Log files ready for review.

If the service is now running correctly with no Exceptions, you will need to update the F2B Data Integration Module Service with an account with Internet Access in the Windows Service Control Manager.

To update the service in this fashion, please do the following:

1. Go to the Start Menu
2. Open the Control Panel
3. Switch to Classic View of the Control Panel (if not already in that view)
4. Open Administrative Tools
5. Open Services
6. Select the F2B Data Integration Service
7. Right Click on the Service
8. Select Properties
9. Go to the Log On tab
10. Click on This Account
11. Enter your Account and Password
12. Click OK

You are now setup to run the F2B Data Integration Module Service through an account with Internet Access.

Invalid Login Issue

The most common issue that can occur is that one of the values entered for your Server Login Info is incorrect.

If you see a message like the one shown below, it means that the Data Integration Module Service had an issue when using the Project’s Login Credentials when attempting to connect to the Field2Base Server.

This usually indicates the Company ID, Username, or Password were not entered correctly and need to be changed.

If you are seeing this error, then you will need to re-enter your Server Login Info. This can be done by doing the following:

1. Open the Data Integration Module Project Manager
2. Go to the File Menu
3. Select Open
4. Open the Project processing in the Data Integration Module Service
5. Go to the File Menu
6. Select the Edit Login Info option
7. Re-Enter all of your Login Credentials
8. Click OK
9. Save the Project
10. Close the Project Manager
11. Open the Data Integration Module Administrator
12. Go to the Service Manager
13. Click the Restart button
14. Confirm the Service gets Restarted

You should now check the Logs again and confirm you do not see the Invalid Login Exception. If you still see this issue, please contact F2BSupport@field2base.com to get your Login Credentials re-sent to you.

You Do Not Have Rights to Use the Service Issue

If you see a message like the one shown below, it means that the Data Integration Module Service cannot connect to the Field2Base Server because your Account does not have the downloading capability of the Data Integration Module Service.

If you encounter this issue, please contact F2BSupport@field2base.com.

Generic Server Error Issue

If you see a message like the one shown below, it means that the server has received too many requests from Data Integration Module services for that Company ID. This occurs when multiple machines are running the Data Integration Module (i.e. a live PC and a test PC) at the same time. Normally, this will only delay the download of Forms by a few minutes and can often be ignored unless downloads are not occurring at all.

The easiest way to resolve this is to only have one machine running Data Integration Module on a regular basis.

Forms Not Downloading in a Timely Manner Issue

This issue usually should only occur if the Data Integration Module services are not running automatically or service has been disrupted. Essentially, if the Data Integration Service is not running for a long length of time and Forms are being sent by the Company’s Tablets and Mobile Devices, there could be a long delay in processing Form Data due to the amount of downloading required.

The reason for this issue is twofold:

1. The default configuration for the Data Integration Service is to download every pending Job (set of Form Data) to the PC before processing will start.
2. The Field2Base Server has a limit on how many Job downloads can occur per each Data Integration Module installation.

These factors combine to form the possibility of the Job downloads taking hours to download all of the data before processing will begin.

If this is an issue that occurs with you, Field2Base does have a change available for the Data Integration Service configuration that will limit the number of Jobs to download before processing can start.

If you have interest in adding this configuration to your Data Integration Module, please contact F2BSupport@field2base.com.

 

back to Top

 

Troubleshooting Processing Issues

CSV Output Issue

Check the file system to see if the Output File or Folder has been moved, deleted, or is inaccessible.

SQL Connection Issue

Check to see if the Database can still be connected to in the Project Manager by making a new SQL Data Transform as a test.

SQL Input String Format Issue

If you see the message below, it means that the format of the Data in the Form does not match the format that is expected for that column in the Database.

The easiest way to fix this is to go into Forms Designer and update the Form’s Region to make sure it matches the expected format in the Database. For more complex situations, scripting within the Form can be used to help handle format issues. Remember to re-publish the Form to the server after making a change.

The other option is to change the Database’s format for this column, but in most scenarios this would not be the preferred solution since the format used in the Database was chosen for a reason (i.e. for Reporting or Analytical purposes).

Custom

Check the file system to see if the Custom Code file has been moved or deleted.

Exec

Check the file system to see if the Executable File has been moved or deleted.

 

back to Top

---

APPENDICES

Using Conditions in Projects

Outputting “Per Job” CSV Files in Projects

Common Database Connection Strings

Creating SQL Updates Data Transformations

Limiting Job Downloads in the Data Integration Module Service

Limiting Download Batch Sizes in the Data Integration Module Service

Data Integration Module Filenames and Folder Structure

Additional Project Options Not Available in the Project Manager

back to Top

 

Using Conditions in Projects

The Data Integration Module Project Manager allows the User to create Conditions on CSV and SQL Data Transformations that will decide whether or not to Output the processed data of a Job. These Conditions can be setup on each Row (CSV) or Insert (SQL). The following sections will provide an overview of using Conditions in the Project Manager Utility.

The Conditions in Data Transformations are evaluated like basic expressions in any Programming language. These expressions require a series of values be evaluated using operators and comparisons to result in a Boolean (True/False) response.

The values used in Condition Expressions use the following types: Numeric Literals, String Literals, and Value References.

• Numeric Literals are Numeric Values and use only numbers and punctuation associated with numeric values (i.e. period (.), negative symbol (-), etc.).
• String Literals are combinations of Alphanumeric Values and use single quotes ‘’ to signify this type.
• Value References are Region Names, Row Names, Header Names, or Internal Variables, and all use braces {} to signify this type.

Here are examples of these types.

• Number Literals: 3.14, -5
• String Literals: 'blah', ‘User1’
• Value References: {Page1@Date}, {Row1}

Region Names, Row Names, and Header Names return a String if defined or Null if not defined.

Internal Variable return whatever Data Type the variable has associated with it.

To evaluate the Literals, Operators are needed to find a True or False result.

Here is a list of the Operators available for use:

Comparison Operators

• Less Than: <
• Greater Than: >
• Less Than or Equal To: <=
• Greater Than or Equal To: >=
• Equal To: ==
• Not Equal To: !=
• Logical Operators
• AND: && (Logical And)
• OR: || (Logical Or)

The Precedence (Highest Precedence first) for Expression Operators are: All Comparison Operators, Logical AND, and then Logical OR.

When Comparison Operators are used, String Values are always converted to Numeric Values. The idea here is to easily support Numeric Comparisons.

Here is an example of what is meant by this:

{Page1@Total} < 10

{Page1@Total} returns a String Value, and since the Less Than Operator has been used, whatever value (i.e. ‘20’) is converted to properly work in the Expression.

With that being said, the following examples all result to True:

• 0 == '0'
• 0 == 0.00
• 0 == '0.00'
• '0' == '0.00'

It is important to note that Expressions also support Parentheses for more advanced Comparison. Here is an example of a valid Expression using Parentheses:

({Page1@Total} – 5) < 10

For the final evaluation of a Condition in a Project, the final value of the Expression is interpreted as a Boolean. Only the Values False, Null, and the Empty String evaluate to the Boolean False. All other Values evaluate to Boolean True, including a result of zero.

If you have any questions about using Conditions, please contact F2BSupport@field2base.com.

back to Top

 

Outputting “Per Job” CSV Files in Projects

The Field2Base Data Integration Module Project Manager allows you to output a new CSV file with every Job (Form) processed by using one of the Internal Values in the Output filename.

The following Internal Values are available:

• InternalFormId - the unique incrementing number assigned to Forms as they are downloaded
• FormProcessTime - the timestamp (which uses the Local Timezone) when the Form began processing

Using these times of Internal Values guarantees a unique CSV Output filename for every Job processed since only one Job is “in process” at any one time. Here is a walkthrough of creating a New CSV Data Transform with this setting:

1. Click on the CSV Data Transform button
2. Enter the Output Path
3. Enter the Filename and include {InternalFormId} or {FormProcessTime} in the Filename
4. Enter the Number of Columns or Column Headers
5. Click OK
6. CSV Data Transform with a new File per Job Complete

Note	Regions Values can be used (i.e. {Page1@Region1), but are unlikely to guarantee a unique output file every time (unless uniqueness is guaranteed elsewhere in your system).

Here is a sample screenshot of what the CSV Data Transformation should look like after running through these steps with FormProcessTime used:

If you have any questions about using Outputting CSV “Per Job” Files, please contact F2BSupport@field2base.comt.

back to Top

 

Common Database Connection Strings

The Field2Base Data Integration Module uses the standard Connection Strings any other Application would require to connect to your Database.

Here is a list of a few common Database Connection Strings (all using Standard Security):

SQL Server 2000, 7.0 OLE DB

Provider=sqloledb;Data Source=myServerAddress;Initial Catalog=myDataBase;User Id=myUsername;Password=myPassword;

SQL Server Compact Edition OLE DB

Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=myPath\myData.sdf;

Access

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydatabase.mdb;User Id=admin;Password=;

Access 2007

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Persist Security Info=False;

Note	You would need to replace any of the “my” variables listed above with your information.

Also, it should be noted that the OLE DB is the recommended method to connect to the Database with the Data Integration Module. However, that does not mean that some ODBC connections could work; it is just not supported or tested with the product.

If you are working with a Database not listed here, please look up your string at ConnectionStrings.com:

http://www.connectionstrings.com/default.aspx

If you need further explanation on what a Connection String is, ConnectionStrings.com has an article that outlines this topic: http://www.connectionstrings.com/default.aspx?article=what_is_a_connection_string

If you have any questions about using Database Connection Strings, please contact F2BSupport@field2base.com.

back to Top

 

Creating SQL Updates Data Transformations

In the Data Integration Module Project Manager, the standard SQL Data Transformation used is an Insert SQL Transaction. Field2Base realizes that some Companies may develop the need to update existing information in their SQL Database and thus we support a SQL Update SQL Transaction which is known as our SQL Update Data Transformation. Due to the limited audience this will affect, the first version of the Project Manager does not have an Interface to create a SQL Update and requires the editing of the Project (.XML) file directly with a Text Editor.

When Project is updated with a SQL Update Data Transformation, an entire block of code is added to execute a SQL statement. If the Project already has a SQL Insert Data Transformation created from the Project Manager, the SQL Update code can be just added to that block. If not, the SQL Connection Information needs to be added as well. With that being said, if a User adds a SQL Update Data Transformation to a Project that has no pre-existing SQL in it, then it is recommended to create a new SQL Insert Data Transform in the Project Manager first to verify the Database connection. When the Project file is edited, the original Insert Statement can be removed.

The Block shown below is a basic SQL Update including SQL Connection Information.

<sql provider="OleDb" connectionString="provider=sqloledb; data source=(local); initial catalog=Test; user id=demo; password=test" transaction="true">

<execCommand>

<commandText>update f2b.bar set delta=? where gamma=3</commandText>

<parameter>{Page1@BatchId}</parameter>

</execCommand>

</sql>

The <sql> block is the SQL Connection Information section. The <execCommand> block is the SQL Update text. The <parameter> block is the actual value of the Parameter from the Project’s Form. The ‘?’ represents each Parameter in the block and are evaluated in order. Parameters do have the option to use a “name” attribute for use in the Update command, but most Transactions will not use this format. Please contact F2BSupport@field2base.com if you have a need to use this.

SQL Updates can use Conditions as well, and it only requires the “condition” attribute to be set. In the sample below, the SQL Update will only run if a value is entered in the “BatchID” on Page 1 of the Job’s Form Data.

<execCommand condition="{Page1@BatchId}">

<commandText>update f2b.bar set delta=? where gamma=3</commandText>

<parameter>{Page1@BatchId}</parameter>

</execCommand>

The steps below will walkthrough how to setup a SQL Update Data Transformation in a Project with NO existing SQL Transactions.

1. Open My Computer
2. Open the Local Drive the F2B Data Integration Module is installed on (i.e. C: drive)
3. Open the Program Files folder
4. Open the F2B Data Integration Module folder
5. Open the Jobs folder
6. Open the Project File (the [FormName].xml file you want to edit) with a Text Editor (like Notepad or Wordpad)
7. In the file, find the <rfiSvc> block (below this block is where the SQL Update will be added)
8. Add the following block to the file (using the same block from above):

<sql provider="OleDb" connectionString="provider=sqloledb; data source=(local); initial catalog=Test; user id=demo; password=test" transaction="true">

<execCommand>

<commandText>update f2b.bar set delta=? where gamma=3</commandText>

<parameter>{Page1@BatchId}</parameter>

</execCommand>

</sql>

1. Save the Project File
2. Exit the Text Editor
3. Open the Data Integration Module Administrator
4. Start (or Restart) the Service to now Process Jobs with the SQL Update in place

When a SQL Update is processed in a Job, additional entries will be shown in the Logs. However, the Logs only show that the SQL statement was executed; it does not show if the Transaction was successful. Here is a sample of what a SQL Update would look like in the Logs:

09/16 11:35:41.92 AM (tid:16156) Info       : Executing sql command

09/16 11:35:42.06 AM (tid:16156) Info       : Command affected 1 row(s)

Finally, there is one component of the SQL Connection Information block that has not been discussed, and that is the “transaction” option. This is an option that can only be changed directly in the Project file and has a True or False value. This “transaction” option was created to allow the SQL Transactions in the Project be linked in a single Transaction block that will roll back if any errors occur. The default value is set to True to link all Transactions into a single block.

Here is a review of what the “transaction” values equate to:

• False: If there are processing errors in SQL Statements after a block has been executed, any transaction set to False is NOT rolled back in the database.
• True: If there are processing errors in SQL Statements after a block has been executed, any transaction set to True IS rolled back in the database.

Also, if there are any processing errors for the Job prior to the SQL Statement being run, no transaction will even occur. 

If you have any questions about using SQL Update Data Transformations, please contact F2BSupport@field2base.com.

back to Top

 

Limiting Job Downloads in the Data Integration Module Service

The Data Integration Module Service defaults to downloading the entire sent history for the Form defined in the Project. For a variety of reasons, it was decided that a way to filter or limit the amount of Jobs that will be downloaded at the Project Level. Since this is not expected to be a common feature amongst our Customer base, this feature is only available by editing the Project (.XML) file directly with a Text Editor.

Within a Text Editor, the Job filtering is actually done by adding a single line to the Project file that limits the Data Integration Module Service to only start downloading Jobs after the Date Entered. This tag that needs to be added is the “minRfiTimestamp” and requires that a Date & Timestamp (must be a valid UTC format) be entered as the value.

The steps below will walkthrough how to edit a Project and add a date filter to limit the Jobs Downloaded. Be sure to not have the Project file open in the Project Manager when doing this update.

1. Open My Computer
2. Open the Local Drive the F2B Data Integration Module is installed on (i.e. C: drive)
3. Open the Program Files folder
4. Open the F2B Data Integration Module folder
5. Open the Jobs folder
6. Open the Project File (the [FormName].xml file you want to edit) with a Text Editor (like Notepad or Wordpad)
7. In the file, find the <rfiSvc> block (this is where the new tag will be added)
8. Below the <formTemplateId> tag, add the following line (10/1/2008 was used here):
       <minRfiTimestamp>10/01/2008</minRfiTimestamp>
9. Save the Project File
10. Exit the Text Editor
11. Open the Data Integration Module Administrator
12. Start (or Restart) the Service to now Download with the Job Limitation in place

This example shows a Date & Timestamp with only a Date defined. The reason this was done is because there really is very little need to define the time when using this Filter unless you are looking to do something specific for Testing purposes.

One final note is that when this type of filtering is in place, there are no changes to the Logs or in any other display for Troubleshooting. The only way to tell this is working is by checking the output after the Jobs have been processed.

If you have any questions about limiting Job Downloads in the Data Integration Module Service, please contact F2BSupport@field2base.com.

back to Top

 

Limiting Download Batch Sizes in the Data Integration Module Service

The Data Integration Module Service defaults to downloading all possible Jobs in a single batch for the Form defined in the Project. For a variety of reasons, it was decided that a way to set the Download Batch Size at the Project Level. The default size is to download every Form ever sent by the Company. As with limiting Job Downloads by Date found in Limiting Job Downloads in the Data Integration Module Service, this feature is only available by editing the Project (.XML) file directly with a Text Editor.

Within a Text Editor, limiting the Download Batch Size is done by adding a single line to the Project file which tells the Data Integration Module Service to only download the number of Jobs specified in this line. The tag used for this configuration option is “maxDownloadBatch” and requires an Integer be entered as the value.

The steps below will walkthrough how to edit a Project with this type of update. Be sure to not have the Project file open in the Project Manager when doing this update.

1. Open My Computer
2. Open the Local Drive the F2B Data Integration Module is installed on (i.e. C: drive)
3. Open the Program Files folder
4. Open the F2B Data Integration Module folder
5. Open the Jobs folder
6. Open the Project File (the [FormName].xml file you want to edit) with a Text Editor (like Notepad or Wordpad)
7. In the file, find the <enabled> flag
8. Below the <enabled> tag, add the following line to limit each to 10 downloads per batch:
         <maxDownloadBatch>10</maxDownloadBatch>
9. Save the Project File
10. Exit the Text Editor
11. Open the Data Integration Module Administrator
12. Start (or Restart) the Service to now Download with the New Batch Size in place

Keep in mind, when this type of filtering is in place, the service should now download and process batches of 10 (or the number defined) Forms rather than downloading every Form job to download before processing. This behavior should be readily confirmed by looking in the Logs of the Administrator Utility.

One final note is that this Project adjustment can be used in conjunction with the MinRfiTimestamp configuration option (found in Limiting Job Downloads in the Data Integration Module Service) to help quickly process or re-process Form Data.

If you have any questions about limiting Download Batch Sizes in the Data Integration Module Service, please contact F2BSupport@field2base.com.

back to Top

 

Data Integration Module Filenames and Folder Structure

The Field2Base Data Integration Module interacts with your local Computer system and this was created to highlight the actual executables that may cause security alerts as well as outline the folder structure of the application.

Application Filenames

In case your PC has any security restrictions in place regarding Executables, here is a list of the Filenames of the Field2Base Data Integration Module Executables that should be granted access.

• Data Integration Module Service: F2BDataIntegrationService.exe
• Project Manager Utility: ProjectManager.exe
• Administrator Utility: ServiceAdmin.exe

All of these files are located in the C:\Program Files\F2B Data Integration Module folder.

Folder Structure

Below is a breakdown of the folder structure of the Data Integration Module.

• C:\Program Files\F2B Data Integration Module
• Backup
• FormData
• Forms
• Help
• Jobs
• Logs
• SavedData

Base Directory: C:\Program Files\F2B Data Integration Module

This is the base directory of the application where all of the “guts” of the application are. This is also where the database for the Data Integration Module resides.

Folder: Backup

The Backup folder contains the auto-saved copies of the Project files. This is the folder where a previous version of a Project can be retrieved.

Folder: FormData

The FormData folder is the location where Form Data is initially download. The Data Integration Service will create subfolders based on the Form ID and save the XML and Images there.

Folder: Forms

The Forms folder is where the Field2Base Forms are located for use with the Project Manager.

Folder: Help

The Help folder contains the documentation (i.e. User Guide) for the Data Integration Module.

Folder: Jobs

The Jobs folder is where the Project Files that are used with the Date Integration Service are located. These files are saved in a XML format.

Folder: Logs

The Logs folder is where the log files from all Data Integration Module Services and Utilities get saved.

Folder: SavedData

The SavedData directory is essentially a Temp folder. It is a generic working area for various Data Integration Components. It is used to save data that needs to be preserved when the service is not running.

Changing the Base Directory Configuration

If you install the Data Integration Module application anywhere other than the C:\Program Files\F2B Data Integration Module folder on your PC, then you will need to change this path during the installation process. The application should automatically update all references to the folder you chose at that point.

 

back to Top

 

Additional Project Options Not Available in the Project Manager

The Field2Base Data Integration Module does have some Project features that can be added by directly editing the Project XML file with a Text Editor. Most of these features are advanced or not often used options and were not included in the User Interface for that very reason.

Updating a Mapping with an Undocumented Project Options

Before the various types of options are listed, it is best to outline the process for updating a Mapping with one of these undocumented options.

First, the Data Integration Module Project Manager should be closed (or at least not have the Project you want to edit open) before starting the update.

Next, the Project file you want to open is found in the following folder:

C:\Program Files\F2B Data Integration Module\Jobs

At this point, it is recommended you manually make a backup of the Project file in another location before proceeding.

Now, open the XML file for the Project using Notepad (or a similar Text Editor).

At this point, you will need to familiarize yourself with the structure of the XML Project file. Most of it can be ignored as these Project Options are added to the Column tags. Find the Mapping you want to update by manually scanning the file or doing a search on “<column name=X>” where X is the name of the Database Column that will be in the Mapping.

Remember to open and properly close any tags you add or edit to the Project file.

Here is an example of updating a Mapping for a Column called “TodaysDate”. Instead of referencing the Date shown on Date region on Page 1 of the Form, the Project will use the Date when the Form is being processed by the Data Integration Module.

            Original Mapping

<column name=”TodaysDate”>{Page1@Date}</column>

            New Mapping Using the “Now” Attribute

<column name=”TodaysDate”><now /></column>

Backup Reminder

Please remember that since you are editing outside of the Project Manager, there are no automatic backups generated so it is highly recommended that you back up the Project file before making edits in this fashion.

Undocumented Project Options List

Listed below are the tags for Project Options not available for use in the Project Manager.

Function Name

now

Description

Returns the current Timestamp.

Attributes

            -utc

This is an optional attribute (Defaults to False) to return the Timestamp with Coordinated Universal Time (UTC) instead of the local time zone.

            Returns

            Returns a DateTime.

Examples

- Get the current Timestamp.

<now />

- Get the UTC version of the current Timestamp.

<now utc=”true” />

Function Name

header

Description

Gets an item from the Metadata Header of the Form being currently processed.

Listed below are Metadata options you can use with this function.

FileVersion

This is the eForm format used to create this XML file.

FormDataId

This is the unique ID assigned to the Form when it was sent.

FormRevisionNumber

This is the revision number for the eForm template.

FormTemplateId

This is the unique ID for the eForm template.

ResendOfFormDataId

This is the original Form Data ID if this Form was Re-Sent.

ResendMode

If the Form was Re-Sent, this is the will describe how it was delivered (current possibilities are Reprocess [Re-Send] and Forward).

SenderMachineName

This is the Windows Computer Name of the Tablet that sent the Form.

SenderSystemUserFriendlyName

This is the First and Last names of the Field2Base User that sent the Form.

SenderSystemUserId

This is the unique ID of the Field2Base User that sent the Form.

StartTimeUtc

This is the timestamp of when the Form was first started on the Tablet.

SendTimeUtc

This is the timestamp of when the Form was sent from the Tablet.

SenderUsername

This is Windows Username of the Form Sender.

Title

            This is the Title of the Form.

Attributes

None

Returns

Returns a String.

Examples

- Get the Form Data ID.

<header>FormDataId</header>

- Get the Sender’s Username.

<header>SenderUsername</header>

Function Name

pageImageIndex

Description

Gets a Page Image of the Form. The first page is index 0.

Attributes

None

            Returns

            Returns a Byte Array (or Null if the Page Does Not Exist or the Data is Not Available).

            Examples

- Get the Page Image from Page 1.

<pageImageIndex>0</pageImageIndex>

Function Name

toString

Description

Converts an Object into String format.

Attributes

-format

This is an optional attribute (there is no default) format string. If this is not set, or the object is not IFormattable, the result of a direct String Conversion is returned.      

            Returns

Returns a String (or Null if the Object Format conversion returned Null).

            Examples

- Convert the current timestamp into just display Year. <toString format=”yyyy”><now /></toString>

 Function Name

incrementMax       

Description

Gets the Max Value from a Database and increments this value by 1. This function does require that you have the Table and Column defined before it can be used.

Attributes

-seed

This is an optional attribute (defaults to 0) which sets the first value when the referenced Table is empty.

            Examples

            - Increment ColumnA by 1 for this Table.

<insert table=”SomeTable”>

<column name=”ColumnA”><incrementMax /></column>

</insert>

Function Name

eval      

Description

Evaluates an expression using the same langauge as Conditional Expressions.

Attributes

None

Examples

- Returns a Boolean when evaluating if the Hours for this Region are greater than 8.

<eval>{Page1@Hours}>8</eval>

Function Name

execScalar

Description

Executes a SQL Query and returns the Result.

Attributes

-commandText

This is an optional child element that contains the actual command text used in the Query and is only required if there are parameters.

            -parameter

This is an optional child element that contains a parameter that will be used in the SQL Query.

            Returns

            Returns the Data Type returned from ADO.NET’s default for the Query.

            Examples

            - Runs a query to increment the EmployeeId by 1 in the Employees Table. <execScalar>select max(EmployeeId) + 1 from Employees</execScalar>

            - Runs the same query as above but uses the commandText attribute. <execScalar>

                        <commandText>select max(EmployeeId) + 1 from Employees</commandText>

</execScalar>

- Runs a query to select the EmployeeId for all Employees that match the parameter entered which is the EmployeeName entered in the Form in this case.

<execScalar>

                        <commandText>

select EmployeeId from Employees where EmployeeName=?

</commandText>

                        <parameter>{Page1@EmployeeName}</parameter>

            </execScalar>

- Runs the same query as above, but the parameter has been defined with a name as well as a data type.

<execScalar>

                        <commandText>

select EmployeeId from Employees where EmployeeName=@empName

</commandText>

                        <parameter name=”@empName” type=”varchar(50)”>

{Page1@EmployeeName}

</parameter>

            </execScalar>

            - Runs a query to perform a simple mathematical addition of the two parameters defined.

<execScalar>

            <commandText>select ? + ?</commandText>

            <parameter>{Page1@Value1}</parameter>

            <parameter>{Page1@Value2}</parameter>
</execScalar>

back to Top