Creating a Data Map for a Data Warehouse

How to build SQL tables through the data warehouse

Use a FormsPro Data Warehouse data map to build SQL tables which are accessible from the SQL database on your server. This makes it easy to create queries and search for a particular piece of data across multiple submissions of a form. You must have access to the database to use this feature.

1. Open the form for which you want to create a data map.
2. Choose (Form Settings), then scroll to the right and click the Data Maps tab.
3. Click (Add button) to open the Data Mapping window.
   
4. Choose FormsPro Data Warehouse as the data map type.
5. Name your data map. The data map is the name for the set of information you’re mapping. Because you can create multiple maps for each form, use a name that will help you identify the information in the map.
   
6. Mark the checkbox next to the fields you want to include in the data warehouse you build. The fields are listed in the order they appear on the form, along with a Submitted By and Submitted On fields if you want to include this information in the mapped data. Static table fields appear as individual fields, but dynamic tables and conditional fields create a separate table because of the nature of the recurring or conditional information. These appear in the database using the table name and can be renamed as well. If you’re mapping many forms, you may want to use the form name or some other identifier when renaming so that the table or conditional field has some context in the database list.
7. You don’t need to add all the fields and, because you can create multiple maps for each form, you can create a map for some fields on your form and a separate one for others. If you know what you’ll be using the data for, it will help you create an appropriate map. For example, if you are required to report on water temperatures, but your form contains oil pressure information, you can create a data map for just the water temperature information needed on the report to simplify the data sorted out.
   
8. You can rename any fields for use in the new database. We recommend shortening long form field names to a few words, such as when you use a long question as a field name but need to report on that data collected.
9. Identify the field length for text fields if you’ll be using the data in an application with specified field lengths. Data is maxed to this length in the new database. For example, if you enter 200, the field size in the database will be 200, the data brought in will be truncated at 200 characters.
10. Click Save. Then click OK to close the Form Settings window.
11. Click (Save) in the form designer to save changes made to the form. After the form is submitted, the database tables are created, and you can use other applications to query, view, or export the form data as you wish.
    
    In this example, the dynamic table populated with the equipment readings is in a separate table:
12. View the Data Maps Log to ensure the data map ran successfully. We recommend that you specify an email to send the Data Map event error log reports to ensure the right personnel is aware of the errors and failures.