Creating, Maintaining & Updating Databases

Maintaining Databases
When you have prepared the CSV file, select the Admin tab, then Databases:

mceclip0.png
Databases on server
The Databases on server panel lists all the databases that have been uploaded to the portal and are available to be incorporated into form templates. If you select an individual table, it will highlight, and display details such as the number of rows in the table, the login used to create it, and the date created. If you select “follow selection” from the radio buttons beneath the Saved Databases section, the database on user devices Sync Logs will display the devices that contain the selected database.

 

Creating and Updating Databases

Making a CSV data file
To create a Formworks Database, you need to save the Database to a CSV file. The easiest way to do this is probably to create an Excel worksheet, placing your Database data in columns, then save it as a CSV file instead of a workbook, using the File, Save As option, then change the file type to CSV.

Uploading the CSV data file
● From the Databases tab, enter the name of the new database in the Name field on the right of the screen. The name must not contain any spaces, but you can use underscores, for example, staff_list.
● Enter a description.
● Browse to the CSV file and select it.
● Select Save.
o A message indicating that the file has been saved will display.
o The same process is used both to create a new database and to update an existing one. To update an existing database simply use the same name.

mceclip1.png

Note:
You cannot delete a database that is in use by a published template. The template must be retired first. If you delete a database in use by a template in Design state, the database will need to be added via the Template Designer.

In addition to the above method, it is also possible to create an automatic upload procedure that
employs the Formworks API so that you don’t need to manually upload CSV files.

 

Converting CSV files to UTF-8 Format
In some instances, where characters such as £ and $ have been used, a CSV file produced by say, Excel, will need to be specifically converted to UTF-8. To accomplish this use the following steps. Open the CSV file using Excel, select File then Save As. Below the name of the CSV file is a list box containing different file extensions. Select ‘CSV UTF-8 (comma delimited). Then press Save.

mceclip2.png

 

Using Databases on your Templates
Once you have uploaded your Database, use the Template Designer screen to attach it to your template. On the Form Properties section of the designer, select the Databases option to list the available databases.

mceclip3.png   mceclip4.png

 

The Script Template window
The use of databases within your template for the Formworks App is dependent on use of the Lua scripting language (Java scripting is used for Webforms), and a full discussion of this is beyond the scope of this guide. But an example of the Lua script required to populate a list box is provided here.

mceclip5.png

The new Script Template area lists which databases are available for use within your code, and if you select a database, the columns drop down list will inform you of the column names, so you can include them within your queries. For details of how to implement databases within a template, refer to the Formworks XD Scripting Guide.

Need further help? Contact our Support Team