Zoho Creator: Build an online calculator tutorial. #2: Creating the background data
Creating a form and importing data into it
In the first tutorial, I explained that the app should contain two forms: one for background data, and one for user input. In this session, I’ll explain how to build the background data form and how to import data into it.
Creating the background data form
The background data form should provide the calculator with the elements’ symbols and molecular weight. For this, two fields should be created. I found the list of chemical elements on Wikipedia to use for this background data, which has some more information than I really need. As I might want to have some extra functionality later on, I chose to include the atomic number and the element name in the background data as well. I’m going to create the form manually with the form builder, but it can also be done with a data import directly.
Zoho Creator’s form builder allows a quite extensive amount of fields to be used, but for this form only 3 Basic Fields and 1 Advanced Field are needed:
- Two Single Line fields for the Element name and the Element symbol.
- One Number field for the Atomic number
- One Decimal field for the Molecular weight
For the Molecular weight, the maximum decimal points in the Wikipedia list are 4, and the maximum digits before the decimal separator is 3. This brings the Max Digits to 8 (3 numbers, a decimal separator and 4 decimal points). The form builder should look like this now:
Populating the background data
Let’s immediately fill our background data. The easiest in this case is to import the data. I chose to import the data using “Paste data”. As there are no records in this form yet, the “Import data” option will be visible immediately when you open the Report. Otherwise, it’s accessible through the hamburger menu in the top right corner.
I chose to use the “Paste data” option as I don’t need (or want) to have the background data saved in a file on my computer. I simply copied the Wikipedia table into a spreadsheet, removed the columns I didn’t need and copied what was left. Now, simply paste it into the text field that opened up. As this came from a spreadsheet, the values are separated by a tab. Make sure to select this under Delimiter! The result should look similar to this:
Upon clicking Next, the Import Data Report opens up. In this report, the imported data should be corrected before uploading it. Creator will give warnings for this, but watch yourself as well! Upon opening, two of the columns were already mapped correctly to the fields, and one warning showed:
The warning is that the column contains text instead of numbers. One of them is very obvious: because of the formatting of the source file, a row only containing “Z” was created. This row can be simply deleted by right-clicking and selecting “Delete row”. Next, the Name and (Da) columns should be mapped to Element name and Molecular weight, respectively. Now, the same warning as before is shown on the Molecular Weight column: there is text in a decimal field. This is caused by some of the values being enclosed in square brackets, and some having [a] behind the value. To remove this, we simply do a couple of Find & Replace actions. Insert the character in the Find box, leave the Replace with blank (to replace it with blank, i.e. remove it) and make sure to select the correct column in Search in. Doing this once for “[“, “]”and “a” cleans up the column.
After doing this, the warnings didn’t go away. This is because the values are still defined as text. This is no problem, as they are formatted correctly: upon import they will be converted. The result should now look like this:
Now that this data is in the app, let’s create the calculator form and link it to this data in the next episode!