Working with Table and Spreadsheet


Zapof provides the table in rows and columns to capture 14 different data types and over 500 spreadsheet functions to tackle any requirements.


A table contains cells arranged in rows and columns. Each column can use one of the following types to capture data:
  • Text.
  • Styled text.
  • Numeric.
  • Currency.
  • Date.
  • Time.
  • Date and time.
  • Birthday.
  • Yes/no.
  • Checkbox.
  • File upload.
  • Image upload.
  • Star rating.
  • Smiley rating.
Each row contains cells as many as the number of columns. The header of the table contains the name of the columns.

Let's create an order form starting from an empty table. 

The table contains four columns:
  • description (text and read-only column),
  • select (checkbox for the user to select),
  • quantity (numeric for the user to enter the quantity of the item),
  • unit price (currency, read-only column, and contains pre-defined values),
  • and sub-total (currency, read-only column and contains calculated values)
The table has 4 items to order and the last row of sub-total column shows the total.





Creating an order form from scratch.




Adding a table and its columns and choosing column data type.

  1. Click the ‘Add Question & Element’ button on the left toolbar. It opens the ‘Add’ panel with all types of questions and web elements.
  2. To find the table element, type table into the search input and press Enter
  3. Click the ‘Add’ button on the ‘Table/spreadsheet’ card.
  4. To edit the text of the input field, click the pencil button on the table's toolbar, or click the ‘Enter Text’.
  5. Type 'Please choose your orders:'
  6. Click the pencil button to deactivate field’s text editing.
  7. Click the 'Add another column' button, and choose the data type as 'Text'.
  8. Click the 'Enter Text' at the column's header and change it to 'Description'.
  9. Click the pencil button to deactivate column's header text editing.
  10. Click the 'Make column read only' (lock) button. Only you, the form creator, can edit the text in this description column. The users cannot change the text in this column when they are submitting the form.
    Note: to change it back to an editable column, click the
    lock button again.
  11. While the selection is still on the description column, click 'Add another column' button on the column's toolbar, and choose the data type as 'Checkbox'.
  12. Click the 'Enter Text' at the column's header and change it to 'Select?'.
  13. Click anywhere outside the table to deselect the table.
You should see a table with two columns:

description-and-select-columns.png





Inserting and moving a column.

  1. Click the description column.
  2. Click the 'Add another column' button, and choose the data type as 'Numeric'. On the numeric option pop up, click 'Apply'.
    Note: numeric data type has options to allow decimal number and to format the value according to the regional setting.
  3. Click the 'Enter Text' at the column's header and change it to 'Quantity'.
  4. Click the pencil button to deactivate column's header text editing.
  5. Click the move handle, drag the handle to the new position after the select column.
  6. While the selection is still on the quantity column, click 'Add another column' button on the column's toolbar, and choose the data type as 'Currency'.
  7. Click the 'Enter Text' at the column's header and change it to 'Unit Price'.
  8. Click the 'Make column read only' (lock) button.
  9. Click 'Add another column' button on the column's toolbar and choose the data type as 'Currency'.
  10. Click the 'Enter Text' at the column's header and change it to 'Sub Total'.
  11. Click the 'Make column read only' (lock) button.




Adding rows, entering pre-defined values and changing cell data type.

  1. Click the table icon on the top left cell to select the table.
  2. Click the 'Add another row' button on the table's toolbar 5 times.
  3. Click cell A1, enter 'Jumper' and press down-arrow key to go to cell A2.
  4. In cell A2, enter 'Shirt' and press down-arrow key to go to cell A3.
  5. In cell A3, enter 'Short' and press down-arrow key to go to cell A4.
  6. In cell A4, enter 'Hat'.
  7. The checkbox in cell B5 should not be selectable and preferably show a blank cell. Click cell B5.
  8. Click the 'Change data type' button on the cell's toolbar and choose the data type as 'Text'.
  9. Click the 'Make cell read-only' button on the cell's toolbar.
  10. The cell C5 should not be selectable. Click cell C5 and click the 'Make cell read-only' button on the cell's toolbar.
  11. Click cell D1, enter numeric value 100 and press down-arrow key to go to cell D2.
  12. In cell D2, enter numeric value 40 and press down-arrow key to go to cell D3.
  13. In cell D3, enter numeric value 40 and press down-arrow key to go to cell D4.
  14. In cell D4, enter numeric value 20 and press down-arrow key to go to cell D5.
  15. While the selection is at cell D5, click the 'Change data type' button on the cell's toolbar and choose the data type as 'Text'.
  16. In cell D5, enter 'Total'.
You should see a table with 5 columns and 5 rows with some initial values:

table-with-5-columns-and-rows-and-predefined-values.png





Resizing table and its column, deleting a column and undoing an action.

  1. Click the table icon on the top left cell to select the table.
  2. Click the small circle on the middle of the right edge of the table and drag to resize the table.
  3. Click the description column.
  4. Click the small circle on the column's header and drag to resize the column.
  5. Repeat for the select, quantity, unit price and sub total columns. Make sure that the columns are not too small for the user inputs and calculated values, such as subtotal and total.
  6. To delete a column, click the column such as the quantity column and click the 'Delete this column' button on the column's toolbar.
  7. To undo the deletion, click the 'Undo' button on the editor toolbar at the top right corner.
You should see an almost completed order form:

resizing-table-and-column.png





Adding spreadsheet functions.

The checkbox value is 1 when it's selected and 0 otherwise. To prevent an accidental user input of negative value to quantity, we use ABS function to evaluate it. The formula for the subtotal is the checkbox's value times the quantity times unit price, and the formula for the total is the sum of the values in cell E1 to E4.

Note: cell variable (A1, A2, ...) and function names are case-insensitive.

  1. Click cell E1 and click the 'Edit formula' button on the cell's toolbar and enter B1 * ABS(C1) * D1 and click 'Apply'.
  2. Repeat for the cell E2, E3, and E4 with formula B2 * ABS(C2) * D2, B3 * ABS(C3) * D3, and B4 * ABS(C4) * D4, respectively.
  3. Click cell E5 and click the 'Edit formula' button on the cell's toolbar and enter SUM(E1:E4) and click 'Apply'
You have now completed your order form. Congratulations!

completed-order-form-with-functions.png






Click the 'Preview' button at the top toolbar to try our order form.










 
Created with Zapof